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
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Thursday, September 25, 2025
Find out if a constraint in PostgreSQL is defined as deferrable
Logged into the relevant database, find out which FK are deferrable:
If condeferrable = t (true), the constraint is created initially deferrable
If condeferrable = f (false), like in my case, it is not.
Wednesday, September 24, 2025
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)
Thursday, September 18, 2025
Convert all contents in a file to lower case using vim
Open your file in vi
vi myfilePress the escape key to enter interactive mode, then type
:%s/.*/\L&/Press escape, then save + exit the editor:
:wq
Tuesday, September 9, 2025
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 queries2. execute the script using the -e flag
psql -e -f myfile.sql
Monday, September 8, 2025
Syntax for drop/recreate of constraints in PostgreSQL
1. Find the constraints belonging to a specific table:
4. Recreate the FK constraints but skip validating existing data (only new inserts/updates are checked):
SELECT conname FROM pg_constraint WHERE conrelid = 'mytable'::regclass; conname ---------------- sys_c002733781 sys_c002733782 (2 rows)2. Drop them:
ALTER TABLE mytable DROP CONSTRAINT sys_c002733781; ALTER TABLE ALTER TABLE mytable DROP CONSTRAINT sys_c002733782; ALTER TABLE3. Load your data using your preferred method
4. Recreate the FK constraints but skip validating existing data (only new inserts/updates are checked):
alter table mytable add constraint sys_c002733781 foreign key(job_id) references jobs(id) on delete cascade not valid; ALTER TABLE alter table mytable add constraint sys_c002733782 foreign key(task_id) references tasks(id) on delete cascade not valid; ALTER TABLE5. Validate constraints:
alter table mytable validate constraint sys_c002733781; ALTER TABLE alter table mytable validate constraint sys_c002733782; ALTER TABLE
Friday, September 5, 2025
Query equivalent to SELECT * FROM USER_OBJECTS in PostgreSQL
SELECT schema_name, object_name, object_type FROM ( -- Tables, Views, Indexes, etc. SELECT n.nspname AS schema_name, c.relname AS object_name, CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'v' THEN 'VIEW' WHEN 'm' THEN 'MATERIALIZED VIEW' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'p' THEN 'PARTITIONED TABLE' WHEN 'I' THEN 'PARTITIONED INDEX' ELSE c.relkind::text END AS object_type FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = current_schema() UNION ALL -- Functions SELECT n.nspname AS schema_name, p.proname AS object_name, 'FUNCTION' AS object_type FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = current_schema() ) AS objects ORDER BY object_type, object_name;It only shows objects in the current schema (like Oracle USER_OBJECTS).
If you want all objects the user owns (across schemas), replace
WHERE n.nspname = current_schema()with
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
Subscribe to:
Posts (Atom)