ALTER TABLE statement is used to add/ drop/ modify the definition for the following.
- 5.2.1 Alter Table Column
- 5.2.2 Alter Constraint
5.2.1 Alter Table Column
You can perform following actions to a table column.
- 5.2.1.1 Add column to table
- 5.2.1.2 Drop existing table column
- 5.2.1.3 Modify definition of existing table column
- 5.2.1.4 Rename existing table column
This section helps you understand altering table columns in Oracle and how you can migrate them to PostgreSQL database.
Let us create a table and alter existing columns or add new columns.
CREATE TABLE Test_alt
(
tid NUMBER,
tname VARCHAR2(20)
);
CREATE TABLE Test_alt
(
tid INTEGER,
tname VARCHAR(20)
);
5.2.1.1 Add column to table
Syntax for adding a column to a table in Oracle –
ALTER TABLE [schema_name] [.] <table_name>
ADD <column_name> <column_data_type>;
SQL> DESC Test_alt
Name Null? Type
---------- ------- ------------
TID NUMBER
TNAME VARCHAR2(20)
Add column “ts_dt” of DATE type, “ts_details” of VARCHAR2(100) and “ts_year” of VARCHAR2(4).
ALTER TABLE Test_alt ADD ts_dt DATE;
ALTER TABLE Test_alt ADD ts_details VARCHAR2(100);
ALTER TABLE Test_alt ADD ts_year VARCHAR2(4);
SQL> DESC Test_alt
Name Null? Type
---------- ------- ------------
TID NUMBER
TNAME VARCHAR2(20)
TS_DATE DATE
TS_DETAILS VARCHAR2(100)
TS_YEAR VARCHAR2(4)
Syntax for adding a column to a table in PostgreSQL-
ALTER TABLE [schema_name] [.] <table_name>
ADD [ COLUMN ] <column_name> <column_data_type>;
# \d Test_alt
Table "public.test_alt"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
tid | integer | | |
tname | character varying(20) | | |
Add column “ts_dt” of DATE type, “ts_details” of VARCHAR(100) and “ts_year” of VARCHAR(4).
ALTER TABLE Test_alt ADD ts_dt DATE;
ALTER TABLE Test_alt ADD ts_details VARCHAR(100);
ALTER TABLE Test_alt ADD ts_year VARCHAR(4);
# \d Test_alt
Table "public.test_alt"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
tid | integer | | |
tname | character varying(20) | | |
ts_dt | date | | |
ts_details | character varying(100) | | |
ts_year | character varying(4) | | |
5.2.1.2 Drop existing table column
Syntax for dropping a column from a table in Oracle –
ALTER TABLE [schema_name] [.] <table_name>
DROP COLUMN <column_name> [ CASCADE CONSTRAINTS ]
Drop existing table column “ts_dt”.
ALTER TABLE Test_alt DROP COLUMN ts_dt;
SQL> DESC Test_alt
Name Null? Type
---------- ------- ------------
TID NUMBER
TNAME VARCHAR2(20)
TS_DETAILS VARCHAR2(100)
TS_YEAR VARCHAR2(4)
Syntax for dropping a column from a table in PostgreSQL –
ALTER TABLE [schema_name] [.] <table_name>
DROP [ COLUMN ] [ IF EXISTS ] <column_name> <column_data_type> [ RESTRICT | CASCADE ]
Drop existing table column “ts_dt”.
ALTER TABLE Test_alt DROP COLUMN ts_dt;
# \d Test_alt
Table "public.test_alt"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
tid | integer | | |
tname | character varying(20) | | |
ts_details | character varying(100) | | |
ts_year | character varying(4) | | |
5.2.1.3 Modify definition of existing table column
Syntax to modify a table column in Oracle –
ALTER TABLE [schema_name] [.] <table_name>
MODIFY <column_name> <column_new_data_type>
Modify data type for existing column “ts_details” from VARCHAR2(100) to VARCHAR2(500) and “ts_year” to NUMBER(4).
ALTER TABLE Test_alt MODIFY ts_details VARCHAR2(500);
ALTER TABLE Test_alt MODIFY ts_year NUMBER(4);
SQL> DESC Test_alt
Name Null? Type
---------- ------- ------------
TID NUMBER
TNAME VARCHAR2(20)
TS_DETAILS VARCHAR2(500)
TS_YEAR NUMBER(4)
Syntax to modify a table column in PostgreSQL –
ALTER TABLE [schema_name] [.] <table_name>
ALTER [ COLUMN ] <column_name> [ SET DATA ] TYPE <column_new_data_type> [ USING expression ]
Modify data type for existing column “ts_details” from VARCHAR(100) to VARCHAR(500) and “ts_year” to SMALLINT.
ALTER TABLE Test_alt ALTER COLUMN ts_details TYPE VARCHAR(500);
ALTER TABLE Test_alt ALTER COLUMN ts_year TYPE SMALLINT;
ERROR: column "ts_year" cannot be cast automatically to type smallint
HINT: You might need to specify "USING ts_year::smallint".
A USING expression is required when you are altering a column data type from another when there is no implicit type conversion from old type to new type.
ALTER TABLE Test_alt ALTER COLUMN ts_year TYPE SMALLINT USING ts_year::SMALLINT;
# \d Test_alt
Table "public.test_alt"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
tid | integer | | |
tname | character varying(20) | | |
ts_details | character varying(500) | | |
ts_year | smallint | | |
5.2.1.4 Rename existing table column
Syntax to modify a table column in Oracle –
ALTER TABLE [schema_name] [.] <table_name>
RENAME COLUMN <old_column_name> TO <new_column_name>
Rename table existing column “ts_details” to “description”.
ALTER TABLE Test_alt RENAME COLUMN ts_details to description;
SQL> DESC Test_alt
Name Null? Type
---------- ------- ------------
TID NUMBER
TNAME VARCHAR2(20)
DESCRIPTION VARCHAR2(500)
TS_YEAR NUMBER(4)
Syntax to modify a table column in PostgreSQL –
ALTER TABLE [schema_name] [.] <table_name>
RENAME [ COLUMN ] <old_column_name> TO <new_column_name>
Rename table existing column “ts_details” to “description”.
ALTER TABLE Test_alt RENAME COLUMN ts_details to description;
# \d Test_alt
Table "public.test_alt"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
tid | integer | | |
tname | character varying(20) | | |
description | character varying(500) | | |
ts_year | smallint | | |
5.2.2 Alter Table Constraint
You can use ALTER TABLE to add/drop/modify table level constraints to a table. Following are the actions you can perform on constraints in a table.
- 5.2.2.1 Altering Primary Key/Unique Key constraint
- 5.2.2.2 Altering Foreign Key constraint
5.2.2.1 Altering Primary Key/Unique Key constraint
This section helps you migrate Oracle Primary key constraints or Unique key constraints to PostgreSQL.
1. Add Primary Key/Unique Key constraint to a table.
Syntax to add a primary key or unique key constraint in Oracle –
ALTER TABLE [schema_name] [.] <table_name>
ADD [ CONSTRAINT ] [ constraint_name ] PRIMARY KEY (<column list>) [ NOVALIDATE ]
ALTER TABLE [schema_name] [.] <table_name>
ADD [ CONSTRAINT ] [ constraint_name ] UNIQUE (<column list>) [ NOVALIDATE ]
Add Primary key in “Test_alt” table for “tid” column.
ALTER TABLE Test_alt ADD CONSTRAINT Test_alt_id_pk PRIMARY KEY(tid);
Add Unique key in “Test_alt” table for “tname” column.
ALTER TABLE Test_alt ADD CONSTRAINT Test_alt_id_pk UNIQUE(tname);
Syntax to add a table level constraint in PostgreSQL –
ALTER TABLE [schema_name] [.] <table_name>
ADD [ CONSTRAINT ] [ constraint_name ] PRIMARY KEY (<column list>)
ALTER TABLE [schema_name] [.] <table_name>
ADD [ CONSTRAINT ] [ constraint_name ] UNIQUE (<column list>)
NOVALIDATE
option with constraint in Oracle, skips constraint validation for all existing rows in the table. This option can be added to any constraint in Oracle.However, PostgreSQL provides
NOT VALID
option similar to OracleNOVALIDATE
. ButNOT VALID
option (in PG 12) is currently only allowed for foreign key and CHECK constraints.
Add Primary key in “Test_alt” table for “tid” column.
ALTER TABLE Test_alt ADD CONSTRAINT Test_alt_id_pk PRIMARY KEY(tid);
Add Unique key in “Test_alt” table for “tname” column.
ALTER TABLE Test_alt ADD CONSTRAINT Test_alt_id_pk UNIQUE(tname);
2. Drop Primary Key/Unique Key constraint in a table.
The DROP CONSTRAINT clause is used to drop a constraint from a table.
You cannot drop a primary key or unique key constraint that is part of a referential integrity constraint without also dropping the foreign key. To drop the referenced key and the foreign key together, use the
CASCADE
clause. If you omitCASCADE
, then Oracle or PostgreSQL database does not drop the primary key or unique constraint if any foreign key references it.
Syntax for dropping a constraint in Oracle –
ALTER TABLE [schema_name] [.] <table_name>
DROP PRIMARY KEY [ CASCADE ] [ { KEEP | DROP } INDEX ]
ALTER TABLE [schema_name] [.] <table_name>
DROP UNIQUE (<column list>) [ CASCADE ] [ { KEEP | DROP } INDEX ]
You can also drop primary key or unique key constraints by specifying constraint name using below syntax.
ALTER TABLE [schema_name] [.] <table_name>
DROP CONSTRAINT <Constraint_name> [ CASCADE ]
DROP PRIMARY KEY
orDROP UNIQUE
clauses are not supported in PostgreSQL database. PostgreSQL only supports dropping constraints through their names.
Drop primary key in “Test_alt” table.
ALTER TABLE Test_alt DROP CONSTRAINT Test_alt_id_pk;
Syntax for dropping a constraint in PostgreSQL –
ALTER TABLE [schema_name] [.] <table_name>
DROP CONSTRAINT [ IF EXISTS ] <Constraint_name> [ RESTRICT | CASCADE ]
Note the
IF EXISTS
clause withDROP CONSTRAINT
. This is very useful as this does not return any error even when the constraint does not exist in the table.Be reminded,
IF EXISTS
clause withDROP CONSTRAINT
|DROP COLUMN
|DROP TABLE
is not available in Oracle database.
Drop primary key in “Test_alt” table.
ALTER TABLE Test_alt DROP CONSTRAINT Test_alt_id_pk;
5.2.2.2 Altering Foreign Key constraint
Let us create following table in Oracle and PostgreSQL to work with foreign keys.
--Parent/Master table
CREATE TABLE P_Test
(
pid NUMBER,
pname VARCHAR2(20)
);
ALTER TABLE P_Test ADD CONSTRAINT P_Test_id_pk PRIMARY KEY(pid);
--Child/Detail table
CREATE TABLE C_Test
(
cid INTEGER,
cname VARCHAR(20),
cxdt DATE
);
ALTER TABLE C_Test ADD CONSTRAINT P_Test_id_pk PRIMARY KEY(cid, cname);
--Parent/Master table
CREATE TABLE P_Test
(
pid INTEGER,
pname VARCHAR(20)
);
ALTER TABLE P_Test ADD CONSTRAINT P_Test_id_pk PRIMARY KEY(pid);
--Child/Detail table
CREATE TABLE C_Test
(
cid INTEGER,
cname VARCHAR(20),
cxdt DATE
);
ALTER TABLE C_Test ADD CONSTRAINT P_Test_id_pk PRIMARY KEY(cid, cname);
1. Add Foreign Key constraint to a table.
Syntax for adding a foreign key constraint to a table in Oracle –
ALTER TABLE [schema_name] [.] <table_name>
ADD [ CONSTRAINT ] [ constraint_name ] FOREIGN KEY (<column list>) REFERENCES <Parent_table_name> (<Parent_column_list>) [ NOVALIDATE ]
ALTER TABLE C_Test ADD CONSTRAINT P_Test_id_fk FOREIGN KEY(cid) REFERENCES P_Test(pid);
Syntax for adding a foreign key constraint to a table in PostgreSQL –
ALTER TABLE [schema_name] [.] <table_name>
ADD [ CONSTRAINT ] [ constraint_name ] FOREIGN KEY (<column list>) REFERENCES <Parent_table_name> (<Parent_column_list>) [ ON DELETE CASCADE | ON DELETE SET NULL ] [ ON UPDATE referential_action ] [ NOT VALID ]
ALTER TABLE C_Test ADD CONSTRAINT P_Test_id_fk FOREIGN KEY(cid) REFERENCES P_Test(pid);
2. Drop Foreign Key constraint in a table.
Syntax for adding a foreign key constraint to a table in Oracle –
ALTER TABLE [schema_name] [.] <table_name>
DROP CONSTRAINT <Constraint_name> [ RESTRICT | CASCADE ]
ALTER TABLE C_Test DROP CONSTRAINT P_Test_id_fk;
Syntax for adding a foreign key constraint to a table in PostgreSQL –
ALTER TABLE [schema_name] [.] <table_name>
DROP CONSTRAINT [ IF EXISTS ] <Constraint_name> [ RESTRICT | CASCADE ]
ALTER TABLE C_Test DROP CONSTRAINT P_Test_id_fk;