PostgreSQL, SQL state: 42601

Question

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

My Tables: circuit:

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)
)

segment:

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?

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.

Answer 1

This constructs an anonymous composite value:

select (1, 'a');

For example:

=> 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.

From the fine manual:

8.16.2. Composite Value Input

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)

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.

When you say this:

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

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