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

Postgresql – How to delete between year-week range?

Imagine there are some data in Postgres table mytable:

year (iso) week
2021 50
2021 51
2021 52
2022 1
2022 2
… …
2022 52
2023 1
2023 2
… …
2023 24 (current week)

I need to delete records that are between (2021, 51) and (2023, 1). It means that expected result should be:

year (iso) week
2021 50
2023 2
… …
2023 24 (current week)

What sql-query could filter this?

Tags:

2

Answers


  1. 0
    According to my experimental suggestion the below one will work
    DELETE FROM mytable
WHERE (year > 2021 AND year < 2023)
   OR (year = 2021 AND week >= 51)
   OR (year = 2023 AND week <= 1);
    Login or Signup to reply.
  2. 0

    This can be done by combining the year and the week, converting them to integers, and then apply a standard where :

    DELETE FROM mytable
WHERE (year, week) IN
(
  select year, week
  from (
    select *, concat(year, to_char(week, 'fm00'))::int as yearWeek
    from mytable
  ) as s
  where yearWeek between concat(2021, to_char(51, 'fm00'))::int 
                       and
                       concat(2023, to_char(1, 'fm00'))::int
)

    Demo here

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

Back To Top
Search