如何创建表并复制指定列结构约束，同步复制其他字段数据

你的需求是创建tablecopy表，只保留原表mytable的id列名+主键约束，以及field2、field3的结构和数据。原语句的问题在于LIKE mytable INCLUDING ALL会复制所有字段（包括不需要的field4）和所有约束，后续插入时又只提供了field2、field3的值，会导致id（主键非空）和field4（如果有非空约束）报错，而且还保留了不需要的field4字段。

下面给你几种精准实现的方法：

方法1：手动定义字段（适合明确知道字段类型的场景） #

这种方式最直观，直接指定需要的字段和约束：

-- 创建tablecopy表，匹配原表的字段结构和约束
CREATE TABLE tablecopy (
    -- 复制原表id的列名、数据类型和主键约束，这里假设原表id是INT，若为自增则用SERIAL/BIGSERIAL
    id INT PRIMARY KEY,
    -- 复制field2的完整结构（比如类型、非空约束，替换为你实际的定义）
    field2 TEXT NOT NULL,
    -- 复制field3的完整结构
    field3 NUMERIC(10, 2)
);

-- 插入原表field2、field3的数据，若id是自增类型（SERIAL），插入时无需指定id
INSERT INTO tablecopy (field2, field3)
SELECT field2, field3 FROM mytable;

方法2：动态复制结构（无需手动写字段类型） #

如果不想硬编码字段类型，可以用CREATE TABLE AS先复制结构，再调整约束：

-- 1. 先复制id、field2、field3的结构（不包含主键约束），LIMIT 0确保不复制数据
CREATE TABLE tablecopy AS
SELECT id, field2, field3 FROM mytable LIMIT 0;

-- 2. 添加主键约束，和原表id的约束一致
ALTER TABLE tablecopy ADD PRIMARY KEY (id);

-- （可选）如果不需要原表的id数据，改为自增主键：
ALTER TABLE tablecopy DROP COLUMN id;
ALTER TABLE tablecopy ADD COLUMN id SERIAL PRIMARY KEY;

-- 3. 插入field2、field3的数据
INSERT INTO tablecopy (field2, field3)
SELECT field2, field3 FROM mytable;

方法3：用LIKE语法复制指定字段（PostgreSQL 12+支持） #

这种方式更灵活，自动复制指定字段的所有结构和约束：

-- 创建表，仅复制id、field2、field3的结构和所有约束
CREATE TABLE tablecopy (
    LIKE mytable (id, field2, field3) INCLUDING ALL
);

-- （可选）如果不需要原表的id数据，改为自增主键：
ALTER TABLE tablecopy ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE IF EXISTS tablecopy_id_seq;
CREATE SEQUENCE tablecopy_id_seq;
ALTER TABLE tablecopy ALTER COLUMN id SET DEFAULT nextval('tablecopy_id_seq');
ALTER SEQUENCE tablecopy_id_seq OWNED BY tablecopy.id;

-- 插入数据
INSERT INTO tablecopy (field2, field3)
SELECT field2, field3 FROM mytable;

关键注意点： #

• 如果原表的id是自增类型（SERIAL/BIGSERIAL），新表的id也需要对应设置序列，否则插入时会因为主键非空报错。
• 如果你不需要新表的id有自增特性，插入时可以手动生成唯一值：

INSERT INTO tablecopy (id, field2, field3)
SELECT ROW_NUMBER() OVER (), field2, field3 FROM mytable;

内容的提问来源于stack exchange，提问作者Nick T