Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Post History
According to the psql (version 14) man page, it's possible to set variables using the -v command-line parameter, then use the variable in a query, automatically quoted correctly, with :'variable_na...
#2: Post edited
by
Alexei
·
2023-06-14T06:03:45Z (over 1 year ago)
added relevant tags
#1: Initial revision
by
Andrew
·
2023-06-12T15:16:38Z (over 1 year ago)
Postgres command-line variable substitution error
According to the `psql` (version 14) man page, it's possible to [set variables using the `-v` command-line parameter](https://www.postgresql.org/docs/14/app-psql.html#APP-PSQL-INTERPOLATION:~:text=Perform%20a%20variable%20assignment%2C%20like%20the%20%5Cset%20meta%2Dcommand.), then [use the variable in a query, automatically quoted correctly](https://www.postgresql.org/docs/14/app-psql.html#APP-PSQL-INTERPOLATION), with `:'variable_name'`. When issuing SQL commands with `-c`, this works:
```bash
$ psql -v foo=bar -c "\echo :foo"
bar
```
...and this works:
```bash
$ psql -v foo=bar -c "\echo :'foo'"
'bar'
```
...but this fails:
```bash
$ psql -v foo=bar -c "SELECT :'foo' AS foo"
ERROR: syntax error at or near ":"
LINE 1: SELECT :'foo' AS foo
^
```
Why doesn't the latter syntax work? How can I get a variable substitution via SQL interpolation from the command line?