Обсуждение: 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
columnsfor insertion (it should obviously). In my case, this breaks all my test suite. AFAIK this is the same for other
frameworksor 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 :

> 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
columnsfor insertion (it should obviously). In my case, this breaks all my test suite. AFAIK this is the same for other
frameworksor 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