How do you use script variables in psql?

sql postgresql variables psql
Craig Walker picture Craig Walker · Aug 31, 2008 · Viewed 231.2k times · Source

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?

Answer

crowmagnumb picture crowmagnumb · Aug 28, 2010

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;

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More