Error in conditional database creation with AlloyDB

Question

I am trying to create a database in alloyDB if it already doesn't exist. I am using the following query

CREATE DATABASE test WHERE NOT EXISTS (SELECT from pg_database WHERE datname=test);

This syntax works with postgres and so my understanding is that it should also work with AlloyDB. However it is resulting in an error ERROR: syntax error at or near "WHERE". Is there any other syntax in AlloyDB for conditional database creation?

Answer 1

As per the solution of Erwin Brandstetter, you can use \\gexec to send query buffer to the server. Below is the workaround you can use for your use case.

SELECT 'CREATE DATABASE test'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'test')\gexec

Also, remember predifined roles needed for you to be able to perform certain commands on the GCP alloydb.