0

In PostgreSQL I can save result if SELECT to another table with SELECT INTO:

SELECT id INTO new_record_ids FROM new_table
  EXCEPT
    SELECT id FROM old_table
ORDER BY id;

However this doesn't work for for joins:

-- doesn't work:
SELECT * INTO new_records FROM new_table
  JOIN new_record_ids ON new_record_ids.id = new_table.id
ORDER BY new_table.id;

The join itself is correct:

-- works:
SELECT * FROM new_table
  JOIN new_record_ids ON new_record_ids.id = new_table.id
ORDER BY new_table.id;

My question is: how to save joint table to another table?

  • SELECT new_table.* INTO ... ? Or select the columns you want their values to be inserted in the new_records table? – ypercubeᵀᴹ Jun 21 '16 at 15:06
  • @ypercubeᵀᴹ: both your solutions work. Can you write it as an answer? – Adobe Jun 21 '16 at 16:19
  • 2
    @ypercubeᵀᴹ: create table .. as select .. is preferred over the non-standard select .. into syntax. – a_horse_with_no_name Jun 21 '16 at 20:07
2

Sounds like you're trying to insert rows to a table that already exists, in which case you'll need something like...

INSERT INTO new_records
SELECT * FROM new_table t JOIN new_record_ids r ON(r.id = t.id)
--this assumes the result of the select has the same column layout as new_records...
--which seems unlikely. replace (SELECT *...) with (SELECT column1, column2, etc...) to match the new_records column layout
ORDER BY t.id
--assuming you meant new_table.id instead of new.id although the ORDER BY clause seems extraneous for an insert anyway

If I misinterpreted and you're trying to select into a new table that has not yet been created, check the docs for CREATE TABLE...AS SELECT syntax.

EDIT:

From the comments under your question it sounds like you're trying to populate a brand new table with the query results, in which case...

CREATE TABLE new_records AS
SELECT t.* FROM new_table t JOIN new_record_ids r ON(r.id = t.id);

...should work just fine. As pointed out in comments, the SELECT INTO docs state:

The PostgreSQL usage of SELECT INTO to represent table creation is historical. It is best to use CREATE TABLE AS for this purpose in new code.

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.