BUG #14718: unable to update table with identity column GENERATED ALWAYS
The following bug has been logged on the website:
Bug reference: 14718
Logged by: zam6ak
Email address: zam6ak@gmail.com
PostgreSQL version: 10beta1
Operating system: Windows Server 2012 R2
Description:
I have downloaded a PG10 distro for Windows from BigSQL.
if I try to update table that uses identity column generated always, the
update fails...
If I switch to "generated by default" it works...
-- create tbl1
create table tbl1 (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- fails
--id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- works
name text NOT NULL
);
-- add some values
insert into tbl1 (name) values ('Tommy');
-- verify
select * from tbl1; -- 1;"Tommy"
-- now try to update
update tbl1 set name = 'Bobby' where id = 1;
ERROR: column "id" can only be updated to DEFAULT
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
********** Error **********
ERROR: column "id" can only be updated to DEFAULT
SQL state: 428C9
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 6/26/17 15:22, zam6ak@gmail.com wrote:
-- now try to update
update tbl1 set name = 'Bobby' where id = 1;ERROR: column "id" can only be updated to DEFAULT
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
This is working as expected. What is the problem?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 2017-06-30 17:03:22 -0400, Peter Eisentraut wrote:
On 6/26/17 15:22, zam6ak@gmail.com wrote:
-- now try to update
update tbl1 set name = 'Bobby' where id = 1;ERROR: column "id" can only be updated to DEFAULT
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.This is working as expected. What is the problem?
"id" is not being updated in that statement?
- Andres
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Peter Eisentraut schrieb am 30.06.2017 um 23:03:
On 6/26/17 15:22, zam6ak@gmail.com wrote:
-- now try to update
update tbl1 set name = 'Bobby' where id = 1;ERROR: column "id" can only be updated to DEFAULT
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.This is working as expected. What is the problem?
I'd say the problem is, that the id column is NOT updated in that statement, only the name.
The identity column is only included in the WHERE clause - it's not changed at all.
Surely that is not the way it's meant to be?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Thomas Kellerer <spam_eater@gmx.net> writes:
Peter Eisentraut schrieb am 30.06.2017 um 23:03:
This is working as expected. What is the problem?
I'd say the problem is, that the id column is NOT updated in that statement, only the name.
Yeah. Looks to me like the code that was added to rewriteTargetListIU
for this did not quite understand the meaning of the "apply_default"
flag.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 6/26/17 15:22, zam6ak@gmail.com wrote:
-- create tbl1
create table tbl1 (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- fails
--id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- works
name text NOT NULL
);
-- add some values
insert into tbl1 (name) values ('Tommy');
-- verify
select * from tbl1; -- 1;"Tommy"-- now try to update
update tbl1 set name = 'Bobby' where id = 1;ERROR: column "id" can only be updated to DEFAULT
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
Fix committed. Thanks for the report.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs