create table calendar as with recursive g("date") as 
  (select '2000-01-01'::date union all select "date"+1 from g 
   where "date" <= '2039-12-31'::date) 
select "date" from g;

字符串
要保持连续编号，可以使用extract()和row_number()window function，窗口定义基于date_trunc()：demo

create table new_table as 
select "date", 
       extract(yearday from "date") as "Fiscal_year_day_nr",
       row_number()over(partition by date_trunc('year',"date"),
                                     date_trunc('quarter',"date")
                        order by "date") 
         as "Fiscal_quarter_daynr"
from calendar
order by "date";

型
| 日期|财政年度日编号|会计季度日r|
| --|--|--|
| 2023-12-31 - 2023| * （非闰年较短）* 365| 92 |
| 2024-01-01 2024-01-01| 1 | 1 |
| 2024-02-28 2024-02-28 2024-02-28| 59 | 59 |
| 2024-02-29 2024-02-29 2024-02-29| * （闰年）* 60| 60 |
| 2024-03-01 2024-03-01 2024-03-01| 61 | 61 |
| 2024-03-31 -03- 01 - 01 - 02| 91 | 91 |
| 2024-04-01 2024-04-01| 92 | 1 |
| 2025-02-28 2025-02-28 2025-02-28| 59 | 59 |
| 2025-03-01 2025-03-01 2025-03-01| * （无差距）60*| 60 |
| 2025-03-31 2025-03-31 2025-03-31| 90 | 90 |
| 2025-04-01 2025-04-01| 91 | 1 |
如果您 * 确实 * 希望在非闰年看到编号间隙，您可以在适当的时候有条件地添加日期。将条件boolean转换为int可以简化此操作：

create table calendar_skipping_feb29 as 
select "date",
       extract(yearday from "date")
          +(extract(month from date_trunc('year',"date")::date+59) = 3
            and 60<=extract(yearday from "date"))::int
          AS "Fiscal_year_day_nr",
       row_number()over(partition by date_trunc('year',"date"),
                                     date_trunc('quarter',"date")
                        order by "date")
          +(extract(month from date_trunc('year',"date")::date+59) = 3
            and 60<=extract(yearday from "date") 
            and 1 =extract(quarter from "date"))::int
          AS "Fiscal_quarter_daynr"
from calendar;

型
如果一年中的第60天（Jan 1st +59天）是在3月（月份3），这是一个非闰年，所以从第60天开始，您将1添加到"Fiscal_year_day_nr"（true转换为int是1）。
| 日期|财政年度日编号|会计季度日r|
| --|--|--|
| 2023-12-31 - 2023| * （增加间隔的非闰年）366*| 92 |
| 2024-01-01 2024-01-01| 1 | 1 |
| 2024-02-28 2024-02-28 2024-02-28| 59 | 59 |
| 2024-02-29 2024-02-29 2024-02-29| * （闰年不受影响）* 60| 60 |
| 2024-03-01 2024-03-01 2024-03-01| 61 | 61 |
| 2024-03-31 -03- 01 - 01 - 02| 91 | 91 |
| 2024-04-01 2024-04-01| 92 | 1 |
| 2025-02-28 2025-02-28 2025-02-28| 59 | 59 |
| 2025-03-01 2025-03-01 2025-03-01| * （增加的差距）61*| 61 |
| 2025-03-31 2025-03-31 2025-03-31| 91 | 91 |
| 2025-04-01 2025-04-01| 92 | 1 |

优雅是很好的，但永远不要低估蛮力的力量。你可以用date functions提供的标准Postgres来实现你想要的，即使是版本8（这是Redshift的基础）和一个小助手函数。（参见演示）

create or replace function is_leap_year(year_in integer)
  returns boolean
 language plpgsql
 immutable
as $$
declare 
    lvar date; 
begin
    lvar = make_date(year_in,02,29); 
    return true; 
exception 
    when others then
        return false;
end;
$$;

with std_calendar(gregorian_dt) as 
     ( select generate_series('2000-01-01'::date 
                             ,'2004-03-01'::date
                             ,interval '1 day'
                             )::date
      ) --select * from std_calendar
insert into fy_table(gre_date
                    ,fiscal_year_day_nr
                    ,fiscal_quarter
                    ,fiscal_quarter_daynr
                    )   
    select gregorian_dt::date
         , extract(doy from gregorian_dt)
               + case when not is_leap_year(extract(year from gregorian_dt)::integer)
                       and extract(month from gregorian_dt) > 2 
                      then 1
                      else 0
                 end fydn 
       , extract(quarter from gregorian_dt) fg 
       , extract( days from (gregorian_dt - date_trunc('quarter', gregorian_dt))) + 1
             + case when not is_leap_year(extract(year from gregorian_dt)::integer)
                     and extract(month from gregorian_dt) > 2 
                    then 1
                    else 0
               end fqdnr
  from std_calendar;

字符串
您没有定义您的财政年度，因此演示假设它对应于日历年，并且您希望具有相同的跳过日期功能。