<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.99 2008/05/09 23:32:03 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.100 2008/10/21 08:38:15 petere Exp $

PostgreSQL documentation

-->

ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]

DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TYPE <replaceable class="PARAMETER">type</replaceable> [ USING <replaceable class="PARAMETER">expression</replaceable> ]

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable>[ SET DATA ]TYPE <replaceable class="PARAMETER">type</replaceable> [ USING <replaceable class="PARAMETER">expression</replaceable> ]

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT

ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL

</varlistentry>

<varlistentry>

<term><literal>ALTER COLUMN TYPE</literal></term>

<term><literal>SET DATA TYPE</literal></term>

<listitem>

<para>

This form changes the type of a column of a table. Indexes and

with time zone</type> via a <literal>USING</literal> clause:

<programlisting>

ALTER TABLE foo

ALTER COLUMN foo_timestamp TYPE timestamp with time zone

ALTER COLUMN foo_timestampSET DATATYPE timestamp with time zone

USING

timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

</programlisting>

<title>Compatibility</title>

<para>

The <literal>ADD</literal>, <literal>DROP</>, and <literal>SET DEFAULT</>

forms conform with the SQL standard. The other forms are

The forms <literal>ADD</literal>, <literal>DROP</>, <literal>SET DEFAULT</>,

and <literal>SET DATA TYPE</literal> (without <literal>USING</literal>)

conform with the SQL standard. The other forms are

<productname>PostgreSQL</productname> extensions of the SQL standard.

Also, the ability to specify more than one manipulation in a single

<command>ALTER TABLE</> command is an extension.

F381Extended schema manipulation01ALTER TABLE statement: ALTER COLUMN clauseYES

F381Extended schema manipulation02ALTER TABLE statement: ADD CONSTRAINT clauseYES

F381Extended schema manipulation03ALTER TABLE statement: DROP CONSTRAINT clauseYES

F382Alter column data typeNOPostgreSQL syntax differs

F382Alter column data typeYES

F391Long identifiersYES

F392Unicode escapes in identifiersNO

F393Unicode escapes in literalsNO

CREATEROLE CREATEUSER CROSS CSV CTYPE CURRENT_P CURRENT_DATE CURRENT_ROLE

CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS

DATA_PDATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS

DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC

DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP

$$ = (Node *)n;

}

{

AlterTableCmd *n = makeNode(AlterTableCmd);

n->subtype = AT_AlterColumnType;

n->name =$3;

n->def = (Node *)$5;

n->transform =$6;

n->def = (Node *)$6;

n->transform =$7;

$$ = (Node *)n;

}

|/*EMPTY*/{$$ =0; }

;

opt_set_data: SET DATA_P{$$ =1; }

|/*EMPTY*/{$$ =0; }

;

| CURRENT_P

| CURSOR

| CYCLE

| DATA_P

| DATABASE

| DAY_P

| DEALLOCATE

{"current_user",CURRENT_USER,RESERVED_KEYWORD},

{"cursor",CURSOR,UNRESERVED_KEYWORD},

{"cycle",CYCLE,UNRESERVED_KEYWORD},

{"data",DATA_P,UNRESERVED_KEYWORD},

{"database",DATABASE,UNRESERVED_KEYWORD},

{"day",DAY_P,UNRESERVED_KEYWORD},

{"deallocate",DEALLOCATE,UNRESERVED_KEYWORD},

{"cardinality",SQL_CARDINALITY,0},

{"connect",SQL_CONNECT,0},

{"count",SQL_COUNT,0},

{"data",SQL_DATA,0},

{"datetime_interval_code",SQL_DATETIME_INTERVAL_CODE,0},

{"datetime_interval_precision",SQL_DATETIME_INTERVAL_PRECISION,0},

{"describe",SQL_DESCRIBE,0},

CREATEROLE CREATEUSER CROSS CSV CTYPE CURRENT_P CURRENT_DATE CURRENT_ROLE

CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS

DATA_PDATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS

DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC

DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP

{$$ = cat_str(4, make_str("drop"),$2,$3,$4); }

{$$ = cat_str(6, make_str("alter"),$2,$3, make_str("type"),$5,$6); }

{$$ = cat_str(7, make_str("alter"),$2,$3,$4,make_str("type"),$6,$7); }

{$$ = cat_str(2, make_str("add"),$2); }

|/*EMPTY*/{$$ = EMPTY; }

;

opt_set_data:SETDATA_P{$$ = make_str("set data"); }

|/*EMPTY*/{$$ = EMPTY; }

;

