patternsqlModerate
Postgres update a table (only) if it exists
Viewed 0 times
updatepostgresexistsonlytable
Problem
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
I want to avoid this
But this
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 existsI 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?Solution
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
$ ;Code Snippets
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
$$ ;Context
StackExchange Database Administrators Q#180318, answer score: 17
Revisions (0)
No revisions yet.