What is the proper syntax for Postgres SET expression

Question

There's a sql batch script that mostly consists of truncate copy and insert instructions. Script is tended to be executed as psql -f ./script.sql

After the tables being moved to specific schema trying to adjust the script I've added

\set search_path = new_schema, "$user", public;

at the top of the script. But this didn't helped. Script fails resolving table names. Besides there was an expression at the top as

\set ON_ERROR_STOP on

which actually works as expected

So my question is mainly how to make search_path works and in general regarding the syntax for the SET expression (are these the similar expressions as the syntax differs)

The leading \. Should it really starts the expression. Within the script truncate and insert expressions don't start with it. While \set and \copy do. Looks like this separates SQL and PSQL commands. Anyway set search_path seemed to be executed fine as SQL query as well. So should it really start with \set search_path
The trailing ;. As well there's a deviation in script in this regard. All the expressions are ended with ; except \set ON_ERROR_STOP on. Is it maybe like the scope of expression (e.g. ; - means single expression, and without it rules for the entire script scope as ON_ERROR_STOP does). So should the search_path as a global be not separated with ; as well. Even more interesting trying to separate \set ON_ERROR_STOP on; with ; fails the expression treating "on" as a string value and claiming to provide a boolean instead.
The = sign. set ON_ERROR_STOP has any delimiter before the value. Still I used to run successfully set search_path = before. And googled for that regard fine even third options as set some_setting to value. So are these three equivalent. Or are these from the different levels (e.g. SQL/PSQL or something). What should be used for the search_path after all.

Thank you

The answer from @mustaccio gives you the specific reason. You really need to read psql and SET for the broader picture. — Adrian Klaver
– Adrian Klaver, Commented Jun 1 at 15:23

mustaccio · Accepted Answer · 2025-06-01 14:31:14Z

2

You are confusing the SET SQL command, which you must use to modify search_path, and the \set psql meta-command, which you can only use to set psql variables. SET follows the SQL syntax rules, including the semicolon to terminate the command.

answered Jun 1 at 14:31

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adrian Klaver Jun 1 at 15:15

Minor point, from here System Administration Functions you can also use the set_config function to change the search_path value.

404 Jun 1 at 16:17

Oh. great. now it works without '\' prefix. As well the doc you linked clarifies { TO | = } option. Thank you

Collectives™ on Stack Overflow

What is the proper syntax for Postgres SET expression

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related