Syntax for drop/recreate of constraints in PostgreSQL

1. Find the constraints belonging to a specific table:

SELECT conname
FROM pg_constraint
WHERE conrelid = 'mytable'::regclass;
    conname
----------------
 sys_c002733781
 sys_c002733782
(2 rows)

2. Drop them:

ALTER TABLE mytable DROP CONSTRAINT sys_c002733781;
ALTER TABLE
ALTER TABLE mytable DROP CONSTRAINT sys_c002733782;
ALTER TABLE

3. Load your data using your preferred method

4. Recreate the FK constraints but skip validating existing data (only new inserts/updates are checked):

alter table mytable add constraint sys_c002733781 foreign key(job_id) references jobs(id) on delete cascade not valid;
ALTER TABLE

alter table mytable add constraint sys_c002733782 foreign key(task_id) references tasks(id) on delete cascade not valid;
ALTER TABLE

5. Validate constraints:

alter table mytable validate constraint sys_c002733781;
ALTER TABLE
alter table mytable validate constraint sys_c002733782;
ALTER TABLE

Query equivalent to SELECT * FROM USER_OBJECTS in PostgreSQL

SELECT schema_name, object_name, object_type
FROM (
    -- Tables, Views, Indexes, etc.
    SELECT
        n.nspname AS schema_name,
        c.relname AS object_name,
        CASE c.relkind
            WHEN 'r' THEN 'TABLE'
            WHEN 'v' THEN 'VIEW'
            WHEN 'm' THEN 'MATERIALIZED VIEW'
            WHEN 'i' THEN 'INDEX'
            WHEN 'S' THEN 'SEQUENCE'
            WHEN 'f' THEN 'FOREIGN TABLE'
            WHEN 'p' THEN 'PARTITIONED TABLE'
            WHEN 'I' THEN 'PARTITIONED INDEX'
            ELSE c.relkind::text
        END AS object_type
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = current_schema()

    UNION ALL

    -- Functions
    SELECT
        n.nspname AS schema_name,
        p.proname AS object_name,
        'FUNCTION' AS object_type
    FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = current_schema()
) AS objects
ORDER BY object_type, object_name;

It only shows objects in the current schema (like Oracle USER_OBJECTS).

If you want all objects the user owns (across schemas), replace

WHERE n.nspname = current_schema()

with

WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')

Spool out all parameters from a datafile to a file

psql -h postgres01.oric.no -U mysuperuser postgres -c "COPY (SELECT name || '='  || setting FROM pg_settings ORDER BY name) TO STDOUT WITH CSV HEADER"   > pg_config.csv

List a table, its partitions and the number of rows it contains

SELECT
    s.schemaname,
    s.relname AS table_name,
    s.n_live_tup,
    s.last_analyze,
    s.last_autoanalyze
FROM
    pg_stat_all_tables s
JOIN
    pg_class c ON c.relname = s.relname
LEFT JOIN
    pg_inherits i ON i.inhrelid = c.oid OR i.inhparent = c.oid
WHERE
    s.schemaname = 'myschema'
    AND (
        s.relname = 'mytable'
        OR c.oid IN (
            SELECT inhrelid
            FROM pg_inherits
            WHERE inhparent = (
                SELECT oid FROM pg_class WHERE relname = 'mytable'
            )
        )
    )
ORDER BY
 s.n_live_tup DESC,s.last_analyze;

Example output

 schemaname      |  table_name     | n_live_tup | last_analyze   		| last_autoanalyze 
-----------------+-----------------+------------+-------------------------------+------------------------------
 myschema 	 | mytable_2015_02 |   95788115 | 2025-08-06 12:58:22.952592+00 | 
 myschema 	 | mytable_2015_03 |   78505350 | 2025-08-06 12:58:37.147433+00 | 
 myschema 	 | mytable_2015_04 |   71211253 | 2025-08-06 12:58:51.311452+00 | 
 myschema 	 | mytable_2015_01 |   68255510 | 2025-08-06 12:58:08.42708+00  | 
 myschema 	 | mytable_2015_05 |   62075043 | 2025-08-06 12:59:05.434118+00 | 
 myschema 	 | mytable_2015_06 |   60888876 | 2025-08-06 12:59:19.918657+00 | 2025-08-06 11:31:19.551345+00
 myschema 	 | mytable_2015_12 |   57485594 | 2025-08-06 13:00:43.112316+00 | 2025-08-27 03:13:11.394606+00
 myschema 	 | mytable_2016_01 |   57405307 | 2025-08-06 13:00:55.395113+00 | 2025-08-25 13:35:07.749773+00

