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

Postgresql – How can I ignore invalid data when casting to timestamp type?

I have a table who has a column timestamp which type is text. Most of the rows have timestamp format as 2024-10-13T00:00:00.000Z. But there are some rows have invalid timestamp value.

When I use this date_trunc to aggregate the timestamp,

select date_trunc('hour', timestamp_candidate::timestamp) hourly

I got this error:

ERROR: invalid input syntax for type timestamp: "xxxxxx"

The invalid value could be anything including null. Is there a way to skip any invalid values?

Tags:

2

Answers


  1. 0

    In PostgreSQL 16 and above, there’s pg_input_is_valid():
    demo at db<>fiddle

    select timestamp_candidate
      ,timestamp_candidate::timestamp
      ,date_trunc('hour', (timestamp_candidate)::timestamp) hourly
from your_table
where pg_input_is_valid(timestamp_candidate,'timestamp');
    timestamp_candidate
    it’s type text    		 timestamp_candidate
    as timestamp    		 hourly
    note Special Date/Time Inputs are valid
    2024-12-13 12:00 2024-12-13 12:00:00 2024-12-13 12:00:00
    2024-12-13 07:41:18.196153+00 2024-12-13 07:41:18.196153 2024-12-13 07:00:00
    2024-12-13 2024-12-13 00:00:00 2024-12-13 00:00:00
    today allballs 2024-12-13 00:00:00 2024-12-13 00:00:00
    epoch 1970-01-01 00:00:00 1970-01-01 00:00:00
    infinity infinity infinity
    -infinity -infinity -infinity
    now 2024-12-13 07:41:18.196153 2024-12-13 07:00:00
    today 2024-12-13 00:00:00 2024-12-13 00:00:00
    tomorrow 2024-12-14 00:00:00 2024-12-14 00:00:00
    yesterday 2024-12-12 00:00:00 2024-12-12 00:00:00

    Here’s what it skipped over. Thing to note is that a text literal 'null' isn’t considered a valid input that would lead to a timestamp-typed field with a null in it and a regular null yields null rather than true or false even though null::timestamp works fine.

    rejected pg_input_is_valid
    not_a_timestamp f
    another thing that is not a timestamp f
    null null
    null f
    allballs
    (it’s a midnight that’s missing a date)    		 f

    From the doc:

    pg_input_is_valid ( string text, type text ) → boolean
    Tests whether the given string is valid input for the specified data type, returning true or false.
    This function will only work as desired if the data type’s input function has been updated to report invalid input as a “soft” error. Otherwise, invalid input will abort the transaction, just as if the string had been cast to the type directly.

    pg_input_is_valid('42', 'integer') → t
pg_input_is_valid('42000000000', 'integer') → f
pg_input_is_valid('1234.567', 'numeric(7,4)') → f

    In PostgreSQL 15 and earlier, you can build your own:

    create or replace function is_interpretable_as(arg text, arg_type text) 
returns boolean language plpgsql as $$
begin
    execute format('select cast(%L as %s)', arg, arg_type);
    return true;
exception when others then
    return false;
end $$;

    Note that this will return false regardless of whether the argument or the type is invalid, or both. Unlike pg_input_is_valid(), it yields true for null, considering it a valid input for any type.

    Login or Signup to reply.
  2. 0

    Postgres does not have a corresponding function to Sql Server’s try_cast() function. But it is a simple matter to create a specific implementation (in this case for timestamp). The following is one such example: (see demo)

    create or replace function is_valid_timestamp(timestamp_candidate text)
   returns timestamp 
  language plpgsql
  strict
as $$
  begin 
      return timestamp_candidate::timestamp;
  exception 
      when invalid_datetime_format then return null;
  end;
$$;

    Note: The function is susceptible to your datestyle setting.

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

Back To Top
Search