Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 9895b35

Browse filesBrowse files
peterepetere
committed
Fix ALTER DOMAIN NOT NULL syntax
This addresses a few problems with commit e5da0fe ("Catalog domain not-null constraints"). In CREATE DOMAIN, a NOT NULL constraint looks like CREATE DOMAIN d1 AS int [ CONSTRAINT conname ] NOT NULL (Before e5da0fe, the constraint name was accepted but ignored.) But in ALTER DOMAIN, a NOT NULL constraint looks like ALTER DOMAIN d1 ADD [ CONSTRAINT conname ] NOT NULL VALUE where VALUE is where for a table constraint the column name would be. (This works as of e5da0fe. Before e5da0fe, this syntax resulted in an internal error.) But for domains, this latter syntax is confusing and needlessly inconsistent between CREATE and ALTER. So this changes it to just ALTER DOMAIN d1 ADD [ CONSTRAINT conname ] NOT NULL (None of these syntaxes are per SQL standard; we are just living with the bits of inconsistency that have built up over time.) In passing, this also changes the psql \dD output to not show not-null constraints in the column "Check", since it's already shown in the column "Nullable". This has also been off since e5da0fe. Reviewed-by: jian he <jian.universality@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/9ec24d7b-633d-463a-84c6-7acff769c9e8%40eisentraut.org
1 parent d21d61b commit 9895b35
Copy full SHA for 9895b35

File tree

Expand file treeCollapse file tree

6 files changed

+99
-16
lines changed
Open diff view settings
Filter options
Expand file treeCollapse file tree

6 files changed

+99
-16
lines changed
Open diff view settings
Collapse file

‎doc/src/sgml/ref/create_domain.sgml‎

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/create_domain.sgml
+14-3Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -24,9 +24,9 @@ PostgreSQL documentation
2424
CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
2525
[ COLLATE <replaceable>collation</replaceable> ]
2626
[ DEFAULT <replaceable>expression</replaceable> ]
27-
[ <replaceable class="parameter">constraint</replaceable> [ ... ] ]
27+
[ <replaceable class="parameter">domain_constraint</replaceable> [ ... ] ]
2828

29-
<phrase>where <replaceable class="parameter">constraint</replaceable> is:</phrase>
29+
<phrase>where <replaceable class="parameter">domain_constraint</replaceable> is:</phrase>
3030

3131
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
3232
{ NOT NULL | NULL | CHECK (<replaceable class="parameter">expression</replaceable>) }
@@ -190,7 +190,7 @@ CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replacea
190190
</variablelist>
191191
</refsect1>
192192

193-
<refsect1>
193+
<refsect1 id="sql-createdomain-notes">
194194
<title>Notes</title>
195195

196196
<para>
@@ -279,6 +279,17 @@ CREATE TABLE us_snail_addy (
279279
The command <command>CREATE DOMAIN</command> conforms to the SQL
280280
standard.
281281
</para>
282+
283+
<para>
284+
The syntax <literal>NOT NULL</literal> in this command is a
285+
<productname>PostgreSQL</productname> extension. (A standard-conforming
286+
way to write the same would be <literal>CHECK (VALUE IS NOT
287+
NULL)</literal>. However, per <xref linkend="sql-createdomain-notes"/>,
288+
such constraints are best avoided in practice anyway.) The
289+
<literal>NULL</literal> <quote>constraint</quote> is a
290+
<productname>PostgreSQL</productname> extension (see also <xref
291+
linkend="sql-createtable-compatibility"/>).
292+
</para>
282293
</refsect1>
283294

284295
<refsect1 id="sql-createdomain-see-also">
Collapse file

‎src/backend/parser/gram.y‎

Copy file name to clipboardExpand all lines: src/backend/parser/gram.y
+57-3Lines changed: 57 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -524,7 +524,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
524524
%type <vsetstmt> generic_set set_rest set_rest_more generic_reset reset_rest
525525
SetResetClause FunctionSetResetClause
526526

527-
%type <node> TableElement TypedTableElement ConstraintElem TableFuncElement
527+
%type <node> TableElement TypedTableElement ConstraintElem DomainConstraintElem TableFuncElement
528528
%type <node> columnDef columnOptions optionalPeriodName
529529
%type <defelt> def_elem reloption_elem old_aggr_elem operator_def_elem
530530
%type <node> def_arg columnElem where_clause where_or_current_clause
@@ -596,7 +596,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
596596
%type <keyword> col_name_keyword reserved_keyword
597597
%type <keyword> bare_label_keyword
598598

599-
%type <node> TableConstraint TableLikeClause
599+
%type <node> DomainConstraint TableConstraint TableLikeClause
600600
%type <ival> TableLikeOptionList TableLikeOption
601601
%type <str> column_compression opt_column_compression column_storage opt_column_storage
602602
%type <list> ColQualList
@@ -4334,6 +4334,60 @@ ConstraintElem:
43344334
}
43354335
;
43364336

