Question 89825 in PostgreSQL Question posted in
The official documentation can be found here.

Add row base on condition of another column using Postgresql

I have a table and I want to add some rows based on column ‘days’. And for each of those rows, the ‘Date’ column will increment by 1 day.

For example:
Table X:

Date Group days
1/1/2023 A 3
5/1/2023 B 4

I want the result like this:

Date Group days
1/1/2023 A 3
2/1/2023 A 3
3/1/2023 A 3
5/1/2023 B 4
6/1/2023 B 4
7/1/2023 B 4
8/1/2023 B 4

How can I do it in postgresql ? Please help me

I have tried and I realy don’t know how to do it. I just a newbie too

Tags:

2

Answers


  1. 0

    was:enter image description here became:enter image description here

    You can call the function with the parameter of the number of rows you want to add:

        CREATE OR REPLACE FUNCTION insertInfo(ZE int, need_date date default null) returns void AS
$$
BEGIN
if need_date is not null then
for z IN 1..ze  LOOP
insert into x ( your_date,your_group,your_days)
select need_date + interval '1' day, your_group,your_days+z from x
where  your_date = need_date;
end loop;
else
FOR i IN 1..ze LOOP
insert into x ( your_date,your_group,your_days)
select your_date + interval '1' day, your_group,your_days+1 from x
where  your_date = (select max(your_date) from x);
end loop;
end if;
END
$$
  LANGUAGE 'plpgsql';

    if necessary date any count any quantity one parametr: select insertInfo(1,'2023-01-01');
    if not necessary, just any quantity one parametr: select insertInfo(1);

    Login or Signup to reply.
  2. 0

    You can use generate_series() function with each incoming row to generate the dates appropriate for that row. (see demo)

    select (generate_series( xdate
                       , xdate + (xdays-1) * interval '1 day'
                       , interval '1 day'
                       )
        )::date "Date"
    , xgroup    "Group"
    , xdays     "Days" 
  from x;

    Note: Each of your columns are poor choices for for names. They are all SQL Standard reserved works and/or Postgres reserved/restricted. They can be used safely if double quoted (i.e. "Date") but then must be double quoted everywhere they are used. Avoid double quoting if all ll possible.
    When posting dates/timestamps to an international audience it is always best to use ISO 8601 Standard date format (yyyy-mm-dd). It is unambiguous regardless of your local format.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search