update / exist column Postgresql

Question

i have a table abonnement with a column topic and other column. i want to update the value of the column topic. But before updating, i want to verify if this column exist or not to use this script with other developers in my team. If exist, I do update, else, i must to create this column and after do update. Here is my script :

IF EXISTS (select topic from abonnement) 
then 
update abonnement set topic ='valeurTopic'; 
else 
ALTER TABLE abonnement ADD COLUMN topic character varying; 
update abonnement set topic= 'valeurTopic';
end if;

I had an error:

ERREUR:  erreur de syntaxe sur ou près de « IF »
LINE 1: IF EXISTS (SELECT topic
    ^

Any solution please ?

because i saw some script when they use 'if'. else, how i can resolve it ? — josef, Aug 02 '16 at 13:17
Rather then implementing workarounds like that, you should change the way you deploy schema changes. The fact that you don't know if the column exists or not _might_ indicate that you don't have a proper process to manage and deploy schema migrations. Check out tools like Liquibase or Flyway. Making sure the database is in a defined state is a much better solution. — , Aug 02 '16 at 13:35

score 0 · Accepted Answer · edited May 23 '17 at 11:44

0

I would change the logic:

DO $$ 
    BEGIN
        BEGIN
            ALTER TABLE <table_name> ADD COLUMN <column_name> <column_type>;
        EXCEPTION
            WHEN duplicate_column THEN 
            UPDATE <table_name> SET <column_name> = 'valeurTopic';
    END;
END; $$

I got the code from How to add column if not exists on PostgreSQL?

edited May 23 '17 at 11:44

Community

1
1

answered Aug 02 '16 at 13:26

librata

150
9

update / exist column Postgresql

1 Answers1