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
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