The following bug has been logged on the website:
Bug reference: 14952
Logged by: Steven Winfield
Email address: steven.winfield@cantabcapital.com
PostgreSQL version: 10.0
Operating system: Linux
Description:
COPYing data to a table with an IDENTITY column, where the column's value
isn't specified in the copied input, fails because COPY attempts to insert a
NULL value for the column:
test=# CREATE TABLE identity_test (id bigint GENERATED ALWAYS AS IDENTITY,
name text);
test=# COPY identity_test (name) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> foo
>> \.
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, foo).
CONTEXT: COPY identity_test, line 1: "foo"
Compare this with a serial column:
test=# CREATE TABLE serial_test (id bigserial, name text);
test=# COPY serial_test (name) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> foo
>> \.
COPY 1
test=# \d identity_test
Table "public.identity_test"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+------------------------------
id | bigint | | not null | generated always as identity
name | text | | |
test=# \d serial_test
Table "public.serial_test"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+-----------------------------------------
id | bigint | | not null |
nextval('serial_test_id_seq'::regclass)
name | text | | |