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;