How do you use script variables in psql?

Question

In MS SQL Server, I create my scripts to use customizable variables:

DECLARE @somevariable int  
SELECT @somevariable = -1

INSERT INTO foo VALUES ( @somevariable )

I'll then change the value of @somevariable at runtime, depending on the value that I want in the particular situation. Since it's at the top of the script it's easy to see and remember.

How do I do the same with the PostgreSQL client psql?

Nobleminded

FWIW, the \set operator appears to be related to the psql command-line tool, not to the pgsql batch language. I could be wrong. – Geordie

What version of Postgres are you on? – Maguire

Answer 1

Postgres variables are created through the \set command, for example ...

\set myvariable value

... and can then be substituted, for example, as ...

SELECT * FROM :myvariable.table1;

... or ...

SELECT * FROM table1 WHERE :myvariable IS NULL;

edit: As of psql 9.1, variables can be expanded in quotes as in:

\set myvariable value 

SELECT * FROM table1 WHERE column1 = :'myvariable';

In older versions of the psql client:

... If you want to use the variable as the value in a conditional string query, such as ...

SELECT * FROM table1 WHERE column1 = ':myvariable';

... then you need to include the quotes in the variable itself as the above will not work. Instead define your variable as such ...

\set myvariable 'value'

However, if, like me, you ran into a situation in which you wanted to make a string from an existing variable, I found the trick to be this ...

\set quoted_myvariable '\'' :myvariable '\''

Now you have both a quoted and unquoted variable of the same string! And you can do something like this ....

INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable;

Futurity

\set is only for psql tool, you cannot use it in stored procedures! – Dinger

@SorinSbarnea the OP asked about script, not procedure – Win

This answer mixes psql meta-commands \set with PostgreSQL commands in a confusing fashion. – Doradorado

As of postgresql 9.1, in psql you can now use :'variable' to have it properly quoted as a value for you, or :"variable" to use it as an identifier. – Counterblast

The second one \set quoted_myvariable '\'' :myvariable '\'' gives you the quotes in the variable. – Futurity

\set works like a C macro. You can't use it to store the result of a function, like RANDOM() or NOW(). – Crabb

\set myvar 'value' does not work the way you describe. You have to use \set myvar '\'value\'' – Apeldoorn

instead of quoting, you can use a cast too: \set myvariable abcd, then select col from table where attr = :myvariable::varchar, for instance – Setter

@laurent: Or use :'myvar' like @Counterblast suggests – Tollefson

It is a bad practice to provide non working code: ':myvariable'. I see code and don't read comments that it is wrong code. So lost some time to detect bug and revoke my script from collegues. A way better is just provide correct code. – Berget

quoting with $$ also works - \set quoted_myvariable $$':myvariable'$$ – Antlia

also variant with arrays: 1. \set args $$'{"text", "text", "text", "text"}'$$::text[] is same as \set args ARRAY[$$'text'$$, $$'text'$$, $$'text'$$, $$'text'$$] – Antlia

Answer 2

One final word on PSQL variables:

1. They don't expand if you enclose them in single quotes in the SQL statement. Thus this doesn't work:

   SELECT * FROM foo WHERE bar = ':myvariable'
   

2. To expand to a string literal in a SQL statement, you have to include the quotes in the variable set. However, the variable value already has to be enclosed in quotes, which means that you need a second set of quotes, and the inner set has to be escaped. Thus you need:

   \set myvariable '\'somestring\''  
   SELECT * FROM foo WHERE bar = :myvariable
   

   EDIT: starting with PostgreSQL 9.1, you may write instead:

   \set myvariable somestring
   SELECT * FROM foo WHERE bar = :'myvariable'
   

Nobleminded

Answer 3

You can try to use a WITH clause.

WITH vars AS (SELECT 42 AS answer, 3.14 AS appr_pi)
SELECT t.*, vars.answer, t.radius*vars.appr_pi
FROM table AS t, vars;

Tsunami

This way is mostly convenient when you are using same computed values few times in your query. – Tsunami

Contrary to Bryce's report, it seems to work fine for me. CREATE TABLE test (name VARCHAR, age INT); INSERT INTO test (name, age) VALUES ('Jack', 21), ('Jill', 20); WITH vars AS (SELECT N'Jack' AS name, 21 AS age) SELECT test.* FROM test, vars WHERE test.name = vars.name and test.age = vars.age; Ouputs Jack and Jack's age, as expected. – Unstrap

For a lot of uses, especially within the context of a web application framework like Python Flask, this is the best solution for reusing complex calculated values within a single query. – Christopher

