I am trying to concatenate some fixed string with a variable to build a table name. This simple example demonstrates the problem. The variable "suffix" resolved properly, but "prefix" does not because I am unable to figure out how to tell psql that "ws" is not part of the variable name.

What is the proper syntax so that the resultant query selects from "my_ws_group" ?

localhost ~ > psql -v prefix=my -v suffix=group
psql (, server 8.0.2)
WARNING: psql version 8.4, server version 8.0.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

dev=> select * from :prefix_ws_:suffix;
ERROR:  syntax error at or near ":"
LINE 1: select * from :prefix_ws_group;

I prefer to use psql 8.4 because that is most compatible with Redshift.

1 Answer 1


psql's pattern matching is not strong feature, so your query cannot be performed.

When parsing your string, psql finds the :prefix_ws_ since it cannot match/find :prefix, but that variable isn't defined by -v option, so psql ignores it. Next, psql finds :suffix, then replaces to group since it is defined.

Additionally if you write "select * from :prefix:suffix", psql can replace both variables such as "select * from mygroup".


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.