Working on a postgres-compatible query compiler has taught me many things.
Things I was better off not knowing.
Let's make a database:
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
What could possibly go wrong?
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\n"}'::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.