On Wed, Apr 10, 2024 at 2:10 PM jian he <jian.universal...@gmail.com> wrote: > > DROP TABLE if exists notnull_tbl2; > CREATE TABLE notnull_tbl2 (c0 int generated by default as IDENTITY, c1 int); > ALTER TABLE notnull_tbl2 ADD CONSTRAINT Q PRIMARY KEY(c0, c1); > ALTER TABLE notnull_tbl2 DROP CONSTRAINT notnull_tbl2_c0_not_null; > ALTER TABLE notnull_tbl2 DROP c1; > \d notnull_tbl2
> ALTER TABLE notnull_tbl2 DROP CONSTRAINT notnull_tbl2_c0_not_null; per above sequence execution order, this should error out? otherwise which "not null" (attribute|constraint) to anchor "generated by default as identity" not null property? "DROP c1" will drop the not null property for "c0" and "c1". if "DROP CONSTRAINT notnull_tbl2_c0_not_nul" not error out, then " ALTER TABLE notnull_tbl2 DROP c1;" should either error out or transform "c0" from "c0 int generated by default as identity" to "c0 int" On Thu, Apr 11, 2024 at 1:23 AM Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > > On 2024-Apr-10, Alvaro Herrera wrote: > > > One thing missing here is pg_dump support. If you just dump this table, > > it'll end up with no constraint at all. That's obviously bad, so I > > propose we have pg_dump add a regular NOT NULL constraint for those, to > > avoid perpetuating the weird situation further. > > Here's another crude patchset, this time including the pg_dump aspect. > +DROP TABLE notnull_tbl1; +-- make sure attnotnull is reset correctly when a PK is dropped indirectly +CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1)); +ALTER TABLE notnull_tbl1 DROP c1; +\d+ notnull_tbl1 + Table "public.notnull_tbl1" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + c0 | integer | | not null | | plain | | + this is not what we expected? "not null" for "c0" now should be false? am I missing something?
