Insert a Select Group By:More Target Columns Than Expressions Error

INSERT a SELECT GROUP BY : more target columns than expressions error

When you enclose expressions in parentheses, Postgres interprets the result as a tuple -- essentially a struct or record.

So, your statement:

SELECT (
        nextval('"KPI_MEASURE_ID_seq"'::regclass),
        now(),
        kpi_project.id,
        kpi_measure.kpi_frequency_id,
        kpi_metric.id ,
        kpi_measure.branch ,
        sum(kpi_measure.value)
     )

is returning one value. That value is a record.

Databases that do not support tuples would return an error.

The solution is to remove the parentheses.

Insert into Multiple Columns from a Grouped by Subquery

try like below if want to use subsqery

INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -
SELECT 'Label A', CATEGORY,  cnt from                                          
(SELECT CATEGORY, COUNT(1) as cnt FROM SUB_TABLE GROUP BY CATEGORY) a

in fact don't need sub-query

 INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -
SELECT 'Label A', CATEGORY,  COUNT(1) as cnt from                                          
  FROM SUB_TABLE GROUP BY CATEGORY

demo link

Error - INSERT has more expressions than target columns

You can just remove it from the select:

INSERT INTO storiacloud.schl_storia_school_status_try (no_of_orders) 
select count(otc_order_number) 
from storiacloud.vw_storia_oms_orders
group by school_ucn;

It will still group, just not report it. I'm not sure what use a bunch of random numbers in a table is though.

Postgres function to create audit table throwing error INSERT has more expressions than target columns

The insert fails because you are declaring three columns for insert (id, status, dateadded), but you are giving it 4 values: the operation (insert, update, delete), then the 3 original columns.

Presumably, your audit table has (or should have) a column that stores the operation that is being performed.

If so, you should list that column in the insert statement:

INSERT INTO api_audit.d_status_list (operation, id, status, dateadded) 
VALUES (TG_OP, NEW.*);

Generally speaking, it is a good practice to avoid * and explicitely list the columns, which makes things easier to track down when they go wrong, so:

INSERT INTO api_audit.d_status_list (operation, id, status, dateadded) 
VALUES (TG_OP, NEW.id, NEW.status, NEW.dateadded);

INSERT INTO ... SELECT with more SELECT columns

you have to same number of column in select ,so remove t4.filter_col from selection

INSERT INTO table1 (t1_col1, t1_col2, t1_col3)
SELECT
  t2.t1_col1,
  t2.t1_col2,
  t3.t1_col3      
FROM table2 t2
  INNER JOIN table3 t3 ON t2.t1_col1 = t3.t1_col1
  INNER JOIN table4 t4 ON t4.filter_col = t2.filer_col
WHERE t4.filter_col = 'value';

Note: you are not bound to select all columns those you used in join or filter

PgSQL insert into select after cast

It says "column is of type double but expression is of type text" so what's really happening is that it's trying to insert one of the expressions where you cast to ::text into a column that is of type double.

If the problem was converting from text to double, you'd get a different message. Besides, the SELECT worked fine, which means it didn't encounter any text data that it couldn't convert to double.

Since you don't specify the target table columns in your INSERT, and you have so many of them, most likely you missed a column or got the order wrong.

Honestly if the table has 270 columns and they have the same name in the source and destination tables, you should really generate the query using something like python from a list of columns, that will be faster than proofreading the 270 lines...

PostgreSQL, SQL state: 42601

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

---

Related Topics