我有一个使用以下命令完成的 PostgreSQL 13 转储文件：

$ sudo -u postgres pg_dump --no-privileges -Fc -n my_schema --file="/path/to/my_schema.sql" my_database

--clean --if-exists但是，当我尝试将其恢复到新的数据库（PG15）时，由于该选项，我遇到了一个意想不到的问题：

$ sudo -u postgres pg_restore  --single-transaction --clean --if-exists --schema=my_schema --no-owner --role="app-user" --dbname=my_database /path/to/my_schema.sql
pg_restore: error: could not execute query: ERROR:  schema "my_schema" does not exist
LINE 1: CREATE TABLE my_schema.my_table (
                     ^
Command was: CREATE TABLE my_schema.my_table (
    id integer NOT NULL,
    name character varying(128),
    description text
);

在实际创建表之前不应该重新创建模式吗？

如果这有帮助，这里是转储文件的相关标题（抱歉，这是一种自定义二进制格式）：

$ head -n27 my_schema.sql
PGDMP0my_database13.16 (Debian 13.16-0+deb11u1)13.16 (Debian 13.16-0+deb11u1)-=0ENCODINENCODINGSET client_encoding = 'UTF8';
false=00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
false=00
SEARCHPATH
SEARCHPATH8SELECT pg_catalog.set_config('search_path', '', false);
false=126217698my_databasDATABASEgCREATE DATABASE my_database WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_US.UTF-8';
"DROP DATABASE my_database;
postgresfalse2615200013my_schemaSCHEMACREATE SCHEMA my_schema;
DROP SCHEMA my_schema;
app-userfalseo12592000138my_tableTABLECREATE TABLE my_schema.my_table (
    id integer NOT NULL,
    name character varying(128),
    description text
);
!DROP TABLE my_schema.my_table;
my_schemaheaapp-userfalse14p12592000144my_table_id_seSEQUENCE}CREATE SEQUENCE my_schema.my_table_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
+DROP SEQUENCE my_schema.my_table_id_seq;
my_schemaapp-userfalse62314=00my_table_id_seqSEQUENCE OWNED BYMALTER SEQUENCE my_schema.my_table_id_seq OWNED BY my_schema.my_table.id;

我能做什么吗？为什么会出现这个错误？

我想提取一些有关如何在给定 PostgreSQL 16 模式中填充表的基本统计信息，例如该模式中所有表中每列的空值的绝对数量和百分比。

目前，我正在使用 Python/psycopg2 获取/计算这些值，但我想知道是否可以在 PostgreSQL 本身内部存储某种函数，以便每次我想获取这些统计数据时都可以调用？

我想从现有的 shell 环境变量中设置一个 psql 变量，我可以在以后的psql命令中使用它。

我找到了\set元命令，但我无法让它在 Linux 中使用现有的环境变量：

user@localhost# MY_DB='test_database'
user@localhost# sudo -u postgres psql
\set dbname ${MY_DB}
\set 
...
dbname = '${MY_DB}'

而我想dbname = 'test_database'在最后一行这样我就可以使用它或像这样的例子：\connect :dbname;

我怎样才能在 debian 11 上使用 pg 15 实现这一目标？

这对我帮助不大，因为 \set dbname `echo ${MY_DB}` 发出的是一个空字符串。

这也不是，因为我只想设置我的 psql 变量一次，以避免冗余并在大量命令中重用它，而不是在每个命令中psql多次设置它。-vpsql

今天尝试恢复自定义转储文件时，我在 Windows server 2019 机器上遇到了一个奇怪的 PostgreSQL ( v.13.8 )。

以下命令有效：

pg_restore -d postgres://postgres@localhost:5432/postgres --no-owner --no-privileges --create --clean --role=<myuser> --if-exists inputfile.backup 2> inputfile.log

但只需将--single-transaction选项添加到命令行，就会引发错误：

pg_restore -d postgres://postgres@localhost:5432/postgres --no-owner --no-privileges --single-transaction --create --clean --role=<myuser> --if-exists inputfile.backup 2> inputfile.log

但我可以在日志文件中阅读：

pg_restore: error : options « -c/--clean » and « -a/--data-only » cannot be used together.

我什至没有使用-a旗帜……这很奇怪，不是吗？
我已经阅读了pg_restore doc，尤其是关于--single-transaction选项的段落，但它并没有说太多，（例如关于潜在的隐式-a标志）：

