Can I use Select from Insert into table function in sql?

Question

I got the following dataset:

CREATE temp TABLE XXX (DATE TEXT, PRICE INTEGER);
INSERT INTO XXX VALUES 
('2019-04-27 01:00', 1), ('2019-04-27 02:30', 3), ('2019-04-27 18:00',2), 
('2019-04-28 17:00', 2), ('2019-04-28 21:00', 5), 
('2019-04-29 17:00',50), ('2019-04-29 21:00',10), 
('2019-04-30 17:00',10), ('2019-04-30 21:00',20), 
('2019-05-01 17:00',40), ('2019-05-01 21:00',10), 
('2019-05-02 17:00',10), ('2019-05-02 21:00', 6);

Then I tried to use this query to take 2 recent rows from table XXX and add them with date + 1 day and same price values to table XXX. As a result, rows with date 05-03 should be added.

with A as (
select * from XXX
order by DATE DESC),

B as (select * from (insert into A (DATE, PRICE),
select * from A
limit 2))

select * from B

Here I got an error:

error at or near "insert"

How can tackle this issue?

I took the liberty to clarify a bit as I understood the question. Please adjust if I got it wrong. — Erwin Brandstetter, Apr 23 '20 at 15:21

score 1 · Answer 1 · answered Apr 23 '20 at 13:39

1

Is this what you are trying to do?

with i as (
       insert into A (DATE, PRICE)
           select * 
           from XXX
           order by DATE TEXT DESC
           limit 2
           returning *
      )
select *
from i;

answered Apr 23 '20 at 13:39

Gordon Linoff

1,242,037
58
646
786

You meant ''' with A as ( select * from XXX order by DATE TEXT DESC),i as ( insert into A (DATE, PRICE) select * from XXX order by DATE TEXT DESC limit 2 returning * ) select * from i;) ? – John Mayer Apr 23 '20 at 13:45
@JohnMayer . . . Are you really trying to insert into a CTE? That is not allowed. – Gordon Linoff Apr 23 '20 at 14:00
yes, I wanted. Are there any other alternatives how to do this manipulation? May be kind of create 2 CTE and then use UNION, but I do not know how. – John Mayer Apr 23 '20 at 14:06
@JohnMayer . . . You can only insert into tables or certain views. – Gordon Linoff Apr 23 '20 at 14:09
Now I understood. I solved it by using CTE A and B in select and then used UNION ALL to create a table from these two CTEs. However, how can I add 1 day to table B? SELECT add_date(DATE), Price did not help – John Mayer Apr 23 '20 at 14:27

Erwin Brandstetter · Answer 2 · 2020-04-23T15:31:36.150

I think, you just want to:

INSERT INTO xxx (date, price)
SELECT date + 1  -- adding a day to a date
     , price
FROM   xxx
ORDER  BY date DESC
LIMIT  2;

This takes the latest two rows according to column date, adds a day and inserts new rows.

Optionally append RETURNING * to also return the rows you just inserted.

It might break if ...

... the column date is not type date (rather don't use basic type names as identifer)
... date is not defined NOT NULL.
... there are not exactly two rows for the latest day.

The operation date + integer adds days to a date in Postgres. Related:

Can I use Select from Insert into table function in sql?

2 Answers2