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;
Asked
Active
Viewed 1.6k times
2
postgreat
- 545
- 3
- 7
- 16
-
It's not duplicate. That article using FUNCTION (PLPGSQL), I need only SQL script. – postgreat Feb 18 '14 at 08:38
-
I don't see any FUNCTION in the accepted answer. – Denys Séguret Feb 18 '14 at 08:39
-
There is also a solution with `EXIST()` which you can use to write your function. – ckruse Feb 18 '14 at 08:39
-
I mean the syntax like : ALTER TABLE IF NOT EXISTS table_name ADD COLUMN column_name data_type; It's OK if there is no codes like that. I just want to know. – postgreat Feb 18 '14 at 08:42
-
So there is no such code ! – postgreat Feb 18 '14 at 09:44
-
somebody please give answer as 'there is no such code' or something to approve as an answer – postgreat Feb 18 '14 at 09:47
3 Answers
3
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' ;
Thalis K.
- 7,363
- 6
- 39
- 54
2
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"
jokie1982
- 56
- 3