4337+
/*
4338+
* DomainConstraint is separate from TableConstraint because the syntax for
4339+
* NOT NULL constraints is different. For table constraints, we need to
4340+
* accept a column name, but for domain constraints, we don't. (We could
4341+
* accept something like NOT NULL VALUE, but that seems weird.) CREATE DOMAIN
4342+
* (which uses ColQualList) has for a long time accepted NOT NULL without a
4343+
* column name, so it makes sense that ALTER DOMAIN (which uses
4344+
* DomainConstraint) does as well. None of these syntaxes are per SQL
4345+
* standard; we are just living with the bits of inconsistency that have built
4346+
* up over time.
4347+
*/
4348+
DomainConstraint:
4349+
CONSTRAINT name DomainConstraintElem
4350+
{
4351+
Constraint *n = castNode(Constraint, $3);
4352+
4353+
n->conname = $2;
4354+
n->location = @1;
4355+
$$ = (Node *) n;
4356+
}
4357+
| DomainConstraintElem { $$ = $1; }
4358+
;
4359+
4360+
DomainConstraintElem:
4361+
CHECK '(' a_expr ')' ConstraintAttributeSpec
4362+
{
4363+
Constraint *n = makeNode(Constraint);
4364+
4365+
n->contype = CONSTR_CHECK;
4366+
n->location = @1;
4367+
n->raw_expr = $3;
4368+
n->cooked_expr = NULL;
4369+
processCASbits($5, @5, "CHECK",
4370+
NULL, NULL, &n->skip_validation,
4371+
&n->is_no_inherit, yyscanner);
4372+
n->initially_valid = !n->skip_validation;
4373+
$$ = (Node *) n;
4374+
}
4375+
| NOT NULL_P ConstraintAttributeSpec
4376+
{
4377+
Constraint *n = makeNode(Constraint);
4378+
4379+
n->contype = CONSTR_NOTNULL;
4380+
n->location = @1;
4381+
n->keys = list_make1(makeString("value"));
4382+
/* no NOT VALID support yet */
4383+
processCASbits($3, @3, "NOT NULL",
4384+
NULL, NULL, NULL,
4385+
&n->is_no_inherit, yyscanner);
4386+
n->initially_valid = true;
4387+
$$ = (Node *) n;
4388+
}
4389+
;
4390+
43374391
opt_no_inherit: NO INHERIT { $$ = true; }
43384392
| /* EMPTY */ { $$ = false; }
43394393
;
@@ -11586,7 +11640,7 @@ AlterDomainStmt:
1158611640
$$ = (Node *) n;
1158711641
}
1158811642
/* ALTER DOMAIN <domain> ADD CONSTRAINT ... */
11589-
| ALTER DOMAIN_P any_name ADD_P TableConstraint
11643+
| ALTER DOMAIN_P any_name ADD_P DomainConstraint
1159011644
{
1159111645
AlterDomainStmt *n = makeNode(AlterDomainStmt);
1159211646

Collapse file

‎src/backend/utils/adt/ruleutils.c‎

Copy file name to clipboardExpand all lines: src/backend/utils/adt/ruleutils.c
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2523,7 +2523,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
25232523
else if (conForm->contypid)
25242524
{
25252525
/* conkey is null for domain not-null constraints */
2526-
appendStringInfoString(&buf, "NOT NULL VALUE");
2526+
appendStringInfoString(&buf, "NOT NULL");
25272527
}
25282528
break;
25292529
}
Collapse file

‎src/bin/psql/describe.c‎

