How to find tables with specific column data types in a PostgreSQL

Thanks to Bart Gawrych for blogging this very useful post on which my own post is based on.

I needed to find all tables in a specific schema that used json or jsonb data type columns. Here is my query, saved in a file called "find_cols.sql":

select col.table_schema,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'
where col.table_schema in ('myschema')
      and col.data_type IN ( 'json','jsonb' )
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Execution in psql directly on server like this:

psql mydb -f find_cols.sql

How to add a string to specific line in a text file

Here is an example of how I inserted a line needed in a postgres pg_restore command, at line 20:

sed -i '20s/^/SET search_path to sales,public;\n/' myfile.txt

If your string contains special characters, remember to escape it properly. In the next example, I want to append the string \timing at line number 7 in myfile.txt:

 sed -i '7s/^/\\timing on\n/' myfile.txt

How to replace a specific string in a very large text file

Here is how I removed the string "public." from a 4G file:

sed -i 's/public\.//g' mylargetextfile.txt

Notice the forward slash in front of the puncuation mark. It tells sed to interpret it literally, not as a special character used in regular expressions. Source: Stackoverflow

An Oracle procedure that allow users to kill their own sessions

First, create a view that lets the users check their own sessions:

connect / as sysdba

create or replace view my_sessions as
select username,sid,serial#,osuser,status,program,machine
from v$session
where username=SYS_CONTEXT('userenv','CURRENT_SCHEMA');

Create a public synonym, and grant select to public:

create public synonym my_sessions for my_sessions;
grant select on my_sessions to public;

To get the procedure to compile in the SYSTEM schema, it is required to grant the "alter system" privilege directly to the user - having the same privilege through a role won't do.

grant alter system to system;
grant select on v_$session to system;

Log in as system, and create the procedure:

CREATE OR REPLACE PROCEDURE KILL_SESSION(PN_SID NUMBER,PN_SERIAL NUMBER) AUTHID DEFINER
AS
LV_USER VARCHAR2(30);
EXE_USER VARCHAR2(30);
BEGIN
    SELECT S.USERNAME INTO LV_USER 
    FROM V$SESSION S, USER_USERS U 
    WHERE S.SID    = PN_SID 
    AND S.SERIAL#  = PN_SERIAL
    AND S.USERNAME = S.USERNAME;

    SELECT USERNAME INTO EXE_USER
    FROM V$SESSION 
    WHERE AUDSID=SYS_CONTEXT('userenv','SESSIONID');

 IF EXE_USER = LV_USER THEN
    EXECUTE IMMEDIATE 'alter system kill session '''||PN_SID||','||PN_SERIAL||'''';
    dbms_output.put_line('Session ' || PN_SID || ',' || PN_SERIAL || ' killed.');
ELSE
    RAISE_APPLICATION_ERROR(-20000,'Only your own sessions may be killed.');
END IF;
END;
/  

Create a synonym for the procedure and give everyone execute privileges on it:

CREATE PUBLIC SYNONYM KILL_SESSION FOR KILL_SESSION;
GRANT EXECUTE ON KILL_SESSION TO PUBLIC;

Now the users have a way to check their own sessions, and to terminate them if they chose to do so:

connect scott
select * from my_session;

USERNAME                    SID    SERIAL# OSUSER               STATUS               PROGRAM                                          MACHINE
-------------------- ---------- ---------- -------------------- -------------------- ------------------------------------------------ ----------------------------------------------------------------
SCOTT                        1152       9510 oracle               INACTIVE             sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com
SCOTT                        1726      31531 oracle               ACTIVE               sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com

Kill a session:

scott@mydb>SQL>exec kill_session(1152,9510);

Afterwards, the view will change to show session 1152 to be in status KILLED:

USERNAME                    SID    SERIAL# OSUSER               STATUS               PROGRAM                                          MACHINE
-------------------- ---------- ---------- -------------------- -------------------- ------------------------------------------------ ----------------------------------------------------------------
SCOTT                        1152       9510 oracle               KILLED             sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com
SCOTT                        1726      31531 oracle               ACTIVE               sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com

How to exctract a specific file from a .tar file

Say I would like to extract an old version of the oracle inventory, packed in a tar file called "oracle.tar". Change directory to the physical location you want to extract the files to:

cd /home/oracle

Find the path to the file you are interested in:

tar -tvf oracle.tar | grep oraInst.loc
-rw-rw---- oracle/dba        55 2018-01-25 15:02 ./oraInventory/oraInst.loc

Extract the tar file using the path displayed above:

tar xvf /u01/data/oracle.tar ./oraInventory/oraInst.loc

How to only insert rows that do not violate a specific constraint

PostgreSQL offers an alternative to raising an exception upon a unique key violation: ON CONFLICT ON CONSTRAINT

INSERT INTO sales.sales_history 
SELECT * from sales.sales_history_p201801 
ON CONFLICT ON CONSTRAINT uc_sh1 DO NOTHING;

Comes in as a handy way of transfering rows from an old table to a partially populated new table. Duplicate rows will simply be ignored.

Documentation for PostgreSQL 11 can be found here.

How to export a single table in PostgreSQL

Example of exporting a single table. In the directory-format:

pg_dump -Fd mydb -t myschema.mytable -v -f  /data/mydirectory -j 2

Where

-Fd = export into a directory-format archive
-t = table name
-v = verbose
-f = the target directory
-j = number of export jobs

Note that -f can be omitted for file based output formats.
Since I am using the directory output format however, I am required to use it, and it specifies the target directory instead of a file. In this case the directory is created by pg_dump and it must not exist before.

In plain text file format:

pg_dump mydb -n 'myschema' -a -Fp  -t mytable | gzip > mydb_myschema_mytable.gz

Where

-Fp = plain text format
-a  = data only, no metadata. In the case of plain text, this means no "create table" statement is generated
-t  = table name

When the export is done, you may unpack the mydb_myschema_mytable.gz and you will observe that the file contains COPY directives for each row.

In custom format:

pg_dump mydb -n 'myschema' -a -Fc  -t mytable | gzip > mydb_myschema_mytable_custom.gz

Where all the options are identical to the plain text format, except for

-Fc = custom data format