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?
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.
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.
try_castfunctiontimestampor typetext? It cannot be both. If timestamp, which one exactly:TIMESTAMP WITH TIME ZONEorTIMESTAMP WITHOUT TIME ZONE?try_cast()type of thing that gives you a default rather than skip the mismatch.[regex]just to add an example showing a mile-long convoluted pattern that still requires acaseto switch to a different one based oncurrent_setting('DateStyle'), and still doesn't quite replicate PostgreSQL internal logic.