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;