I'm on a bit of a random kick lately. Today I learned you can randomly sort rows returned in a query using Postgres' random() function.
SELECT *
FROM users
ORDER BY random()What it does is it calculates a random value for each row returned, and then orders by that calculated value. If the query returns a lot of rows this can be slow, so probably more useful for exploration or one-offs not necessarily for production uses.
PostgreSQL has a function called jsonb_to_recordset that will return your jsonb data as if it were rows in the database.
Say we have the following example table:
create table notes (title varchar, bullets jsonb);
insert into notes (title, bullets)
values ('Inspection', '[{"field": "Tires", "measured": "Tread Height", "value": "3mm"},{"field": "Brakes", "measured": "Caliper Boot", "value": "cracked"}]');
To use the jsonb_to_recordset function, we can do the following:
select title, field, measured, value
from notes
cross join lateral jsonb_to_recordset(notes.bullets)
as temp(field varchar, measured varchar, value varchar);The function gives us the following output:
title | field | measured | value
------------+--------+--------------+---------
Inspection | Tires | Tread Height | 3mm
Inspection | Brakes | Caliper Boot | crackedWhen Postgres is running comparisons, any null values will yield null at the end of the comparison. This is because the null is an unknown value that Postgres can't run the comparison against.
Take the following simple example of a users table and query:
create table users (name text, email text);
insert into users (name, email)
values ('Joe', 'joe@hashrocket.com'),
('Rick', null);
select * from users where email not like '%gmail.com';
-- name | email
-- ------+--------------------
-- Joe | joe@hashrocket.com
-- (1 row)
You'll notice that the Rick user is not returned in the results.
If you want rows with the null value included in your results, you can coalesce the column to an empty string. This allows Postgres to run the comparison against two known values and return the rows with the null values.
select * from users where coalesce(email, '') not like '%gmail.com';
-- name | email
-- ------+--------------------
-- Joe | joe@hashrocket.com
-- Rick | ø
-- (2 rows)Postgres 15 gave us the ability to specify how we want null values to be treated when dealing with unique indexes.
By default, nulls are considered unique values in Postgres:
create table users (name text, email text unique);
-- CREATE TABLE
insert into users values ('Joe', null), ('Jane', null);
-- INSERT 0 2
This default behavior can also be explicitly set using the nulls distinct clause:
create table users (name text, email text unique nulls distinct);
-- CREATE TABLE
insert into users values ('Joe', null), ('Jane', null);
-- INSERT 0 2
To change the default behavior and prevent nulls from being considered unique values, you can use the nulls not distinct clause:
create table users (name text, email text unique nulls not distinct);
-- CREATE TABLE
insert into users values ('Joe', null), ('Jane', null);
-- ERROR: duplicate key value violates unique constraint "users_email_key"
-- DETAIL: Key (email)=(null) already exists.See this change in the Postgres 15 release notes
If you use the pattern match operators in PSQL, you'll want to mind the column types passed to these statements
If you use a string, you will get a boolean return -
select 'a' like '%b%';
?column?
----------
f
(1 row)
select 'a' like '%a%';
?column?
----------
t
(1 row)But if you select a null in one of these statements, the return is null as well -
select null like '%a%';
?column?
----------
ø
(1 row)Moral of the story - if you're expecting a boolean, you can coalesce the column before the pattern match -
select coalesce(null, '') like '%a%';
?column?
----------
f
(1 row)https://www.postgresql.org/docs/current/functions-matching.html
PSQL has some helpful commands built in for viewing foreign data servers and tables if you are using them:
\des - list foreign servers
\deu - list user mappings
\det - list foreign tables
\dtE - list both local and foreign tables
\d <name of foreign table> - show columns, data types, and other table metadata
PostgreSQL treats NULL values a bit different than most of the languages we work with. Said that, if you try this:
SELECT * FROM users WHERE has_confirmed_email <> TRUE
This would return users with has_confirmed_email = FALSE only, so NULL values that you have on your DB would be ignored. In this case if you want to get users with has_confirmed_email as FALSE or NULL then you can use IS DISTINCT FROM:
SELECT * FROM users WHERE has_confirmed_email IS DISTINCT FROM TRUE
There's nothing special about this, I'm just a dummy who only ever used ilike and never thought twice about it.
Today, I learned that ilike is just a case-insensitive version of like.
This would return any rows with a name of Peter
select * from users
where name ilike '%peter%'
;
This would not return any rows with a name of Peter
select * from users
where name like '%peter%'
;Stay tuned for tomorrow's TIL where I tell you about how I learned the sky is blue! 😂
The initCap function in Posgtres will capitalize each word of a string for you. V convenient. I was surprised to find this was a thing :)
=> select initCap('this IS mY test STRING');
initcap
------------------------
This Is My Test String
(1 row)If you ever need to find the location of your Postgres config file, you can connect to Postgres and run:
show config_fileThis command will return the absolute path for the config file.
Run show timezone to see the current timezone on your postgres database:
my_db_test=# show timezone;
TimeZone
------------------
America/New_YorkTo alter the timezone:
alter database my_db_test set timezone to 'utc';
You might want to restart your psql session for the new timezone to apply.
I recently learned that you can use PostGIS alongside the Tiger Geocoder extension to geocode an address in Postgres. This is especially handy if you have a specific locale (US or state level) that you need to Geocode. In my case, I need lat/long coordinates for addresses in Florida and Illinois.
Another reason I like this is because it is free - no need to pay for an additional service.
Here's what the API looks like:
select
result.rating,
ST_X(result.geomout) as longitude,
ST_Y(result.geomout) as latitude
from geocode('320 1st St N, Jacksonville Beach FL', 1) as result;
rating | longitude | latitude
--------+--------------------+--------------------
1 | -81.39021163774713 | 30.291481272126084
(1 row)https://postgis.net/docs/manual-3.3/postgis_installation.html#loading_extras_tiger_geocoder
h/t Mary Lee
In Postgres if you want to see how much disk space your relation (including data and any indexes) is taking, you can use pg_total_relation_size(<relation_name>)
SELECT pg_total_relation_size('<relation_name>') as size;
This can be used in conjunction with pg_size_pretty() to give a more readable output
SELECT pg_size_pretty(pg_total_relation_size('<relation_name>')) as size;
Let's say you want to find all purchases that don't match a specific coupon. You can use != to filter them:
select * from purchases where coupon != 'JULY4';
The problem with that is that it doesn't match records that have null values. One way to solve that is by doing a or:
select * from purchases where coupon != 'JULY4' or coupon is null;
Better than that is to use is distinct from:
select * from purchases where coupon is distinct from 'JULY4';Earlier today, I was trying to join a table on a formatted string row, but wasn't getting the results I expected. Turns out that my formatting string had blank padding and I discovered "fill mode".
When using postgres formatting functions, like to_char, some of the formatting options include padding in the result. For example, the day format string will be blank padded to 9 chars.
select to_char(current_date, 'day');
to_char
-----------
sunday
You can use the "fill mode" (FM) option to remove any leading zeros or blank padding characters by prepending it to your format option:
select to_char(current_date, 'FMday')
to_char
---------
sundayhttps://www.postgresql.org/docs/current/functions-formatting.html
You can lookup your postgres function OIDs (object identifiers) using the pg_proc table:
select oid from pg_proc where proname ='my_function_name';This is very useful if you need to find more info about a function but only know the function name.
The alter table, vacuum full, reindex, and refresh materialized view statements are a few examples that may take out an ACCESS EXCLUSIVE lock by default. This means that select statements are blocked until one of these statements has finished.
Passing null or all to the limit clause effectively ignores the limit clause.
-- both statements return all user emails
select email from users limit null;
select email from users limit all;
The EXPLAIN command in Postgres can help you understand the query plan for a given query. Furthermore, you can use EXPLAIN ANALYZE to see the estimated query plan and cost vs the actual time and rows.
To take it a step further, you can use EXPLAIN (ANALYZE, BUFFERS) to include a number that represents the I/O disk usage of certain parts of your query.
explain (analyze, buffers)
select
*
from floor_plans
order by created_at desc
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Sort (cost=2.56..2.60 rows=18 width=159) (actual time=0.062..0.065 rows=16 loops=1)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 28kB
Buffers: shared hit=2
-> Seq Scan on floor_plans (cost=0.00..2.18 rows=18 width=159) (actual time=0.018..0.032 rows=16 loops=1)
Buffers: shared hit=2
Planning Time: 0.111 ms
Execution Time: 0.106 ms
(8 rows)
Make sure that if you run this with a query that writes, that you wrap it in a BEGIN...ROLLBACK statement.
https://www.postgresql.org/docs/current/using-explain.html
Today I learned that we can aggregate PostgreSQL boolean values with BOOL_OR or BOOL_AND so if we group a query we could return if any of those values are TRUE, or if all of those values are TRUE respectively.
Check this out
You can add a primary key to a table with alter an alter table statement:
Table with no primary key:
create table no_pks (id int generated by default as identity not null);
insert into no_pks select from generate_series(0,999);[local] dillon@dillon=# \d no_pks
Table "public.no_pks"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as identityYou can add it later:
alter table no_pks add primary key (id);[local] dillon@dillon=# \d no_pks
Table "public.no_pks"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as identity
Indexes:
"no_pks_pkey" PRIMARY KEY, btree (id)When trying to figure out why a function raised an exception you can print the error code raised to lookup in the table Appendix A-1.
One method is to capture others and then raise the magic sqlstate variable (only available in exception handlers)
create or replace function do_it(name text)
returns void
as $$
begin
select 42 from nothing;
exception
when others then
raise '%: %', sqlstate, sqlerrm;
end;
$$
security definer
language plpgsql
;Then you can view the error:
select do_it('hi');
ERROR: 42P01: relation "nothing" does not exist
CONTEXT: PL/pgSQL function do_it(text) line 6 at RAISE
You can ignore your ~/.psqlrc when running psql commands by using the -X or --no-psqlrc flags.
So when you have all this in your rc file:
/* ~/.psqlrc */
\x auto
\timing
\set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '
\set PROMPT2 '[more] %R > '
\pset null '☢'
\setenv PSQL_PAGER pspg
\setenv PAGER pspgThis command becomes quite noisy:
psql -c 'select 1'
Expanded display is used automatically.
Timing is on.
Null display is "☢".
Time: 0.210 ms
?column?
----------
1
(1 row)
Time: 0.297 msIf you run without the config file:
psql -X -c 'select 1'
?column?
----------
1
(1 row)on-line manual pages:
-X,
--no-psqlrc
Do not read the start-up file (neither the system-wide psqlrc file nor the
user's ~/.psqlrc file).
You can search in a an array by using the ANY function:
create table numbers as
select * from (
values
(1, array[1,2]),
(2, array[2,3,4]),
(3, array[4,5,6])
)
as numbers(id, positions);
select *
from numbers
where 4 = any(positions); id | positions
----+-----------
2 | {2,3,4}
3 | {4,5,6}
(2 rows)Only foreign key constraints may be altered in PostgreSQL:
create extension citext;
create table users (id int generated by default as identity primary key);
create table user_emails (
user_id int not null references users,
email citext primary key
);Now we can see the constraint:
[local] dillon@test=# \d user_emails
Table "public.user_emails"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
user_id | integer | | not null |
email | citext | | not null |
Indexes:
"user_emails_pkey" PRIMARY KEY, btree (email)
Foreign-key constraints:
"user_emails_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)But now we can change the foreign key to be deferrable:
alter table user_emails
alter constraint user_emails_user_id_fkey deferrable initially immediate;After:
[local] dillon@test=# \d user_emails
Table "public.user_emails"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
user_id | integer | | not null |
email | citext | | not null |
Indexes:
"user_emails_pkey" PRIMARY KEY, btree (email)
Foreign-key constraints:
"user_emails_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) DEFERRABLEIn ruby I would use something like
"PostgreSQL".downcase.start_with?("p")
=> trueand the equivalent in a query would be:
select lower(left('PostgreSQL', 1)) = 'p';
?column?
----------
t
(1 row)and if you have the citext extension enabled you could do:
select left('PostgreSQL', 1)::citext = 'p';
?column?
----------
t
(1 row)Other things:
select *
from users
where left(display_name, 1)::citext = 'a'
;It's as simple as:
select zip_code, (array_agg(company_name))[1]
from locations
group by zip_code
;
zip_code | array_agg
----------+--------------------------
90210 | In-N-out
46368 | Johnny's Round the Clock
(2 rows)source:
create table locations (
id bigint generated by default as identity primary key,
zip_code text not null,
company_name text not null
);
insert into locations (zip_code, company_name) values
('46368', 'Johnny''s Round the Clock'),
('90210', 'In-N-out'),
('46368', 'Albanese Candy');
Clients task me to export data to a CSV from time to time. My favorite workflow for authoring queries is a split tmux session, vim (with the excellent coc-sql) on one side, and a psql session on the other. I modify my query and then re-execute via \i.
This is great, until I'm ready to export the CSV. historically I've edited the query, cumbersomely breaking my REPL flow by turning it into a COPY command. Now I do this:
\pset format csv
\o output-file.csv
\i my-query.sql
To reset my psql session output...
\pset format aligned
\o
And thus my-query.sql remains a working query.
When we CREATE INDEX in a partitioned table, PostgreSQL automatically "recursively" creates the same index on all its partitions.
As this operation could take a while we can specify the ONLY parameter to the main table index to avoid the index to be created on all partitions and later creating the same index on each partition individually.
CREATE INDEX index_users_on_country ON ONLY users USING btree (country);
CREATE INDEX users_shard_0_country_idx ON users_shard_0 USING btree (country);
CREATE INDEX users_shard_1_country_idx ON users_shard_0 USING btree (country);
Sometimes when checking the EXPLAIN of a query results in a sequential scan where you expected an index to be used. This is expected as an optimization of PostgreSQL to decide which is the best method to use.
QUERY PLAN
-----------------------------------------------------------------------------------
Seq Scan on widgets (cost=0.00..70.92 rows=1038 width=402)
Filter: ((deleted_at IS NULL) AND ((widget_type)::text = 'foo'::text))But what if you WANT to see how the index will be used? Well you can force sequential scanning to be turned off.
SET enable_seqscan TO off;Now if we view the query plan again we can see the index in use.
QUERY PLAN
-----------------------------------------------------------------------------------------------
Bitmap Heap Scan on widgets (cost=32.87..100.76 rows=1038 width=402)
Recheck Cond: ((widget_type)::text = 'foo'::text)
Filter: (deleted_at IS NULL)
-> Bitmap Index Scan on index_widgets_on_widget_type (cost=0.00..32.61 rows=1111 width=0)
Index Cond: ((widget_type)::text = 'foo'::text)Hooray!
Now just don't forget to re-enable the previous functionality
SET enable_seqscan TO on;
Postgres supports POSIX regex pattern matching using the squiggle (~) operator
-- Check for a match
select 'wibble' ~ 'ubb';
-- returns false
select 'wibble' ~ 'ibb';
-- returns true
-- Case insensitive match checking
select 'wibble' ~ 'IBB';
-- returns false
select 'wibble' ~* 'IBB';
-- returns true
-- Check for no match
select 'wibble' !~ 'ibb';
-- returns false
select 'wibble' !~ 'ubb';
-- returns trueFull postgres pattern matching documentation can be found here
Postgres has a strpos function for finding the position of a substring in a given string:
select strpos('wibble', 'ibb');
The function returns an integer representing the location of the substring in the provided string, or a 0 if the substring cannot be found in the provided string (the locations are 1-based indexes, so you don't have to worry about collisions!).
You can split text in postgres
select email from users;bob@example.com
mary@example.comselect split_part(email, '@', 1) from users;bob
maryselect split_part(email, '@', 2) from users;@example.com
@example.comWhich can be helpful when sanitizing data:
update users
set email = split_part(email, '@', 1) || '@example.com';
Working in Postgres, I've gotten used to seeing the contraints on a table listed with \d. Working in MySQL I wasn't seeing similar with describe <table>;. Turns out you can see all the constraints by selecting from information_schema.referential_constraints:
SELECT *
FROM information_schema.referential_constraints
WHERE constraint_schema='db_name'
AND table_name='table_of_interest';When working with foreign data wrappers, one uses a materialized view to store the downloaded foreign table data. The process of downloading could be very expensive and managed by another process or program, but your program needs to define the parameters of materialzied view/foreign table. Maybe the data is loaded out of band by cron:
@daily /usr/bin/psql -d cool_db -c 'refresh materialized view big_view;'
So to create the materialized view without loading the data we use the WITH NO DATA clause:
create foreign table measurement_y2016m07
partition of measurement for values from ('2016-07-01') to ('2016-08-01')
server server_07;
create materialized view big_view
as select *
from measurement_y2016m07
with no data;
This way we are able to execute the create foreign table and create materialized view statements in a very short amount of time. A different process can start the download with refresh materialized view
The Postgres wiki recommends not using the serial type, and instead added identity columns to replace them.
Old way:
create table todos (
id bigserial primary key,
todo text not null
);The new way with identity columns:
create table todos (
id bigint generated by default as identity primary key,
todo text not null
);Data:
insert into todos (todo) values
('write a til'),
('get some coffee');
select *
from todos; id | todo
----+-----------------
1 | write a til
2 | get some coffee
(2 rows)Source: PG wiki: Don't use serial
select
alias,
token
from ts_parse('default', 'https://hashrocket.com/team/brian-dunn')
natural join ts_token_type('default') alias | token
----------+--------------------------------
protocol | https://
url | hashrocket.com/team/brian-dunn
host | hashrocket.com
url_path | /team/brian-dunn
(4 rows)
pg_dump will dump foreign tables schemas, but not their data.
$ pg_dump -d db_with_foreign_tables
CREATE FOREIGN TABLE public.users (
name text,
...
)
SERVER foreign_server_somewhere
OPTIONS (
query 'select * from foreign_users'
);Nice job Heroku, you made this super simple:
heroku pg:backups:capture
heroku pg:backups:download
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump
Want to execute a PostgreSQL command from the command line? You can! The --command or -c flag takes a string argument that will be executed on your database of choice.
I've been using it as part of a script that creates a remote database backup, downloads the backup, drops and creates a local database, dumps the database backup into the local database, and then runs a select statement on the dataset. That final command looks like this (query has been simplified):
$ psql -d tilex_prod_backup -c "select count(*) from posts";
count
-------
2311
(1 row)
Postgres' psql meta command \d stands for display or describe.
h/t Jack Christensen
Postgres can automatically generate constraint names if you omit the word constraint:
alter table users
add foreign key (location_id) references locations;
alter table users
add unique (email);I cooked up this Heroku/subshell command today, and I like it:
$ psql $(heroku config:get DATABASE_URL)This will connect me to the primary PostgreSQL database for my Heroku application in the psql client. Now I'm ready to query!
NOTE: why not $ heroku pg:psql? I'm not sure. I think Heroku was reporting status issues today, and I wanted to bypass any infrastructure I could.
When performing full-text search in Postgres you can add :* to the end of a string to be searched:
select title from posts where title::tsvector @@ 'instr:*'::tsquery;
This will match any post that has a word in its title that starts with instr
Always watch out for null in Postgres. When null sneaks into a result set it may confuse the results of your query.
Without nulls a where in query could look like this:
psql> select 'found it' as c0 where 1 in (1);
c0
----------
found it
(1 row)
For the where in clause a null does not change the results.
psql> select 'found it' as c0 where 1 in (null, 1);
c0
----------
found it
(1 row)
The where not in formulation however is sensitive to null. Without a null it looks like this:
psql> select 'found it' as c0 where 17 not in (1);
c0
----------
found it
(1 row)
Add in the null and the results can be counterintuitive:
psql> select 'found it' as c0 where 17 not in (1, null);
c0
----
(0 rows)
Watch out for those nulls!!
null is weird in postgres. Sure, it's a way of saying that there is no data. But if there is a null value Postgres doesn't want to be responsible for filtering the null value unless you explicitly tell it to.
psql> select 1 where null;
?column?
----------
(0 rows)
Comparing null to null with = returns null, not true.
psql> select 1 where null = null;
?column?
----------
(0 rows)And comparing a value to null returns neither true nor false, but null.
psql> select 1 where 17 != null or 17 = null;
?column?
----------
(0 rows)
So when we apply a comparison to a nullable column over many rows, we have to be cognisant that null rows will not be included.
psql> select x.y from (values (null), (1), (2)) x(y) where x.y != 1;
y
---
2
(1 row)
To include the rows which have null values we have to explicitly ask for them with is null.
psql> select x.y from (values (null), (1), (2)) x(y) where x.y != 1 or x.y is null;
y
---
ø
2
(2 rows)
SQL has a structure called a CURSOR that, according to the docs:
Rather than executing a whole query at once, read the query result a few rows at a time
This is mainly for solving memory usage issues, but probably not very applicable to web applications. Here's an example syntax to periodically fetch a limited amount of rows:
begin;
declare posts_cursor cursor for select * from posts;
fetch 10 from posts_cursors;
fetch 10 from posts_cursors;
commit;This is not advised due to leaving a transaction open, but a simple example. More powerful use cases would be iterating a query in a function for updating a small amount of records at a time.
I discovered this morning that random() when used in a subquery doesn't really do what you think it does.
Random generally looks like this:
> select random() from generate_series(1, 3)
random
-------------------
0.856217631604522
0.427044434007257
0.237484132871032
(3 rows)
But when you use random() in a subquery the function is only evaluated one time.
> select (select random()), random() from generate_series(1, 3);
random | random
-------------------+-------------------
0.611774671822786 | 0.212534857913852
0.611774671822786 | 0.834582580719143
0.611774671822786 | 0.415058249142021
(3 rows)So do something like this:
insert into things (widget_id)
select
(select id from widgets order by random() limit 1)
from generate_series(1, 1000);
Results in 1000 entries into things all with the same widget_id.
Postgres has a record type that you can use with a comma seperated list of values inside of parenthesis like this:
> SELECT pg_typeof((1, 2));
pg_typeof
-----------
record
(1 row)What is also interesting is that you can compare records:
> select (1, 2) = (1, 2);
?column?
----------
t
(1 row)And additionally, a select statement results in a record:
> select (1, 2) = (select 1, 2);
?column?
----------
t
(1 row)What this allows you to do is to create a where statement where the expression can check to see that 2 or more values are contained in the results of a subquery:
> select true where (1, 2) in (
select x, y
from
generate_series(1, 2) x,
generate_series(1, 2) y
);
bool
------
t
(1 row)This is useful when you declare composite keys for your tables.
Today I learned how to change psql prompt to add some color and manipulate which info to show:
$ psql postgres
postgres=# \set PROMPT1 '%[%033[1;32m%]@%/ => %[%033[0m%]%'
@postgres => \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------------------+------------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
Check this documentation if you want to know more.