linux 解决psql postgresql中no schema has been selected to create in以及relation “XXX“ does not exist

问题

在运行别人的.sql文件时发生如下报错

\i ./load_table.sql

psql:/home/load_table.sql:26: ERROR: no schema has been selected to create in

LINE 1: create table XXX

psql:/home/load_table.sql:27: ERROR: relation "XXX" does not exist

解决问题

思路

Google了很久都没有解决问题，非常奇怪。然后有一次发现：才进入psql命令行的时候\dt命令能够正常运行

\dt

List of relations

Schema | Name | Type | Owner

--------+------+-------+----------

public | XXX | table | postgres

(1 row)

但是只要运行了.sql文件过后，运行\dt命令就会报错

\dt

Did not find any relations.

非常奇怪，所以怀疑是别人的.sql文件有问题。然后仔细阅读代码，发现代码中第一行就会设置search_path

set search_path = the_search_path2be_set;

解决方法

知道是search_path被更改就好办了 psql命令行执行：

CREATE SCHEMA the_search_path2be_set;

为用户添加search_path

ALTER USER user_name SET search_path to "$user", public, the_search_path2be_set;

金钥匙