Postgres update a table (only) if it exists

Question

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?

• Luan Huynh: WHERE lock IS NOT NULL AND WHERE EXISTS ( should be WHERE lock IS NOT NULL AND EXISTS ( ... . But even like that, it cannot work. You can use postgresql.org/docs/9.6/static/sql-do.html for your case. do $$ begin if..else.. end if; end$$

Answer 1

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
 $$ ;

Answer 2

I tend to prefer:

DO $$
BEGIN
 UPDATE recipes SET lock = null 
 WHERE lock IS NOT NULL;
EXCEPTION WHEN UNDEFINED_TABLE THEN
 -- do nothing
END$$;

Answer 3

CREATE TABLE IF NOT EXISTS recipes (... ) 

And continue with update ...

see https://stackoverflow.com/questions/1766046/postgresql-create-table-if-not-exists