CODEWITHSUNDEEP
sqlpostgresqlselectsql-insert
Mordin Solus ME
Mordin Solus ME

Reputation: 59

INSERT INTO ... SELECT with more SELECT columns

Thank you for your time.

I have a INSERT INTO ... SELECT setup, but I also want to filter using extra columns (that do not exist in the table I am inserting into)

For example:

INSERT INTO table1 (t1_col1, t1_col2, t1_col3)
SELECT
  t2.t1_col1,
  t2.t1_col2,
  t3.t1_col3,
  t4.filter_col
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';

table1 only has columns t1_col1, t1_col2 and t1_col3 so when I attempt to run this it fails as expected:

ERROR:  INSERT has more expressions than target columns

So my question is, how can I still include the filter, but specify which columns from my SELECT statement should be used in the INSERT INTO statement.

Many thanks for any help!

Upvotes: 0

Views: 1836

Answers (2)

colosso
colosso

Reputation: 2525

It's almost right, but you select one column too much. Try this:

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';

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 1

Related Questions