How to cast String value to Integer upon data insertion in Postgresql?

Question

I created a temporary table in the hopes of cleaning up the CSV file that will be imported to the final table. Since there is an assumption that the CSV files can be edited by a user freely, I created a temporary table in varchar data type.

I execute some queries to weed out unwanted data but the problem now is there are columns in the final table that accepts only Integer values (e.g. network_id and agent_id) while the temporary table are all in varchar datatype.

CREATE TABLE IF NOT EXISTS dummy_table(
    network_address varchar(128),
    network_alias varchar(64),
    network_id varchar(64) NOT NULL,
    agent_id varchar(64),
    agent_name varchar(64) NOT NULL,
    property_value varchar(128)
);

CREATE TABLE IF NOT EXISTS main_table(
    network_address varchar(128),
    network_alias varchar(64),
    network_id int4 NOT NULL,
    agent_id int4,
    agent_name varchar(64) NOT NULL,
    property_value varchar(128)
);

My attempt in doing this is:

INSERT INTO main_table
SELECT network_address, 
       network_alias, 
       CAST(network_id AS INTEGER), 
       CAST(agent_id AS INTEGER), 
       agent_name, 
       property_value 
FROM dummy_table;

But I encounter an error:

ERROR: column "network_id" is of type integer but expression is of type character

I suspect that maybe my casting is incorrect or perhaps double casting is needed but I have no idea on how to apply if ever that is the case. Your thoughts and ideas will be highly appreciated.

Gordon Linoff · Accepted Answer · 2017-10-25 02:48:09Z

2

When doing inserts, you should always list the columns explicitly:

INSERT INTO main_table (network_address, network_alias, network_id, agent_id, agent_name, property_value)
    SELECT network_address, network_alias, network_id::INTEGER,    
           agent_id::INTEGER, agent_name, property_value
     FROM dummy_table;

I can't be 100% sure this is the problem. However, the table name you are inserting into is different from the one that is defined, so I suspect this might be the problem.

answered Oct 25, 2017 at 2:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Junior Over a year ago

I wrongly typed the table name but I edited it already. I also tried your solution and yes it fixed my issue. Thank you very much!

Collectives™ on Stack Overflow

How to cast String value to Integer upon data insertion in Postgresql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related