Postgres data type cast

by 
cast(varchar_col AS int)  -- SQL standard

or

varchar_col::int          -- Postgres syntax shorthand

Theses syntax variants are valid (almost) anywhere. The second may require nesting parentheses in special situations:

And the first may be required where only functional notation is allowed by syntax restrictions:

There are two more variants:

int4(varchar_col)         -- only works for some type names
int '123'                 -- must be an untyped, quoted string literal

Note how I wrote int4(varchar_col). That’s the internal type name and there is also a function defined for it. Wouldn’t work as integer() or int().

Note also that the last form does not work for array types. int[] '{1,2,3}' has to be '{1,2,3}'::int[] or cast('{1,2,3}' AS int[]).

Details in the manual here and here.

To be valid for integer, the string must be comprised of an optional leading sign (+/-) followed by digits only. Leading / trailing white space is ignored.

More Related Contents:

  1. What are the pros and cons of performing calculations in sql vs. in your application
  2. org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
  3. How do I use PostgreSQL JSON(B) operators containing a question mark “?” via JDBC
  4. Why don’t Java’s +=, -=, *=, /= compound assignment operators require casting?
  5. Downcasting in Java
  6. How do I get the size of a java.sql.ResultSet?
  7. What is the best approach using JDBC for parameterizing an IN clause? [duplicate]
  8. How to print a query string with parameter values when using Hibernate
  9. MySQL and JDBC with rewriteBatchedStatements=true
  10. Does Java casting introduce overhead? Why?
  11. Cast Double to Integer in Java
  12. How does the Java cast operator work?
  13. how can I create new XML file from existing database in PostgreSQL database using java [closed]
  14. java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname [duplicate]
  15. How to cast an Object to an int
  16. How to have Java method return generic list of any type?
  17. How can I avoid ResultSet is closed exception in Java?
  18. Any Java libraries out there that validate SQL syntax? [closed]
  19. Casting to generic type in Java doesn’t raise ClassCastException?
  20. Hibernate use of PostgreSQL sequence does not affect sequence table
  21. Hibernate Group by Criteria Object
  22. How to use prepared statement for select query in Java?
  23. How to Iterate through HashMap in MyBatis foreach?
  24. JPA Select latest instance for each item
  25. Should I use composite primary keys or not?
  26. How to make a database listener with java?
  27. How to use SELECT IN clause in JDBCTemplates?
  28. Getting last record from mysql
  29. How to use Postgres JSONB datatype with JPA?
  30. How to avoid OOM (Out of memory) error when retrieving all records from huge table?

Leave a Comment