How Do I Effectively Use Variables in PostgreSQL's psql?

Using script variables in PostgreSQL's psql

The way custom scripts use variables in PostgreSQL is different from MS SQL Server. In psql, use the set command to create variables:

<code class="language-sql">\set myvariable value</code>

To replace variables in a query, use the following syntax:

<code class="language-sql">SELECT * FROM :myvariable.table1;</code>

Alternatively, you can use it in a conditional statement:

<code class="language-sql">SELECT * FROM table1 WHERE :myvariable IS NULL;</code>

Starting with psql 9.1, variables can be expanded within quotes:

<code class="language-sql">\set myvariable value
SELECT * FROM table1 WHERE column1 = :'myvariable';</code>

In versions prior to psql 9.1, when using variables in conditional string queries, for example:

<code class="language-sql">SELECT * FROM table1 WHERE column1 = ':myvariable';</code>

Need to include quotes around the variable itself:

<code class="language-sql">\set myvariable 'value'</code>

For string manipulation, consider the following tips:

<code class="language-sql">\set quoted_myvariable '\'' :myvariable '\''</code>

This creates quoted and unquoted variables of the same string, allowing you to do the following:

<code class="language-sql">INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable;</code>

The above is the detailed content of How Do I Effectively Use Variables in PostgreSQL's psql?. For more information, please follow other related articles on the PHP Chinese website!