---
title: "Find the rows a PostgreSQL column type change will reject or misread | Proofline"
description: "A type change with USING fails on the first value it cannot convert, and it converts some values to the wrong thing without an error."
url: https://proofline.sh/learn/postgres-column-type-conversion-check/
---

[Learning center](/learn/)

# Find the rows a PostgreSQL column type change will reject or misread

A type change with USING fails on the first value it cannot convert, and it converts some values to the wrong thing without an error. Before you deploy one, list the rows that will fail with pg\_input\_is\_valid, then look for values whose meaning depends on a session setting.

Breaking migrations The Proofline team November 20, 2026 3 min read

## A text column that should hold numbers and dates

A shipping service stored package weights and promised delivery dates as text. A migration converts both to proper types. Five rows show what years of hand-entered and imported data tend to contain. We ran this on PostgreSQL 16.15; the validity functions need PostgreSQL 16 or later.

```
CREATE TABLE shipments (
    id integer PRIMARY KEY,
    weight_grams text NOT NULL,
    promised_on text
);
INSERT INTO shipments VALUES
  (1, '1250', '2026-11-03'),
  (2, ' 980 ', '2026-11-04'),
  (3, '1.5kg', '04/11/2026'),
  (4, '', NULL),
  (5, '3000000000', '2026-02-30');

ALTER TABLE shipments
    ALTER COLUMN weight_grams TYPE integer USING weight_grams::integer;
```

```
ERROR:  invalid input syntax for type integer: "1.5kg"
```

The error names one value. On a real table you would fix it, run the migration again, and meet the next one, each time under an `ACCESS EXCLUSIVE` lock that blocks the table.

## List every row that will fail

[`pg_input_is_valid`](https://www.postgresql.org/docs/16/functions-info.html#FUNCTIONS-INFO-VALIDITY) tests a string against a type without raising an error. `pg_input_error_info` returns the message and SQLSTATE the cast would raise. Both run as a plain `SELECT`, so you can run them against a read replica before the migration is merged.

```
SELECT id, weight_grams,
       (pg_input_error_info(weight_grams, 'integer')).message
FROM shipments
WHERE NOT pg_input_is_valid(weight_grams, 'integer')
ORDER BY id;
```

| id | weight\_grams | message |
| --- | --- | --- |
| 3 | 1.5kg | invalid input syntax for type integer: "1.5kg" |
| 4 |  | invalid input syntax for type integer: "" |
| 5 | 3000000000 | value "3000000000" is out of range for type integer |

Row 2 does not appear: the integer parser accepts surrounding spaces. Each listed row needs a decision before the migration can run. Row 3 needs a unit conversion. Row 4 needs a rule for an unknown weight, which may mean a nullable column. Row 5 needs `bigint`, or it is an error in the source data. Put the decision in the migration as a `USING` expression or a prior data fix, and keep the query in the pull request so a reviewer can run it again.

## Check values that convert to the wrong thing

The same check on the dates finds one invalid row:

```
SELECT id, promised_on,
       (pg_input_error_info(promised_on, 'date')).message
FROM shipments
WHERE promised_on IS NOT NULL
  AND NOT pg_input_is_valid(promised_on, 'date');
```

| id | promised\_on | message |
| --- | --- | --- |
| 5 | 2026-02-30 | date/time field value out of range: "2026-02-30" |

Row 3 passes, and that is the more dangerous result. `04/11/2026` is ambiguous, and PostgreSQL reads it according to the session's [`DateStyle`](https://www.postgresql.org/docs/16/runtime-config-client.html#GUC-DATESTYLE):

```
SHOW DateStyle;               -- ISO, MDY
SELECT '04/11/2026'::date;    -- 2026-04-11

SET DateStyle = 'ISO, DMY';
SELECT '04/11/2026'::date;    -- 2026-11-04
```

The conversion succeeds under either setting, so no error will tell you that a shipment promised for 4 November now reads 11 April. The result depends on the settings of whichever connection runs the migration. Find values in an ambiguous format and convert them with an explicit pattern:

```
SELECT id, promised_on FROM shipments
WHERE promised_on !~ '^\d{4}-\d{2}-\d{2}$';

-- after confirming which format the source used
UPDATE shipments
SET promised_on = to_char(to_date(promised_on, 'DD/MM/YYYY'), 'YYYY-MM-DD')
WHERE promised_on ~ '^\d{2}/\d{2}/\d{4}$';
```

## Rehearse on the full column

The checks above read every row, so run them on a replica or a restored snapshot with the production row count. Record the number of rows in each failure group in the pull request. When the count is zero and no value relies on a session setting, the type change itself still holds a lock while it rewrites the table. [Which column type changes rewrite the table](/learn/postgres-alter-column-type-rewrite/) shows how to measure that part.

Find the other changes that need a rehearsal in [which PostgreSQL migration needs a rehearsal](/learn/postgres-migration-rehearsal/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Which PostgreSQL column type changes rewrite the table](/learn/postgres-alter-column-type-rewrite/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
    
-   ### [Which PostgreSQL migration needs a rehearsal before the next deploy](/learn/postgres-migration-rehearsal/)
    
    Breaking migrations The Proofline team November 20, 2026 4 min read