How to check if a PostgreSQL streaming replicate server is up-to-date with the primary

On the primary, run

SELECT pg_current_wal_lsn(); -- On the primary

In my case, the result was

 pg_current_wal_lsn
--------------------
 0/E015420

On the standby, run

SELECT pg_last_wal_replay_lsn();

In my case, the result was

pg_last_wal_replay_lsn
------------------------
 0/E015420

This means that

All WAL records have been received

All WAL recores have been replayed (not just flushed) on the standby

There is no lag between primary and standby

Basic postgres connect syntax

psql -h hostname -U username -d database

Options:

Option	Description
-h <host>	Hostname or IP address of the PostgreSQL server
-p <port>	Port number (default is 5432)
-U <username>	Username to connect as
-d <dbname>	Database name
-W Prompt for password (useful if password auth is required)
-f <filename>	Execute commands from a SQL script file
-c "<command>"	Execute a single SQL command and exit
-v var=value	Set psql variable (used inside scripts)
--set=var=value	Same as -v, more explicit
--dbname=<connstr>	Full connection string, e.g.: postgresql://user:pass@host:port/dbname

Example

psql -h pg01.oric.no -p 5432 -U myuser -d mydb

Alternatively, use an URL, which is more scripting friendly:

psql "postgresql://replicate@pg01.oric.no:5432/mydb"

How to find the columns in a specific postgres data dictionary table

For example, to check which columns belongs to the view "pg_stat_wal_receiver"

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'pg_stat_wal_receiver';

Output

     column_name
-----------------------
 pid
 last_msg_receipt_time
 latest_end_lsn
 latest_end_time
 sender_port
 receive_start_lsn
 receive_start_tli
 written_lsn
 flushed_lsn
 received_tli
 last_msg_send_time
 status
 slot_name
 sender_host
 conninfo
(15 rows)

Or use the psql metacommand "\d+":

echo '\d+ pg_stat_wal_receiver' |psql

Output

                                  View "pg_catalog.pg_stat_wal_receiver"
        Column         |           Type           | Collation | Nullable | Default | Storage  | Description
-----------------------+--------------------------+-----------+----------+---------+----------+-------------
 pid                   | integer                  |           |          |         | plain    |
 status                | text                     |           |          |         | extended |
 receive_start_lsn     | pg_lsn                   |           |          |         | plain    |
 receive_start_tli     | integer                  |           |          |         | plain    |
 written_lsn           | pg_lsn                   |           |          |         | plain    |
 flushed_lsn           | pg_lsn                   |           |          |         | plain    |
 received_tli          | integer                  |           |          |         | plain    |
 last_msg_send_time    | timestamp with time zone |           |          |         | plain    |
 last_msg_receipt_time | timestamp with time zone |           |          |         | plain    |
 latest_end_lsn        | pg_lsn                   |           |          |         | plain    |
 latest_end_time       | timestamp with time zone |           |          |         | plain    |
 slot_name             | text                     |           |          |         | extended |
 sender_host           | text                     |           |          |         | extended |
 sender_port           | integer                  |           |          |         | plain    |
 conninfo              | text                     |           |          |         | extended |
View definition:
 SELECT pid,
    status,
    receive_start_lsn,
    receive_start_tli,
    written_lsn,
    flushed_lsn,
    received_tli,
    last_msg_send_time,
    last_msg_receipt_time,
    latest_end_lsn,
    latest_end_time,
    slot_name,
    sender_host,
    sender_port,
    conninfo
   FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, written_lsn, flushed_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name, sender_host, sender_port, conninfo)
  WHERE pid IS NOT NULL;