Execute gexec in a psql command

Question

I'm trying to pass the -c option of psql a query using \gexec but it's not working as I would like:

psql -d postgres://postgres@localhost:5432/postgres -c "SELECT format( 'ALTER TABLE %I.%I ADD PRIMARY KEY (id);', table_schema, table_name ) FROM information_schema.tables WHERE table_schema = 'my_schema' AND table_type = 'BASE TABLE' \gexec"

ERROR:  syntax error at or near "\"
LINE 1: ...able_schema = 'my_schema' AND table_type = 'BASE TABLE' \gexec

Is there anything I can do to fix that / or achieve the desired output in another way, or is it simply impossible to run such command using psql?

I tried to write \\gexec (thinking the backslash was the issue) instead but it's not working either.

I encountered this problem both on Windows 10 and Ubuntu 22.04 servers.

Answer 1

General options:

-c, --command=COMMAND run only single command (SQL or internal) and exit

Since the command you're trying to run doesn't begin with a backslash, it's considered to be an SQL command and is sent to the server, which obviously balks at \gexec.

You can use a here-doc or here-string to send multiple commands, e.g.

psql -d postgres://postgres@localhost:5432/postgres <<<"SELECT format( 'ALTER TABLE %I.%I ADD PRIMARY KEY (id);', table_schema, table_name ) FROM information_schema.tables WHERE table_schema = 'my_schema' AND table_type = 'BASE TABLE' \gexec"