5.2 ALTER TABLE

Oracle to PostgreSQL Migration

  2. Home
  4. Docs
  6. Oracle to PostgreSQL Migration
  8. 5 SQL Commands
  10. 5.2 ALTER TABLE

5.2 ALTER TABLE

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.

OraclePostgreSQL
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

OraclePostgreSQL

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

OraclePostgreSQL

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

OraclePostgreSQL

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

OraclePostgreSQL

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.
OraclePostgreSQL

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 Oracle NOVALIDATE. But NOT 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 omit CASCADE, then Oracle or PostgreSQL database does not drop the primary key or unique constraint if any foreign key references it.

OraclePostgreSQL

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 or DROP 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 with DROP 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 with DROP 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.

OraclePostgreSQL
--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.
OraclePostgreSQL

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.
OraclePostgreSQL

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;
Was this article helpful to you? Yes No

How can we help?