You can use ALTER TABLE name ADD COLUMN statement to add one or more columns in PostgreSQL. You can also use ALTER TABLE to add columns in Oracle, but the syntax is different, so the conversion is required.
PostgreSQL:
-- Sample table CREATE TABLE items ( name VARCHAR(30) ); -- Add single column (COLUMN keyword is optional) ALTER TABLE items ADD COLUMN description TEXT NOT NULL; ALTER TABLE items ADD notes TEXT; -- Add multiple columns ALTER TABLE items ADD COLUMN status CHAR(1), ADD COLUMN cnt INTEGER;
In Oracle, the COLUMN keyword is not used (and it is optional in PostgreSQL), and you have to use parentheses () to add multiple columns:
Oracle:
-- Add single column ALTER TABLE items ADD description VARCHAR2(4000) NOT NULL; ALTER TABLE items ADD notes VARCHAR2(4000); -- Add multiple columns ALTER TABLE items ADD (status CHAR(1), cnt INTEGER);
PostgreSQL allows you to specify IF NOT EXISTS in ALTER TABLE ADD COLUMN:
PostgreSQL:
-- Trying to add the same column one more time with IF NOT EXISTS ALTER TABLE items ADD IF NOT EXISTS notes TEXT NOT NULL; # NOTICE: column "notes" of relation "items" already exists, skipping
Oracle does not support IF NOT EXISTS clause and the statement fails if the column already exists:
Oracle:
-- Add the column one more time ALTER TABLE items ADD notes VARCHAR2(4000); # ORA-01430: column being added already exists in table
Note that if the table is not empty you will get an error when adding NOT NULL column in Oracle:
Oracle:
-- Table has rows ALTER TABLE items ADD description VARCHAR2(4000) NOT NULL; # ORA-01758: table must be empty to add mandatory (NOT NULL) column
For more information, see PostgreSQL to Oracle Migration.