How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?

by

I was just wrestling with a similar problem myself, but didn’t want the overhead of a function. I came up with the following query:

SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\\d+$';

Postgres shortcuts its conditionals, so you shouldn’t get any non-integers hitting your ::integer cast. It also handles NULL values (they won’t match the regexp).

If you want zeros instead of not selecting, then a CASE statement should work:

SELECT CASE WHEN myfield~E'^\\d+$' THEN myfield::integer ELSE 0 END FROM mytable;

More Related Contents:

  1. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  2. Casting NULL type when updating multiple rows
  3. Double colon `::` notation in SQL
  4. Discard millisecond part from timestamp
  5. How to convert PostgreSQL 9.4’s jsonb type to float
  6. Refactor a PL/pgSQL function to return the output of various SELECT queries
  7. PostgreSQL “Column does not exist” but it actually does
  8. Is SELECT or INSERT in a function prone to race conditions?
  9. Index for finding an element in a JSON array
  10. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  11. Column ‘mary’ does not exist
  12. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  13. Get top row(s) with highest value, with ties
  14. Delete parent if it’s not referenced by any other child
  15. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  16. I keep getting the error “relation [TABLE] does not exist”
  17. Export specific rows from a PostgreSQL table as INSERT SQL script
  18. Postgres – Function to return the intersection of 2 ARRAYs?
  19. Which datatype should be used for currency?
  20. How to return two columns with function
  21. COPY with dynamic file name
  22. Table or column name cannot start with numeric?
  23. Convert String to Date in MS Access Query
  24. Count cumulative total in Postgresql
  25. PostgreSQL column ‘foo’ does not exist
  26. Prepared Statement on Postgresql in Rails
  27. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  28. PostgreSQL: Full Text Search – How to search partial words?
  29. Temporal database design, with a twist (live vs draft rows)
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment