Background

The aim is to the create database and user on startup if they do not exist. Using the bash work around described here: Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

The create user syntax appears to be correct as per: https://www.postgresql.org/docs/8.0/sql-createuser.html CREATE USER davide WITH PASSWORD 'jw8s0F4';

There seems to be an issue with the quotes. I've tried a number of suggestions but none have fixed it. Any ideas?

This works:

echo "SELECT 'CREATE USER myuser' WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'myuser')\gexec" | psql -h myhost -U admin -d mydb

But fails due to (I presume) quote errors, when setting the password:

echo "SELECT 'CREATE USER myuser' WITH PASSWORD 'mypassword' WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'myuser')\gexec" | psql -h myhost -U admin -d mydb

Error message:

ERROR:  syntax error at or near "WITH"
LINE 1: SELECT 'CREATE USER myuser' WITH PASSWORD 'mypassword' WHERE..

CodePudding user response：

echo "SELECT 'CREATE USER myuser WITH PASSWORD ''mypassword''' WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'myuser')\gexec" | psql -h myhost -U admin -d mydb

Key takeaways: escape single quotes with double single quotes, and the quoted string must not contain the WHERE clause, since \gexec expects the quoted content as a query, and CREATE USER does not have a WHERE parameter.