typecast string to integer - Postgres

postgresql casting
Vijay DJ picture Vijay DJ · May 9, 2012 · Viewed 350.3k times · Source

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 <column_name>::integer as well as to_number(<column_name>,'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.

Answer

Frank Heikens picture Frank Heikens · May 9, 2012

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

SELECT
    NULLIF(your_value, '')::int

Related questions

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

I want to extract just the date part from a timestamp in PostgreSQL. I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value. For example, if I have 2011/05/26 09:00:00, …

Read More
Change type of varchar field to integer: "cannot be cast automatically to type integer"

I have a small table and a certain field contains the type "character varying". I'm trying to change it to "Integer" but it gives an error that casting is not possible. Is there a way around this or should I …

Read More
PostgreSQL: ERROR: operator does not exist: integer = character varying

Here i am trying to create view as shown below in example: Example: create view view1 as select table1.col1,table2.col1,table3.col3 from table1 inner join table2 inner join table3 on table1.col4 = table2.col5 /* Here col4 of …

Read More