## TL;DR
Find the malformed values with a regex filter, then parse with `TO_TIMESTAMP` using an explicit format string instead of relying on the cast. The error means at least one value isnt in a format Postgres recognizes as a timestamp, and a bare `::timestamp` cast gives you no way to say what format to expect.

```text
ERROR: invalid input syntax for type timestamp: "2026-13-45 99:99:99"
```

## Use this when
- A cast, insert, or COPY fails naming type timestamp
- You need to find which rows have bad timestamp strings
- Source data uses a non-ISO datetime format

## Not for this skill when
- The question is about timezones (AT TIME ZONE covers that)
- The timestamps parse but the math is wrong
- You are on a database with different timestamp literal rules

## Steps

1. Find the bad rows before fixing anything. Filter for values that dont look like timestamps:

```sql
SELECT id, created_at_raw
FROM staging_orders
WHERE created_at_raw !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}([ T][0-9]{2}:[0-9]{2}(:[0-9]{2})?)?$'
LIMIT 20;
```
Expected output: the offending rows, things like empty strings, `"N/A"`, `"13/45/2026"`, or month 13. You now know the actual formats you are dealing with instead of guessing.

2. Handle the easy cases: empty strings and obvious placeholders become NULL:

```sql
SELECT NULLIF(TRIM(created_at_raw), '') AS cleaned
FROM staging_orders
LIMIT 5;
```
Expected output: empty and whitespace-only strings as NULL. A bare cast chokes on `''`, so clean it first.

3. Parse known non-ISO formats with TO_TIMESTAMP and an explicit format string:

```sql
SELECT TO_TIMESTAMP('10/04/2026 02:30 PM', 'MM/DD/YYYY HH:MI PM') AS parsed;
```
Expected output: `2026-10-04 14:30:00`. The format string tells Postgres exactly where each part is, so American-style dates parse correctly.

4. Apply the parse across the staging table, keeping the raw value for audit:

```sql
ALTER TABLE staging_orders ADD COLUMN created_at timestamptz;
UPDATE staging_orders
SET created_at = TO_TIMESTAMP(TRIM(created_at_raw), 'MM/DD/YYYY HH12:MI AM')
WHERE created_at_raw IS NOT NULL AND TRIM(created_at_raw) <> '';
```
Expected output: an update count. If it errors, the WHERE clause missed a format variant; go back to step 1 and widen the net.

5. Prevent recurrence by typing the landing column correctly and validating at ingest:

```sql
ALTER TABLE staging_orders ALTER COLUMN created_at_raw TYPE text;
-- in the load job: reject or quarantine rows where the regex in step 1 matches
```
Expected output: future bad values get quarantined instead of killing the whole load. Land timestamps as text, validate, then cast into a real timestamp column.

## Variant phrasings

### invalid input syntax for type timestamp with time zone
Same fix. `timestamptz` accepts the same formats plus a zone; a value like `"2026-10-04"` (no zone) parses using the session TimeZone setting.

### date/time field value out of range
The sibling error: the format parsed but the values are impossible (month 13, hour 25). Step 1s regex wont catch these; add range checks or let TO_TIMESTAMP fail loudly on the leftovers.

### COPY failed invalid input syntax timestamp
COPY casts every value with no format hook. Load into a text staging column first (step 5), then convert with TO_TIMESTAMP.

## Why it happens
Postgres timestamp input accepts ISO 8601 plus a set of traditional formats, but it has to guess when the format is ambiguous, and it refuses to guess on values that match nothing. ETL sources emit whatever their locale and code produce (MM/DD/YYYY, DD.MM.YYYY, epoch integers as strings), so the cast that worked in testing breaks on the first production file with a different convention.

## Edge cases
- Epoch seconds stored as text: cast to bigint first, then `TO_TIMESTAMP(epoch_col)` (the single-argument form takes double precision seconds).
- Two-digit years: TO_TIMESTAMP interprets them with a pivot rule you probably dont want; normalize to four digits upstream.
- Fractional seconds with more than 6 digits get truncated, not rounded; usually harmless, but know it.
- `00/00/0000`-style zero dates from MySQL dumps: map them to NULL explicitly, Postgres has no zero date.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_B3x3pKLv2iEU0O7keppjJQ