You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
It has been observed that the generation of INSERT statements fails under certain circumstances when using PostgreSQL.
In particular, when it is used with a table that has a column with the same name as the table.
The observation was made in the context of a TG entity PayrollCode which has property payrollCode. Incidentally, both the entity and the property had been mapped to the same name on the DB level: payrollcode_.
Invocation of the INSERT generating procedure produced the following error:
[322] ERROR: malformed record literal: "Placeholder"
[322] DETAIL: Missing left parenthesis.
[322] CONTEXT: SQL statement "SELECT format('%L', ((($1)::payrollcode_)._id))"
PL/pgSQL function create_insert_statement(regclass,anyelement) line 24 at EXECUTE
In general, an SQL statement invoking the generation of INSERTs look like so:
SELECT create_insert_statements(tableoid, mytable) FROM mytable;
Function create_insert_statements is called with 2 arguments:
tableoid -- a special value from the system column tableoid of mytable
mytable -- context-dependent:
a. if mytable has a column named mytable, then that column will be used
b. otherwise, the table itself will be used (this is the desired outcome)
It can be seen that the meaning of the 2nd argument is context-sensitive, leading to an undesired
ambiguity. Fortunately, the ambiguity can be avoided by explicitly referring to the table in
question, which can be accomplished in several ways:
aliasing
SELECT create_insert_statements(tableoid, t) FROM mytable as t;
This has the disadvantage that additional effort is required to ensure the name used for the alias
is unique.
homedirectory
changed the title
PostgreSQL: Insert statement generation breaks for tables that have a column with the same as the table
PostgreSQL: Insert statement generation breaks for tables that have a column with the same name as the table
Apr 22, 2024
Description
It has been observed that the generation of
INSERT
statements fails under certain circumstances when using PostgreSQL.In particular, when it is used with a table that has a column with the same name as the table.
The observation was made in the context of a TG entity
PayrollCode
which has propertypayrollCode
. Incidentally, both the entity and the property had been mapped to the same name on the DB level:payrollcode_
.Invocation of the
INSERT
generating procedure produced the following error:In general, an SQL statement invoking the generation of
INSERT
s look like so:Function
create_insert_statements
is called with 2 arguments:tableoid
-- a special value from the system columntableoid
ofmytable
mytable
-- context-dependent:a. if
mytable
has a column namedmytable
, then that column will be usedb. otherwise, the table itself will be used (this is the desired outcome)
It can be seen that the meaning of the 2nd argument is context-sensitive, leading to an undesired
ambiguity. Fortunately, the ambiguity can be avoided by explicitly referring to the table in
question, which can be accomplished in several ways:
This has the disadvantage that additional effort is required to ensure the name used for the alias
is unique.
mytable.*
From the PostgreSQL documentation (
SELECT
List):Expected outcome
Generation of insert statements does not break due to the described edge case.
The text was updated successfully, but these errors were encountered: