The following bug has been logged on the website:
Bug reference: 15212
Logged by: Jürgen Strobel
Email address: juergen+postgresql@strobel.info
PostgreSQL version: 10.4
Operating system: Debian
Description:
I found unexpected behavior when playing around with declarative
partitioning.
First, any way to define defaults on (child) partition tables is silently
ignored when inserting into the master table, but not when inserting into
the child table. The easiest way to reproduce this is:
jue=> create table ptest (a int, b int) partition by list (a);
CREATE TABLE
jue=> create table ptest1 partition of ptest (b default 7) for values in
(1);
CREATE TABLE
jue=> insert into ptest (a) values (1);
INSERT 0 1
jue=> table ptest;
a | b
---+---
1 |
(1 row)
jue=> insert into ptest1 (a) values (1);
INSERT 0 1
jue=> table ptest;
a | b
---+---
1 |
1 | 7
(2 rows)
Second, this is a way to violate a NOT NULL constraint, presumably because a
default value should be applied later but isn't:
jue=> create table ptest (a int, b int not null) partition by list (a);
CREATE TABLE
jue=> create table ptest1 partition of ptest (b default 7) for values in
(1);
CREATE TABLE
jue=> insert into ptest (a) values (1);
INSERT 0 1
jue=> select * from ptest where b is null;
a | b
---+---
1 |
(1 row)
The same happens for defaults using nextval(sequence), either if specified
directly or as SERIAL columns with ALTER TABLE ... ATTACH PARTITION. My
current workaround is to use a before-row trigger to apply the default
value.
