PostgreSQL Update Table Only If It Exists

postgresql

I have a Server and several Client Applications. The Server has to be started before a client can be started. The clients then create tables in the database if they don't exists.

When the Server is started (some tables do not exist) and the following query gives me an exception:

UPDATE recipes SET lock = null 
WHERE lock IS NOT NULL;

Relation >>recipes<< does not exists

I want to avoid this exception by checking if this table exists or not.

UPDATE recipes SET lock = null
WHERE lock IS NOT NULL AND
WHERE EXISTS (
   SELECT 1
   FROM   information_schema.tables 
   WHERE  table_schema = 'public'
   AND    table_name = 'recipes'
   );

But this query does not work. Can you tell me where my mistake is?

Best Answer

You need a pl/pgsql block to do the IF:

DO $$                  
    BEGIN 
        IF EXISTS
            ( SELECT 1
              FROM   information_schema.tables 
              WHERE  table_schema = 'public'
              AND    table_name = 'recipes'
            )
        THEN
            UPDATE recipes 
            SET lock = NULL
            WHERE lock IS NOT NULL ;
        END IF ;
    END
   $$ ;
Related Question