BUG #14952: COPY fails to fill in IDENTITY column default value

From: steven(dot)winfield(at)cantabcapital(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Cc: steven(dot)winfield(at)cantabcapital(dot)com
Subject: BUG #14952: COPY fails to fill in IDENTITY column default value
Date: 2017-12-07 11:29:30
Message-ID: 20171207112930.21696.79944@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14952
Logged by: Steven Winfield
Email address: steven(dot)winfield(at)cantabcapital(dot)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 | | |

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jaroslav Urik 2017-12-07 13:36:15 Re: BUG #14949: array_append() - performance issues (in update)
Previous Message Jan Schulz 2017-12-07 09:54:20 Re: BUG #14948: cost overflow