Trouble importing .csv file

0
votes

Please forgive my ignorance, I am incredibly new to coding/postgreSQL. I'm following the instructions in a book and trying to import test data provided. Below is what I have to import a .csv file.

SET SESSION datestyle = "ISO, DMY"; COPY main.gps_data(
gps_sensors_code, line_no, utc_date, utc_time, lmt_date, lmt_time, ecef_x,
ecef_y, ecef_z, latitude, longitude, height, dop, nav, validated, sats_used,
ch01_sat_id, ch01_sat_cnr, ch02_sat_id, ch02_sat_cnr, ch03_sat_id,
ch03_sat_cnr, ch04_sat_id, ch04_sat_cnr, ch05_sat_id, ch05_sat_cnr,
ch06_sat_id, ch06_sat_cnr, ch07_sat_id, ch07_sat_cnr, ch08_sat_id,
ch08_sat_cnr, ch09_sat_id, ch09_sat_cnr, ch10_sat_id, ch10_sat_cnr,
ch11_sat_id, ch11_sat_cnr, ch12_sat_id, ch12_sat_cnr, main_vol, bu_vol,
temp, easting, northing, remarks)
FROM
'C:\Users\Documents\SpatialDatabaseBook\tracking_db\data\sensors_data\GSM01438.csv'
WITH (FORMAT csv, HEADER, DELIMITER ';')

I'm getting the following error:

ERROR:  malformed array literal: "3D"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  COPY gps_data, line 2, column nav: "3D"
SQL state: 22P02

My .csv file has the nav columns values as either: "3D", "2D", or "NO" (with the quotes).
The nav column is set as Character Varying (2).

    gps_data_id serial,
gps_sensors_code character varying,
line_no integer,
utc_date date,
utc_time time without time zone,
lmt_date date,
lmt_time time without time zone,
ecef_x integer,
ecef_y integer,
ecef_z integer,
latitude double precision,
longitude double precision,
height double precision,
dop double precision,
nav character varying (2),
validated character varying (3),
sats_used integer,
ch01_sat_id integer,
ch01_sat_cnr integer,
ch02_sat_id integer,
ch02_sat_cnr integer,
ch03_sat_id integer,
ch03_sat_cnr integer,
ch04_sat_id integer,
ch04_sat_cnr integer,
ch05_sat_id integer,
ch05_sat_cnr integer,
ch06_sat_id integer,
ch06_sat_cnr integer,
ch07_sat_id integer,
ch07_sat_cnr integer,
ch08_sat_id integer,
ch08_sat_cnr integer,
ch09_sat_id integer,
ch09_sat_cnr integer,
ch10_sat_id integer,
ch10_sat_cnr integer,
ch11_sat_id integer,
ch11_sat_cnr integer,
ch12_sat_id integer,
ch12_sat_cnr integer,
main_vol double precision,
bu_vol double precision,
temp double precision,
easting integer,
northing integer,
remarks character varying
);
COMMENT ON TABLE main.gps_data
IS 'Table that stores raw data as they come from the sensors (plus the ID of the sensor).';

Anything else you need to know to help?

1
arrayspostgresql
Apparently your nav column is defined as character varying[2] not character varying(2) - note the square brackets vs. the parentheses - a_horse_with_no_name
Sorry, that's just my typing error. I defined the column using the pgAdmin4 in the properties when setting up the columns so I'm pretty sure that's correct. I'll try and find the SQL statement for that. - enlargent
The CREATE TABLE statement and the offending line from the file would help. - Laurenz Albe
I added the create table statement...I think. I'm not sure what you're looking for regarding the offending line from the file. The values in question are written in the original question. - enlargent
This: ERROR: malformed array literal: "3D" DETAIL: Array value must start with "{" or dimension information., says that nav is defined as character varying[2](an array). - Adrian Klaver

1 Answers

0
votes

You mistakenly defined a column as array, perhaps using

nav character varying[2]

when you meant

nav character varying(2)

If the table is still empty, the easiest fix is to change the data type, discarding all existing values:

ALTER TABLE main.gps_data
   ALTER nav TYPE character varying(2) USING (NULL);