28

I get a little confused some times when working with psql between when to use a set vs. \set vs. \pset. I think that:

  • set is for session variables on my connection to the db. For example SET ROLE dba;
  • \set is for local variables for this psql session. For example \set time 'select current_timestamp'
  • \pset is for psql settings for this psql session. For example '\pset border 2'

But, I've never found what I thought was a good explanation of each. Are my assumptions above correct?

I'm using PostgreSQL 9.4

David S
  • 12,967
  • 12
  • 55
  • 93

1 Answers1

28

Basically correct. The important difference is that SET is an SQL command while the other two are psql meta-commands - indicated by the leading \.

SET is an SQL command to change run-time parameters. It is executed on the server and has nothing to do with psql per se.

\set is a psql meta-command:

Sets the psql variable name to value [...]

Note: This command is unrelated to the SQL command SET.

\pset is another psql meta-command:

This command sets options affecting the output of query result tables

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 9
    To make it easier to remember, think of the P in \pset as "print". pset values are ones that change how psql prints information out to you. \set changes other types of psql configuration. – Greg Smith Aug 19 '20 at 22:07