Index

Postgres CRUD

select

select a raw string
select 'foo'
select 'foo' as bar
concat null if either operant is null
'' || ''
concat ignore nulls
concat('abcde', 2, NULL, 22)
get a value from a sequence
select nextval('[name]');
select where timestamp no more than 3 days old
select * from [table] where now() - INTERVAL '3 days' < [column]
Reading a field of type text
select convert_from(loread(lo_open([COLUMN]::int, x'40000'::int), x'40000'::int),  'UTF8') from [TABLE];
select a random record
select * from [table] order by random() limit 1;
more efficient, but not as efficient as TABLESAMPLE
select * from [table] offset floor(random() * (select count(*) from [table])) limit 1;

joins

inner join
select * from [table]
    INNER JOIN [other table] ON [table].[column] = [other table].[column] where [condition]
;
join the results of two queries with compatible columns (filter duplicates)
SELECT [column]* FROM [table1] UNION SELECT [column]* FROM [table2]
join the results of two queries with compatible columns (include duplicates)
SELECT [column]* FROM [table1] UNION ALL SELECT [column]* FROM [table2]
only use the second query if the first query has no results
SELECT [column]* FROM [table1] UNION SELECT [column]* FROM [table2] WHERE ((SELECT COUNT(*) FROM [table1])=0);
documentation
http://www.postgresqltutorial.com/postgresql-union/

string functions

coalesce returns the first value that is non null (great for providing defaults)
select coalesce([column], [default]) from [table]
starts_with
select starts_with('[word]', '[prefix]')
documentation
https://www.postgresql.org/docs/current/functions-conditional.html
https://www.postgresql.org/docs/current/functions-string.html

insert

insert
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
insert with select
INSERT INTO [table] (column1, column2, column3, ...)
	SELECT * FROM [table]
conditional insert
INSERT INTO [table] ([column]) SELECT '[value_to_insert]' where not exists (
	select * from [table] where [condition]
);
insert multiple rows
insert into [table] ([column]) values (8), (12);
insert multiple rows from query
insert into [destination_table] ([col1], [col2]) 
	select [col1], [col2] from [source_table]

update

update single column
update [table] set [column] = '[value]' where [condition];
update multiple columns
update [table] set ([column1], [column2]) = ([value1],[value2]) where [condition];
update timestamp
update [table] set [column] = '2016-06-22 19:10:25-07' where ...
update with join
UPDATE vehicle SET vehicle.price = shipment.price
	FROM shipment WHERE vehicle.shipment_id = shipment.id

delete

delete with join
DELETE FROM films USING producers
  WHERE producer_id = producers.id AND producers.name = 'foo';

postgres regex

regex operator
~ 	Matches regular expression, case sensitive
~* 	Matches regular expression, case insensitive
!~ 	Does not match regular expression, case sensitive
!~* 	Does not match regular expression, case insensitive
regex classes
\d 	[[:digit:]]
\s 	[[:space:]]
\w 	[[:alnum:]_] (note underscore is included)
\D 	[^[:digit:]]
\S 	[^[:space:]]
\W 	[^[:alnum:]_] (note underscore is included)
regex syntax
https://www.postgresql.org/docs/current/functions-matching.html

examples

regex is blank
~'^\s*$'
regex 6 digits
~'^\d{6}$'
~'^[0-9]{6}$'
regex with boolean logic
VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$'
regex tester
https://rextester.com/OKFCC50792
regex tester sample code
CREATE TABLE tabq(myCode CHAR(6) PRIMARY KEY CHECK (myCode ~'^\d{6}$'));
INSERT INTO tabq(myCode) VALUES ('0000');
SELECT * FROM tabq;