Can anyone suggest how this might work in an insert? – Serin

@Serin create table t(x integer); insert into t(x) with sub as (select 999 as num) select num from sub; select * from t; – Artisan

with vars as (select 42 as uid) select id from users where id = vars.uid returns error: ERROR: missing FROM-clause entry for table "vars" – Canzonet

@Canzonet try adding vars to the FROM clause: ... from users, vars where ... – Tsunami

Answer 4

Specifically for psql, you can pass psql variables from the command line too; you can pass them with -v. Here's a usage example:

$ psql -v filepath=/path/to/my/directory/mydatafile.data regress
regress=> SELECT :'filepath';
               ?column?                
---------------------------------------
 /path/to/my/directory/mydatafile.data
(1 row)

Note that the colon is unquoted, then the variable name its self is quoted. Odd syntax, I know. This only works in psql but doesn't work with -c / --command; you have to send the command via stdin or via -f. It won't work in (say) PgAdmin-III too.

This substitution happens during input processing in psql, so you can't (say) define a function that uses :'filepath' and expect the value of :'filepath' to change from session to session. It'll be substituted once, when the function is defined, and then will be a constant after that. It's useful for scripting but not runtime use.

Jobi

psql variables, e.g. :'filepath', you pointed out: "Note that the colon is unquoted, then the variable name its self is quoted." Thank! You! I already put a bunch of forehead-shaped dents into my desk trying to make this work, and you just saved me a ton more. Exactly what I needed for some scripting. – Caddish

This doesn't seem to work with --command="SELECT :'filepath';", which is unfortunate. How can this be useful if it seems to require interactive terminal sessions? – Nuristan

However echo "\set filepath /path/to/my/directory/mydatafile.data \\\\\\\\ SELECT :'filepath';" | psql does work. Note the excessive number of escaping slashes to achieve two sequential \ characters, as per the advice under the command arg here, with a Bash shell anyway. – Nuristan

@Nuristan Yep, you can use psql -f - etc. Like many things, psql isn't exactly pretty, but it gets the job done. It's easier to use -v than to compose a \set command in stdin though. – Jobi

Variable expansion does not appear to be supported inside a \COPY command as of PSQL 15. \COPY (SELECT * FROM mydata WHERE item_id = :'item_id') TO './myfile.tsv' results in a syntax error at :'item_id'. – Barimah

@Barimah Very likely, there are all sorts of weird limitations in it. For exmple psql's conditionals cannot be used to guard the COPY data stream because the parser for \endif is confused by the \. to end the copy-stream... – Jobi

Answer 5

FWIW, the real problem was that I had included a semicolon at the end of my \set command:

\set owner_password 'thepassword';

The semicolon was interpreted as an actual character in the variable:

\echo :owner_password thepassword;

So when I tried to use it:

CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD :owner_password NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

...I got this:

CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD thepassword; NOINHERIT CREATEDB CREATEROLE VALID UNTIL 'infinity';

That not only failed to set the quotes around the literal, but split the command into 2 parts (the second of which was invalid as it started with "NOINHERIT").

The moral of this story: PostgreSQL "variables" are really macros used in text expansion, not true values. I'm sure that comes in handy, but it's tricky at first.

Nobleminded

Answer 6

postgres (since version 9.0) allows anonymous blocks in any of the supported server-side scripting languages

DO '
DECLARE somevariable int = -1;
BEGIN
INSERT INTO foo VALUES ( somevariable );
END
' ;

http://www.postgresql.org/docs/current/static/sql-do.html

As everything is inside a string, external string variables being substituted in will need to be escaped and quoted twice. Using dollar quoting instead will not give full protection against SQL injection.

Keyte

Answer 7

You need to use one of the procedural languages such as PL/pgSQL not the SQL proc language. In PL/pgSQL you can use vars right in SQL statements. For single quotes you can use the quote literal function.

Sarthe

It cannot be done in postgres itself, but it can be done in the PSQL client application. – Postage

plpgsql can (now) be used in postgres (since version 9.0) )postgresql.org/docs/9.0/static/sql-do.html – Keyte

Answer 8

I solved it with a temp table.

CREATE TEMP TABLE temp_session_variables (
    "sessionSalt" TEXT
);
INSERT INTO temp_session_variables ("sessionSalt") VALUES (current_timestamp || RANDOM()::TEXT);

This way, I had a "variable" I could use over multiple queries, that is unique for the session. I needed it to generate unique "usernames" while still not having collisions if importing users with the same user name.

Crabb

This seem to be the only working way in visual tools like Heidi SQL. – Germinative

