On this page

Install Jump to heading

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

    

Tables Jump to heading

Create table Jump to heading

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

    

Drop table Jump to heading

DROP TABLE public.tags;

    

Rename table Jump to heading

ALTER TABLE public.tags RENAME TO public.bookmarks_tags;

    

Add column Jump to heading

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

    

Junction table Jump to heading

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 Jump to heading

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

    

Rename a value in an array Jump to heading

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

    

Update a value in all rows Jump to heading

UPDATE table_name
SET column_name = 'column value'

    

Constraints Jump to heading

List constraints on a table Jump to heading

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

    

Drop constraint on a table Jump to heading

ALTER TABLE table_name DROP CONSTRAINT table_name_field_fkey;

    

Backup and restore Jump to heading

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 Jump to heading

Using pg_dump

Backup DB and content Jump to heading

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

    

Only backup DB schemas Jump to heading

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 Jump to heading

Using pg_restore

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

    

Meta Jump to heading

Get all RLS policies Jump to heading

select * from pg_policies

    

All databases and their sizes Jump to heading

select * from pg_user;

    

---

← Back home