Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit9895b35

Browse files
committed
Fix ALTER DOMAIN NOT NULL syntax
This addresses a few problems with commite5da0fe ("Catalog domainnot-null constraints").In CREATE DOMAIN, a NOT NULL constraint looks like CREATE DOMAIN d1 AS int [ CONSTRAINT conname ] NOT NULL(Beforee5da0fe, 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 VALUEwhere VALUE is where for a table constraint the column name would be.(This works as ofe5da0fe. Beforee5da0fe, this syntaxresulted in an internal error.)But for domains, this latter syntax is confusing and needlesslyinconsistent 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 withthe bits of inconsistency that have built up over time.)In passing, this also changes the psql \dD output to not show not-nullconstraints in the column "Check", since it's already shown in thecolumn "Nullable". This has also been off sincee5da0fe.Reviewed-by: jian he <jian.universality@gmail.com>Discussion:https://www.postgresql.org/message-id/flat/9ec24d7b-633d-463a-84c6-7acff769c9e8%40eisentraut.org
1 parentd21d61b commit9895b35

File tree

6 files changed

+99
-16
lines changed

6 files changed

+99
-16
lines changed

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

Lines 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">

‎src/backend/parser/gram.y

Lines 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_setset_restset_rest_moregeneric_resetreset_rest
525525
SetResetClauseFunctionSetResetClause
526526

527-
%type<node>TableElementTypedTableElementConstraintElemTableFuncElement
527+
%type<node>TableElementTypedTableElementConstraintElemDomainConstraintElemTableFuncElement
528528
%type<node>columnDefcolumnOptionsoptionalPeriodName
529529
%type<defelt>def_elemreloption_elemold_aggr_elemoperator_def_elem
530530
%type<node>def_argcolumnElemwhere_clausewhere_or_current_clause
@@ -596,7 +596,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
596596
%type<keyword>col_name_keywordreserved_keyword
597597
%type<keyword>bare_label_keyword
598598

599-
%type<node>TableConstraintTableLikeClause
599+
%type<node>DomainConstraintTableConstraintTableLikeClause
600600
%type<ival>TableLikeOptionListTableLikeOption
601601
%type<str>column_compressionopt_column_compressioncolumn_storageopt_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+
CONSTRAINTnameDomainConstraintElem
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+
|NOTNULL_PConstraintAttributeSpec
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:NOINHERIT{$$ =true; }
43384392
|/* EMPTY*/{$$ =false; }
43394393
;
@@ -11586,7 +11640,7 @@ AlterDomainStmt:
1158611640
$$ = (Node *) n;
1158711641
}
1158811642
/* ALTER DOMAIN <domain> ADD CONSTRAINT ...*/
11589-
|ALTERDOMAIN_Pany_nameADD_PTableConstraint
11643+
|ALTERDOMAIN_Pany_nameADD_PDomainConstraint
1159011644
{
1159111645
AlterDomainStmt *n = makeNode(AlterDomainStmt);
1159211646

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

Lines 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
elseif (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
}

‎src/bin/psql/describe.c

Lines 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"),

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

Lines 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;

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

Lines 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
alterdomain con addconstraint tcheck (VALUE<34);
459459
alterdomain con addcheck (VALUE>0);
460460

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

479481
insert into domconnotnulltest defaultvalues;
480-
alterdomain connotnull addnot null value;-- fails
482+
alterdomain connotnull addnot null;-- fails
481483

482484
update domconnotnulltestset col1=5;
483-
alterdomain connotnull addnot null value;-- fails
485+
alterdomain connotnull addnot null;-- fails
484486

485487
update domconnotnulltestset col2=6;
486488

487-
alterdomain connotnull addconstraint constr1not null value;
489+
alterdomain connotnull addconstraint constr1not null;
488490
selectcount(*)from pg_constraintwhere contypid='connotnull'::regtypeand contype='n';
489-
alterdomain connotnull addconstraint constr1bisnot null value;-- redundant
491+
alterdomain connotnull addconstraint constr1bisnot null;-- redundant
490492
selectcount(*)from pg_constraintwhere contypid='connotnull'::regtypeand contype='n';
491493

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

494498
alterdomain connotnull dropconstraint constr1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp