*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
***************
*** 37,42 **** ALTER TABLE name
--- 37,43 ----
ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
+ ALTER [ COLUMN ] column ADD column_constraint
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] )
***************
*** 145,150 **** ALTER TABLE name
--- 146,164 ----
+ ADD column_constraint [, ...]
+
+
+ This form adds new column constraints, using the same syntax as
+ . Note: while it is possible to specify
+ names for NOT NULL constraints this way, they are
+ currently discarded silently.
+
+
+
+
+
+
SET/DROP NOT NULL
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 2655,2660 **** AlterTableGetLockLevel(List *cmds)
--- 2655,2661 ----
* These subcommands affect write operations only.
*/
case AT_ColumnDefault:
+ case AT_ColumnConstraint:
case AT_ProcessedConstraint: /* becomes AT_AddConstraint */
case AT_AddConstraintRecurse: /* becomes AT_AddConstraint */
case AT_EnableTrig:
***************
*** 2853,2858 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
--- 2854,2865 ----
/* No command-specific prep needed */
pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP;
break;
+ case AT_ColumnConstraint: /* ALTER COLUMN ADD CONSTRAINT */
+ /* should have been discarded by transformAlterTableStmt */
+ pass = AT_PASS_ADD_CONSTR; /* keep compiler quiet */
+ elog(ERROR, "unrecognized alter table type: %d",
+ (int) cmd->subtype);
+ break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
***************
*** 3106,3111 **** ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
--- 3113,3123 ----
case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */
ATExecColumnDefault(rel, cmd->name, cmd->def, lockmode);
break;
+ case AT_ColumnConstraint: /* ALTER COLUMN ADD CONSTRAINT */
+ /* should have been discarded by transformAlterTableStmt */
+ elog(ERROR, "unrecognized alter table type: %d",
+ (int) cmd->subtype);
+ break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATExecDropNotNull(rel, cmd->name, lockmode);
break;
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 1680,1685 **** alter_table_cmd:
--- 1680,1699 ----
n->def = $4;
$$ = (Node *)n;
}
+ /* ALTER TABLE ALTER [COLUMN] ADD CONSTRAINT ... */
+ | ALTER opt_column ColId ADD_P ColQualList
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ CollateClause *collate;
+ n->subtype = AT_ColumnConstraint;
+ n->name = $3;
+ SplitColQualList($5, (List **) &n->def, &collate, yyscanner);
+ if (collate != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("COLLATE clause not allowed in ALTER TABLE / ALTER COLUMN")));
+ $$ = (Node *)n;
+ }
/* ALTER TABLE ALTER [COLUMN] DROP NOT NULL */
| ALTER opt_column ColId DROP NOT NULL_P
{
*** a/src/backend/parser/parse_utilcmd.c
--- b/src/backend/parser/parse_utilcmd.c
***************
*** 78,83 **** typedef struct
--- 78,84 ----
List *ckconstraints; /* CHECK constraints */
List *fkconstraints; /* FOREIGN KEY constraints */
List *ixconstraints; /* index-creating constraints */
+ List *nnconstraints; /* NOT NULL constraints */
List *inh_indexes; /* cloned indexes from INCLUDING INDEXES */
List *blist; /* "before list" of things to do before
* creating the table */
***************
*** 120,125 **** static IndexStmt *transformIndexConstraint(Constraint *constraint,
--- 121,128 ----
static void transformFKConstraints(CreateStmtContext *cxt,
bool skipValidation,
bool isAddConstraint);
+ static void transformColumnConstraints(CreateStmtContext *cxt,
+ ColumnDef *column);
static void transformConstraintAttrs(CreateStmtContext *cxt,
List *constraintList);
static void transformColumnType(CreateStmtContext *cxt, ColumnDef *column);
***************
*** 212,217 **** transformCreateStmt(CreateStmt *stmt, const char *queryString)
--- 215,221 ----
cxt.ckconstraints = NIL;
cxt.fkconstraints = NIL;
cxt.ixconstraints = NIL;
+ cxt.nnconstraints = NIL;
cxt.inh_indexes = NIL;
cxt.blist = NIL;
cxt.alist = NIL;
***************
*** 293,302 **** static void
transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
{
bool is_serial;
- bool saw_nullable;
- bool saw_default;
- Constraint *constraint;
- ListCell *clist;
cxt->columns = lappend(cxt->columns, column);
--- 297,302 ----
***************
*** 360,365 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
--- 360,366 ----
CreateSeqStmt *seqstmt;
AlterSeqStmt *altseqstmt;
List *attnamelist;
+ Constraint *constraint;
/*
* Determine namespace and name to use for the sequence.
***************
*** 471,476 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
--- 472,494 ----
}
/* Process column constraints, if any... */
+ transformColumnConstraints(cxt, column);
+ }
+
+ /*
+ * transformColumnConstraints -
+ * Transform constraints as found on a column definition.
+ *
+ * in addition to transformColumnDefinition, this is used by ALTER TABLE
+ * ADD CONSTRAINT.
+ */
+ static void
+ transformColumnConstraints(CreateStmtContext *cxt, ColumnDef *column)
+ {
+ bool saw_nullable;
+ bool saw_default;
+ ListCell *clist;
+
transformConstraintAttrs(cxt, column->constraints);
saw_nullable = false;
***************
*** 478,484 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
foreach(clist, column->constraints)
{
! constraint = lfirst(clist);
Assert(IsA(constraint, Constraint));
switch (constraint->contype)
--- 496,502 ----
foreach(clist, column->constraints)
{
! Constraint *constraint = lfirst(clist);
Assert(IsA(constraint, Constraint));
switch (constraint->contype)
***************
*** 491,497 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
! column->is_not_null = FALSE;
saw_nullable = true;
break;
--- 509,515 ----
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
! column->is_not_null = false;
saw_nullable = true;
break;
***************
*** 503,509 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
! column->is_not_null = TRUE;
saw_nullable = true;
break;
--- 521,529 ----
column->colname, cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
! constraint->colname = column->colname;
! cxt->nnconstraints = lappend(cxt->nnconstraints, constraint);
! column->is_not_null = true;
saw_nullable = true;
break;
***************
*** 2280,2285 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
--- 2300,2306 ----
cxt.ckconstraints = NIL;
cxt.fkconstraints = NIL;
cxt.ixconstraints = NIL;
+ cxt.nnconstraints = NIL;
cxt.inh_indexes = NIL;
cxt.blist = NIL;
cxt.alist = NIL;
***************
*** 2287,2294 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
/*
* The only subtypes that currently require parse transformation handling
! * are ADD COLUMN and ADD CONSTRAINT. These largely re-use code from
! * CREATE TABLE.
*/
foreach(lcmd, stmt->cmds)
{
--- 2308,2315 ----
/*
* The only subtypes that currently require parse transformation handling
! * are ADD COLUMN, ADD CONSTRAINT and ALTER COLUMN ADD CONSTRAINT. These
! * largely re-use code from CREATE TABLE.
*/
foreach(lcmd, stmt->cmds)
{
***************
*** 2346,2351 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
--- 2367,2391 ----
newcmds = lappend(newcmds, cmd);
break;
+ case AT_ColumnConstraint:
+ /*
+ * The original ColumnConstraint node doesn't go to newcmds
+ */
+ {
+ ColumnDef *phony = makeNode(ColumnDef);
+
+ phony->colname = cmd->name;
+ phony->constraints = (List *) cmd->def;
+ transformColumnConstraints(&cxt, phony);
+ if (phony->raw_default)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DEFAULT clause not supported in ALTER TABLE / ALTER COLUMN")));
+
+ if (list_length(cxt.fkconstraints) > 0)
+ skipValidation = false;
+ }
+ break;
default:
newcmds = lappend(newcmds, cmd);
break;
***************
*** 2384,2390 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
}
cxt.alist = NIL;
! /* Append any CHECK or FK constraints to the commands list */
foreach(l, cxt.ckconstraints)
{
newcmd = makeNode(AlterTableCmd);
--- 2424,2430 ----
}
cxt.alist = NIL;
! /* Append any CHECK, NOT NULL or FK constraints to the commands list */
foreach(l, cxt.ckconstraints)
{
newcmd = makeNode(AlterTableCmd);
***************
*** 2399,2404 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
--- 2439,2451 ----
newcmd->def = (Node *) lfirst(l);
newcmds = lappend(newcmds, newcmd);
}
+ foreach(l, cxt.nnconstraints)
+ {
+ newcmd = makeNode(AlterTableCmd);
+ newcmd->subtype = AT_SetNotNull;
+ newcmd->name = ((Constraint *) lfirst(l))->colname;
+ newcmds = lappend(newcmds, newcmd);
+ }
/* Close rel but keep lock */
relation_close(rel, NoLock);
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 1177,1182 **** typedef enum AlterTableType
--- 1177,1183 ----
AT_AddColumnRecurse, /* internal to commands/tablecmds.c */
AT_AddColumnToView, /* implicitly via CREATE OR REPLACE VIEW */
AT_ColumnDefault, /* alter column default */
+ AT_ColumnConstraint, /* alter column add constraint */
AT_DropNotNull, /* alter column drop not null */
AT_SetNotNull, /* alter column set not null */
AT_SetStatistics, /* alter column set statistics */
***************
*** 1545,1550 **** typedef struct Constraint
--- 1546,1554 ----
char fk_upd_action; /* ON UPDATE action */
char fk_del_action; /* ON DELETE action */
+ /* Fields used for a NOT NULL constraints: */
+ char *colname; /* column name */
+
/* Fields used for constraints that allow a NOT VALID specification */
bool skip_validation; /* skip validation of existing rows? */
bool initially_valid; /* mark the new constraint as valid? */
*** a/src/test/regress/expected/alter_table.out
--- b/src/test/regress/expected/alter_table.out
***************
*** 667,672 **** insert into atacc1 (test,test2) values (4,5);
--- 667,701 ----
insert into atacc1 (test,test2) values (5,4);
insert into atacc1 (test,test2) values (5,5);
drop table atacc1;
+ -- try the column constraint variant
+ create table atacc0 (test int, test2 int);
+ alter table atacc0 alter column test add constraint pk primary key;
+ NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pk" for table "atacc0"
+ alter table atacc0 alter column test2 add constraint uq unique;
+ NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "uq" for table "atacc0"
+ insert into atacc0 values (1, 1), (4, 2);
+ create table atacc1 (test int, test2 int);
+ alter table atacc1 alter column test add constraint test_fk references atacc0 initially deferred deferrable
+ check (test <> 4) constraint fnn not null;
+ insert into atacc1 values (1, 0);
+ insert into atacc1 values (2, 0);
+ ERROR: insert or update on table "atacc1" violates foreign key constraint "test_fk"
+ DETAIL: Key (test)=(2) is not present in table "atacc0".
+ begin;
+ insert into atacc1 values (2, 0);
+ insert into atacc0 values (2, 3);
+ end;
+ insert into atacc1 values (4, 0);
+ ERROR: new row for relation "atacc1" violates check constraint "atacc1_test_check"
+ insert into atacc1 values (null, 0);
+ ERROR: null value in column "test" violates not-null constraint
+ alter table atacc1 alter column test2 add constraint test2_fk references atacc0 (test2);
+ ERROR: insert or update on table "atacc1" violates foreign key constraint "test2_fk"
+ DETAIL: Key (test2)=(0) is not present in table "atacc0".
+ insert into atacc0 values (5, 0);
+ alter table atacc1 alter column test2 add constraint test2_fk references atacc0 (test2);
+ insert into atacc1 values (1, 3);
+ drop table atacc1, atacc0;
-- lets do some naming tests
create table atacc1 (test int, test2 int, primary key(test));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
*** a/src/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
***************
*** 599,604 **** insert into atacc1 (test,test2) values (5,4);
--- 599,626 ----
insert into atacc1 (test,test2) values (5,5);
drop table atacc1;
+ -- try the column constraint variant
+ create table atacc0 (test int, test2 int);
+ alter table atacc0 alter column test add constraint pk primary key;
+ alter table atacc0 alter column test2 add constraint uq unique;
+ insert into atacc0 values (1, 1), (4, 2);
+ create table atacc1 (test int, test2 int);
+ alter table atacc1 alter column test add constraint test_fk references atacc0 initially deferred deferrable
+ check (test <> 4) constraint fnn not null;
+ insert into atacc1 values (1, 0);
+ insert into atacc1 values (2, 0);
+ begin;
+ insert into atacc1 values (2, 0);
+ insert into atacc0 values (2, 3);
+ end;
+ insert into atacc1 values (4, 0);
+ insert into atacc1 values (null, 0);
+ alter table atacc1 alter column test2 add constraint test2_fk references atacc0 (test2);
+ insert into atacc0 values (5, 0);
+ alter table atacc1 alter column test2 add constraint test2_fk references atacc0 (test2);
+ insert into atacc1 values (1, 3);
+ drop table atacc1, atacc0;
+
-- lets do some naming tests
create table atacc1 (test int, test2 int, primary key(test));
-- only first should succeed