PostgreSQL，SQL状态：42601 [英] PostgreSQL, SQL state: 42601

问题描述

我想使用从2个表（段和wgs）中获取值的select插入表（电路）。我的查询：

I want to insert into a table (circuit) using a select which takes values from 2 tables (segment and wgs). My query:

INSERT INTO circuit (id_circuit, description, date_start, date_end, speed,
length, duration)
SELECT (seg.id_segment, cir.nomcircuit, seg.date_start, seg.date_end, seg.speed_average,
cir.shape_leng, (seg.date_end - seg.date_start)) 
FROM segment seg, wgs cir where seg.id = 13077

我的表格：电路：

CREATE TABLE circuit
(
  id serial NOT NULL,
  id_circuit integer,
  description character varying(50),
  date_start time without time zone,
  date_end time without time zone,
  speed double precision,
  length double precision,
  duration double precision,
  CONSTRAINT circuit_pkey PRIMARY KEY (id)
)

段：

CREATE TABLE segment
(
  id serial NOT NULL,
  id_segment integer,
  date_start timestamp without time zone,
  date_end timestamp without time zone,
  speed_average double precision,
  mt_identity character varying,
  truck_type character varying,
  CONSTRAINT segment_pkey PRIMARY KEY (id)
)

wgs：

CREATE TABLE wgs
(
  id serial NOT NULL,
  nomcircuit character varying(50),
  shape_leng numeric,
  CONSTRAINT wgs_pkey PRIMARY KEY (id)
)

但是当我运行查询时，出现此错误：

But when I run my query, this error comes:

ERROR:  INSERT has more target columns than expressions
LINE 1: INSERT INTO circuit (id_circuit, description, dat...
                                                    ^
HINT:  The insertion source is a row expression containing the same number of columns
expected by the INSERT. Did you accidentally use extra parentheses?

据我所知，我没有多余的括号，我再次检查了列数据类型并确保它们匹配并且进行了各种尝试，但是我仍然不知道为什么会出现错误。
PS：13077只是想以一个我肯定有的值尝试一下。

As far I can see, I do not have extra parentheses, I double checked the columns data type and made sure they match and various tries, but I still don't get why the error comes. PS: the 13077 is just to try it out with one value I'm sure I have.

推荐答案

此构造一个匿名复合值：

This constructs an anonymous composite value:

select (1, 'a');

例如：

=> select (1, 'a');
  row  
-------
 (1,a)
(1 row)

=> select row(1, 'a');
  row  
-------
 (1,a)
(1 row)

请注意，这是一个复合值，而不是多个值。

Note that that is a single composite value, not multiple values.

来自精巧手册：

8.16.2。复合值输入

要将复合值写为文字常量，请将字段值括在括号内并用逗号分隔。您可以在任何字段值两边加上双引号，并且如果包含逗号或括号，则必须这样做。

[...]

ROW 表达式语法也可以用于构造复合值。在大多数情况下，这比字符串文字语法要简单得多，因为您不必担心多层引用。上面我们已经使用了这种方法：

To write a composite value as a literal constant, enclose the field values within parentheses and separate them by commas. You can put double quotes around any field value, and must do so if it contains commas or parentheses.
[...]
The ROW expression syntax can also be used to construct composite values. In most cases this is considerably simpler to use than the string-literal syntax since you don't have to worry about multiple layers of quoting. We already used this method above:

ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)

ROW 关键字只要您在表达式中具有多个字段，实际上是可选的，因此这些可以简化为：

The ROW keyword is actually optional as long as you have more than one field in the expression, so these can simplify to:

('fuzzy dice', 42, 1.99)
('', 42, NULL)

行构造器 部分可能也很有趣。

The Row Constructors section might also be of interest.

您这样说：

INSERT INTO circuit (id_circuit, description, date_start, date_end, speed,
length, duration)
SELECT (...)
FROM segment seg, wgs cir where seg.id = 13077

您的 SELECT 子句只有一列，因为整个（...）表达式表示一个值。解决方案是简单地删除这些括号：

your SELECT clause only has one column as the whole (...) expression represents a single value. The solution is to simply drop those parentheses:

INSERT INTO circuit (id_circuit, description, date_start, date_end, speed, length, duration)
SELECT seg.id_segment, ..., (seg.date_end - seg.date_start)
FROM segment seg, wgs cir where seg.id = 13077

这篇关于PostgreSQL，SQL状态：42601的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！