BUG #16441: Cannot multi-insert into generated column with DEFAULT value
The following bug has been logged on the website:
Bug reference: 16441
Logged by: Colin Darie
Email address: colin@darie.eu
PostgreSQL version: 12.3
Operating system: macos
Description:
Multiple inserts of DEFAULT value for generated columns are failing. Single
inserts are OK.
CREATE TABLE squares (num INTEGER NOT NULL, square INTEGER GENERATED ALWAYS
AS (num * num) STORED);
INSERT INTO squares(num, square) VALUES (1, DEFAULT);
=> INSERT 0 1
INSERT INTO squares(num, square) VALUES (2, DEFAULT), (3, DEFAULT);
=> ERROR: cannot insert into column "square"
=> DETAIL: Column "square" is a generated column.
Tested with postgresql 12.2 and 12.3 on macos.
On 2020-05-15 23:07, PG Bug reporting form wrote:
Multiple inserts of DEFAULT value for generated columns are failing. Single
inserts are OK.CREATE TABLE squares (num INTEGER NOT NULL, square INTEGER GENERATED ALWAYS
AS (num * num) STORED);INSERT INTO squares(num, square) VALUES (1, DEFAULT);
=> INSERT 0 1
INSERT INTO squares(num, square) VALUES (2, DEFAULT), (3, DEFAULT);
=> ERROR: cannot insert into column "square"
=> DETAIL: Column "square" is a generated column.
Yes, this has been reported before. This is basically just not
implemented. Is there a reason you need to use this form, or are you
just trying things out?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Rails test framework insert fixtures records this way, and there is currently no way to tell Rails to ignore these columns for insertion (it should obviously). In my case, this breaks all my test suite. AFAIK this is the same for other frameworks or batch records tools.
Colin Darie
Sent with ProtonMail Secure Email.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
Le lundi 18 mai 2020 16:57, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> a écrit :
Show quoted text
On 2020-05-15 23:07, PG Bug reporting form wrote:
Multiple inserts of DEFAULT value for generated columns are failing. Single
inserts are OK.
CREATE TABLE squares (num INTEGER NOT NULL, square INTEGER GENERATED ALWAYS
AS (num * num) STORED);
INSERT INTO squares(num, square) VALUES (1, DEFAULT);
=> INSERT 0 1
INSERT INTO squares(num, square) VALUES (2, DEFAULT), (3, DEFAULT);
=> ERROR: cannot insert into column "square"
=> DETAIL: Column "square" is a generated column.Yes, this has been reported before. This is basically just not
implemented. Is there a reason you need to use this form, or are you
just trying things out?--------------------------------------------------------------------------------------------------------------------------------------------------------------
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2020-05-18 17:10, Colin Darie wrote:
Rails test framework insert fixtures records this way, and there is currently no way to tell Rails to ignore these columns for insertion (it should obviously). In my case, this breaks all my test suite. AFAIK this is the same for other frameworks or batch records tools.
OK, that makes sense. I'll take another look.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services