Re: psql variable interpolation with subsequent underscore

[Rémi Cura <remi.cura@xxxxxxxxx>]

maybe a stupid answer, 

but why not use another language (plpgsql? python?).

Is it because of transaction issue?
Sorry for not being more helpfull.

Cheers,

Rémi-C

2013/11/8 Tim Kane <tim.kane@xxxxxxxxx>

Hi all,

I’m having difficulty using variable interpolation within psql, where that variable is within a table name…

Like so..

=# set YYYYMM 201310

=# select :YYYYMM;

 ?column?

----------

   201309

(1 row)

=# alter table my_table rename to my_table_:YYYYMM_raw;

ERROR:  syntax error at or near ":"

LINE 1: …my_table rename to my_table_:YYYYMM_ra...

                                     ^

The problem is that psql tries to interpret ‘YYYYMM_raw’ as the variable name, but my intention is to only interpret ‘YYYYMM’ followed by a literal underscore.

I can’t find any other way to encapsulate the variable name in this way…  Is there a trick to it?

I suspect I’ll need to work around this by altering the naming convention such that the YYYYMM is at the ‘end’ of the table name.  Maybe..   Thoughts?

Tim