2

Is there a way to insert two or more columns in a table from a SELECT statement?

E.g. If i do two insert:

INSERT INTO temp_table(dist0)
SELECT min(term1)
FROM graph_path
WHERE distance = 0
GROUP BY term2_id
ORDER BY term2_id;

AND

INSERT INTO temp_table(dist1)
SELECT min(term1)
FROM graph_path
WHERE distance = 1
GROUP BY term2_id
ORDER BY term2_id;

The result is:

dist0    dist1
1
2
3
          4
          5
          6

How can i get a result like this? :

dist0   dist1
1         4
2         5
3         6

Postgresql version 9.5

AS ASKED: E.g. with term2_id

select term2_id, min(term1_id)
from graph_path
where distance = 1
group by term2_id
order by term2_id;

RESULT:

term2_id   min
2          1507
3          107 
5          1
4
  • 1
    We insert rows, not columns. But what columns does the temp table have? You need a select that returns these (3) rows. Show us the output of the selects after including the term2_id in the select lists. Aug 27, 2016 at 12:14
  • Done as you asked =)
    – xCloudx8
    Aug 27, 2016 at 12:18
  • 1
    And the first query? Aug 27, 2016 at 12:20
  • For distance = 0 the result is as term2_id just because is the first term.
    – xCloudx8
    Aug 27, 2016 at 12:23

1 Answer 1

3

We insert rows, not columns, in a table.

You need a query that returns both results, combined in 3 rows. Assuming that the term2_id values should match:

INSERT INTO temp_table
  (dist0, dist1)
SELECT 
  min(term1) FILTER (WHERE distance = 0),
  min(term1) FILTER (WHERE distance = 1)
FROM graph_path
WHERE distance IN (0, 1)
GROUP BY term2_id
ORDER BY term2_id ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.