Working on a postgres-compatible query compiler has taught me many things.

Things I was better off not knowing.

Let's begin:

jamie=# create table nums(a int primary key, b text); CREATE TABLE jamie=# insert into nums(a,b) values (0, 'foo'), (1, 'foo'), (2, 'foo'), (3, 'bar'); INSERT 0 4

jamie=# select a+1 from nums group by a+1; ?column? ---------- 2 4 1 3 (4 rows) jamie=# select a+1 from nums group by 1+a; ERROR: column "nums.a" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select a+1 from nums group by 1+a; ^

jamie=# select a, b from nums order by 2; a | b ---+----- 3 | bar 0 | foo 1 | foo 2 | foo (4 rows) jamie=# select a, b from nums order by 1+1; a | b ---+----- 0 | foo 1 | foo 2 | foo 3 | bar (4 rows)

jamie=# (select a from nums) order by b; a --- 3 0 1 2 (4 rows) jamie=# ((select a from nums) union (select a from nums)) order by b; ERROR: column "b" does not exist LINE 1: ...(select a from nums) union (select a from nums)) order by b;

jamie=# select a+1 from nums group by "?column?"; ?column? ---------- 3 4 2 1 (4 rows)

jamie=# select b from nums group by a; b --- (0 rows) jamie=# select a from nums group by b; ERROR: column "nums.a" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select a from nums group by b;

jamie=# create table """" (x int); CREATE TABLE jamie=# \d List of relations Schema | Name | Type | Owner --------+------+-------+------- public | " | table | jamie public | nums | table | jamie (2 rows)

jamie=# select null = null; ?column? ---------- (1 row) jamie=# select array[null] = array[null]; ?column? ---------- t (1 row)

jamie=# select '{foo,bar,null}'::text[]; text ---------------- {foo,bar,NULL} (1 row)

jamie=# select '{"ABC

"}'::text[] = ARRAY['ABCn']; ?column? ---------- t (1 row)

jamie=# select array_dims(array['a', 'a'] :: text[]); array_dims ------------ [1:2] (1 row) jamie=# select array_dims(array['a'] :: text[]); array_dims ------------ [1:1] (1 row) jamie=# select array_dims(array[] :: text[]); array_dims ------------ (1 row)

jamie=# create table users ("user" text, "password" text); CREATE TABLE jamie=# insert into users values ('bob', 'whatever'); INSERT 0 1 jamie=# select user, password from users; user | password -------+---------- jamie | whatever (1 row)

jamie=# select 1, generate_series(1,2), generate_series(1,3); ?column? | generate_series | generate_series ----------+-----------------+----------------- 1 | 1 | 1 1 | 2 | 2 1 | | 3 (3 rows)

jamie=# select generate_series(generate_series(1,2), generate_series(3,100)); generate_series ----------------- 1 2 3 2 3 4 (6 rows)

jamie=# select jsonb_array_elements(jsonb_array_elements('[[1,2],[3,4]]')), jsonb_array_elements('[7,8,9]'); jsonb_array_elements | jsonb_array_elements ----------------------+---------------------- 1 | 7 2 | 7 3 | 8 4 | 8 (4 rows)

jamie=# select ('[["foo"]]'::jsonb)->>0; ?column? ---------- ["foo"] (1 row) jamie=# select ('["foo"]'::jsonb)->>0; ?column? ---------- foo (1 row) jamie=# select ('"foo"'::jsonb)->>0; ?column? ---------- foo (1 row)

jamie=# select ('1'::jsonb)::int; int4 ------ 1 (1 row) jamie=# select ('true'::jsonb)::bool; bool ------ t (1 row) jamie=# select ('"foo"'::jsonb)::text; text ------- "foo" (1 row) jamie=# select ('1'::jsonb)->>0; ?column? ---------- 1 (1 row) jamie=# select ('true'::jsonb)->>0; ?column? ---------- true (1 row) jamie=# select ('"foo"'::jsonb)->>0; ?column? ---------- foo (1 row)

Thanks to Nikhil Benesch, Justin Jaffray and several decades of "wouldn't it be nice if..." for contributions.

There will likely be a sequel.