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 4jamie=# 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.
Read More Full coverage and live updates on the Coronavirus (Covid- 100
GIPHY App Key not set. Please check settings