Table of contents

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;