To understand a statement in Postgres

Question

How do you read the following line of code? The code is from SO by John Saunders. (Please add a link to it if you find it)

SET search_path TO so,"$user", public;

Context

START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;   
CREATE SCHEMA SO 
SET search_path TO so,"$user", public;        /// here
...

I read the line as "set your chosen PATH to your schema for a given user publicly". Public seems to mean that the PATH or user is public. I am not sure why we need to quota the variable user, since everything is lowercase.

Answer 1

It sets the current database connection's order of schema searching when an sql statement is presented without explicit schema notation.

In this case, the database query engine will search schema 'so' first, then a schema of the same name as the current database connection, finally a schema called public. I assume the quotes are because of the dollar sign.

Answer 2

The PostgreSQL documentation on the search_path option contains a good description of its purpose.

This is probably a rare command because the default is almost always exactly what you want. I've never needed to use it in any of the PostgreSQL work I've done.

Answer 3

Setting the search_path is extremely common when you have a database with enough tables that they've been split up into multiple schemas. The path to understand what it does and is used for is to start with the schema documentation, which covers the role of the public schema in things too.

The way $user is substituted when it appears in that path is described in the docs for search_path:

"If one of the list items is the special value $user, then the schema having the name returned by SESSION_USER is substituted, if there is such a schema".

Obviously your next question is then what's SESSION_USER? That starts as the username you connect to the database as, but can change via commands like SET SESSION AUTHORIZATION. The idea is that it can help you setup a schema for each user, which can be a useful way to lock down security for each user. I've only ever heard of that being used in a simple database hosting type of environment where each user gets their own role and schema but not their own database. You can probably leave the "$user" part out of there and not impact anything, it just shows up by default and most don't know you can remove it if you're not relying on it.

$user is in quotes because that is a reserved word in the SQL standard. You can name things using reserved words if you must in PostgreSQL, even though it's a bad idea, but you have to put them in quotes all the time so that the SQL parser doesn't misinterpret them. This can bite you with odd errors if you try and use a reserved word as the name of a column or table. Some you might accidentally try to use as column names in addition to "user" because they map to common real-world things include case, default, end, full, left, new, offset, order, and references.