Difference between set, \set and \pset in psql

postgresql psql postgresql-9.4

David S · Apr 12, 2015 · Viewed 12.7k times · Source

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

Answer

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

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 and, per documentation:

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

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

This command sets options affecting the output of query result tables