<p>In MS SQL Server, I create my scripts to use customizable variables:</p>

<pre><code>DECLARE @somevariable int  
SELECT @somevariable = -1

INSERT INTO foo VALUES ( @somevariable )
</code></pre>

<p>I'll then change the value of <code>@somevariable</code> 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.</p>

<p>How do I do the same with the PostgreSQL client <code>psql</code>?</p>

[{'answer_id': 36979, 'author': 'Craig Walker', 'author_id': 3488, 'author_profile': 'https://Stackoverflow.com/users/3488', 'pm_score': 4, 'selected': False, 'text': '<p>FWIW, the real problem was that I had included a semicolon at the end of my \\set command:</p>\n\n<blockquote>\n  <p>\\set owner_password \'thepassword\';</p>\n</blockquote>\n\n<p>The semicolon was interpreted as an actual character in the variable:</p>\n\n<blockquote>\n  <p>\\echo :owner_password\n  thepassword;</p>\n</blockquote>\n\n<p>So when I tried to use it:</p>\n\n<blockquote>\n  <p>CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD :owner_password NOINHERIT CREATEDB CREATEROLE VALID UNTIL \'infinity\';</p>\n</blockquote>\n\n<p>...I got this:</p>\n\n<blockquote>\n  <p>CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD thepassword; NOINHERIT CREATEDB CREATEROLE VALID UNTIL \'infinity\';</p>\n</blockquote>\n\n<p>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"). </p>\n\n<p>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.</p>\n'}, {'answer_id': 36989, 'author': 'Craig Walker', 'author_id': 3488, 'author_profile': 'https://Stackoverflow.com/users/3488', 'pm_score': 6, 'selected': False, 'text': "<p>One final word on PSQL variables:</p>\n\n<ol>\n<li><p>They don't expand if you enclose them in single quotes in the SQL statement. \nThus this doesn't work:</p>\n\n<pre><code>SELECT * FROM foo WHERE bar = ':myvariable'\n</code></pre></li>\n<li><p>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 <em>second</em> set of quotes, and the inner set has to be escaped. Thus you need:</p>\n\n<pre><code>\\set myvariable '\\'somestring\\''  \nSELECT * FROM foo WHERE bar = :myvariable\n</code></pre>\n\n<p><strong>EDIT</strong>: starting with PostgreSQL 9.1, you may write instead:</p>\n\n<pre><code>\\set myvariable somestring\nSELECT * FROM foo WHERE bar = :'myvariable'\n</code></pre></li>\n</ol>\n"}, {'answer_id': 113366, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 4, 'selected': False, 'text': '<p>You need to use one of the procedural languages such as PL/pgSQL not the SQL proc language.\nIn PL/pgSQL you can use vars right in SQL statements.\nFor single quotes you can use the quote literal function. </p>\n'}, {'answer_id': 3588796, 'author': 'crowmagnumb', 'author_id': 433432, 'author_profile': 'https://Stackoverflow.com/users/433432', 'pm_score': 9, 'selected': True, 'text': "<p>Postgres variables are created through the \\set command, for example ...</p>\n\n<pre><code>\\set myvariable value\n</code></pre>\n\n<p>... and can then be substituted, for example, as ...</p>\n\n<pre><code>SELECT * FROM :myvariable.table1;\n</code></pre>\n\n<p>... or ...</p>\n\n<pre><code>SELECT * FROM table1 WHERE :myvariable IS NULL;\n</code></pre>\n\n<p><em>edit: As of psql 9.1, variables can be expanded in quotes as in:</em>  </p>\n\n<pre><code>\\set myvariable value \n\nSELECT * FROM table1 WHERE column1 = :'myvariable';\n</code></pre>\n\n<p><em>In older versions of the psql client:</em>  </p>\n\n<p>... If you want to use the variable as the value in a conditional string query, such as ...</p>\n\n<pre><code>SELECT * FROM table1 WHERE column1 = ':myvariable';\n</code></pre>\n\n<p>... then you need to include the quotes in the variable itself as the above will not work.  Instead define your variable as such ...</p>\n\n<pre><code>\\set myvariable 'value'\n</code></pre>\n\n<p>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 ...</p>\n\n<pre><code>\\set quoted_myvariable '\\'' :myvariable '\\''\n</code></pre>\n\n<p>Now you have both a quoted and unquoted variable of the same string!  And you can do something like this ....</p>\n\n<pre><code>INSERT INTO :myvariable.table1 SELECT * FROM table2 WHERE column1 = :quoted_myvariable;\n</code></pre>\n"}, {'answer_id': 9910705, 'author': 'Nate', 'author_id': 1085691, 'author_profile': 'https://Stackoverflow.com/users/1085691', 'pm_score': 2, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>\\set deployment_user username    -- username\n\\set deployment_pass '\\'string_password\\''\nALTER USER :deployment_user WITH PASSWORD :deployment_pass;\n</code></pre>\n\n<p>This way you can define the variable in one statement.  When you use it, single quotes will be embedded into the variable.</p>\n\n<p>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.</p>\n"}, {'answer_id': 13318869, 'author': 'Craig Ringer', 'author_id': 398670, 'author_profile': 'https://Stackoverflow.com/users/398670', 'pm_score': 5, 'selected': False, 'text': "<p>Specifically for <code>psql</code>, you can pass <code>psql</code> variables from the command line too; you can pass them with <code>-v</code>. Here's a usage example:</p>\n\n<pre><code>$ psql -v filepath=/path/to/my/directory/mydatafile.data regress\nregress=&gt; SELECT :'filepath';\n               ?column?                \n---------------------------------------\n /path/to/my/directory/mydatafile.data\n(1 row)\n</code></pre>\n\n<p>Note that the colon is unquoted, then the variable name its self is quoted. Odd syntax, I know. This only works in psql; it won't work in (say) PgAdmin-III.</p>\n\n<p>This substitution happens during input processing in psql, so you can't (say) define a function that uses <code>:'filepath'</code> and expect the value of <code>:'filepath'</code> to change from session to session. It'll be substituted once, when the function is defined, and then will be a constant after that. It's useful for scripting but not runtime use.</p>\n"}, {'answer_id': 13318876, 'author': 'Craig Ringer', 'author_id': 398670, 'author_profile': 'https://Stackoverflow.com/users/398670', 'pm_score': 3, 'selected': False, 'text': '<p>Another approach is to (ab)use the PostgreSQL GUC mechanism to create variables. See <a href="https://stackoverflow.com/a/13172964/398670">this prior answer</a> for details and examples.</p>\n\n<p>You declare the GUC in <code>postgresql.conf</code>, then change its value at runtime with <code>SET</code> commands and get its value with <code>current_setting(...)</code>.</p>\n\n<p>I don\'t recommend this for general use, but it could be useful in narrow cases like the one mentioned in the linked question, where the poster wanted a way to provide the application-level username to triggers and functions.</p>\n'}, {'answer_id': 13961901, 'author': 'Kaiko Kaur', 'author_id': 1306381, 'author_profile': 'https://Stackoverflow.com/users/1306381', 'pm_score': 2, 'selected': False, 'text': "<p>I really miss that feature. Only way to achieve something similar is to use functions.</p>\n\n<p>I have used it in two ways:</p>\n\n<ul>\n<li>perl functions that use $_SHARED variable</li>\n<li>store your variables in table</li>\n</ul>\n\n<p>Perl version:</p>\n\n<pre><code>   CREATE FUNCTION var(name text, val text) RETURNS void AS $$\n        $_SHARED{$_[0]} = $_[1];\n   $$ LANGUAGE plperl;\n   CREATE FUNCTION var(name text) RETURNS text AS $$\n        return $_SHARED{$_[0]};\n   $$ LANGUAGE plperl;\n</code></pre>\n\n<p>Table version:</p>\n\n<pre><code>CREATE TABLE var (\n  sess bigint NOT NULL,\n  key varchar NOT NULL,\n  val varchar,\n  CONSTRAINT var_pkey PRIMARY KEY (sess, key)\n);\nCREATE FUNCTION var(key varchar, val anyelement) RETURNS void AS $$\n  DELETE FROM var WHERE sess = pg_backend_pid() AND key = $1;\n  INSERT INTO var (sess, key, val) VALUES (sessid(), $1, $2::varchar);\n$$ LANGUAGE 'sql';\n\nCREATE FUNCTION var(varname varchar) RETURNS varchar AS $$\n  SELECT val FROM var WHERE sess = pg_backend_pid() AND key = $1;\n$$ LANGUAGE 'sql';\n</code></pre>\n\n<p>Notes:</p>\n\n<ul>\n<li>plperlu is faster than perl</li>\n<li>pg_backend_pid is not best session identification, consider using pid combined with backend_start from pg_stat_activity</li>\n<li>this table version is also bad because you have to clear this is up occasionally (and not delete currently working session variables)</li>\n</ul>\n"}, {'answer_id': 15296222, 'author': 'skaurus', 'author_id': 320345, 'author_profile': 'https://Stackoverflow.com/users/320345', 'pm_score': 6, 'selected': False, 'text': '<p>You can try to use a <a href="http://www.postgresql.org/docs/9.2/static/queries-with.html" rel="noreferrer">WITH</a> clause.</p>\n\n<pre><code>WITH vars AS (SELECT 42 AS answer, 3.14 AS appr_pi)\nSELECT t.*, vars.answer, t.radius*vars.appr_pi\nFROM table AS t, vars;\n</code></pre>\n'}, {'answer_id': 26588299, 'author': 'geon', 'author_id': 446536, 'author_profile': 'https://Stackoverflow.com/users/446536', 'pm_score': 3, 'selected': False, 'text': '<p>I solved it with a temp table. </p>\n\n<pre><code>CREATE TEMP TABLE temp_session_variables (\n    "sessionSalt" TEXT\n);\nINSERT INTO temp_session_variables ("sessionSalt") VALUES (current_timestamp || RANDOM()::TEXT);\n</code></pre>\n\n<p>This way, I had a "variable" I could use over multiple queries, that is unique for the session. I needed it to generate unique "usernames" while still not having collisions if importing users with the same user name.</p>\n'}, {'answer_id': 37150314, 'author': 'Jasen', 'author_id': 471930, 'author_profile': 'https://Stackoverflow.com/users/471930', 'pm_score': 4, 'selected': False, 'text': '<p>postgres (since version 9.0) allows anonymous blocks in any of the supported server-side scripting languages</p>\n\n<pre><code>DO \'\nDECLARE somevariable int = -1;\nBEGIN\nINSERT INTO foo VALUES ( somevariable );\nEND\n\' ;\n</code></pre>\n\n<p><a href="http://www.postgresql.org/docs/current/static/sql-do.html" rel="noreferrer">http://www.postgresql.org/docs/current/static/sql-do.html</a></p>\n\n<p>As everything is inside a string, external string variables being substituted in will need to be escaped and quoted twice.  Using dollar quoting instead will not give full protection against SQL injection.</p>\n'}, {'answer_id': 53235522, 'author': 'Alexander Kleinhans', 'author_id': 3049865, 'author_profile': 'https://Stackoverflow.com/users/3049865', 'pm_score': 2, 'selected': False, 'text': '<p>Variables in <code>psql</code> 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:</p>\n\n<p>Let\'s say I want to declare an integer variable <code>my_var</code> and insert it into a table <code>test</code>:</p>\n\n<p>Example table <code>test</code>:</p>\n\n<pre><code>thedatabase=# \\d test;\n                         Table "public.test"\n Column |  Type   |                     Modifiers                     \n--------+---------+---------------------------------------------------\n id     | integer | not null default nextval(\'test_id_seq\'::regclass)\nIndexes:\n    "test_pkey" PRIMARY KEY, btree (id)\n</code></pre>\n\n<p>Clearly, nothing in this table yet:</p>\n\n<pre><code>thedatabase=# select * from test;\n id \n----\n(0 rows)\n</code></pre>\n\n<p>We declare a variable. <strong>Notice how the semicolon is on the next line!</strong></p>\n\n<pre><code>thedatabase=# \\set my_var 999\nthedatabase=# ;\n</code></pre>\n\n<p>Now we can insert. We have to use this weird "<code>:\'\'</code>" looking syntax:</p>\n\n<pre><code>thedatabase=# insert into test(id) values (:\'my_var\');\nINSERT 0 1\n</code></pre>\n\n<p>It worked!</p>\n\n<pre><code>thedatabase=# select * from test;\n id  \n-----\n 999\n(1 row)\n</code></pre>\n\n<p><strong>Explanation:</strong></p>\n\n<p>So... what happens if we don\'t have the semicolon on the next line? The variable? Have a look:</p>\n\n<p>We declare <code>my_var</code> without the new line.</p>\n\n<pre><code>thedatabase=# \\set my_var 999;\n</code></pre>\n\n<p>Let\'s select <code>my_var</code>.</p>\n\n<pre><code>thedatabase=# select :\'my_var\';\n ?column? \n----------\n 999;\n(1 row)\n</code></pre>\n\n<p>WTF is that? It\'s not an <em>integer</em>, it\'s a <strong>string</strong> <code>999;</code>!</p>\n\n<pre><code>thedatabase=# select 999;\n ?column? \n----------\n      999\n(1 row)\n</code></pre>\n'}, {'answer_id': 54322929, 'author': 'Brev', 'author_id': 443211, 'author_profile': 'https://Stackoverflow.com/users/443211', 'pm_score': 1, 'selected': False, 'text': '<p>I\'ve posted a new solution for this <a href="https://stackoverflow.com/questions/13316773/is-there-a-way-to-define-a-named-constant-in-a-postgresql-query/54322738#54322738">on another thread</a>.</p>\n\n<p>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.</p>\n'}]
['https://Stackoverflow.com/questions/36959', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3488/']