How do you use script variables in psql?

by

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;

More Related Contents:

  1. Is there a shortcut for SELECT * FROM?
  2. PostgreSQL: encoding problems on Windows when using psql command line utility
  3. Store and reuse value returned by INSERT … RETURNING
  4. Select rows which are not present in other table
  5. Optimize GROUP BY query to retrieve latest row per user
  6. How to implement a many-to-many relationship in PostgreSQL?
  7. Aggregate columns with additional (distinct) filters
  8. Check if value exists in Postgres array
  9. PostgreSQL GROUP BY different from MySQL?
  10. Return multiple columns of the same row as JSON array of objects
  11. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  12. Normalize array subscripts so they start with 1
  13. Getting all Buildings in range of 5 miles from specified coordinates
  14. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  15. How to select id with max date group by category in PostgreSQL?
  16. Call a set-returning function with an array argument multiple times
  17. Fastest check if row exists in PostgreSQL
  18. PostgreSQL ORDER BY issue – natural sort
  19. Discard millisecond part from timestamp
  20. Return setof record (virtual table) from function
  21. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  22. SQL column reference “id” is ambiguous
  23. How to use multiple CTEs in a single SQL query?
  24. IN Clause with NULL or IS NULL
  25. postgres – where in (list) – column does not exist
  26. incomplete information from query on pg_views [duplicate]
  27. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  28. Insert data and set foreign keys with Postgres
  29. Fetch records that are non zero after the decimal point in PostgreSQL
  30. How to use a ring data structure in window functions

Leave a Comment