Observed an issue in CREATE TABLE syntax
I observed an issue that even if invalid syntax is provided for CREATE TABLE, table is getting created successfully.
Below table creation succeed even though same constraint name is given multiple times.
None of the below constraints has any meaning of giving multiple times.
postgres=# create table new (id int NULL NULL);
CREATE TABLE
postgres=# create table new1(id serial NOT NULL NOT NULL);
CREATE TABLE
postgres=# create table new2 (id int unique unique);
CREATE TABLE
Should we not throw error for above syntaxes?
Please find the attached patch with the fix.
Thanks and Regards,
Kumar Rajeev Rastogi
Attachments:
multiconstissuev1.patchapplication/octet-stream; name=multiconstissuev1.patchDownload
*** a/src/backend/parser/parse_utilcmd.c
--- b/src/backend/parser/parse_utilcmd.c
***************
*** 298,303 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
--- 298,304 ----
bool saw_default;
Constraint *constraint;
ListCell *clist;
+ int num_not_null;
cxt->columns = lappend(cxt->columns, column);
***************
*** 469,474 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
--- 470,476 ----
saw_nullable = false;
saw_default = false;
+ num_not_null = 0;
foreach(clist, column->constraints)
{
***************
*** 478,487 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
switch (constraint->contype)
{
case CONSTR_NULL:
! if (saw_nullable && column->is_not_null)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("conflicting NULL/NOT NULL declarations for column \"%s\" of table \"%s\"",
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
--- 480,489 ----
switch (constraint->contype)
{
case CONSTR_NULL:
! if (saw_nullable)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("conflicting/multiple NULL/NOT NULL declarations for column \"%s\" of table \"%s\"",
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
***************
*** 490,503 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
break;
case CONSTR_NOTNULL:
! if (saw_nullable && !column->is_not_null)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("conflicting NULL/NOT NULL declarations for column \"%s\" of table \"%s\"",
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
column->is_not_null = TRUE;
saw_nullable = true;
break;
--- 492,515 ----
break;
case CONSTR_NOTNULL:
! /*
! * Dont throw error for case tbl(id serial not null). For
! * serial type, a NOT NULL constraint is implicitly added.
! * So we should not stop user to give another NOT NULL.
! * So in below check second part of condition is to handle
! * this situation only.
! */
! if (saw_nullable && (!is_serial || (!column->is_not_null ||
! num_not_null > 1)))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("conflicting/multiple NULL/NOT NULL declarations for column \"%s\" of table \"%s\"",
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
+
column->is_not_null = TRUE;
+ num_not_null++;
saw_nullable = true;
break;
***************
*** 1436,1441 **** transformIndexConstraints(CreateStmtContext *cxt)
--- 1448,1464 ----
index->deferrable == priorindex->deferrable &&
index->initdeferred == priorindex->initdeferred)
{
+ /*
+ * Primary Key is considered to be explicit unique also, so
+ * if user has given primary and unique both, then it should be
+ * not be considered as repeatative unique constraints.
+ */
+ if ((!priorindex->primary && !index->primary)
+ && (priorindex->unique && index->unique))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("multiple UNIQUE constraints for a column not allowed")));
+
priorindex->unique |= index->unique;
/*
Rajeev rastogi <rajeev.rastogi@huawei.com> writes:
Should we not throw error for above syntaxes?
No. There's nothing wrong with those statements, and complaining about
them will accomplish nothing except to break applications that used to
work. Admittedly, code that generates such declarations would be a bit
sloppy, but people won't thank us for breaking it.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers