LIST OUT ALL (FORIEN KEY,PRIMARY KEY, UNIQUE KEY) CONSTRAINTS WITH REFERENCED TABLE & FIELD :

Here is the simple utility query for list out all the constraint( CHECK, UNIQUE, PRIMARY & FOREIGN KEY) their name, table name,column name from where constraint created and references table name with column name.

from this query you can also get hierarchical list of all tables

SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,

rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc

LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name

LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name

LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name

WHERE lower(tc.constraint_type) in ('foreign key')

here you can comment WHERE condition to list out all constraint, else it ll list only FOREIGN KEY constraint.

By running above utility you can get foreign key hierarchy in postgresql.

================================================

Below query will return the referencing table and field information for the parent table test_master.

Get all child table for the given parent table

SELECT *
FROM information_schema.table_constraints tc
right JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_catalog = ccu.constraint_catalog
AND tc.constraint_schema = ccu.constraint_schema
AND tc.constraint_name = ccu.constraint_name
and ccu.table_name in ('test_master')
WHERE lower(tc.constraint_type) in ('foreign key');

To find all child tables that belong to a parent table you can use above query, change the table (test_master) .

Passwordless postgresql login

We can log in postgres without promoting/enter password through psql.

syntax:

sudo -u user_name psql db_name

i.e
sudo -u postgres psql postgres

postgres -- database name, if you omit this by default it will log in with postgres database only

may forget the password simply enter into psql mode simply by above command and alter user with new password by

alter user user_name with password 'new_password'


note:

use sudo for log in if you face any issue by log in throuogh

su postgres
psql