How to pass parameters to a psql script

To use variables in your sql script, use the -v option to set variables on the command line.

Consider this example:

export PGPASSWORD=mysecretpassword
psql -h myserver.oric.no -d db01 -U scott -v schema=scott -v table=mytable -f myquery.sql

The query in myquery.sql can then referance the "schema" and "table" variables, like this:

SELECT
    att.attname AS column_name
FROM
    pg_attribute att
JOIN
    pg_class cls ON cls.oid = att.attrelid
JOIN
    pg_namespace ns ON ns.oid = cls.relnamespace
WHERE
    ns.nspname = :'schema'
    AND cls.relname = :'table'
    AND att.attnotnull
    AND att.attnum > 0
    AND NOT att.attisdropped;

Find out if a constraint in PostgreSQL is defined as deferrable

Logged into the relevant database, find out which FK are deferrable:

SELECT conname, condeferrable, condeferred
FROM pg_constraint
WHERE conrelid in ( 'mytable1'::regclass,'mytable2'::regclass) 
AND contype='f';
           conname            | condeferrable | condeferred
------------------------------+---------------+-------------
 mytable1_id_fkey             | f             | f
 mytable1_id_fkey             | f             | f
 mytable2_id_fkey             | f             | f

If condeferrable = t (true), the constraint is created initially deferrable

If condeferrable = f (false), like in my case, it is not.

Terminate PostgresSQL sessions

-- Terminate all connections from user "sales"
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'sales';

In my case the result was:

 pg_terminate_backend
----------------------
 t
 t
(2 rows)

Check active sessions in PostgreSQL

The closest equivalent to this oracle query:

SELECT USERNAME, COUNT(*) 
FROM v$session 
WHERE type <> 'BACKGROUND' 
GROUP BY username;

Would be

SELECT datname, usename AS username, COUNT(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename
ORDER BY datname, usename;

Example output:

 datname  |     username      | count
----------+-------------------+-------
 postgres | postgres          |     1
 postgres | postgres_exporter |     1
 mydb     | myser             |     2
(3 rows)

Convert all contents in a file to lower case using vim

Open your file in vi

vi myfile

Press the escape key to enter interactive mode, then type

:%s/.*/\L&/

Press escape, then save + exit the editor:

:wq

How to set echo in a SQL script executed in psql

Use one of two options 1. in the sql file, add the following directive

\set ECHO queries

2. execute the script using the -e flag

psql -e -f myfile.sql

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