--单笔交易

将还原作为单个事务执行（即将发出的命令包装在 BEGIN/COMMIT 中）。这可确保所有命令都成功完成，或者不应用任何更改。此选项意味着 --exit-on-error。

任何人都可以告诉我使用此选项时到底发生了什么？

我正在尝试使用虚拟数据生成虚拟表，以测试某些 SQL 任务的性能。

基于这个旧线程：有没有办法将多行插入到所有列的默认值的表中？

我注意到，对于一个IDENTITY类型，使用建议的generate_series(1, N)解决方案不再可能用虚拟数据填充表。

那么，我如何将 1'000 个虚拟数据插入到例如下表 (PG 14) 中，利用该字段的默认值name而不覆盖该字段的系统值id：

CREATE TABLE foo (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT DEFAULT md5(random()::text)
);

尝试以下失败：

INSERT INTO foo (id) 
  SELECT generate_series(1, 1000);

-- which results in:
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
SQL state: 428C9

作为一种解决方法，我现在正在创建一个带有额外字段的表，以便i简单INTEGER地提供这个字段，而不是id在插入数据后将其删除，但它对我来说并不是 100% 干净的。

我希望有一些简单明了的东西，例如：

INSERT INTO foo DEFAULT VALUES (1000);

但这显然是一种无效的语法。

我已经打开了一个 SSH 隧道来连接到远程服务器，如下所示：

$ ssh -f -N -L 5433:127.0.0.1:5432 username@servername

这条隧道已精确开通如下（如 所示ps aux | grep ssh）：

ssh -f -N -L 5433:127.0.0.1:5432 username@servername

我确实有一个~/.pg_service.conf：

[my-pg-service]
    host=127.0.0.1
    port=5433
    dbname=mydatabase
    user=pguser

# just append the .pgpass file here:
[my-pg-service-2]
    host=127.0.0.1
    port=5433
    dbname=mydatabase
    user=pguser
    passfile=~/.pgpass

运行时：

$ psql service=my-pg-service

它目前（并且令人惊讶地）无需询问数据库密码即可连接！（可能它存储在某种缓存中，因为我在该命令之前已经使用过它？）

但是在使用时psql service=my-pg-service-2它实际上要求输入数据库密码。
我希望它会以相反的方式表现！

我的~/.pgpass( chmod 0600) 文件如下所示：

#hostname:port:database:username:password

