Re: not null constraints, again - Mailing list pgsql-hackers

> > We only have this Synopsis
> > ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
>
> Yeah, this syntax is intended to add a "normal" not-null constraint,
> i.e. one that inherits.
>
> > --tests from src/test/regress/sql/inherit.sql
> > CREATE TABLE inh_nn_parent (a int, NOT NULL a NO INHERIT);
> > ALTER TABLE inh_nn_parent ALTER a SET NOT NULL;
> > current fail at ATExecSetNotNull
> > ERROR:  cannot change NO INHERIT status of NOT NULL constraint
> > "inh_nn_parent_a_not_null" on relation "inh_nn_parent"
>
> This is correct, because here you want a normal not-null constraint but
> the table already has the weird ones that don't inherit.
>

i found a case,that in a sense kind of support to make it a no-op.
no-op means, if this attribute is already not-null, ALTER column SET NOT NULL;
won't have any effect.
or maybe there is a bug somewhere.

drop table if exists pp1;
create table pp1 (f1 int not null no inherit);
ALTER TABLE pp1 ALTER f1 SET NOT NULL;
ALTER TABLE ONLY pp1 ALTER f1 SET NOT NULL;

There is no child table, no partition, just a single regular table.
so, in this case, with or without ONLY should behave the same?
now "ALTER TABLE ONLY" works, "ALTER TABLE" error out.

per sql-altertable.html:
name
The name (optionally schema-qualified) of an existing table to alter.
If ONLY is specified before the table name, only that table is
altered. If ONLY is not specified, the table and all its descendant
tables (if any) are altered.




diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index 93b3f664f2..57c4ecd93a 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -77,6 +77,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } |
UNLOGGED ] TABLE [ IF NOT EXI

 [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
 { CHECK ( <replaceable class="parameter">expression</replaceable> ) [
NO INHERIT ] |
+  NOT NULL <replaceable class="parameter">column_name</replaceable> [
NO INHERIT ] |
   UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable
class="parameter">column_name</replaceable> [, ... ] ) <replaceable
class="parameter">index_parameters</replaceable> |
   PRIMARY KEY ( <replaceable
class="parameter">column_name</replaceable> [, ... ] ) <replaceable
class="parameter">index_parameters</replaceable> |
   EXCLUDE [ USING <replaceable
class="parameter">index_method</replaceable> ] ( <replaceable
class="parameter">exclude_element</replaceable> WITH <replaceable
class="parameter">operator</replaceable> [, ... ] ) <replaceable
class="parameter">index_parameters</replaceable> [ WHERE (
<replaceable class="parameter">predicate</replaceable> ) ] |

we can
create table pp1 (f1 int not null no inherit);
create table pp1 (f1 int, constraint nn not null f1 no inherit);

"NO INHERIT" should be applied for column_constraint and table_constraint?