No such column when using schema in returning clause
By Emile Fugulin (
sytten
) on 2022-05-05 19:03:52
[link]
[source]
There seems to be a limitation not listed in the documentation when using the schema name in a
RETURNING
.
Consider the following example:
CREATE TABLE test (
id integer,
name string
INSERT INTO test VALUES (1, 'testing') RETURNING main.test.id;
This currently returns an error: no such column: main.test.id
despite being a valid expression. This is not a big deal when using the main schema but it can become an important limitation when attaching another database.
Many ORM always put the full name of the column in select and returning statements since that is a behaviour supported in postgres. For example, the rust ORM Diesel has this issue.
Unless there is specific reason for not supporting it, I think it would be better to align the behaviour with postgres and support the schema name in the expression (and update the documentation limitations in the meantime).
Thanks
The RETURNING clause is only able to return columns from the table that
is being inserted into. So the "main.test." part of the column name is
pointless. Just use "id". That should be completely unambiguous.
Like I mentioned in the post, enabling this feature is primarily to play nice with ORMs that most developers use so they don't have to write an exception for SQlite and they can basically reuse the code that they already have for Postgres.
I can also reverse the argument and say why support table.column
at all as this is also "useless" currently.
Since PostgreSQL also doesn't need the qualification with schema and table name, it would be wise for all tools to not include these into the statement.
Since there is no returning clause in the standard as it is am idea of the PostgreSQL developers, it's no violation of the syntax of any dbms.