descriptor_item:SQL_CARDINALITY{$$ = ECPGd_cardinality; }

|SQL_DATA{$$ = ECPGd_data; }

|DATA_P{$$ = ECPGd_data; }

|SQL_DATETIME_INTERVAL_CODE{$$ = ECPGd_di_code; }

|SQL_DATETIME_INTERVAL_PRECISION {$$ = ECPGd_di_precision; }

|SQL_INDICATOR{$$ = ECPGd_indicator; }

|SQL_CALL{$$ = make_str("call"); }

|SQL_CARDINALITY{$$ = make_str("cardinality"); }

|SQL_COUNT{$$ = make_str("count"); }

|SQL_DATA{$$ = make_str("data"); }

|SQL_DATETIME_INTERVAL_CODE{$$ = make_str("datetime_interval_code"); }

|SQL_DATETIME_INTERVAL_PRECISION{$$ = make_str("datetime_interval_precision"); }

|SQL_FOUND{$$ = make_str("found"); }

|CTYPE{$$ = make_str("ctype"); }

|CURSOR{$$ = make_str("cursor"); }

|CYCLE{$$ = make_str("cycle"); }

|DATA_P{$$ = make_str("data"); }

|DATABASE{$$ = make_str("database"); }

|DEALLOCATE{$$ = make_str("deallocate"); }

for (i=0;i<3;i++)

printf("item[%d] = %d\n",i,ind[i] ?-1 :item[i]);

{ECPGdo(__LINE__,0,1,NULL,0,ECPGst_normal,"drop table T ",ECPGt_EOIT,ECPGt_EORT);

{ECPGdo(__LINE__,0,1,NULL,0,ECPGst_normal,"alter table T alter Item1 type bigint ",ECPGt_EOIT,ECPGt_EORT);

#line 31 "parser.pgc"

if (sqlca.sqlwarn[0]=='W')sqlprint();

if (sqlca.sqlcode<0)sqlprint();}

#line 31 "parser.pgc"

{ECPGdo(__LINE__,0,1,NULL,0,ECPGst_normal,"alter table T alter column Item2 set data type smallint ",ECPGt_EOIT,ECPGt_EORT);

#line 32 "parser.pgc"

if (sqlca.sqlwarn[0]=='W')sqlprint();

#line 32 "parser.pgc"

if (sqlca.sqlcode<0)sqlprint();}

#line 32 "parser.pgc"

{ECPGdo(__LINE__,0,1,NULL,0,ECPGst_normal,"drop table T ",ECPGt_EOIT,ECPGt_EORT);

#line 34 "parser.pgc"

if (sqlca.sqlwarn[0]=='W')sqlprint();

#line 34 "parser.pgc"

if (sqlca.sqlcode<0)sqlprint();}

#line 34 "parser.pgc"

{ECPGdisconnect(__LINE__,"ALL");

#line33 "parser.pgc"

#line36 "parser.pgc"

if (sqlca.sqlwarn[0]=='W')sqlprint();

#line33 "parser.pgc"

#line36 "parser.pgc"

if (sqlca.sqlcode<0)sqlprint();}

#line33 "parser.pgc"

#line36 "parser.pgc"

return0;

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_get_data on line 26: RESULT: offset: -1; array: yes

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_execute on line 31: query:drop table T ; with 0 parameter(s) on connection regress1

[NO_PID]: ecpg_execute on line 31: query:alter table T alter Item1 type bigint ; with 0 parameter(s) on connection regress1

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_execute on line 31: using PQexec

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_execute on line 31: OK: DROP TABLE

[NO_PID]: ecpg_execute on line 31: OK: ALTER TABLE

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_execute on line 32: query: alter table T alter column Item2 set data type smallint ; with 0 parameter(s) on connection regress1

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_execute on line 32: using PQexec

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_execute on line 32: OK: ALTER TABLE

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_execute on line 34: query: drop table T ; with 0 parameter(s) on connection regress1

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_execute on line 34: using PQexec

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_execute on line 34: OK: DROP TABLE

[NO_PID]: sqlca: code: 0, state: 00000

[NO_PID]: ecpg_finish: connection regress1 closed

[NO_PID]: sqlca: code: 0, state: 00000

for (i=0; i<3; i++)

printf("item[%d] = %d\n", i, ind[i] ? -1 : item[i]);

EXEC SQL ALTER TABLE T ALTER Item1 TYPE bigint;

EXEC SQL ALTER TABLE T ALTER COLUMN Item2 SET DATA TYPE smallint;

EXEC SQL DROP TABLE T;

EXEC SQL DISCONNECT ALL;