How to check if your EBS system is on AD/TXK Delta 13

SELECT bug_number, creation_date
FROM   ad_bugs
WHERE  bug_number IN (
  -- AD/TXK Delta 13 driver patch numbers
  '35163625','35163283','35163924','35162879'
)
ORDER BY creation_date DESC;

If no rows are returned, you are not on AD/TXK Delta 13, yet

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