PostgreSQL – All about (1)

What is difference between Postgres and PgAdmin

• Postgres.app is a full-featured PostgreSQL installation packaged as a standard Mac app. It sets up a PostgreSQL database server on your computer when you install it.
• PgAdmin is graphical user interface administration tool for PostgreSQL. It is a client tool for working with existing local or remote PostgreSQL servers. It does not include a PostgreSQL database server.

How to search specific value in all tables?

Dump DB to file

$ pg_dump --data-only --column-inserts -U postgres your-db-name > a.tmp

Search text in file

$ grep country_code a.tmp
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('US', 'United  States');
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('GB', 'United Kingdom');

How to create table with foreign key?

CREATE TABLE users
(
    "userId" text NOT NULL,
    "fullName" text,
    CONSTRAINT users_pkey PRIMARY KEY ("userId")
);

CREATE TABLE devices
(
    "deviceId" text NOT NULL,
    "userId" text NOT NULL,
    "fcmToken" text,
    "active" boolean NOT NULL DEFAULT true,
    "createdAt" timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT devices_pkey PRIMARY KEY ("deviceId"),
    CONSTRAINT fk_user
      FOREIGN KEY("userId") 
	  REFERENCES users("userId")
);

CREATE TABLE notifications
(
    id SERIAL PRIMARY KEY,
    userId text NOT NULL,
    message text,
    readAt timestamp,
    sentAt timestamp,
	createdAt timestamp NOT NULL DEFAULT now(),
    FOREIGN KEY (userId) REFERENCES users("userId")
);

How to connect PostgreSQL with Node?

const express = require("express");
const Sequelize = require("sequelize");

// Connect to DB server
// const sequelize = new Sequelize("postgresql://[host name]/[db name]");
const sequelize = new Sequelize("postgresql://localhost/hung-nb");
sequelize
  .authenticate()
  .then(() => {
    console.log("Connection has been established successfully.");
  })
  .catch((err) => {
    console.error("Unable to connect to the database:", err);
  });

How to insert a row?

INSERT INTO public.users(
	"userId", "firstName", "lastName")
	VALUES ('h.nguyen', 'Hung', 'Nguyen');

How to join table?

inner join

Return common records from 2 tables

SELECT usr."userId", "fullName", "fcmToken", "active"
	FROM users usr LEFT JOIN devices dvc ON usr."userId" = dvc."userId"

left join

• Return all records from LEFT table
• Return matching records from RIGHT table

SELECT usr."userId", "fullName", "fcmToken", "active"
	FROM users usr LEFT JOIN devices dvc ON usr."userId" = dvc."userId"

How to select distinct?

Select distinct on 1 column

SELECT DISTINCT ON ("userId") "userId", "fcmToken" FROM devices

Select distinct on multiple columns

SELECT DISTINCT "userId", "fullName" FROM users

Errors

Cannot insert multiple commands into a prepared statement

This error occurs when you try to execute multiple queries

-- Put all queries into this block
with i as (
    insert into table1 (
        name,
        email,
        phone,
        created_at,
        updated_at
    ) values (
        $1, 
        $2, 
        $3,
        current_timestamp, 
        current_timestamp
), u as (
    update table2 
    set column_1 = $1
    where id = $4
)

-- Put last query here
update contacts 
    set column_2 = $2
    where id = $4;