CODEWITHSUNDEEP
postgresqlshellpsql
Blomex
Blomex

Reputation: 417

syntax error at or near ":" when running parametrized query from shell

I'm trying to run a parameterized query from shell.

But when I run:

p='some stuff'
psql -d "dbname" -v v1="$p" -c "SELECT * FROM table WHERE name=:'v1'"

I'm getting the following error:

ERROR:  syntax error at or near ":"

Meanwhile:

psql -d "dbname" -v v1="$p" -c "\echo :'v1'"

works normally. (returns as expected: 'some stuff')

Upvotes: 2

Views: 953

Answers (1)

Cà phê đen
Cà phê đen

Reputation: 1953

You cannot use the variable defined in -v in -c command (see below). Try passing the command into the standard input:

psql -d "dbname" -v v1="$p" <<< "SELECT * FROM table WHERE name=:'v1'"

From the document:

-c command

--command command

...

command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command.

...

-v does set the psql's internal variable, which is psql-specific features. That's why you got the syntax error.

Upvotes: 7

Related Questions