Thread: COPY command on a table column marked as GENERATED ALWAYS
Hi All, I'm able to insert data into a table column marked as GENERATED ALWAYS using COPY command however, it fails with INSERT command. Isn't that a bug with COPY command? Here is the test-case for more clarity. postgres=# create table tab_always (i int generated always as identity, j int); CREATE TABLE postgres=# insert into tab_always values(1, 10); ERROR: cannot insert into column "i" DETAIL: Column "i" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override. [ashu@localhost bin]$ cat /tmp/always.csv 13 10 14 20 15 30 16 40 postgres=# copy tab_always from '/tmp/always.csv'; COPY 4 postgres=# select * from tab_always; i | j ----+---- 13 | 10 14 | 20 15 | 30 16 | 40 (4 rows) -- With Regards, Ashutosh Sharma EnterpriseDB:http://www.enterprisedb.com
On Fri, Jul 26, 2019 at 03:12:28PM +0530, Ashutosh Sharma wrote: > Hi All, > > I'm able to insert data into a table column marked as GENERATED ALWAYS > using COPY command however, it fails with INSERT command. Isn't that a > bug with COPY command? Per the documentation in the section for GENERATED ALWAYS: https://www.postgresql.org/docs/devel/sql-createtable.html "The clauses ALWAYS and BY DEFAULT determine how the sequence value is given precedence over a user-specified value in an INSERT statement. If ALWAYS is specified, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is specified, then the user-specified value takes precedence. See INSERT for details. (In the COPY command, user-specified values are always used regardless of this setting.)" So it behaves as documented. -- Michael
Attachment
On Mon, Jul 29, 2019 at 7:27 AM Michael Paquier <michael@paquier.xyz> wrote: > > On Fri, Jul 26, 2019 at 03:12:28PM +0530, Ashutosh Sharma wrote: > > Hi All, > > > > I'm able to insert data into a table column marked as GENERATED ALWAYS > > using COPY command however, it fails with INSERT command. Isn't that a > > bug with COPY command? > > Per the documentation in the section for GENERATED ALWAYS: > https://www.postgresql.org/docs/devel/sql-createtable.html > > "The clauses ALWAYS and BY DEFAULT determine how the sequence value is > given precedence over a user-specified value in an INSERT > statement. If ALWAYS is specified, a user-specified value is only > accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If > BY DEFAULT is specified, then the user-specified value takes > precedence. See INSERT for details. (In the COPY command, > user-specified values are always used regardless of this setting.)" > > So it behaves as documented. Okay, Thanks for the pointer! -- With Regards, Ashutosh Sharma EnterpriseDB:http://www.enterprisedb.com