SQL Rob

SQL Server and other database technologies

Postgres Reference

Datetimes

Get current datetime:

CURRENT_TIMESTAMP - with timezone
LOCALTIMESTAMP - without timezone
NOW() - PG function - Non-standard
CLOCK_TIMESTAMP() = with timezone - Instead of the 
date/time at the start of the transaction, 
the actual current time

Get datetime parts:

SELECT DATE_PART('DAY', NOW());

Date Parts:
century, day, decade, dow(Day Of Week), doy(Day Of Year), epoch (number of seconds since 1970-01-01), hour, julian, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week, year

Add or subtract from dates:

SELECT NOW() + INTERVAL '1' DAY

Get first day of current month:

SELECT DATE_TRUNC('month', NOW());

Current Time To UTC:

(CURRENT_TIMESTAMP AT TIME ZONE 'utc')

Columns:

Change Data Type:

ALTER TABLE public.customer ALTER COLUMN name
    TYPE varchar(25);

Change Nullability:

ALTER TABLE public.customer ALTER COLUMN name
    SET NOT NULL;
ALTER TABLE public.customer ALTER COLUMN name
    DROP NOT NULL;

Rename Column:

ALTER TABLE public.customer 
    RENAME COLUMN address TO street_address;	

Add column if it hasn’t already been added:

ALTER TABLE test.test_table1 
ADD IF NOT EXISTS col1 text NOT NULL;

Create identity Column:

id int GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY

Timestamp With Current UTC date/time as Default:

modified_date timestamp NOT NULL 
DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')

Miscellaneous:

Rename Table:

ALTER TABLE IF EXISTS table_name
    RENAME TO new_table_name;

Querying for database objects:

information_schema.columns
information_schema.tables
pg_catalog.pg_indexes

Reference a list of values as a table:

SELECT * 
FROM (VALUES
	(1, 'Sally'),
	(2, 'Doug')
) as t(record_id, first_name)

Find position of character in a string – Equivalent to CHARINDEX in SQL Server

SELECT POSITION('d' in 'abcdefg');

Truncate Table and reset Identity values:

TRUNCATE dealer RESTART IDENTITY CASCADE;

Insert a specific value for primary key in table with a sequence:

INSERT INTO public.customer(id, first_name, last_name) 
OVERRIDING SYSTEM VALUE
VALUES 
(1, 'Jane', 'Doe');

Capture the number of records affected by an operation:

DECLARE v_rows_affected BIGINT;
UPDATE table SET column = TRUE;
GET DIAGNOSTICS v_rows_affected = ROW_COUNT;
RAISE NOTICE '% Insert Complete. % rows affected.', clock_timestamp(), v_rows_affected;

Delete from table while joining to other tables:

DELETE FROM public.customer as c
USING public.customer_request as r
WHERE r.customer_id = c.customer_id
    AND r.status = 'REMOVE';

Execution Plan: Explain Analyze
Maintenance:
ANALYZE table_name;
VACUUM (VERBOSE, ANALYZE) table_name;