TO KNOW ABOUT THE POSTGRESQL USERS

SELECT * FROM pg_catalog.pg_user ORDER BY usesysid DESC;

SELECT * FROM pg_authid ORDER BY 1

DATE SERIES - POSTGRESQL

 SELECT dates::date FROM generate_series(CURRENT_DATE,   CURRENT_DATE-4, '-1 day'::interval)dates

DROP USER IN POSTGRSQL

REASSIGN OWNED BY aaa TO bbb;  

DROP OWNED BY aaa;

DROP USER aaa;

POSTGRSQL - DROP TRIGGERS

SELECT 

'DROP TRIGGER abc.' || trigger_name || ' ON ' || event_object_table || ';'

FROM information_schema.triggers

WHERE trigger_schema = 'abc';

SQL - FIND DUPLICATE RECORDS

SELECT offset, COUNT(offset)  

FROM log 

GROUP BY offset  

HAVING COUNT(offset) > 1;  

CALCULATE TIME DIFFERENCE BETWEEN TWO CONTINUOUS ROWS

SELECT A.id, A.ts, TIMEDIFF (B.ts,A.ts) AS timedifference

FROM log A INNER JOIN log B ON B.id = (A.id + 1)

ORDER BY TIMEDIFF (B.ts,A.ts) DESC

SELECT A.id, A.ts, CONVERT(TIMEDIFF (B.ts,A.ts),CHAR) AS timedifference,a.offset,a.input_offset

FROM log A INNER JOIN log B ON B.id = (A.id + 1)

ORDER BY TIMEDIFF (B.ts,A.ts) DESC 

CALCULATE DIFFERENCE BETWEEN TWO TIMESTAMP IN POSTGRESQL

EXTRACT(DAY FROM MAX(joindate)-MIN(joindate)) AS DateDifference

EXTRACT(DAY FROM created-updated)

EXTRACT(HOUR FROM (updated - created)) AS HourDifference

CENTURY: It is the number of centuries

DAY: It is the day of the month (1-31) or the number of days

DECADE: It is the decade that is the year divided by 10.

DOW: It is the day of the week Sunday (0) to Saturday (6).

DOY: it is the day of the year that ranges from 1 to 366.

EPOCH: It is the total number of seconds in the interval

HOUR: It is the number of hours

MILLENNIUM: It is the number of millennium

MILLISECONDS: It is the second’s field, including fractional parts,

MINUTE: It is the minute (0-59) or a number of minutes.

MONTH: The number of months (1-12).

SECOND: It is the number of seconds.

WEEK: It is the number of weeks

YEAR: It is the year.

Postgresql - To find the time differences between two columns in a table

SELECT 

A.id, A.ts, TIMEDIFF (B.ts,A.ts) AS timedifference

FROM log A 

INNER JOIN log B ON B.id = (A.id + 1)

ORDER BY TIMEDIFF (B.ts,A.ts) DESC

SELECTING WHERE TWO COLUMNS ARE IN A SET

 -- works in PostgreSQL, Oracle, MySQL, DB2, HSQLDB 

SELECT whatever 

FROM t                              

WHERE 

(col1, col2) IN ((val1a, val2a), (val1b, val2b), ...) ;

List views in postgresql

SELECT 

table_name 

FROM INFORMATION_SCHEMA.views 

WHERE 

table_schema = ANY (current_schemas(false)) 

ORDER BY 1

Postgresql - Get the last 'X' months

To get the last 'x' months from the current date,

Method 1:

SELECT date_trunc('month', current_date - interval '4' month) as mydate

e.g. Current date is, 2022-01-03 19:30:00

Output is, 2022-09-01 00:00:00

Method 2:

SELECT CURRENT_DATE - INTERVAL '4 months' as mydate

e.g. Current date is, 2022-01-03 19:30:00

Output is, 2022-09-03 00:00:00

Postgresql - Extract YEAR/ MONTH from TIME STAMP

 SELECT 

EXTRACT(YEAR FROM created),

EXTRACT(MONTH FROM created),

COUNT(*) 

FROM 

schemaname.tablename

GROUP BY 

EXTRACT(YEAR FROM created),

EXTRACT(MONTH FROM created) 

ORDER BY 1,2

Postgresql - Between two timestamp/date

SELECT * FROM schemaname.tablename

WHERE columnname between '2022-12-14 00:00:00' AND '2022-12-19 14:00:03' ORDER BY 1 DESC

NOTE: Includes both the dates 2022-12-14 & 2022-12-19

Postgresql - Add/ Subtract Hours/Minutes from Date/ Timestamp

SELECT now() - interval '5 minutes';

SELECT now() - interval '36 hours';

DISCARD MILLISECOND PART FROM TIMESTAMP

 SELECT now()::timestamp(0);

Create Index

 CREATE INDEX sync_idx_abc ON schemaname.tablename(columnname);

Drop Not Null Constraint

 ALTER TABLE schemaname.tablename ALTER COLUMN abc DROP NOT NULL;

Text To Timestamp Conversion

 TO_TIMESTAMP('1999-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

Get the record count from all tables in a schema

select table_schema, 

       table_name, 

       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count

from (

  select table_name, table_schema, 

         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count

  from information_schema.tables

  where table_schema = 'test'

AND table_name IN ('abc') --<< change here for the schema you want

) t order by 3 DESC

---------------------------------------------

APPROACH 2 with WHERE CONDITION

---------------------------------------------

select table_schema, 

       table_name, 

       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count

from (

  select table_name, table_schema, 

         query_to_xml(format('select count(*) as cnt from %I.%I  WHERE updated > ''2022-10-01 00:00:00''', table_schema, table_name), false, true, '') as xml_count

  from information_schema.tables

  where table_schema = 'test'

AND table_name NOT IN ('abc')

AND table_name NOT LIKE ('ad_%') --<< change here for the schema you want

) t order by 3 DESC

GET THE INDEX LIST FROM DB - POSTGRESQL

 select

    t.relname as table_name,

    i.relname as index_name,

    array_to_string(array_agg(a.attname), ', ') as column_names

from

    pg_class t,

    pg_class i,

    pg_index ix,

    pg_attribute a

where

    t.oid = ix.indrelid

    and i.oid = ix.indexrelid

    and a.attrelid = t.oid

    and a.attnum = ANY(ix.indkey)

    and t.relkind = 'r'

    --and t.relname like 'test%'

group by

    t.relname,

    i.relname

order by

    t.relname,

    i.relname;