💽Postgres

10 Nov 2023

Install

brew install postgresql@15
fish_add_path /opt/homebrew/opt/postgresql@15/bin

Tables

Create table

CREATE TABLE public.tags (
    id serial PRIMARY KEY,
    name text UNIQUE NOT NULL
);

Drop table

DROP TABLE public.tags;

Rename table

ALTER TABLE public.tags RENAME TO public.bookmarks_tags;

Add column

ALTER TABLE public.bookmarks_tags ADD COLUMN tag_id integer REFERENCES public.tags(id) ON DELETE CASCADE;

Junction table

Given a many-to-many relationship between two tables, create a junction table to represent the relationship. This query creates a junction table for a many-to-many relationship between the bookmarks and tags tables.

CREATE TABLE public.bookmark_tag (
  bookmark_id uuid REFERENCES public.bookmarks(id) ON DELETE CASCADE,
  tag_id integer REFERENCES public.tags(id) ON DELETE CASCADE,
  PRIMARY KEY (bookmark_id, tag_id)
);

Create enum

CREATE TYPE status AS ENUM ('active', 'inactive', 'deleted', 'archived', 'draft');

Rename a value in an array

UPDATE bookmarks
SET tags = array_replace(tags, 'open', 'public')

Update a value in all rows

UPDATE table_name
SET column_name = 'column value'

Constraints

List constraints on a table

SELECT *
FROM information_schema.constraint_table_usage
WHERE table_name = 'table_name'

Drop constraint on a table

ALTER TABLE table_name DROP CONSTRAINT table_name_field_fkey;

Backup and restore

Install the libpq CLI tool:

brew install libpq

Add it to your path:

fish_add_path /opt/homebrew/opt/libpq/bin

(I use Fish shell so the above command is for Fish. For Bash, use export PATH="/opt/homebrew/opt/libpq/bin:$PATH")

Database backup & restore

Using pg_dump

Backup DB and content

pg_dump -U postgres -W -h db.******.supabase.co -p 5432 -n public -F t -f db-backup.tar postgres

Only backup DB schemas

Notice the -s flag

pg_dump -U postgres -W -h db.******.supabase.co -p 5432 -s -n public -F t -f db-backup.tar postgres

Restore DB

Using pg_restore

pg_restore -U postgres -W -h db..******..supabase.co -p 5432 -F t -C -d postgres db-backup.tar