Sample Header Ad - 728x90

PSQL: passing variables into a select 'create user ... ' subquery

0 votes
2 answers
61 views
In Postgres I'm trying to automatize the creation of a non-existing db-user (using it in Debian's postinst script) in the following way: SELECT 'CREATE USER :v1' WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = ':v1')\gexec and calling via psql -v v1="flop" This certainly dosen't work, as :v1 is taken as username string itself and is not substituted. The following error is returned: > LINE 1: CREATE USER :v1 Modifying the line to ... 'CREATE USER ':v1' ' WHERE NOT ... returns: > Syntax error at >>' ' > LINE 1: SELECT 'CREATE USER 'flop' ' WHERE NOT EXISTS (SELECT FROM ... The variable :v1 is substituted, but it seems to me, the whole command, beginning from SELECT, isn't accepted. Changing by adding some single quotes according to some guidelines, to: SELECT 'CREATE USER ':'v1'' ' WHERE NOT EXISTS ... returns > Syntax error at 'flop' > > LINE 1: CREATE USER 'flop' Which looks at me as the substring CREATE USER produces the error due to quotation error. After adding some extra space between the single quotes like SELECT 'CREATE USER ':'v1' ' ' WHERE ... the query returns the following error: > Syntax error at >>' ' > LINE 1: SELECT 'CREATE USER ''flop' ' ' WHERE ... When removing the forst variable :v1 and putting the username explicitly, like SELECT 'CREATE USER flop' WHERE NOT ... everything works together with the variable substitution at usename=. So is seems to me it's "just" a quotation problem. But how cat I remove the single quotes and get the script working?
Asked by Paule (3 rep)
Jun 5, 2025, 02:07 PM
Last activity: Jun 7, 2025, 12:01 PM