How do I save result of a join to another table?

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?

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.

StackExchange Database Administrators Q#141823, answer score: 5