PostgreSQL error code
PostgreSQL error 22011
22011 is the SQLSTATE PostgreSQL reports for substring error, in class 22, Data Exception.
| SQLSTATE | 22011 |
|---|---|
| Condition name | substring_error |
| Class | 22 — Data Exception |
Reproduce it, then fix it
For a value without an @, position() returns 0, so the length becomes -1. split_part returns the whole string when the separator is missing. Run the broken query to see the error, then switch to the fixed one. Edit either freely; every run is rolled back.
SELECT substring(email FROM 1 FOR position('@' IN email) - 1) AS local_part
FROM (VALUES ('alice@example.com'), ('no-email')) AS v(email);SELECT split_part(email, '@', 1) AS local_part
FROM (VALUES ('alice@example.com'), ('no-email')) AS v(email);