PostgreSQL query that mimics Oracle's DBA_OBJECTS aggregation

with base_objects as (
  select 
    case 
      when c.relkind = 'r' then 'TABLE'
      when c.relkind = 'v' then 'VIEW'
      when c.relkind = 'm' then 'MATERIALIZED VIEW'
      when c.relkind = 'i' then 'INDEX'
      when c.relkind = 'S' then 'SEQUENCE'
      when c.relkind = 'f' then 'FOREIGN TABLE'
      when c.relkind = 'p' then 'PARTITIONED TABLE'
      when c.relkind = 'I' then 'PARTITIONED INDEX'
      else 'OTHER'
    end as object_type
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
  where n.nspname = 'myschema'

  union all

  select 
    case p.prokind
      when 'f' then 'FUNCTION'
      when 'p' then 'PROCEDURE'
      when 'a' then 'AGGREGATE'
      when 'w' then 'WINDOW'
      else 'OTHER'
    end as object_type
  from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
  where n.nspname = 'myschema'

  union all
  select 'TYPE'
  from pg_type t
  join pg_namespace n on n.oid = t.typnamespace
  where n.nspname = 'myschema'
    and t.typtype in ('c', 'e', 'd') -- composite, enum, domain

  union all

  select 'CONSTRAINT'
  from pg_constraint c
  join pg_namespace n on n.oid = c.connamespace
  where n.nspname = 'myschema'
)

select object_type, count(*)
from base_objects
group by object_type
order by object_type;

How to delete all lines above and below the current line in the text editor vi

In vi (or vim), you can delete all lines above the current line with this one simple command:

:1,.−1d

To delete all rows above the current lin, press d (delete) followed by capital G (end of document):

d + G

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)