Today I Learned

A Hashrocket project

198 posts about #sql

Postgres Identity Column

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

Supercharge Your Script with psql -c πŸ₯ž

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)

Heroku Psql Oneline 🐘

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.

Postgres `null` and `where <VALUE> not in`

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!!

Equality comparison and null in postgres

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)

What a cursor is in postgres

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.

`random()` in subquery is only executed once

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.

Where in with multiple values in postgres

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.

Change PostgreSQL psql prompt colors

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)

image

Check this documentation if you want to know more.

Are All Values True in Postgres

If you have values like this:

chriserin=# select * from (values (true), (false), (true)) x(x);
 x
---
 t
 f
 t

You might want to see if all of them are true. You can do that with bool_and:

chriserin=# select bool_and(x.x) from (values (true), (false), (true)) x(x);
bool_and 
---
 f

And when they are all true:

chriserin=# select bool_and(x.x) from (values (true), (true), (true)) x(x);
bool_and 
---
 t

Aggregate Arrays In Postgres

array_agg is a great aggregate function in Postgres but it gets weird when aggregating other arrays.

First let’s look at what array_agg does on rows with integer columns:

select array_agg(x) from (values (1), (2), (3), (4)) x (x);
-- {1,2,3,4}

It puts each value into an array. What if are values are arrays?

select array_agg(x)
from (values (Array[1, 2]), (Array[3, 4])) x (x);
-- {{1,2},{3,4}}

Put this doesn’t work when the arrays have different numbers of elements:

select array_agg(x)
from (values (Array[1, 2]), (Array[3, 4, 5])) x (x);
-- ERROR:  cannot accumulate arrays of different dimensionality

If you are trying to accumulate elements to process in your code, you can use jsonb_agg.

select jsonb_agg(x.x)
from (values (Array[1, 2]), (Array[3, 4, 5])) x (x);
-- [[1, 2], [3, 4, 5]]

The advantage of using Postgres arrays however is being able to unnest those arrays downstream:

select unnest(array_agg(x))
from (values (Array[1, 2]), (Array[3, 4])) x (x);
--      1
--      2
--      3
--      4

ALL CAPS SQL

A while ago I read The Mac Is Not a Typewriter by Robin Williams. In it, the author claims:

Many studies have shown that all caps are much harder to read. We recognize words not only by their letter groups, but also by their shapes, sometimes called the β€œcoastline.” β€”pg. 31, The Mac Is Not a Typewriter

I’ve found this to be true. When we teach SQL, students are often surprised that we don’t capitalize PostgreSQL keywords, preferring this:

select * from posts limit 5;

To this, which you might see in an SQL textbook:

SELECT * FROM posts LIMIT 5;

My arguments against the latter syntax: it’s practically redundant in PostgreSQL, it’s harder to type, and it’s unnecessary because any good text editor highlights the keywords. Now I have another: such writing has been, in typesetting, shown to be harder to read. WHERE and LIMIT look similar from a distance in all-caps, but they mean and do different things.

It’s a style opinion each developer gets to refine for themselves. To quote Williams: β€œBe able to justify the choice.”

Count true values with postgres

I can try to count everything that is not null in postgres:

select count(x.x)
from (
  values (null), ('hi'), (null), ('there')
) x (x);
# 2

But if I try to count everything that is true in postgres, I don’t get what I want:

select count(x.x)
from (
  values (false), (true), (false), (true)
) x (x);
# 4

I can, however, take advantage of the postgres ability to cast boolean to int:

select true::int;
# 1
select false::int;
# 0

Using ::int I get:

select count(x.x::int)
from (
  values (false), (true), (false), (true)
) x (x);
# 4

Postgres is still counting everything that is not null, but what if use sum instead?

select sum(x.x::int)
from (
  values (false), (true), (false), (true)
) x (x);
# 2

Because everything is either a 0 or a 1, sum behaves like count.

Now you can do something like this:

select
  sum((status = 'Awesome')::int) as awesomes,  
  sum((status = 'Terrible')::int) as terribles
from statuses;

