How do you use script variables in psql?

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'
   

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.

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.

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.

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)

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.