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
– josef, Commented Aug 2, 2016 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. — user330315
– user330315, Commented Aug 2, 2016 at 13:35

Community · Accepted Answer · 2017-05-23 11:44:20Z

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, 2017 at 11:44

CommunityBot

11 silver badge

answered Aug 2, 2016 at 13:26

librata

15010 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

update / exist column Postgresql

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related