Add Column If Not Exists in Postgresql

Question

Is postgresql (9.3.2) can do check the existence of a column before add a new column? I don't want to create a function just for to check the existence.

Just simply like this :

  ALTER TABLE IF NOT EXISTS table_name ADD COLUMN column_name data_type;

Solution 1

So, there is no such query. I should using PLPGSQL.

Solution 2

You'll need to write your own stored procedure in Plpgsql to check if the table has this column. For this you'll need the tables PG_ATTRIBUTE and PG_CLASS where Postgres stores the schema metadata and in particular the information about columns and tables respectively.

The query whose result you need to check in your stored procedure would be a JOIN like:

SELECT A.ATTNAME FROM PG_ATTRIBUTE A, PG_CLASS C                                             
WHERE A.ATTRELID = C.OID AND A.ATTNAME = 'column_name_check_if_exists' AND C.relname= 'table_name' ;

Solution 3

In DDL, you can only:

• Add columns
• Remove columns
• Add constraints
• Remove constraints
• Change default values
• Change column data types
• Rename columns
• Rename tables

ALTER TABLE: SYNOPSIS AND EXAMPLES -> http://www.postgresql.org/docs/9.3/static/sql-altertable.html

For validations... you need make "PL/SQL"

Comments

[postgreat]

So, the answer is no.

[postgreat]

@ThalisKalfigkopoulos : thanks for confirmation