new table from two different select column results (from the same table) in postgresql-CodePudding

much appreciate any help. I have a table named lifelong like presented below.

   id           first_meal            last_meal
0   1  2022-07-25 12:28:00  2022-07-25 20:06:00
1   2  2022-07-26 13:12:00  2022-07-26 19:09:00
2   3  2022-07-27 14:13:00  2022-07-27 20:13:00
3   4  2022-07-28 15:10:00  2022-07-28 21:22:00

I skip one row from column first_meal with

 select  f.id, f.first_meal  from lifelong   f offset 1;

I select all from the column last_meal with

 select id, last_meal from lifelong

Now, I need to save this two different selects form the same table as a new table

I tried union or union all, I tried to put these two selects in parenthesis - no result so far

I expect an output like below

 id           first_meal            last_meal
0   1  2022-07-26 13:12:00  2022-07-25 20:06:00
1   2  2022-07-27 14:13:00  2022-07-26 19:09:00
2   3  2022-07-28 15:10:00  2022-07-27 20:13:00
3   4                  NaN  2022-07-28 21:22:00

CodePudding user response：

I would suggest using ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY first_meal DESC) rn
    FROM lifelong
)

INSERT INTO newTable (id, first_meal, last_meal)
SELECT id, CASE WHEN rn > 1 THEN first_meal END, last_meal
FROM cte;