CODEWITHSUNDEEP
postgresqlpostgresql-9.4
sibert
sibert

Reputation: 2228

bulk insert from a query into table (postgresql)

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

Upvotes: 0

Views: 3944

Answers (2)

Jonathan Jacobson
Jonathan Jacobson

Reputation: 1518

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

Upvotes: 0

user330315
user330315

Reputation:

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.

Upvotes: 3

Related Questions