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

Postgresql – How to delete the rows which can cast to date?

I have a varchar field and want to remove rows which can cast only to date

value
01.01.2024
1.2
1
0.6
11.11.2024

I tried this script, but it’s work incorrect

select * from values where value > '01.01.1970'

Result:

value
01.01.2024
1
11.11.2024

I need

value
01.01.2024
11.11.2024

This script getting exception:

select * from values where cast(value as date) > '01.01.1970'

Incorrect sintax for type "1.2"

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    just need to use regex

     select * from values where value similar to '[0-9]{2}.[0-9]{2}.[0-9]{4}'

  2. 0

    use the STR_TO_DATE function to check if a string can be converted to a date, and then use that condition in a DELETE statement.

    DELETE FROM your_table
WHERE STR_TO_DATE(your_column, '%Y-%m-%d') IS NOT NULL;
    Login or Signup to reply.
  3. 0

    As of version 16 you can use the function pg_input_is_valid() to test if some content matches a data type, like a date.

    SELECT
    CAST(content AS date)
FROM (VALUES ('foo'), ('01.01.2024'), ('1.2.1900')) AS sub(content)
WHERE pg_input_is_valid(content,'date')
AND CAST(content AS date) > '1970-01-01';

    Without this condition, the query will fail:

    [22007] ERROR: invalid input syntax for type date: "foo"

    And of course you will always use a valid ISO date in your database.

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

Back To Top
Search