-- timestamp literal
TIMESTAMP '2025-06-29 08:00:00'
TIMESTAMPTZ '2025-06-29 08:00:00 America/New_York'

-- convert integer to TIMESTAMPTZ
to_timestamp(1619037717)

-- show timestamp in time zone
select time_created at time zone 'UTC';

DELETE FROM ONLY mytable
WHERE ctid IN (
    SELECT ctid
    FROM mytable
    WHERE ...
    ORDER BY ...
    LIMIT 1
);

-- auto-incrementing primary key
id SERIAL PRIMARY KEY

-- fixed-point decimal (9 total digits, 2 after decimal point)
amount DECIMAL(9, 2)

-- string belongs to set
category TEXT NOT NULL CHECK (category IN ('A', 'B'))

-- foreign key column
parent_id INTEGER NOT NULL REFERENCES parent(id) ON DELETE CASCADE
-- or ON DELETE SET NULL

CREATE TABLE objects(
  ...,

  UNIQUE(column_a, column_b)
)

\copy my_table(col1, col2) from 'myfile.csv' with (format csv, header true);

-- list databases
\l

-- connect to database
\c NAME

-- list tables
\d

-- show schema
\d+ TABLE

-- drop all tables in database
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO <role>

CREATE ROLE <name> LOGIN;
-- allow role to create databases
ALTER ROLE <name> CREATEDB;
-- allow role to create other roles
ALTER ROLE <name> CREATEROLE;
-- create a database with an owner
CREATE DATABASE <dbname> OWNER <role>;
-- give full access to a table to a role
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE <table_name> TO <role>;
-- or: GRANT ALL PRIVILEGES
-- give access to sequence (e.g., primary key)
GRANT USAGE, SELECT ON SEQUENCE <table>_<column>_seq TO <role>;
-- revoke privileges
REVOKE ALL PRIVILEGES ON TABLE <table_name> FROM <role>;

-- grant all privileges on all existing tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <role>;
-- grant all privileges on all future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO <role>;

-- list roles
SELECT rolname FROM pg_roles;

-- create role if not exists
DO $$
BEGIN
IF NOT EXISTS (
    SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = '...'
) THEN CREATE ROLE <name> LOGIN;
END IF;
END
$$;

-- rename role
ALTER ROLE <old_name> RENAME TO <new_name>;

-- drop role
REASSIGN OWNED BY <old_role> TO <new_role>;
DROP OWNED BY <old_role>;
DROP ROLE <old_role>;

CREATE INDEX ON <table>(<column>);
-- index on expression
CREATE INDEX ON <table>((lower(path)));
-- unique index, with explicit name
CREATE UNIQUE INDEX path_idx ON <table>(path);

CREATE VIEW vw_whatever AS
  SELECT *
  FROM t;

CREATE OR REPLACE PROCEDURE tasks_mark_open(this_task_id TEXT)
LANGUAGE SQL
BEGIN ATOMIC
  UPDATE tasks
  SET status = 'open', time_closed = NULL, blocked_on = NULL, backlog_until = NULL
  WHERE task_id = this_task_id;
END;