typecast string to integer - Postgres

Question

I am importing data from a table which has raw feeds in Varchar  I need to import a column in varchar into a string column  I tried using the  lt column name gt   integer as well as to number  lt column name gt   9999999   but I am getting errors  as there are a few empty fields  I need to retrieve them as empty or null into the new table    Kindly let me know if there is a function for the same

User · Answer

The only way I succeed to not having an error because of NULL  or special characters or empty string is by doing this   SELECT REGEXP REPLACE COALESCE  lt column gt   character varying   0       0-9      0    integer FROM table

User · Answer

You can even go one further and restrict on this coalesced field such as  for example -  SELECT CAST coalesce  lt column gt    0   AS integer  as new field from  lt table gt  where CAST coalesce  lt column gt    0   AS integer   gt   10

User · Answer

If you need to treat empty columns as NULLs  try this   SELECT CAST nullif  lt column gt       AS integer     On the other hand  if you do have NULL values that you need to avoid  try   SELECT CAST coalesce  lt column gt    0   AS integer     I do agree  error message would help a lot

User · Answer

If the value contains non-numeric characters  you can convert the value to an integer as follows   SELECT CASE WHEN  lt column gt  E    d    THEN CAST   lt column gt  AS INTEGER  ELSE 0 END FROM table    The CASE operator checks the  lt  column   if it matches the integer pattern  it converts the rate into an integer  otherwise it returns 0

User · Answer

you can use this query  SUM NULLIF conversion units       numeric

User · Answer

Common issue  Naively type casting any string into an integer like so  SELECT     integer   Often results to the famous error   Query failed  ERROR  invalid input syntax for integer       Problem  PostgreSQL has no pre-defined function for safely type casting any string into an integer   Solution  Create a user-defined function inspired by PHP s intval   function   CREATE FUNCTION intval character varying  RETURNS integer AS     SELECT CASE     WHEN length btrim regexp replace  1     0-9        g     gt 0 THEN btrim regexp replace  1     0-9        g     integer     ELSE 0 END AS intval      LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT    Usage     Example 1    SELECT intval  9000    -- output  9000     Example 2    SELECT intval  9gag    -- output  9     Example 3    SELECT intval  the quick brown fox jumps over the lazy dog    -- output  0

User · Answer

Wild guess  If your value is an empty string  you can use NULLIF to replace it for a NULL   SELECT     NULLIF your value       int

User · Answer

I m not able to comment  too little reputation  I m pretty new  on Lukas  post   On my PG setup to number NULL  does not work  so my solution would be   SELECT CASE WHEN column   NULL THEN NULL ELSE column    Integer END FROM table

[postgresql] typecast string to integer - Postgres

Examples related to postgresql

Examples related to casting