PostgreSQL Triggers and Views 🐘

Today I learned a lot about PostgreSQL.

  1. Views can have triggers!
  2. Deleting such a view also deletes the trigger!

To demonstrate, let’s create a table and record, and a view that looks at our table.

create table some_table (name varchar);
insert into some_table values ('some name');
create view some_view as select name from some_table;

Now, we need a test function:

create or replace function always_null() returns trigger as $always_null$
  begin
    return null;
  end;
$always_null$ language plpgsql;

Let create a trigger for our function:

create trigger some_table_trigger
  instead of delete on some_view
  for each row execute procedure always_null();

Here’s our view… with a trigger attached!

backup=# \d some_view
                   View "public.some_view"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 name   | character varying |           |          |
Triggers:
    some_table_trigger INSTEAD OF DELETE ON some_view FOR EACH ROW EXECUTE PROCEDURE always_null()

Our trigger can be inspected (lots of interesting info omitted):

backup=# select * from information_schema.triggers;
-[ RECORD 1 ]--------------+--------------------------------
trigger_name               | some_table_trigger

Drop the view, and lose the trigger:

backup=# drop view some_view;
DROP VIEW
backup=# select * from information_schema.triggers;
(0 rows)

PostgreSQL: Table or View?

I’m doing some database exploration today and needed to find out if a collection of tables are all tables, or if some of them are views. By querying the information schema via the following: select table_name, table_type from information_schema.tables;, I learned more about each table and its type:

> select table_name, table_type from information_schema.tables;
              table_name               | table_type
---------------------------------------+------------
 cats                                  | BASE TABLE
 dog                                   | BASE TABLE
 dogs_and_cats                         | VIEW

Combine Records from Different Tables

Let’s say you have 2 users and 3 categories and you want a query to return the combination of all the records, resulting in 6 rows.

You can use cross join to do that:

select
  users.id as user_id,
  categories.id as category_id
from users cross join categories


 user_id | category_id
---------+-------------
       1 |           1
       1 |           2
       1 |           3
       2 |           1
       2 |           2
       2 |           3

Create a temp table from values

In postgres, if you are looking for a way to create a quick data set to experiment with you can create a temporary table using the values expression.

create temp table test as values ('a', 1), ('b', 2), ('c', 3);

In postgres, a temp table is a table that will go away at the end of the session in which it was created.

The types of the columns are inferred as you can see when examining the table in psql:

> \d test
               Table "pg_temp_3.test"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 column1 | text    |           |          |
 column2 | integer |           |          |

What happens if we try to mix types?

> create temp table test as values ('a', 1), (1, 'c');
ERROR:  invalid input syntax for integer: "a"

You can also be specific about types by casting the values of the first row.

> create temp table test as values ('a'::varchar(1), 1::decimal), ('b', 1);
> \d test
                    Table "pg_temp_3.test"
 Column  |       Type        | Collation | Nullable | Default
---------+-------------------+-----------+----------+---------
 column1 | character varying |           |          |
 column2 | numeric           |           |          |

Choose only one row for a given value w Distinct

If you have a table with many rows but you only want one row for a given value, you can use distinct on (<column>)

select distinct on (letter) * 
from (
values 
('y', 512), 
('y',128), 
('z', 512), 
('x',128), 
('z', 256)) 
as x (letter, number);

Which produces

letter | number
--------+--------
 x      |    128
 y      |    512
 z      |    512
(3 rows)

Distinct implicitly orders by the specified column ascending. Putting order by letter at the end of the select statement produces the exact same output (and execution plan).

Distinct chooses the first row for the given column after sorting, so changing the sort order for the second column will change the results.

Here are the results after adding a order by letter, number asc to the above select statement.

 letter | number
--------+--------
 x      |    128
 y      |    128
 z      |    256
(3 rows)

Values clause in a select statement

You’ve all seen the VALUES clause before. It is typically used to insert data.

insert into colors (name, brightness) 
values ('red', 10), ('black', 0), ('blue', 5);

You can also use the VALUES clause in a select statement:

select * from (values ('red', 10), ('black', 0), ('blue', 5)
);

This is great when experimenting with different parts of sql at the command line.

Additionally, values is a first class expression on its own:

> values (1,2), (2,3);

 column1 | column2
---------+---------
       1 |       2
       2 |       3
(2 rows)

Generating a postgres query with no rows

For testing purposes, it is nice to be able to simulate a query that does not result in any rows.

select * from generate_series(0, -1) x;
 x
---
(0 rows)

This is because (from the docs):

When step is positive, zero rows are returned if start is greater than stop.

Step (the third argument to generate series) defaults to 1, so anything where the from has to go backwards to get to the to will result in 0 rows.

select * from generate_series(4, 3) x;
 x
---
(0 rows)

And now you can see how sum() behaves without any rows:

select sum(x) from generate_series(1, 0) x;
sum
-----
   ΓΈ
(1 row)

Ohhh, it returns null. I hope you’re planning for that.

Group by and order by can use aliases

In PostgreSQL the alias of a selected expression can be used in the group by and order by clauses instead of repeating the expression.

For example, this:

select left(lower(email), 1) as first_letter, count(*) as num
from users
group by left(lower(email), 1)
order by count(*) desc;

Can be replaced with this:

select left(lower(email), 1) as first_letter, count(*) as num
from users
group by first_letter
order by num desc;

PostgreSQL query with an array of regexes

I recently wanted to query my Postgres database by matching a column based on an array of regular expressions:

To query where the column matches all expressions in the array:

select * 
from my_table 
where my_column ilike all (array['%some%', '%words%'])

To query where the column matches at least one, but not necessarily all, of the expressions in the array:

select * 
from my_table 
where my_column ilike any (array['%some%', '%words%'])

First Postgres Queries: a Join and an Alias

To see some 2018 TIL stats, I learned to write some Postgres queries. One was:

select developers.username, count(*) from posts join developers on posts.developer_id = developers.id  where published_at >= '2018-01-01' and published_at < '2019-01-01' group by developers.username order by count(*) desc

This gave me the number of posts in 2018 associated with each username. I joined the posts table and the developers table with join developers on posts.developer_id = developers.id. That lines up the developer_id column in the posts table to the id column in the developers table. Now I can select the username column from the developers table: developers.username.

Next, I wanted to see the average number of likes per post for each developer. I do that by having the query evaluate the expression sum(likes)/count(*). I followed that with the phase as average. This creates an alias that can save some thinking and typing later in the query. The final query:

select developers.username, sum(likes)/count(*) as average from posts join developers on posts.developer_id = developers.id  where published_at >= '2018-01-01' and published_at < '2019-01-01' group by developers.username order by average desc

Congrats Taylor, Brian, and Jack for having the most loved posts of 2018!

Change the delimiter in mysql for procedures

Creating procedures in mysql has a weird quirk. When you terminate a statement within the procedure with a ; the entire create procedure statement gets executed up until the semi-colon. This causes a syntax error.

-> create procedure thing()
-> begin
-> set @x = 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

To get past this, you need to declare a different delimiter so that mysql know when you are ready to execute the entire create procedure statement. To change the delimiter to //, run:

delimiter //

Now you can execute the create procedure statement by putting // after end:

create procedure thing()
begin
  set @x = 0;
end //

Convert A String To A Timestamp In PostgreSQL

If you have a string that represents a point in time, there are a couple ways that you can convert it to a PostgreSQL timestamptz value.

If the string is in ISO 8601 format, then it can be simply cast to timestamptz.

> select '2018-10-24'::timestamptz;
      timestamptz
------------------------
 2018-10-24 00:00:00-05

A more general purpose approach is to use the to_timestamp function.

> select to_timestamp('2018-10-24', 'YYYY-MM-DD');
      to_timestamp
------------------------
 2018-10-24 00:00:00-05

The first argument is our string-to-be-converted in whatever format. The second argument is another string describing in what format that string is.

Note: Both of these approaches produce a timestamptz value.

Doing Date Math In MySQL

