如何修复PostgreSQL中‘WITH查询"owners"有1列可用但指定2列’错误？

问题场景 #

需要执行两次连续插入，第二次插入依赖第一次插入的结果，尝试的SQL代码如下：

drop table if exists persons cascade;
create table persons (
  id serial primary key,
  name text not null);
drop table if exists accounts cascade;
create table accounts (
  id serial primary key,
  login text not null,
  owner int not null references persons(id));
with
  people (name, login) as (
    values
    ('Alice', 'ali'),
    ('Bob',   'bob')),
  owners (id, name) as (
    insert into persons (name)
    select p.name from people p
    returning (id, name))
insert into accounts (login, owner)
select (people.login, owners.id)
from people
join owners using (name);
select * from persons;
select * from accounts;

执行后报错：

ERROR: WITH query "owners" has 1 columns available but 2 columns specified
LINE 6: owners (id, name) as (
^

错误含义 #

这个错误的核心是：你在WITH子句里定义owners包含id和name两列，但实际插入返回的结果只有1列。原因是returning (id, name)的写法错误——加了括号后，PostgreSQL会把这两个字段打包成一个行类型的单列，而不是两个独立的字段，和你定义的两列不匹配。

解决方法 #

需要修正两处语法问题：

• 把returning (id, name)改成returning id, name，这样会返回两个独立的字段，和owners (id, name)的定义匹配。
• 把插入accounts时的select (people.login, owners.id)改成select people.login, owners.id，同样，括号会把两个字段变成一个行类型，不符合accounts表的列要求。

修正后的完整代码 #

drop table if exists persons cascade;
create table persons (
  id serial primary key,
  name text not null);
drop table if exists accounts cascade;
create table accounts (
  id serial primary key,
  login text not null,
  owner int not null references persons(id));
with
  people (name, login) as (
    values
    ('Alice', 'ali'),
    ('Bob',   'bob')),
  owners (id, name) as (
    insert into persons (name)
    select p.name from people p
    returning id, name) -- 去掉括号，返回两个独立字段
insert into accounts (login, owner)
select people.login, owners.id -- 去掉括号，分别指定字段
from people
join owners using (name);
select * from persons;
select * from accounts;

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