PostgreSQL error code
PostgreSQL error 22003
22003 is the SQLSTATE PostgreSQL reports for numeric value out of range, in class 22, Data Exception.
| SQLSTATE | 22003 |
|---|---|
| Condition name | numeric_value_out_of_range |
| Class | 22 — Data Exception |
Reproduce it, then fix it
extract(epoch ...) fits in an integer, but multiplying by 1000 goes past 2,147,483,647. Cast to bigint before the multiplication, not after it. Run the broken query to see the error, then switch to the fixed one. Edit either freely; every run is rolled back.
SELECT id, extract(epoch FROM created_at)::int * 1000 AS created_ms FROM orders;
SELECT id, extract(epoch FROM created_at)::bigint * 1000 AS created_ms FROM orders;