How to correctly escape characters in command-line PostgreSQL query?

Question

[servername]# su postgres -c 'cd ~; psql -c "SELECT * FROM pg_catalog.pg_user WHERE usename LIKE name;"'
ERROR:  column "name" does not exist
LINE 1: SELECT * FROM pg_catalog.pg_user WHERE usename LIKE name;
                                                            ^

Tried vary ways but no one of them return the correct response. May be there's a prettier way to do command-line queries?

Have you tried: `su postgres -c 'cd ~; psql -c "SELECT * FROM pg_catalog.pg_user WHERE usename LIKE \'name\';"'` ? — D. Mika, Nov 18 '22 at 05:55
Yep, it start waiting the input like if you just type 'command1 \' — Алексей Пашнин, Nov 18 '22 at 05:59

score 0 · Answer 1 · answered Nov 18 '22 at 06:36

0

/usr/local/pgsql15/bin/psql  -c "SELECT * FROM pg_catalog.pg_user WHERE usename like 'admin'"

make it two line.

/usr/local/pgsql15/bin/psql test15  -c  \
"SELECT * FROM pg_catalog.pg_user WHERE usename like 'admin'"

or

/usr/local/pgsql15/bin/psql test15  <<<"SELECT * FROM pg_catalog.pg_user WHERE usename like 'admin'"

or

echo "SELECT * FROM pg_catalog.pg_user WHERE usename like 'admin'" | /usr/local/pgsql15/bin/psql test15

percent sign still works.

/usr/local/pgsql15/bin/psql test15  -c  \
"SELECT * FROM pg_catalog.pg_user WHERE usename like '%admi%'"

answered Nov 18 '22 at 06:36

jian

4,119
1
17
32

The issue is that in one line I need to switch user and its directory, otherwise the command would not execute as well – Алексей Пашнин Nov 18 '22 at 06:40
@АлексейПашнин use to psql -U option. – jian Nov 18 '22 at 07:16
I need to switch OS user, not the DB user – Алексей Пашнин Nov 18 '22 at 07:25
I know. it's hard. I don't know. one work around is `psql -U postgres.` after you are in psql then \! to change directory. – jian Nov 18 '22 at 07:37
ex: psql -U postgres -d postgres -c "\du" return: psql: FATAL: Peer authentication failed for user "postgres" so I don't need to use any password, I need imitate usual command-line usage via switching OS user – Алексей Пашнин Nov 18 '22 at 07:38
can you try https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge then can you try `sudo psql --host=localhost --dbname=database-name --username=postgres` – jian Nov 18 '22 at 07:42
Last cmd asks a password input, and the way from the question above I saw, but I cannot change the config file – Алексей Пашнин Nov 18 '22 at 08:15
I found the way, post in separate answer here – Алексей Пашнин Nov 18 '22 at 08:20

score 0 · Accepted Answer · answered Nov 18 '22 at 08:20

0

su postgres -c "cd ~; psql -c 'SELECT * FROM pg_catalog.pg_user WHERE usename LIKE '\'name\'';'"

answered Nov 18 '22 at 08:20

Алексей Пашнин

23
7

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Mark Rotteveel Nov 20 '22 at 09:30

How to correctly escape characters in command-line PostgreSQL query?

2 Answers2