PostgreSQL – How to Save Result of a Join to Another Table

join;postgresql

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?

Best Answer

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.

Related Solutions

Sql-server – Forcing Join Order

It seems that I can specify OPTION (FORCE ORDER) at the end of the query and that will make the joins happen in the right order. There are lots of people warning not to do this because it stops SQL from optimising my query so I deffinately will be using it sparingly (and watching those queries closely).

MySQL – How to Simulate a Table Join on Itself

You'll need to first create a list of every product_number and date combination. You can do this using a CROSS JOIN of your table:

select distinct p.product_number, d.date
from yourtable p
cross join yourtable d;

See SQL Fiddle with Demo. This will create a list of data similar to:

| PRODUCT_NUMBER |                            DATE |
|----------------|---------------------------------|
|            100 |  January, 01 2010 00:00:00+0000 |
|            200 |  January, 01 2010 00:00:00+0000 |
|            100 | February, 01 2010 00:00:00+0000 |

You will then use the above query and LEFT JOIN to your table to return the final result:

select 
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join yourtable d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo. Giving a final result of:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |

The LEFT JOIN returns all rows from your list of products and dates regardless of whether a matching row exists in the other table.

This could also be written as:

select 
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable 
) p  
cross join
( 
  -- list of every date
  select distinct date
  from yourtable 
) d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. This may have better performance depending on your table size.

Now if you wanted to return a list of all dates, regardless of whether or not they appear in the table, then I would suggest creating a table of dates. This table would be used in a similar manner to create a list of all dates/products which you would then join.

The table would be similar to:

CREATE TABLE dates
    (`date` datetime)
;

INSERT INTO dates
    (`date`)
VALUES
    ('2010-01-01 00:00:00'),
    ('2010-02-01 00:00:00'),
    ('2010-03-01 00:00:00'),
    ('2010-04-01 00:00:00'),
    ('2010-05-01 00:00:00')
;

You'd then use the following query to get the list of dates/products:

select distinct p.product_number, d.date
from yourtable p
cross join dates d

And finally, you would join that back to your table:

select 
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join dates d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo. Or an alternative:

select 
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable 
) p  
cross join
 dates
  d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. Again this may have better performance based on the table size. Using this type of solution, you'd return all dates even those not in your table:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            100 |    April, 01 2010 00:00:00+0000 | (null) |
|            100 |      May, 01 2010 00:00:00+0000 | (null) |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |    April, 01 2010 00:00:00+0000 | (null) |
|            200 |      May, 01 2010 00:00:00+0000 | (null) |

Best Answer

Related Solutions

Sql-server – Forcing Join Order

MySQL – How to Simulate a Table Join on Itself

Related Question