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
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.