PostgreSQL – Add Variable Value in Single Quoted String

postgresqlpsql

I have tried different variations of the below commands but non work.
I simply want to run the following command using a variable within the location string:

CREATE TABLESPACE data OWNER appuser
  LOCATION '/var/lib/pgsql95/:variable/pg_tblspc/data';

What has been tried (variations):

EXECUTE CREATE TABLESPACE data OWNER appuser
  LOCATION ''/var/lib/pgsql95/' || base || '/pg_tblspc/data'';';

and

PREPARE tablespace (text) AS
  CREATE TABLESPACE aspire_data OWNER aspireapp
  LOCATION '/var/lib/pgsql95/$1/pg_tblspc';

EXECUTE tablespace('base');

and

\set myvariable 'base'
CREATE TABLESPACE aspire_data OWNER aspireapp
  LOCATION '/var/lib/pgsql95/':myvariable'/pg_tblspc';

Best Answer

Try this:

-- This will concatenate the values:
\set myvariable '/var/lib/pgsql95/' :myvariable '/pg_tblspc'

-- This will expand the variable single-quoted:
CREATE TABLESPACE aspire_data OWNER aspireapp LOCATION :'myvariable'
Related Question