bulk insert from a query into table (postgresql)

Question

I cannot find an answer on this:

I want to insert the result of this query into a table 'days':

WITH days AS ( 
SELECT 
date_part('dow',generate_series('2017-11-01','2017-11-30', interval '1 day')) as dow, 
to_char(generate_series('2017-11-01','2017-11-30', interval '1 day'),'YYYY-MM-DD') as date ) 

SELECT 0 as id,date, 
CASE 
WHEN dow=1 then 8 
WHEN dow=2 then 8 
WHEN dow=3 then 8 
WHEN dow=4 then 8 
WHEN dow=5 then 8 
WHEN dow=6 then 0 
WHEN dow=0 then 0 
ELSE 0 END as wtime 
FROM days

The table is as simple as this:

CREATE TABLE days
    (id serial, dates date, wtime numeric(8,2));

In my dreams it should look like this:

INSERT * INTO days FROM (

WITH days AS ( 
SELECT 
date_part('dow',generate_series('2017-11-01','2017-11-30', interval '1 day')) as dow, 
to_char(generate_series('2017-11-01','2017-11-30', interval '1 day'),'YYYY-MM-DD') as date ) 

SELECT 0 as id,date, 
CASE 
WHEN dow=1 then 8 
WHEN dow=2 then 8 
WHEN dow=3 then 8 
WHEN dow=4 then 8 
WHEN dow=5 then 8 
WHEN dow=6 then 0 
WHEN dow=0 then 0 
ELSE 0 END as wtime 
FROM days)

Is this possible? How?

http://sqlfiddle.com/#!15/91c720/8

user330315 · Accepted Answer · 2017-11-12 10:17:04Z

The CTE (WITH) needs to come first, then the insert. You also need to give the CTE a different name, otherwise the name days would be ambigous

with gen_days (
...)
insert into days 
select *
from gen_days;

You will get a problem however as your query generates varchar values for the date column, not a real DATE type. You need to change that to cast the result of generate_series() to a date. You also only need a single generate_series() not two:

WITH gen_dates AS ( 
 SELECT date_part('dow',d.dt) as dow, 
        dt::date as date
  from generate_series('2017-11-01','2017-11-30', interval '1 day') as d(dt)
) 
insert into days (dates, wtime)
SELECT date, 
       CASE 
         WHEN dow=1 then 8 
         WHEN dow=2 then 8 
         WHEN dow=3 then 8 
         WHEN dow=4 then 8 
         WHEN dow=5 then 8 
         WHEN dow=6 then 0 
         WHEN dow=0 then 0 
         ELSE 0
       END as wtime 
FROM gen_dates

Unrelated, but: you don't really need the CTE. You can simplify the insert to:

insert into days (dates, wtime)
SELECT dt::date, 
       CASE date_part('dow', dt) 
         WHEN 1 then 8 
         WHEN 2 then 8 
         WHEN 3 then 8 
         WHEN 4 then 8 
         WHEN 5 then 8 
         WHEN 6 then 0 
         WHEN 0 then 0 
         ELSE 0
       END as wtime 
FROM generate_series('2017-11-01','2017-11-30', interval '1 day') as d(dt);

Note the short form of the CASE statement where you don't have to repeat the expression that you test against.

Works like expected. And all other extra stuff is appreciated. Thank You!

Jonathan Jacobson · Accepted Answer · 2017-11-12 10:21:05Z

Perhaps your problem was that the CTE and your target table have the same name. Another problem was that your "date" source column wasn't a real date data type:

INSERT * INTO days
WITH days_cte AS ( 
SELECT 
date_part('dow',generate_series('2017-11-01','2017-11-30', interval '1                     day')) as dow, 
to_char(generate_series('2017-11-01','2017-11-30', interval '1 day'),'YYYY-MM-DD') as date ) 
SELECT 0 as id, "date"::date, 
CASE 
WHEN dow=1 then 8 
WHEN dow=2 then 8 
WHEN dow=3 then 8 
WHEN dow=4 then 8 
WHEN dow=5 then 8 
WHEN dow=6 then 0 
WHEN dow=0 then 0 
ELSE 0 END as wtime 
FROM days_cte

Collectives™ on Stack Overflow

bulk insert from a query into table (postgresql)

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related