1

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..
user45097
  • 561
  • 2
  • 7
  • 16
  • You did not post what error message you got. – user1934428 Nov 11 '22 at 07:56
  • I've added the error to the description. – user45097 Nov 11 '22 at 08:43
  • Try removing the `\gexec` from the SQL command. – Niloct Nov 11 '22 at 08:47
  • @Niloct same issue. Seems to be an issue with handing the quotes. – user45097 Nov 11 '22 at 08:51
  • The `\gexec` is a bit convoluted, it makes whatever text is output by the `SELECT` command to be used as a new SQL query, but perhaps you can do the above command without this feature. – Niloct Nov 11 '22 at 08:59
  • can you try `echo "SELECT 'CREATE USER myuser WITH PASSWORD $$mypassword$$' WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'myuser')\gexec"` – jian Nov 11 '22 at 08:59
  • This is mainly an SQL issue, not much related to bash. I suggest that you first try out the query interactively (without bash), and once it works put it into your script. In any case `SELECT 'CREATE USER myuser' WITH PASSWORD` looks wrong to me. AFIK, SQL does not have a `SELECT ... WITH PASSWORD`. What are you trying to achieve here? – user1934428 Nov 11 '22 at 09:14
  • @jian using the $$ around the password doesn't get it working. – user45097 Nov 13 '22 at 03:46
  • I've added some background information as to what the motivation is. If there is a simpler alternative please share. – user45097 Nov 13 '22 at 03:46

1 Answers1

1

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.

Niloct
  • 9,491
  • 3
  • 44
  • 57
  • Thanks for the suggestion but it doesn't work. `echo "SELECT 'CREATE USER myuser WITH PASSWORD ''mypassword''' WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'myuser')"` Results in this error: ` ?column? ----------------------------------------------- CREATE USER myuser WITH PASSWORD 'mypassword' (1 row) ` – user45097 Nov 13 '22 at 03:39
  • Did you put the `\gexec ` at the end ? – Niloct Nov 13 '22 at 07:21
  • You have to use the `\gexec` as in your original code, with my updated SQL statement. Otherwise it will just print the query as you commented. Copy and paste my answer. I've tested this on my Mac before replying and it does work. – Niloct Nov 14 '22 at 15:25