CODEWITHSUNDEEP
databasepostgresql
AntiGMO
AntiGMO

Reputation: 1587

postgresql import csv file

I need upload a csv file to postgresql database table. the csv file like

A           B               C       D               E       F

16777216    16777471        AU      AUSTRALIA       OC      OCEANIA
16777472    16778239        CN      CHINA           AS      ASIA
16778240    16779263        AU      AUSTRALIA       OC      OCEANIA
16779264    16781311        CN      CHINA           AS      ASIA

and the table is 

CREATE TABLE ipligence
(
  ip_from numeric(11,0) NOT NULL DEFAULT 0,
  ip_to numeric(11,0) NOT NULL DEFAULT 0,
  country_code character varying(10)[] NOT NULL,
  country_name character varying(225)[] NOT NULL,
  continent_code character varying(10)[] NOT NULL,
  continent_name character varying(255)[] NOT NULL,
  CONSTRAINT ipligence_pkey PRIMARY KEY (ip_to )
 )
WITH (
  OIDS=FALSE
 );
ALTER TABLE ipligence

and i use copy command in pgsql, 

copy ipligence 
from '/home/pgsql/ipligence-lite.csv' 
delimiter as ',' 
csv quote as '"';

but it shows 

  ERROR:  array value must start with "{" or dimension information
  CONTEXT:  COPY ipligence, line 1, column country_code: "AU"

how can i successfully input csv file into postgresql database. Thanks!

Upvotes: 5

Views: 2918

Answers (1)

Andy Lester
Andy Lester

Reputation: 93676

Your table is created incorrectly. For example, this column declaration:

country_code character varying(10)[] NOT NULL,

is creating an array of varchar(10). You only want one. Drop the [] on those columns.

Also, the sample data you posted is not actually comma-separated, but I'm assuming that the actual data file is.

Upvotes: 8

Related Questions