Select Wat from SQL, Hacker News

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  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, 
 );  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.