*** 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/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 1680,1685 **** alter_table_cmd:
--- 1680,1700 ----
n->def = $4;
$$ = (Node *)n;
}
+ /* ALTER TABLE ALTER [COLUMN] ADD CONSTRAINT ... */
+ | ALTER opt_column ColId ADD_P ColQualList
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ ColumnDef *col = makeNode(ColumnDef);
+ n->subtype = AT_AddConstraint;
+ col->colname = $3;
+ SplitColQualList($5, &col->constraints, &col->collClause, yyscanner);
+ if (col->collClause != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("COLLATE clause not allowed in ALTER TABLE / ALTER COLUMN")));
+ n->def = (Node *) col;
+ $$ = (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)
{
***************
*** 2325,2331 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
/*
* The original AddConstraint cmd node doesn't go to newcmds
*/
! if (IsA(cmd->def, Constraint))
{
transformTableConstraint(&cxt, (Constraint *) cmd->def);
if (((Constraint *) cmd->def)->contype == CONSTR_FOREIGN)
--- 2346,2362 ----
/*
* The original AddConstraint cmd node doesn't go to newcmds
*/
! if (IsA(cmd->def, ColumnDef))
! {
! transformColumnConstraints(&cxt, (ColumnDef *) cmd->def);
! if (((ColumnDef *) cmd->def)->raw_default)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("DEFAULT clause not allowed in ALTER TABLE / ALTER COLUMN")));
! if (list_length(cxt.fkconstraints) > 0)
! skipValidation = false;
! }
! else if (IsA(cmd->def, Constraint))
{
transformTableConstraint(&cxt, (Constraint *) cmd->def);
if (((Constraint *) cmd->def)->contype == CONSTR_FOREIGN)
***************
*** 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);
--- 2415,2421 ----
}
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)
--- 2430,2442 ----
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
***************
*** 1545,1550 **** typedef struct Constraint
--- 1545,1553 ----
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,706 ----
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);
+ -- these are disallowed
+ alter table atacc1 alter column test2 add default 4;
+ ERROR: DEFAULT clause not allowed in ALTER TABLE / ALTER COLUMN
+ alter table atacc1 alter column test2 add constraint test3_fk references atacc0 (test2) collate C;
+ ERROR: COLLATE clause not allowed in ALTER TABLE / ALTER COLUMN
+ 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,629 ----
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);
+ -- these are disallowed
+ alter table atacc1 alter column test2 add default 4;
+ alter table atacc1 alter column test2 add constraint test3_fk references atacc0 (test2) collate C;
+ drop table atacc1, atacc0;
+
-- lets do some naming tests
create table atacc1 (test int, test2 int, primary key(test));
-- only first should succeed