Showing posts with label Postgres. Show all posts
Showing posts with label Postgres. Show all posts
Wednesday, March 11, 2026

How to dump the entire schema's DDL into a file

›
pg_dump -d mydb \ --schema-only \ --no-owner \ --no-privileges \ -n myschema \ -f create_schema.sql The flag "--no-owner...

Some examples of how to use the function pg_partition_tree

›
From PostgreSQL 11, the fuction pg_partition_tree has been available Usage, in its simplest form: SELECT * FROM pg_partition_tree('ld...

Find active queries in PostgreSQL

›
SELECT pid, state, now() - query_start AS duration, left(query, 80) AS query FROM pg_stat_activity WHERE state != 'idle' AND usena...
Thursday, March 5, 2026

Identity columns vs free-standing sequences in PostgreSQL

›
Are the columns typically used in primary key columns, defined as IDENTITY columns, or as standalone columns with a default value generated...

How to find the current value for a PostgreSQL sequence

›
SELECT last_value FROM myschema.mysequence; or SELECT nextval('myschema.mysequence'); Note: nextval() advances the sequence — us...
Friday, February 20, 2026

How to find triggers in a PostgreSQL schema

›
Execute the SQL below as a user with ownership to the schema. To verify that you have schema privileges, execute: SELECT has_schema_privil...
Thursday, January 29, 2026

Compression for Postgres tables

›
In PostgreSQL you don’t create a “compressed table” in the Oracle sense. Rather, compression is achieved per-column compression via TOAST, p...
Thursday, November 20, 2025

Find total size of all databases in PostgreSQL cluster

›
SELECT database, size FROM ( SELECT datname AS database, pg_size_pretty(pg_database_size(datname)) AS size, 0 AS sort_o...
Monday, November 17, 2025

Find the number of columns in a table in postgreSQL

›
SELECT count(*) FROM information_schema.columns WHERE table_name = 'mytable' AND table_schema = 'jim';
Monday, November 10, 2025

Generate truncate table statements in PostgreSQL

›
To generate a script in postgreSQL, equivalent to the Oracle-style shown below: select 'truncate table ' || table_name || ' ca...

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 ...
Friday, October 31, 2025

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 ...
Monday, September 29, 2025

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=mys...
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: SELECT conname, condeferrable, condeferred FROM pg_constraint WHERE co...
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...

Check active sessions in PostgreSQL

›
The closest equivalent to this oracle query: SELECT USERNAME, COUNT(*) FROM v$session WHERE type 'BACKGROUND' GROUP BY username...
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 queries 2. execute the script using the -e flag psql -...
Monday, September 8, 2025

Syntax for drop/recreate of constraints in PostgreSQL

›
1. Find the constraints belonging to a specific table: SELECT conname FROM pg_constraint WHERE conrelid = 'mytable'::regclass; ...
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, ...
Thursday, August 28, 2025

Spool out all parameters from a datafile to a file

›
psql -h postgres01.oric.no -U mysuperuser postgres -c "COPY (SELECT name || '=' || setting FROM pg_settings ORDER BY name) TO...