CODEWITHSUNDEEP
sqlpostgresqlcasting
Junior
Junior

Reputation: 147

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

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.

Upvotes: 1

Views: 3977

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 2

Related Questions