Answer 9

Another approach is to (ab)use the PostgreSQL GUC mechanism to create variables. See this prior answer for details and examples.

You declare the GUC in postgresql.conf, then change its value at runtime with SET commands and get its value with current_setting(...).

I don't recommend this for general use, but it could be useful in narrow cases like the one mentioned in the linked question, where the poster wanted a way to provide the application-level username to triggers and functions.

Jobi

Answer 10

I really miss that feature. Only way to achieve something similar is to use functions.

I have used it in two ways:

• perl functions that use $_SHARED variable
• store your variables in table

Perl version:

   CREATE FUNCTION var(name text, val text) RETURNS void AS $$
        $_SHARED{$_[0]} = $_[1];
   $$ LANGUAGE plperl;
   CREATE FUNCTION var(name text) RETURNS text AS $$
        return $_SHARED{$_[0]};
   $$ LANGUAGE plperl;

Table version:

CREATE TABLE var (
  sess bigint NOT NULL,
  key varchar NOT NULL,
  val varchar,
  CONSTRAINT var_pkey PRIMARY KEY (sess, key)
);
CREATE FUNCTION var(key varchar, val anyelement) RETURNS void AS $$
  DELETE FROM var WHERE sess = pg_backend_pid() AND key = $1;
  INSERT INTO var (sess, key, val) VALUES (sessid(), $1, $2::varchar);
$$ LANGUAGE 'sql';

CREATE FUNCTION var(varname varchar) RETURNS varchar AS $$
  SELECT val FROM var WHERE sess = pg_backend_pid() AND key = $1;
$$ LANGUAGE 'sql';

Notes:

• plperlu is faster than perl
• pg_backend_pid is not best session identification, consider using pid combined with backend_start from pg_stat_activity
• this table version is also bad because you have to clear this is up occasionally (and not delete currently working session variables)

Joktan

Answer 11

Variables in psql suck. If you want to declare an integer, you have to enter the integer, then do a carriage return, then end the statement in a semicolon. Observe:

Let's say I want to declare an integer variable my_var and insert it into a table test:

Example table test:

thedatabase=# \d test;
                         Table "public.test"
 Column |  Type   |                     Modifiers                     
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('test_id_seq'::regclass)
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

Clearly, nothing in this table yet:

thedatabase=# select * from test;
 id 
----
(0 rows)

We declare a variable. Notice how the semicolon is on the next line!

thedatabase=# \set my_var 999
thedatabase=# ;

Now we can insert. We have to use this weird ":''" looking syntax:

thedatabase=# insert into test(id) values (:'my_var');
INSERT 0 1

It worked!

thedatabase=# select * from test;
 id  
-----
 999
(1 row)

Explanation:

So... what happens if we don't have the semicolon on the next line? The variable? Have a look:

We declare my_var without the new line.

thedatabase=# \set my_var 999;

Let's select my_var.

thedatabase=# select :'my_var';
 ?column? 
----------
 999;
(1 row)

WTF is that? It's not an integer, it's a string 999;!

thedatabase=# select 999;
 ?column? 
----------
      999
(1 row)

Skydive

The reason the semicolon does unexpected things for you is that a semicolon terminates a SQL statement, but you're typing a psql command, \set, which isn't SQL and does NOT take a terminating semicolon. Putting a semicolon on the next line won't hurt, but does absolutely nothing. It's an empty statement. – Quamash

Also, you shouldn't use the :'my_var' syntax for an integer value. :my_var works just fine. – Bulgar

Answer 12

I've found this question and the answers extremely useful, but also confusing. I had lots of trouble getting quoted variables to work, so here is the way I got it working:

\set deployment_user username    -- username
\set deployment_pass '\'string_password\''
ALTER USER :deployment_user WITH PASSWORD :deployment_pass;

This way you can define the variable in one statement. When you use it, single quotes will be embedded into the variable.

NOTE! When I put a comment after the quoted variable it got sucked in as part of the variable when I tried some of the methods in other answers. That was really screwing me up for a while. With this method comments appear to be treated as you'd expect.

Vevina

\set deployment_pass 'string_password' ALTER USER :deployment_user WITH PASSWORD :'deployment_pass'; – Keyte

\set is not SQL it's a psql built-in command sql comments are not supported. – Keyte

Answer 13

I've posted a new solution for this on another thread.

It uses a table to store variables, and can be updated at any time. A static immutable getter function is dynamically created (by another function), triggered by update to your table. You get nice table storage, plus the blazing fast speeds of an immutable getter.

Winged