Explore Courses Blog Tutorials Interview Questions
0 votes
in SQL by (20.3k points)

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 a 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?

1 Answer

0 votes
by (40.7k points)

Postgres variables can be created by using the \set command, like this :

\set myvariable value

 Then, it can be substituted, this way:

SELECT * FROM :myvariable.table1;


SELECT * FROM table1 WHERE :myvariable IS NULL;

edit: In 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, like this :

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

Then you must include the quotes in the variable itself as the above query will not work. 

Instead, you can define your variable like this:

\set myvariable 'value'

However, if you want to make the string from an existing variable, Then use this:

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

Now you are having both the quoted and unquoted variable of the same string! And you can do in the similar manner  :

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

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 23, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

30.5k questions

32.5k answers


108k users

Browse Categories