I was hoping someone could help me understand the rules around using row values and the VALUES keyword. I am having a little trouble understanding what is and isn't allowed.
Starting with a simple table containing a key, value:
create table kv (key text, val integer);
insert into kv (key, val) values ('k1', 1), ('k2', 2), ('k3', 3);
The following works with Sqlite:
select * from kv
where (key, val) = ('k1', 1);
The following seems to work in Postgres, but Sqlite is reporting "row value misused":
select * from kv
where (key, val) in (('k1', 1), ('k3', 3));
The following works in both Postgres and Sqlite:
select * from kv
where (key, val) in (values ('k1', 1), ('k3', 3));
--------
Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near "(":
select *
from kv
inner join (values ('k1', 1), ('k3', 3)) as bar("k", "v")
on kv.key = bar.k and kv.val = bar.v;
The following, unsurprising, also works in Postgres but not Sqlite:
select *
from kv, (values ('k1', 1), ('k3', 3)) as bar(k, v)
where (kv.key = bar.k AND kv.val = bar.v);
Moving it into a CTE works in both Postgres and Sqlite:
with bar(k, v) as (values ('k1', 1), ('k3', 3))
select * from kv
inner join bar
on (kv.key = bar.k and kv.val = bar.v);
---------
My questions are:
* When should one use plain row-values versus VALUES?
* Why does (key, val) = ('k1', 1) work but (key, val) IN (('k1', 1)...) not?
* Are there ways of constructing the above type of query which would be better or more idiomatic with Sqlite?
Thank you in advance for the help.