PostgreSQL未知分区键值时，如何预创建数据导入所需分区？

现有一张名为phone_number的电话号码表：

我们希望按国家代码（country_code）对该表进行分区，因此创建了表phone_number_bis：

CREATE TABLE phone_number_bis (
                phone_number VARCHAR,
                country_code INTEGER,
                owner VARCHAR NOT NULL,
                PRIMARY KEY (phone_number, country_code)
) PARTITION BY LIST(country_code)

尝试将phone_number的内容导入phone_number_bis时，出现如下错误：

INSERT INTO phone_number_bis( phone_number, country_code, owner)
SELECT  phone_number, country_code, owner
FROM phone_number;

ERROR: no partition of relation "phone_number_bis" found for row
Partition key of the failing row contains (country_code) = (44)

请问是否存在SQL命令，能够在不预先知晓country_code列内容的情况下，在导入数据前创建所有必要的分区？

注：对电话号码表进行分区可能并不合适，此处仅为简化复杂问题而举的示例。

PostgreSQL没有单条原生SQL命令直接自动创建所有所需分区，但可以通过动态SQL结合PL/pgSQL实现，无需提前知道country_code的具体值：

1. 自动创建所有必要分区 #

使用匿名DO块，查询原表中所有唯一的country_code，动态生成并执行分区创建语句：

DO $$
DECLARE
  rec record;
BEGIN
  -- 遍历原表中所有唯一的country_code
  FOR rec IN SELECT DISTINCT country_code FROM phone_number LOOP
    -- 动态创建对应分区，IF NOT EXISTS避免重复创建
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS phone_number_bis_cc_%s PARTITION OF phone_number_bis FOR VALUES IN (%s);',
      rec.country_code,
      rec.country_code
    );
  END LOOP;
END $$;

这段代码会为每个唯一的国家代码创建一个对应的LIST分区，分区命名为phone_number_bis_cc_<国家代码>（比如phone_number_bis_cc_44）。

2. 导入数据 #

分区创建完成后，即可正常执行导入语句：

INSERT INTO phone_number_bis(phone_number, country_code, owner)
SELECT phone_number, country_code, owner
FROM phone_number;

原理说明 #

• 利用SELECT DISTINCT country_code FROM phone_number获取所有需要分区的键值；
• format()函数安全拼接SQL语句，避免注入风险；
• EXECUTE执行动态生成的SQL；
• IF NOT EXISTS保证即使分区已存在也不会报错，提升鲁棒性。

内容的提问来源于stack exchange，提问作者Noan Cloarec