# Remote pg database on server servername when using an SSH tunnel (5433)
127.0.0.1:5433:mydatabase:pguser:8+k3&4d2ihs1=&gp!*y)62xoh+^^z$&*ino!66jj()(yw@o36

请注意，此命令还要求输入数据库密码：

$ psql -d postgres://pguser@localhost:5433/mydatabase

但这不是：

$ psql -d postgres://[email protected]:5433/mydatabase

（我只更改localhost为127.0.0.1）。

这正是因为文件中没有以开头localhost的行.pgpass：

localhost:5433:mydatabase:pguser:8+k3&4d2ihs1=&gp!*y)62xoh+^^z$&*ino!66jj()(yw@o36

如果我添加此行，psql则在前面两种情况下都无需提示输入密码即可连接，但我仍然遇到 pg 服务的问题，例如，当指定passfile=~/.pgpass它时要求输入密码，而当没有时，它不会。

有人可以解释这种行为以及我做错了什么吗？
这可能与过于宽泛的服务器端配置有关吗？

以防万一，数据库在远程服务器上被 dockerized。它使用了官方的 postgis docker 镜像( 13:3.2)，它呈现了这些默认特性：

$ docker run \
  --rm \
  --name postgis \
  -e POSTGRES_DB=postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=changeme \
  -d postgis/postgis:13-3.2

$ docker exec -it postgis bash -c "tail -n 21 /var/lib/postgresql/data/pg_hba.conf"

# CAUTION: Configuring the system for local "trust" authentication
# allows any local user to connect as any PostgreSQL user, including
# the database superuser.  If you do not trust all your local users,
# use another authentication method.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

host all all all md5

操作系统：Ubuntu 21.10
PG：14

文档：
https ://www.postgresql.org/docs/14/libpq-pgservice.html
https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-PARAMKEYWORDS
https://www。 postgresql.org/docs/14/libpq-pgpass.html

我想使用复制表的 5 列进行导出，其中 3 列包含 ID、文本和时间戳值，但其中 2 列包含 JSON 和 GeoJSON 元素，例如：

 // Please, note the non-quoted string Value1
 // when there is no space in the value itself:
{"key": "value with space", "other_key": Value1, ...}

但是当使用这个命令时：

psql \
  -d <connection_uri> \
  --command "\copy public.mytable (id, json_array, geojson_field, status, timestamp) TO './file.csv' DELIMITER ';' CSV HEADER ENCODING 'UTF8' QUOTE '\"' ;"

结果看起来很奇怪：

11784,"{'"First value'",'"Second value'", ThirdValue, '"Fourth val'",...

即它以奇怪的顺序放置引号：“single-double <field_value> single-double”

（我也没有得到标题......）

我希望我可以在 csv 文件中以一个真正的 JSON 对象结束，我可以复制/粘贴到验证器中，例如这里没有错误。

难道我做错了什么？
如果是，如何解决？

PG:13

我想知道是否有比以下更好的解决方案来构造一个新列（place_type_new），方法是place_type根据第三列的值（inhabitant在此处显示的案例）：

  UPDATE places_table SET place_type_new = CASE
      WHEN inhabitant = 0 AND place_type LIKE 'Village' THEN 'Village_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'Village' THEN 'Village_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'Village' THEN 'Village_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'Village' THEN 'Village_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'Village' THEN 'Village_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'Village' THEN 'Village_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'Village' THEN 'Village_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'Village' THEN 'Village_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'Village' THEN 'Village_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'VillagePart' THEN 'VillagePart_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXXL'
      ELSE place_type
  END;

这里Village,VillagePart和是更大的一组地方的一部分（例如还有,Neighborhood等等），我只希望这条规则适用于这 4 个元素，因此我的陈述无处不在。NeighborhoodPartTownCityLIKE

但我觉得这非常丑陋，但它工作得很好。附加到现有字段值places_table以构建新字段值的后缀始终根据此模式place_table_new匹配列中的相同数字：inhabitant

0     : XXXS
10    :  XXS
20    :   XS
100   :    S
2000  :    M
5000  :    L
10000 :   XL
20000 :  XXL
50000 : XXXL

为了实现这一点，用这个映射构建一个中间表会更好吗？

我在 Ubuntu 18.04 上使用 PostgreSQL 12。我还将这里的问题缩小到尽可能简单的范围内，希望不会忘记一些重要的事情。

我在 PostgreSQL 12 中有一个触发函数，它执行如下操作：

CREATE OR REPLACE FUNCTION "my_latlon_function"()
RETURNS trigger AS
$$
BEGIN
    latcolumn:= (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lat.*'
    );
    loncolumn := (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lon.*'
    );
    EXECUTE 'select $1.' || loncolumn USING NEW INTO lon;
    EXECUTE 'select $1.' || latcolumn USING NEW INTO lat;

    -- do much stuff

    RETURN NEW;

END
$$
LANGUAGE 'plpgsql';

问题是，latandlon列的名称中有大写字母，例如myLatitudeand myLongitude。触发器函数能够检索这些名称，这没问题。

问题出在两个EXECUTE语句中，其中列名似乎变成了小写，如该错误所述（在触发触发器时的基础 QUERY 中）：

ERROR:  column "mylongitude" not found in data type gpspoints
LINE 1: select $1.myLongitude
               ^
QUERY:  select $1.myLongitude
CONTEXT:  PL/pgSQL function my_latlon_function() line 24 at EXECUTE
********** Error **********

ERROR: column "mylongitude" not found in data type gpspoints
SQL state: 42703
Context: PL/pgSQL function my_latlon_function() line 24 at EXECUTE

我知道 PostgreSQL 确实需要将名称中包含大写字母的列名括在双引号中。因此，我尝试在两个 EXECUTE 语句中设置双引号，例如：

    EXECUTE 'select $1.' || "loncolumn" USING NEW INTO lon;
    EXECUTE 'select $1.' || "latcolumn" USING NEW INTO lat;

但是错误保持不变。

如果可能，如何在 PostgreSQL 触发器函数中处理 CamelCase 列名？

如果不是，为什么？

语境

我正在使用PostgreSQL 10.12。

我希望能够在某些具有地理数据的特定表上触发触发器功能。

此触发函数旨在将PotGIS点创建到geom来自lat和lon值的列中。
每次插入新行时，我想在多个表上应用相同的触发器函数。
保存纬度和经度值的列的命名在表之间并不一致，但它遵循一种模式......

我 100% 肯定的是：
1. 总是有两列包含纬度和经度值，
2. 这些列的名称在不同的表中并不总是相同（否则它会太简单），但是“纬度”一词和“经度”一词 始终出现在它们的名称中，而从不出现在其他列名中。

例如在表 A 中：

_loc_longitude_和_loc_latitude_

在表 B 中：

_building_longitude_center和_building_latitude_center

例如（+许多其他）。

触发函数如下：

CREATE OR REPLACE FUNCTION make_point_with_latlon()
  RETURNS trigger AS
  $$
  DECLARE
      varlon := NULL;
      varlat := NULL;
  BEGIN
      IF to_jsonb(NEW) SIMILAR TO '.*longitude.*' THEN
          varlon := NEW.the_column_whith_longitude_in_its_name;
      END IF;
      IF to_jsonb(NEW) SIMILAR TO '.*latitude.*' THEN
          varlat := NEW.the_column_whith_latitude_in_its_name;
      END IF;
      NEW.geom = ST_MakePoint(varlon, varlat);
  END
  $$
LANGUAGE 'plpgsql';

使用以下触发器：

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table1
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table2
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table3
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

-- (...and many more other tables that need the same trigger).

我不知道如何.*latitude.*在传递给函数的列名称中搜索模式，以及如何将它们的值转换为我可以用来构建点的两个变量。
而且我搜索这类东西的谷歌搜索技巧远非完美，因此返回嘈杂的结果。

问题

有没有可能这样做？

语境

我在包含观察点的PostgreSQL (+ Postgis ) 表上设置了一个触发器。

此触发器必须为每个插入或更新的点计算一个新几何。这个新几何必须计算为当前插入的特征点的几何投影到另一个包含线特征的表的最近线上。

我做了什么

触发器如下，EXPLAIN ANALYZE在新插入后正确触发：

插入新点时不会引发错误，但投影几何列proj_geom保持为空：

CREATE OR REPLACE FUNCTION project_funct()
  RETURNS trigger AS
  $$
  BEGIN
    -- Here I wish I could update several fields (line_id, dist and proj_geom) at the same time
    -- but for the moment I only put the focus on the new geometry proj_geom:
    NEW.projected_geom := ( 
      SELECT sub.proj_geom
      FROM
        ( SELECT
             points.id AS point_id,
             schema.lines.id AS line_id,
             ST_Distance(schema.lines.geom, NEW.geom) AS dist,
             ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
          FROM schema.line, points
            -- Something is weird to me there, I should probably not have this WHERE statement here as the 
            -- trigger should be used on a single row at a time, hence with a single ID for each execution:
            WHERE points.id = NEW.id 
            ORDER BY dist
            LIMIT 1
        ) AS sub
    );
      RETURN NEW;
  END
  $$
LANGUAGE 'plpgsql';
CREATE TRIGGER proj_trigger
    -- Here, I don't really know if I must used BEFORE or AFTER, AFTER sounds better to me 
    -- but I may be totally wrong. I also wish I could keep the same code for both UPDATE and
    -- INSERT but this may not be a good idea?:
    BEFORE INSERT OR UPDATE ON points 
    FOR EACH ROW
    EXECUTE PROCEDURE project_funct();

正如我昨天晚上发现的触发器一样，即使我已经尽力了，我的代码也可能充满了错误。
我还注意到一些我已经拥有并且实际上正在工作的 SQL 脚本，......不再以这种花哨的（？）编写触发器的方式工作（例如，我不知道双美元符号的含义$$）。

注意：
如果我取出最里面的SELECT语句，并将单词替换为NEWpoints 表的实际名称，则查询，当直接在具有指定 ID 的pgAdmin中运行时，例如WHERE points.id = 41返回正确的结果：

在触发器中，我的第一条评论与我希望能够在每次插入观察时同时更新表的line_id,dist和proj_geom字段这一事实有关。points

问题

我怎样才能修复我的代码，至少能够获得新的几何图形？
我很确定我遗漏了一些微小的细节（可能是我在代码中留下了一些我有实际感觉的注释的地方）但我无法弄清楚它们，我现在已经做了太多的hit'n'try使用触发功能（它们都不像上面的那样好用）。

环境

Ubuntu 18.04
PostgreSQL：10.12
PostGIS：2.4