PostgreSQL 11
What is the best way to generate default values for identity columns on partition tables.
E.g
CREATE TABLE data.log
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
INCREMENT BY 1
MINVALUE -9223372036854775808
MAXVALUE 9223372036854775807
START WITH -9223372036854775808
RESTART WITH -9223372036854775808
CYCLE
),
epoch_millis BIGINT NOT NULL,
message TEXT NOT NULL
) PARTITION BY RANGE (epoch_millis);
CREATE TABLE data.foo_log
PARTITION OF data.log
(
PRIMARY KEY (id)
)
FOR VALUES FROM (0) TO (9999999999);
If I do:
INSERT INTO data.foo_log (epoch_millis, message)
VALUES (1000000, 'hello');
I get:
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, 1000000, hello).
SQL state: 23502
because the default generated value is not applied to the partition UNLESS I insert it into the root table like this:
INSERT INTO data.log (epoch_millis, message)
VALUES (1000000, 'hello');
There are times though that I want to insert directly into a specific partition for performance reasons (like doing bulk COPY).
The only way I can get this to work is to create the partition while knowing about the sequence that was implicitly created for the identity column like this:
CREATE TABLE data.foo_log
PARTITION OF data.log
(
id DEFAULT nextval('data.log_id_seq'),
PRIMARY KEY (id)
)
FOR VALUES FROM (0) TO (9999999999);
Is there a better way to do this and if so how?
COPY
commands include theid
column and if so, should those override the default? Since the parent hasGENERATED ALWAYS
I would assume you want user values to be discarded or an exception raised? (But that's not what your current solution does.)