SQLite Forum

SQLite with insert returning not working as in PostgreSQL
Login

SQLite with insert returning not working as in PostgreSQL

(1) By Domingo (mingodad) on 2021-02-04 09:26:26

Testing the recently welcome added "returning" clause for inserts with this query that does work in PostgreSQL but doesn't in SQLite:

====
create table test(id integer primary key, data text);

with insdata as (insert into test(data) values('one') returning id)
select * from insdata;
====

Is this a limitation of the current implementation ?

(2) By Stephan Beal (stephan) on 2021-02-04 09:31:19 in reply to 1 [link]

> Is this a limitation of the current implementation ?

See [](/forumpost/bd948b3b89).

(3) By anonymous on 2021-02-04 09:41:51 in reply to 2 [link]

Except that thread somehow couldn't get the same SQL to work in PostgreSQL.  
While here, Domingo claims this is working fine in PostgreSQL, and SQLite  
does strive for compatibility in that area. Thus that matters.

But indeed, as long as divergence and limitations are clearly documented,  
it's completely OK for Richard to reduce the surface area of this new feature,  
which is also a welcome addition from my end.

If Richard could actually explain how it works, and more precisely how that  
*magic trigger* allows gaining `RETURNING` support, I'd be glad. I know he's  
a super busy person, but I really wish for more deep dives from him on SQLite  
subjects, like SQLite internals. He is such an interesting and amazing mind.