3

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';
6

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'
  • I like this, can define the entire string (in my case the comment for a table description) at the top of a psql script. – Merlin Jan 20 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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