public inbox for [email protected]  

help / color / mirror / Atom feed

From: jian he <[email protected]>
To: Alvaro Herrera <[email protected]>
Cc: Tender Wang <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Can't find not null constraint, but \d+ shows that
Date: Thu, 11 Apr 2024 14:40:04 +0800
Message-ID: <CACJufxHX9d1-beCC3HaR7_DXpfjb0j2ybRJ_Av5q41N2wido_A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

On Wed, Apr 10, 2024 at 2:10 PM jian he <[email protected]> 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 <[email protected]> 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?

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Can't find not null constraint, but \d+ shows that
  In-Reply-To: <CACJufxHX9d1-beCC3HaR7_DXpfjb0j2ybRJ_Av5q41N2wido_A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox