PostgreSQL

PostgreSQL’s psql \set versus SET

It is easy for someone who is new to PostgreSQL and who uses PostgreSQL’s terminal editor psql to confuse the commands \set and SET. This post contrasts these commands and provides a brief overview of other commands that include the word “set”.

The easiest way to remember how to differentiate \set from SET is to keep in mind that the “backslash commands” such as \set are “meta commands” for the command-line psql tool and do not mean anything to the PostgreSQL database itself. The SET command, which lacks a backslash, is a PostgreSQL database command that happens to be executed against the database from the psql command-line client.

Contrasting \set and SET
Command\setSET or set
(or other case-insensitive variation1)
Contextpsql terminal editor configuration meta command
(interactive client terminal configuration)
PostgreSQL database configuration command
(server configuration)
Ends with Semicolon?NoYes
Command Case Sensitive?Yes
(must be exactly \set)
No
Parameter/Variable Case Sensitive?Yes2No
How are Settings Displayed?\set3SHOW ALL; or show all;4
\echo :variable_nameSHOW variable_name;5
Examples\set AUTOCOMMIT onSET search_path TO myschema, public;
Footnotes

 

 

  1. I prefer to use all uppercase letters for SET to more clearly differentiate from /set.
  2. Two variables with same letters but different cases are two distinct variables.
  3. \set displays all variables when no arguments are provided to it.
  4. Any case variation (even sHoW aLl;) works.
  5. Any case variation of command and/or variable name also works.

 

 

There are two more psql meta commands that that “set” things and include the name “set”. The \pset met command configures how psql presents “query result tables.” Like \set, \pset can be specified without argument to see all of the current presentation settings.

Unlike the psql meta commands \set and \pset, the \gset psql metacommand does affect the PostgreSQL server because \gset submits the query buffer to the server and then stores the output returned from the server into specified psql variables. I discussed \gset with a few additional details in the blog post “Setting PostgreSQL psql Variable Based Upon Query Result.”

Although \set and SET can be used to set variables, the easiest way to distinguish between them is to consider that the backslash commands such as \set are psql commands (and so \pset sets variables in the psql client tool) and commands without the backslash such as SET are PostgreSQL commands sent to the server from psql or from any other client (but ultimately set variables on the server).

Published on System Code Geeks with permission by Dustin Marx, partner at our SCG program. See the original article here: PostgreSQL’s psql set versus SET

Opinions expressed by System Code Geeks contributors are their own.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button