MySQL has an array of functions for interacting with date and datetime values. If you’d like to do math with a date to compute a date in the future or the past, you can use the DATE_ADD() and DATE_SUB() functions.

mysql> select now() Now, date_add(now(), interval 10 minute) '10 Minutes Later';
+---------------------+---------------------+
| Now                 | 10 Minutes Later    |
+---------------------+---------------------+
| 2018-10-18 15:53:29 | 2018-10-18 16:03:29 |
+---------------------+---------------------+

mysql> select now() Now, date_sub(now(), interval 9 day) '9 Days Earlier';
+---------------------+---------------------+
| Now                 | 9 Days Earlier      |
+---------------------+---------------------+
| 2018-10-18 15:54:01 | 2018-10-09 15:54:01 |
+---------------------+---------------------+

There are equivalent ADDDATE() and SUBDATE() functions if you prefer.

Check out the docs for more details.

Group dates by week in Postgres

Timestamps are everywhere in our postgres tables. To group them together you can use date_trunc to normalize the timestamp by minute, hour, day, etc.

You can also normalize the date by week.

> select date_trunc('week', now());
2018-10-08 00:00:00+00

The above example returns the first day of the week for the current moment which is a Monday. Postgres truncates dates to Monday rather than Sunday.

To group dates by week just use the truncated value in both the select clause and the group clause.

select count(*), 
  date_trunc('week', request_time) 
from requests 
group by 
  date_trunc('week', request_time) 
order by date_trunc desc ;

Reference rejected values on conflict in postgres

I have a table of fruits and I have their quantity:

create table fruits (name text primary key, quantity int);

In that table I have a row for apples:

insert into fruits (name, quantity) values ('apple', 10);

So generally, when I try to insert another apples row, because name is the primary key, I’ll get a duplicate key error:

insert into fruits (name, quantity) values ('apple', 11);
duplicate key value violates unique constraint "fruits_pkey"

So if I don’t know wether to insert or update I can use the on conflict functionality in postgres to set the quantity even if apples is already there.

insert into fruits (name, quantity) values ('apple', 13) on conflict (name) do update set quantity = excluded.quantity;

I can use the excluded special table to reference the row that was rejected by postgres and which contains the quantity that I want to update the row quantity to.

MySQL Average

MySQL; it’s like a calculator inside your computer. Today I learned how to compute the average of something using this popular RDBMS.

Let’s compute the average length of name in table dog.

mysql> select avg(char_length(name)) from dog;

Result:

+------------------------+
| avg(char_length(name)) |
+------------------------+
|                 9.2965 |
+------------------------+

Watch a Query with psql

Postgres’ REPL psql includes a \watch meta command that repeatedly executes a query every n seconds. Here’s the official description:

\watch [ seconds ] Repeatedly execute the current query buffer (like \g) until interrupted or the query fails. Wait the specified number of seconds (default 2) between executions.

\watch executes the current query buffer. If you aren’t sure what that query is, print it with \p:

dev=# \p
select title from posts;

Run \watch to β€˜tail’ that query result as it changes:

dev=# \watch
Sun Jul 29 17:06:13 2018 (every 2s)

        title
----------------------
 Psql Watch
(1 row)

It prints a timestamp each time it watches.

psql docs

Show materialized view definition in #postgresql

PSQL offers a handy \dv function for showing the definition of a view. That function does not seem to work on materialized views.

To see the definition of a materialized view use the following instead:

select pg_get_viewdef('search_documents');

Another option is to use \d+ search_documents which shows both the query and the columns.

(Replace search_documents with the name of your view.)

Case insensitive `in` query in #postgres #psql

If you have a list of strings and you want to query a column to get all the matching records, but you do not care about the casing, Postgres offers a cool and easy way of doing that with the citext extension.

Given this table:

id | company_name
 1 | Abibas
 2 | Nykey
 3 | Pumar

We want to match the following:

select company_name
from vendors 
where company_name in ('Abibas', 'NyKey', 'PUMAr');

First you will have to make sure you have the citext extension created if you haven’t already:

