List all files containg a string in a subdirectory structure

grep -Rl 'aberdeen' /var/lib/pgsql/cities/aberdeen/schema

Result:

/var/lib/pgsql/cities/aberdeen/schema/grants/grant.sql
/var/lib/pgsql/cities/aberdeen/schema/sequences/sequence.sql
/var/lib/pgsql/cities/aberdeen/schema/sequence_values/sequence_value.sql
/var/lib/pgsql/cities/aberdeen/schema/tables/table.sql
/var/lib/pgsql/cities/aberdeen/schema/tables/AUTOINCREMENT_table.sql
/var/lib/pgsql/cities/aberdeen/schema/tablespaces/tablespace.sql
/var/lib/pgsql/cities/aberdeen/schema/triggers/trigger.sql
/var/lib/pgsql/cities/aberdeen/schema/views/view.sql

Generate truncate table statements in PostgreSQL

To generate a script in postgreSQL, equivalent to the Oracle-style shown below:

select 'truncate table ' || table_name || ' cascade;' from dba_tables where owner='MYSCHEMA';

, put this in a file called gen_truncate.sql

/*
| Setting                  | Effect                                                    |
| ------------------------ | --------------------------------------------------------- |
| `\o /path/to/file`       | Redirects all query output to the file                    |
| `\pset format unaligned` | Produces plain text output (no table formatting)          |
| `\pset tuples_only on`   | Suppresses headers and row counts                         |
| `\pset footer off`       | Removes `x rows` footer                                   |
| `\pset border 0`         | Removes any border formatting (mostly for aligned format) |
*/

\o truncate_tables.sql
\pset format unaligned
\pset tuples_only on
\pset footer off
\pset border 0
SELECT 'TRUNCATE TABLE ' || schemaname || '.' || tablename || ' CASCADE;' AS stmt
FROM pg_tables
WHERE schemaname = 'myschema';
\o

Execute it:

psql mydb -f gen_truncate.sql

or login to the desired database directly as the owner of the table:

psql -h localhost -d mydb -U myuser -f gen_truncate.sql

Do I need special privileges to create temporary tables in a database in PostgreSQL?

To verify if your user has the privilege to create temporary segments in a postgreSQL database, use this check:

psql -h localhost -d mydb -U myuser

 SELECT has_database_privilege(current_user, current_database(), 'TEMP');

If the value returned shows:

has_database_privilege
------------------------
 f

To be able to create a temporary table in this database, my user needs privileges to do so:

psql
postgres=# grant temp on database mydb to myser;
GRANT

The same test will now yield true instead of false:

SELECT has_database_privilege(current_user, current_database(), 'TEMP');
 has_database_privilege
------------------------
 t
(1 row)

I can now create a temporary table:

CREATE TEMP TABLE table_counts (table_name text, row_count bigint);

Before the grant, the access privileges for my database was

mydb=> \l+ mydb

  |      Access privileges       |
  +------------------------------+
  | postgres=CTc/postgres       +|
  | myuser=c/postgres           +|
 

After the grant:

  |      Access privileges       |
  +------------------------------+
  | postgres=CTc/postgres       +|
  | myuser=Tc/postgres          +|

Notice how the privileges for "myuser" has a "T" ammended to it. This indicate the permission to create temporary objects.

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;