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.
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, then use the variable in a query, automatically quoted correctly, with
:'variable_name'. When issuing SQL commands with
-c, this works:
$ psql -v foo=bar -c "\echo :foo" bar
...and this works:
$ psql -v foo=bar -c "\echo :'foo'" 'bar'
...but this fails:
$ 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?