CODEWITHSUNDEEP
sqlpostgresqlsql-insertdate-arithmetic
John Mayer
John Mayer

Reputation: 113

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

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?

Upvotes: 1

Views: 727

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657902

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:

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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;

Upvotes: 1

Related Questions