psql执行脚本未创建表，pgAdmin查询工具执行却正常的问题咨询

问题现象 #

• 通过Windows控制台执行数据库脚本，全程无报错，但在pgAdmin中找不到预期创建的表
• 将脚本内容复制到pgAdmin的Query Tool中执行，却能正常创建表并查看数据

脚本内容 #

DROP DATABASE IF EXISTS test01 WITH (FORCE); -- 强制删除正在使用的数据库

CREATE DATABASE test01
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'German_Germany.1252'
    LC_CTYPE = 'German_Germany.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False
    ;

CREATE TABLE customers
(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts
(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
    CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
      ON DELETE CASCADE 
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');     
   
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev'); 

执行命令 #

通过Windows控制台执行脚本的命令：

"C:\Program Files\PostgreSQL\15\bin\psql.exe" -U postgres -f "C:\Users\my user name\Desktop\db_create.sql" postgres

问题原因与解决 #

核心原因 #

脚本创建test01数据库后，后续的建表、插入语句仍在初始连接的postgres数据库中执行，而非新创建的test01库。你在pgAdmin中查看的是test01库，自然看不到实际创建在postgres库中的表。

而在pgAdmin的Query Tool中执行时，你大概率是手动切换到了test01库后执行的建表语句，所以能正常看到结果。

解决方法 #

在脚本的CREATE DATABASE语句之后，添加psql的元命令\c test01，用于切换到新创建的数据库。修改后的脚本如下：

DROP DATABASE IF EXISTS test01 WITH (FORCE); -- 强制删除正在使用的数据库

CREATE DATABASE test01
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'German_Germany.1252'
    LC_CTYPE = 'German_Germany.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False
    ;

-- 切换到新创建的test01数据库
\c test01

CREATE TABLE customers
(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts
(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
    CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
      ON DELETE CASCADE 
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');     
   
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev'); 

再次通过控制台执行修改后的脚本，表就会正确创建在test01数据库中，pgAdmin也能正常看到。

内容的提问来源于stack exchange，提问作者Paul Efford