create extension citext;

Then you can cast the searched field to citext:

select company_name
from vendors 
where company_name::citext in ('Abibas', 'NyKey', 'PUMAr');

h/t joshbranchaud for helping me find this

Postgres automatically appends timezone

Dates can be notoriously hard, particularly when it comes to timezones.

If you select a date without timezone information and cast it to a data type w/timezone, Postgres will assume the timezone of the server:

select '2000-01-01'::timestamptz;
β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚      timestamptz       β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚ 2000-01-01 00:00:00-06 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

I’m located in CST hence the -06 at the end of the time specification (6 hours before UTC time).

To use a specific timezone such as UTC, instead of your server’s timezone which can be pretty arbitrary:

select '2000-01-01'::timestamptz at time zone 'UTC';
β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚      timezone       β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚ 2000-01-01 06:00:00 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Postgres now displays the time in UTC. Still probably not what you expected (6am?) - Postgres infers the 00:00:00 time in your timezone and converts it to UTC (in my case adding 6 hours).

So how would you get it to show 00:00:00 and still be in UTC timezone?

select '2000-01-01 00:00:00 UTC'::timestamptz at time zone 'UTC';
β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚      timezone       β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚ 2000-01-01 00:00:00 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

If you use timezone aware data types it is recommended to always specify the timezone when inserting data, otherwise you are in for a world of trouble.

Quick syntax reference for #SQL directly in #psql

Whenever I forget the syntax for a certain SQL command in Posgres I usually reach for Dash or simply search DuckDuckGo for the the specific command. That usually yields the Postgres official documentation website which is great…

Wouldn’t it be nice though if I could stay right inside psql and get the documentation I am looking for?

It would.. and it’s possible:

\h create index

create index screenshot

Use \h followed by the SQL command - this is not the full verbose documentation that you would find on the Postgres docs website but it’s more of a syntax reference - which is most of the time what you need.

If you are not sure what to type or simply want to explore new commands try typing \h without anything after it - you will see something like this:

slash h by itself screenshot

Postgres locale settings are inherited from OS

By default, postgres inherits locale settings from operating system, which can greatly affect sort. Comparing linux and unix, both using the locale en_US.UTF-8, we see the following sort outputs:

Unix

select name from unnest(array['No one', 'None', ' Not']) name order by name;
name
--------
 Not
No one
None

Linux

select name from unnest(array['No one', 'None', ' Not']) name order by name;
name
--------
None
No one
 Not

You’ll notice that on the linux system, whitespaces are ignored during sorting.

To get consistent behavior across operating systems, you can use the postgres provided C locale:

select name from unnest(array['No one', 'None', ' Not']) name order by name collate 'C';
name
--------
 Not
No one
None

Page and Search Through MySQL

Need to explore results in the MySQL REPL? The pager command can help.

Let’s set our pager of choice (we’ll use Less):

mysql> pager less
PAGER set to 'less'

Then, execute the query:

mysql> select * from users;

The result will be loaded in the Less pager. From here, we can navigate or search through the results.

Leave the custom pager with \n:

mysql> \n
PAGER set to stdout

List all available extensions in #Postgres

Postgres comes packed with extensions just waiting to be enabled!

To see a list of those extensions:

select * from pg_available_extensions;

This will list the extension’s name, default_version, installed_version, and the comment which is a one liner description of what the extension does.

Here’s an interesting one for example:

name              | earthdistance
default_version   | 1.1
installed_version | ΓΈ
comment           | calculate great-circle distances on the surface of the Earth

To enable an extension, simply call create extension on the name:

create extension if not exists earthdistance;

Show Create Statement For A Table In MySQL

In MySQL, you can get a quick rundown of a table using describe users. An alternative to this approach is to have MySQL show the create statement for a table.

> show create table users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(80) NOT NULL,
  `last_name` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL,
  `middle_initial` varchar(80) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This includes some additional information like primary key and index information. It is also a great way to study the SQL that it takes to create all the facets of a table.

See the show create table docs for more details.

h/t Jake Worth