Postgres

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
information_schema.table_constraints
information_schema.views
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';

Round:

SELECT ROUND(123.11); -- = 123
SELECT ROUND(123.45678, 2); -- = 123.46

Title Case:

SELECT INITCAP('AAA BBB'); -- = Aaa Bbb

Create Primary Key:

ALTER TABLE table_name
ADD CONSTRAINT some_name PRIMARY KEY (column_name)

Create Superuser:

CREATE ROLE test_user SUPERUSER LOGIN PASSWORD 'password1';

Function:

CREATE OR REPLACE FUNCTION get_team_location (team_name text)
RETURNS text
LANGUAGE plpgsql
AS
$$
DECLARE return_value text;
BEGIN
	SELECT
		CASE WHEN team_name = 'FALCONS' THEN 'ATLANTA'
			WHEN team_name = 'PANTHERS' THEN 'CAROLINA'
			WHEN team_name = 'SAINTS' THEN 'NEW ORLEANS'
			WHEN team_name = 'BUCS' THEN 'TAMPA'
			ELSE NULL END
	INTO return_value;

	RETURN return_value;
END;
$$;

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