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
  • 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. – ypercubeᵀᴹ Aug 27 '16 at 12:14
  • Done as you asked =) – xCloudx8 Aug 27 '16 at 12:18
  • 1
    And the first query? – ypercubeᵀᴹ Aug 27 '16 at 12:20
  • For distance = 0 the result is as term2_id just because is the first term. – xCloudx8 Aug 27 '16 at 12:23
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, privacy policy and cookie policy

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