为什么不同的 postgres 表不能使用同名的索引？
我正在尝试将一些表从基本表更改为每月分区表。正常的解决方案似乎是
CREATE TABLE copy (LIKE original INCLUDING INDEXES) PARTITION BY RANGE xxxxINSERT INTO copy (SELECT * FROM original WHERE ???)DROP TABLE originalALTER TABLE copy RENAME TO original我当前的问题是，尽管CREATE TABLE ... LIKE ... INCLUDING INDEXES将使用所有相同的字段名称和数据类型，但它将构成一组新的索引名称。由于各种原因，这些数据库上的索引需要符合固定的命名约定。
有没有办法复制一个表并将索引名称复制到？
我有一些简单的 SQL 来重置表中的一些值
UPDATE tableA SET field1 = 0
WHERE field2 IN (SELECT .......)
这工作正常，但我想修改它以返回所有field1值在重置为零之前的值。这是我试过的......
UPDATE tableA SET field1 = 0
WHERE field2 IN (SELECT .......)
RETURNING field1
要注意的是，这仅在修改后返回 field1 的值。是否可以事先获得价值？不运行单独的查询？
我有一张大部分时间都是空的桌子。偶尔会有大量数据被转储到其中，需要尽快处理。
我有一个持续检查表并处理它找到的任何数据的过程。
测试表中任何数据的最快/最有效的方法是什么？
显而易见的解决方案是......
SELECT count(*) FROM myTable;
但这可能需要一段时间（特别是如果其中有几百万行）。另一种解决方案是
SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'myTable'
但这只是给出了一个近似值。
有没有更好/更快的解决方案。注意 - 我不是在寻找确切的行数，我在寻找简单的空/非空布尔结果。
我正在运行 Postgres 14.6，并且有许多会话描述wait event为Timeout: VacuumDelay. 会话正在运行的 SQL 是......
autovacuum: VACUUM public.mytable_202110 (to prevent wraparound)
（此信息是从 pgAdmin4 的仪表板上收集的）
我有很多问题
没有列出任何阻塞 PIDS，并且已授予所有锁。数据库已启动并正在运行（尽管速度很慢）
更新——这可能是相关的，这个数据库刚刚从一台旧的、慢的服务器转移到一台更新更好的机器上。我希望看到明显更好的性能，但我猜测表上的统计数据和分析数据不会与原始数据一起传输，因此新数据库花费了大量资源来分析旧的存档数据。
我有一个将记录插入表中的过程。
该表是用这样的触发器定义的......
CREATE TRIGGER myTrig AFTER INSERT ON myTable FOR EACH ROW EXECUTE PROCEDURE myProcedure();
进入 myTable的过程不在INSERT我的控制范围内，它基本上只是全天 24/7 将记录喷到表中。
中的功能myProcedure()需要数据库资源。在繁忙时期，记录到达的速率大于数据库可以处理的速度（大约每秒 500 条记录是极限）。如果我使用禁用触发器，ALTER TABLE myTable DISABLE TRIGGER myTrig 那么系统每秒可以处理 1000 条记录。
然后我可以在系统不那么忙时重新启用触发器，但显然那些在禁用触发器时添加的记录将不会得到正确处理。
有什么方法可以在表中已经选定的记录上重新运行触发器吗？
我目前最好的猜测是，在繁忙时期，我应该将传入的记录转移到与myTable原始记录相同的副本（但没有触发器），然后当系统稍后安静下来时，我可以从副本插入，回到原来的位置应该由触发器处理（尽管晚于它们应该处理的时间）
我需要向表中添加新字段。该字段将仅存储 0 到 30 之间的数字，因此（理论上）单个字节字段应该没问题。
文档中列出的最小整数数据类型是 SMALLINT，它需要 2 个字节。
我要向其添加字段的表已分区，目前包含大约 30 亿行。因此，向每一行添加一个 2 字节的字段应该需要 60 亿字节，或 6 GB。
由于我仍然没有弄清楚的原因，（请参阅这个问题）我在向大表添加 SMALLINT 字段时空间不足。
问题 - 是否有一种单字节数据类型可以用来存储只有很小范围值的数字？
我正在尝试将一个INTEGER DEFAULT 0字段添加到分布在两个表空间中的非常大的（分区的）表中。
每个表空间都在不同的磁盘上（一个在 C 盘上，另一个在 D 盘上）。我收到以下错误
ERROR: could not extend file "pg_tblspc/31575/PG_10201707211/31576/1155134.27": No space left on device
HINT: Check free disk space
SQL state: 53100
C盘和D盘都有一个名为1155134.27的目录，pg_tblspc/31575/PG_10201707211/31576但都没有一个名为1155134.27的文件（我猜这是一个临时文件？）
C 盘有 70 GB 的可用空间，D 盘有 350 GB 的可用空间。
问题 1 - 如何确定两个磁盘中哪一个实际空间不足？
问题 2 - 如何计算出实际需要多少可用空间？一个整数需要4个字节存储，表由按月分区的数据组成，每个月有大约6000万行，表有4年的数据。4 x 60 x 12 x 4 = 大约 12,000 Mbytes 或 12 GB 因此根据我的简单数学计算，每张光盘上都应该有足够的空间来满足所需的所有额外空间。那么为什么 postgres 需要更多呢？
(postgres 版本 10.4)
每张光盘上的文件结构可能是相同的，因为每个表空间都是用非常相似的命令创建的……
CREATE TABLESPACE fastDb OWNER dbOwn LOCATION 'c:/pgdata/fdb';
CREATE TABLESPACE slowDb OWNER dbOwn LOCATION 'd:/pgdata/fdb';
我已经成功地通过重新运行查询（需要数小时）并监视操作系统的可用空间来回答第一个问题 - d：驱动器是空间不足的驱动器。
仍然无法计算出需要多少空间。我在 d: 驱动器上释放了 800Gb，并切换到 SMALLINT 字段（2 个字节而不是 4 个字节），但我的空间仍然不足。
是否有任何技术/工具/方法来调查 postgres 表中的行级损坏？
这个论坛上提出的大多数解决方案都归结为“尝试执行 VACUUM FULL 来重建表，如果这不起作用，请删除表并从备份中恢复”
反正有没有真正修复数据？是否有读取和手动编辑原始 postgres 数据文件并仅删除/修复损坏的行的方法？
我正在使用的系统直接从外部提要读取数据到 postgres 表中，然后在夜间备份过程有机会保存它之前显示损坏的证据。重新运行外部提要不是一种选择，因此在实践中，如果我有任何损坏，一整天的数据都会丢失。
UPDATE如果它尝试更改的记录被另一个进程锁定（而不是等待锁定被释放），是否可以编写一个简单退出的查询？
我有一个应该更新表中记录的过程，有时这些记录被锁定。更新这些记录是可取的，但不是必需的。如果记录正在使用中，我宁愿我的过程只是忘记更新并转移到更重要的事情上。
我目前的方法是将命令超时设置为 1 秒，但即使这比我想等待的时间还要长 - 正常更新需要几分之一毫秒，所以等待一秒是一个主要开销。
我在连接到 postgres 数据库的 Windows 上运行 powershell 脚本。
在正常情况下一切正常，但我正在努力使系统更强大，并在断电或重新启动后自动重新启动一切。
问题是，虽然脚本和数据库在启动时都成功启动，但脚本运行得更快——事实上它在数据库可用之前重新启动。
该脚本与包含此内容的连接字符串连接。...
Timeout=60;CommandTimeout=8
我的理解（也许有缺陷？）是Timeout=60指连接超时，应该意味着“尝试连接到数据库长达 60 秒，如果没有连接则退出并显示错误消息”。但是，在实践中，连接尝试会立即失败并出现错误
the database system is starting
为什么它立即失败而不等待 60 秒？在尝试连接之前，是否有任何方法可以测试数据库是否已重新启动？还是我只需要继续尝试连接直到成功？
我有一个 postgres 数据库，其中包含一个字段 (VARCHAR)，它是与数据库位于同一服务器上的文件的完整路径和文件名。
我可以这样列出文件。
SELECT myFileName FROM tableA;
有一个问题 - 一些文件实际上并不存在（反正不在它们列出的位置）。
我可以使用 pg_stat_file() 列出任何存在的文件的信息
SELECT myFileName, pg_stat_file(myFileName) FROM tableA;
但是，如果我的数据库中引用的一个文件实际上并不存在，那么上面的查询就会失败并显示相应的错误消息
ERROR: could not stat file "?????????????????": No such file or directory.
问题 - 是否可以编写 postgres SQL 查询来指示文件是否存在？
我有两个相同的数据库，开发和生活。
我对他们两个都运行这个查询....
select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device)
在开发盒上查询大约需要 100 毫秒，在现场大约需要一个小时。
这两个表都有一个名为 的主键字段deviceId，两个数据库具有相同的结构，并且都具有非常相似的数据（没有一个表包含超过百万行）。两个数据库都定期清理/分析，都在过去 12 小时内完成。
每个数据库都在不同的计算机上，每个数据库的硬件都不同，但在 99% 的时间里，live box 的速度会慢 10% 左右。
据我所知，两个数据库的配置相同。
我的猜测是每个数据库上的数据分布存在一些细微的差异，这会影响解释计划的选择。
我的问题是：
解释计划开发- 不到一秒钟：
Aggregate (cost=7806.68..7806.69 rows=1 width=8)
-> Seq Scan on deviceb (cost=4624.86..7702.06 rows=41848 width=0)
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Unique (cost=0.29..4404.59 rows=88108 width=7)
-> Index Only Scan using device_pkey on device (cost=0.29..4184.32 rows=88108 width=7)
现场解释计划- 大约需要一个小时：
Finalize Aggregate (cost=747368287.67..747368287.68 rows=1 width=8)
-> Gather (cost=747368287.46..747368287.67 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=747367287.46..747367287.47 rows=1 width=8)
-> Parallel Seq Scan on deviceb (cost=0.42..747367217.98 rows=27789 width=0)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.42..26001.96 rows=156913 width=7)
-> Unique (cost=0.42..22822.40 rows=156913 width=7)
-> Index Only Scan using device_pkey on device (cost=0.42..21930.12 rows=156913 width=7)
我已经使用 pgAdmin4 比较了表的开发/实时版本 - 只需突出显示对象并选择每个表的属性及其索引和约束。
另外 - 我的测试（在两个数据库上）是在没有连接其他用户的情况下完成的。
DDL，根据要求：
CREATE TABLE public.device (
deviceid character(6) COLLATE pg_catalog."default" NOT NULL,
recentfixtime timestamp without time zone,
newfixes integer DEFAULT 0,
lastfixid bigint DEFAULT 0,
CONSTRAINT device_pkey PRIMARY KEY (deviceid)
USING INDEX TABLESPACE fastspace
);
CREATE TABLE public.deviceb (
deviceid character(6) COLLATE pg_catalog."default" NOT NULL,
flightid integer DEFAULT 0,
lastfirstfixtime timestamp without time zone,
lastprocfixtime timestamp without time zone,
lastprocfly boolean,
priority integer DEFAULT 0,
lastlandtime timestamp without time zone,
CONSTRAINT deviceb_pkey PRIMARY KEY (deviceid)
USING INDEX TABLESPACE fastspace
)
更新 - 已解决，感谢 Jjanes，建议的版本在两个数据库上都非常快。
select count(*) FROM DeviceB where NOT EXISTS (
SELECT 1 from Device where Device.deviceID=DeviceB.deviceID
)
我承认我仍然不确定根本原因是什么，但为了完整起见，我将尝试回答一些进一步的问题。自动分析仍在进行中（pgAdmin/statistics 显示了今天早上早些时候的最后一个自动分析日期。两个表（实时）的行数都少于 200,000，开发上的两个表都少于 100,000。开发/实时盒子上的硬件不同（最大区别是 dev 有 32G 内存，live 只有 16G）。可能最显着的区别是版本，dev 是 13.1，live 仍然是 10.4
此错误消息指的是什么数据类型？
ERROR: numeric field overflow DETAIL: A field with precision 1, scale 0 must round to an absolute value less than 10^1.
我收到上面列出的错误，它来自插入触发器。由于我的数据类型都没有明确定义为NUMERIC(1,0)，并且最大值是 10 的 1 次方（即 10），我猜这个错误实际上是指一个BOOLEAN字段。但是，我找不到对此的确认。
更新
解决。我的触发器正在尝试将一些字符串解析为数字。当to_number()未获得预期格式的字符串时，将返回列出的错误。
这将返回 1.4（正确）-
select to_number('1.4','9D9');
这将返回上述错误
select to_number('1d4','9D9');
仍然不太清楚它为什么抱怨，NUMERIC(1,0)但至少我知道为什么会出现错误。
两个 postgres 配置文件中哪个postgresql.conf优先pg_hba.conf？
pg_hba.conf控制客户端身份验证方法，包括“md5”和“scram-sha-256”
postgresql.conf包括 password_encryption 的条目，它可以是“md5”或“scram-sha-256”（或空白，我猜）
因此，如果这些值没有设置为相同的值，哪个优先？
我正在尝试自动创建在 Windows 10 上运行的 postgres 数据库。我希望能够从 postgres 的默认安装开始并自动执行以下步骤（不一定按此顺序）
将其简化为一系列数据库命令（只要它们以正确的顺序执行）并不难，问题是对于少数命令，我一直遇到错误“无法在事务块内发出”。这意味着一些命令，如 DROP DATABASE IF EXISTS 和 CREATE TABLESPACE 只能通过手动单步执行（据我所知）运行，而我可以将我的表创建脚本作为一个块调用并重新创建一个100 张桌子在几分之一秒内完成。
我不太担心单步执行一些单独的命令会浪费额外的几秒钟，但是通过手动执行其中的一些操作，我冒着丢失步骤和出错的风险。
如何使整个过程自动化（即编写脚本）？
更新，脚本看起来有点像这样......
DROP DATABASE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????
ALTER DEFAULT privileges IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM ????;
ALTER DEFAULT privileges IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM ????;
DROP USER IF EXISTS ????;
DROP USER IF EXISTS ????;
DROP USER IF EXISTS ????;
CREATE USER ???? PASSWORD '????';
CREATE USER ???? PASSWORD '????';
CREATE USER ???? PASSWORD '????';
CREATE TABLESPACE fastspace OWNER ???? LOCATION 'c:/????/????';
CREATE TABLESPACE slowspace OWNER ???? LOCATION 'd:/????/????';
DROP DATABASE ????;
CREATE DATABASE ????
WITH OWNER = ????
ENCODING = 'UTF8'
TABLESPACE = fastspace
LC_COLLATE = 'English_United Kingdom.1252'
LC_CTYPE = 'English_United Kingdom.1252'
CONNECTION LIMIT = -1;
GRANT CREATE ON DATABASE ???? TO ????;
COMMENT ON DATABASE ????
IS '????database';
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO ????;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ????;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ????;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ????;
ALTER DEFAULT privileges IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ????;
上述许多（大多数）命令可以作为事务块的一部分运行，但少数不能（例如删除和创建数据库）。
我有一个这样的查询......
with subquery as
(
SELECT A, B, C, D,
row_number() OVER(PARTITION BY X ORDER BY Y) as RN
FROM blah blah
ORDER BY A,B
LIMIT 100
)
SELECT C,D
FROM subquery
WHERE RN=1
如您所见，子查询已排序，但顶级查询未排序。
顶级查询没有做任何复杂的事情，它只是根据partition by子句进行选择。
子查询的排序顺序是否会保留在最终结果中？
对我测试过的各种数据集的观察表明排序顺序保持不变，但我不确定这是否只是一个幸运的巧合。
我有许多带有 INT 类型列的 postgres 表，我需要将其更改为 BIGINT。表的每个分区包含大约 1 亿行，我的意图是分离各个分区 ALTER 列类型，然后重新附加。
我的问题是，需要更新的列已编入索引。删除索引、更改列类型然后重新创建索引可能会更快吗？或者我应该保留索引并更改列类型？
请注意，列值不会改变，只是数据类型（不确定这是否相关！）
谢谢
如果我VACUUM在 Postgres 表上执行操作（没有FULL选项），我知道它应该为数据库提供任何可用空间，而不是操作系统。
它应该使任何数据库对象都可以使用空闲空间吗？还是只是被吸尘的桌子？
在大表上运行 VACUUM FULL之前，是否可以检查表空间中有多少可重用空间？
我有一个大的 postgres 表（大约 20G），偶尔会出现 VACUUM FULL。该驱动器上的可用空间在 15-25 GB 之间变化。在尝试每次清理之前，我会记录表大小（使用 postgres 查询）和可用磁盘空间（使用 OS 工具）。
我知道 VACUUM FULL 需要制作表格的完整副本。因此，如果表是 20G，则需要 20G 的可用空间。
有时表是 20G，只有 15G 的操作系统空间可用，而 Vacuum 可以工作。我猜需要的额外 5G 是从表空间内部恢复的。
其他时候由于空间不足，真空会失败，我猜在这些情况下，在表空间中找不到所需的额外 5G。
我希望能够事先检查我是否有足够的空间容纳 VACUUM FULL，我该怎么做？我知道表有多大，我知道操作系统有多少可用空间，但我不知道表空间中有多少可回收空间。
我正在尝试将一个表合并到一个分区中，合并有效但性能不佳。我读过这个链接，特别是这个引用....
在运行 ATTACH PARTITION 命令之前，建议在要附加的表上创建一个 CHECK 约束，描述所需的分区约束。这样，系统将能够跳过扫描以验证隐式分区约束。如果没有这样的约束，将扫描表以验证分区约束，同时在父表上持有 ACCESS EXCLUSIVE 锁。然后可以在 ATTACH PARTITION 完成后删除约束，因为它不再是必需的。
这是我正在使用的 SQL……
ALTER TABLE mess_201811 ADD CONSTRAINT const_201811 CHECK ( loadedtime >= DATE '2018-11-01' AND loadedtime < DATE '2018-11-23' );
ALTER TABLE mess ATTACH PARTITION mess_201811 FOR VALUES FROM ('2018-11-01') TO ('2018-11-23');
ALTER TABLE mess_201811 DROP CONSTRAINT const_201811 ;
我尽可能地从提到的链接中复制了我的 SQL。这三个命令都没有问题，问题是它花费的时间。添加约束大约需要 60 秒（很公平，表中大约有 200 万条记录）。然而，ATTACH PARTITION 命令也需要大约 60 秒，这向我表明正在对表进行第二次扫描以验证分区。我的理解是，有了 CHECK 约束，ATTACH 应该是一个简单的 DDL 语句，并且只需要几分之一秒。
问题是 ATTACH PARTITION 需要对 table mess的排他锁，这会阻止插入记录 60 秒。
我正在使用 postgres v10.5。table mess 的定义以
PARTITIONED BY loadedtime;
在我测试时，数据库上没有其他用户，也没有锁定问题。
在 jjanes 解决方案之后，我检查了原始文档，引用的示例确实使用了 NOT NULL 字段约束，尽管它没有提到这是强制性的。我希望附加约束不会对该表的正常插入产生明显影响。