Copy file name to clipboardExpand all lines: src/bin/psql/describe.c
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4449,7 +4449,7 @@ listDomains(const char *pattern, bool verbose, bool showSystem)
44494449
" CASE WHEN t.typnotnull THEN 'not null' END as \"%s\",\n"
44504450
" t.typdefault as \"%s\",\n"
44514451
" pg_catalog.array_to_string(ARRAY(\n"
4452-
" SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
4452+
" SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid AND r.contype = 'c'\n"
44534453
" ), ' ') as \"%s\"",
44544454
gettext_noop("Schema"),
44554455
gettext_noop("Name"),
Collapse file

‎src/test/regress/expected/domain.out‎

Copy file name to clipboardExpand all lines: src/test/regress/expected/domain.out
+18-4Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -785,6 +785,13 @@ alter domain con add constraint t check (VALUE < 1); -- fails
785785
ERROR: column "col1" of table "domcontest" contains values that violate the new constraint
786786
alter domain con add constraint t check (VALUE < 34);
787787
alter domain con add check (VALUE > 0);
788+
\dD con
789+
List of domains
790+
Schema | Name | Type | Collation | Nullable | Default | Check
791+
--------+------+---------+-----------+----------+---------+--------------------------------------
792+
public | con | integer | | | | CHECK (VALUE < 34) CHECK (VALUE > 0)
793+
(1 row)
794+
788795
insert into domcontest values (-5); -- fails
789796
ERROR: value for domain con violates check constraint "con_check"
790797
insert into domcontest values (42); -- fails
@@ -805,26 +812,33 @@ create table domconnotnulltest
805812
, col2 connotnull
806813
);
807814
insert into domconnotnulltest default values;
808-
alter domain connotnull add not null value; -- fails
815+
alter domain connotnull add not null; -- fails
809816
ERROR: column "col1" of table "domconnotnulltest" contains null values
810817
update domconnotnulltest set col1 = 5;
811-
alter domain connotnull add not null value; -- fails
818+
alter domain connotnull add not null; -- fails
812819
ERROR: column "col2" of table "domconnotnulltest" contains null values
813820
update domconnotnulltest set col2 = 6;
814-
alter domain connotnull add constraint constr1 not null value;
821+
alter domain connotnull add constraint constr1 not null;
815822
select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n';
816823
count
817824
-------
818825
1
819826
(1 row)
820827

821-
alter domain connotnull add constraint constr1bis not null value; -- redundant
828+
alter domain connotnull add constraint constr1bis not null; -- redundant
822829
select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n';
823830
count
824831
-------
825832
1
826833
(1 row)
827834

835+
\dD connotnull
836+
List of domains
837+
Schema | Name | Type | Collation | Nullable | Default | Check
838+
--------+------------+---------+-----------+----------+---------+-------
839+
public | connotnull | integer | | not null | |
840+
(1 row)
841+
828842
update domconnotnulltest set col1 = null; -- fails
829843
ERROR: domain connotnull does not allow null values
830844
alter domain connotnull drop constraint constr1;
Collapse file

‎src/test/regress/sql/domain.sql‎

Copy file name to clipboardExpand all lines: src/test/regress/sql/domain.sql
+8-4Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -458,6 +458,8 @@ alter domain con add constraint t check (VALUE < 1); -- fails
458458
alter domain con add constraint t check (VALUE < 34);
459459
alter domain con add check (VALUE > 0);
460460

461+
\dD con
462+
461463
insert into domcontest values (-5); -- fails
462464
insert into domcontest values (42); -- fails
463465
insert into domcontest values (5);
@@ -477,18 +479,20 @@ create table domconnotnulltest
477479
);
478480

479481
insert into domconnotnulltest default values;
480-
alter domain connotnull add not null value; -- fails
482+
alter domain connotnull add not null; -- fails
481483

482484
update domconnotnulltest set col1 = 5;
483-
alter domain connotnull add not null value; -- fails
485+
alter domain connotnull add not null; -- fails
484486

485487
update domconnotnulltest set col2 = 6;
486488

487-
alter domain connotnull add constraint constr1 not null value;
489+
alter domain connotnull add constraint constr1 not null;
488490
select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n';
489-
alter domain connotnull add constraint constr1bis not null value; -- redundant
491+
alter domain connotnull add constraint constr1bis not null; -- redundant
490492
select count(*) from pg_constraint where contypid = 'connotnull'::regtype and contype = 'n';
491493

494+
\dD connotnull
495+
492496
update domconnotnulltest set col1 = null; -- fails
493497

494498
alter domain connotnull drop constraint constr1;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.