Quantcast

SQL state: 22P02 Error during a COPY FROM a CSV file

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

SQL state: 22P02 Error during a COPY FROM a CSV file

Sherman Brown
I am new to PostgresSQL and I am trying to populate a table with data from a CSV file.
I have PostgresSQL loaded on a windows XP machine with multiple hard drives.
The CSV file was generated from Excel 2007.  
I have a couple of fields with embedded commas as show in the second line of the data.


This is the table structure:
CREATE TABLE mp3.songs
(
  "Name" character varying[] NOT NULL,
  "Artist" character varying[] NOT NULL,
  "Composer" character varying[],
  "Album" character varying[] NOT NULL,
  "Grouping" character varying[],
  "Genre" character varying[],
  "Size" integer,
  "Time" integer,
  "Disc Number" integer,
  "Disc Count" integer,
  "Track Number" integer,
  "Track Count" integer,
  "Year" date,
  "Date Modified" date,
  "Date Added" date,
  "Bit Rate" integer,
  "Sample Rate" integer,
  "Volume Adjustment" integer,
  "Kind" character varying[],
  "Equalizer" character varying[],
  "Comments" character varying[],
  "Play Count" integer,
  "Last Played" date,
  "Skip Count" integer,
  "Last Skipped" date,
  "My Rating" character varying[],
  "Location" character varying[],
  CONSTRAINT songs_pkey PRIMARY KEY ("Name", "Artist", "Album")
)
WITH (OIDS=FALSE)
TABLESPACE mp3;
ALTER TABLE mp3.songs OWNER TO postgres;

Here are the first 4 lines from my CSV file:
Name,Artist,Composer,Album,Grouping,Genre,Size,Time,Disc Number,Disc Count,Track Number,Track Count,Year,Date Modified,Date Added,Bit Rate,Sample Rate,Volume Adjustment,Kind,Equalizer,Comments,Play Count,Last Played,Skip Count,Last Skipped,My Rating,Location
Talking About,Susan Tedeschi,"Susan Tedeschi, Doyle Bramhall II & Derek Trucks",Back To The River,,Blues,4175697,263,1,1,1,11,2008,1/1/2009 16:38,1/1/2009 16:38,128,44100,,AAC audio file,,,1,1/1/2009 16:47,,,,C:\Documents and Settings\SB\My Documents\My Music\iTunes\iTunes Music\Susan Tedeschi\Back To The River\01 Talking About.m4a
700 Houses,Susan Tedeschi,"Susan Tedeschi, John Leventhal & Ted Pecchio",Back To The River,,Blues,4401663,278,1,1,2,11,2008,1/1/2009 16:38,1/1/2009 16:38,128,44100,,AAC audio file,,,1,1/1/2009 16:51,,,,C:\Documents and Settings\SB\My Documents\My Music\iTunes\iTunes Music\Susan Tedeschi\Back To The River\02 700 Houses.m4a
Back To The River,Susan Tedeschi,Susan Tedeschi & Tony Joe White,Back To The River,,Blues,3827357,236,1,1,3,11,2008,1/1/2009 16:39,1/1/2009 16:39,128,44100,,AAC audio file,,,1,1/1/2009 16:57,,,,C:\Documents and Settings\SB\My Documents\My Music\iTunes\iTunes Music\Susan Tedeschi\Back To The River\03 Back To The River.m4a

Here is my COPY command:

Copy mp3.songs from 'G:/PostgreSQL/8.3/data/RecAdded.csv' with DELIMITER AS ',' CSV HEADER QUOTE AS '"'

Here is the error I am getting:

ERROR:  array value must start with "{" or dimension information
CONTEXT:  COPY songs, line 2, column Name: "Talking About"

********** Error **********

ERROR: array value must start with "{" or dimension information
SQL state: 22P02
Context: COPY songs, line 2, column Name: "Talking About"


I think that the problem may be simple but I cannot find any references or examples in the Archives to help me discover a solution.

Any help or advice would be appreciated.

Sherman






--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL state: 22P02 Error during a COPY FROM a CSV file

Richard Huxton
Sherman Brown wrote:
> CREATE TABLE mp3.songs
> (
>   "Name" character varying[] NOT NULL,
>   "Artist" character varying[] NOT NULL,
>   "Composer" character varying[],
>   "Album" character varying[] NOT NULL,
...

> Here are the first 4 lines from my CSV file:
> Name,Artist,Composer,Album,Grouping,Genre,Size,Time,Disc Number,Disc Count,Track Number,Track Count,Year,Date Modified,Date Added,Bit Rate,Sample Rate,Volume Adjustment,Kind,Equalizer,Comments,Play Count,Last Played,Skip Count,Last Skipped,My Rating,Location
> Talking About,Susan Tedeschi,"Susan Tedeschi, Doyle Bramhall II & Derek Trucks",Back To The River,,Blues,4175697,263,1,1,1,11,2008,1/1/2009 16:38,1/1/2009 16:38,128,44100,,AAC audio file,,,1,1/1/2009 16:47,,,,C:\Documents and Settings\SB\My Documents\My Music\iTunes\iTunes Music\Susan Tedeschi\Back To The River\01 Talking About.m4a

> Here is the error I am getting:
>
> ERROR:  array value must start with "{" or dimension information
> CONTEXT:  COPY songs, line 2, column Name: "Talking About"

The problem is that you've defined your "name" column as being an array
of varchar values. That's not what you're providing.

=> CREATE TABLE mp3s (track_name text[]);
CREATE TABLE
=> INSERT INTO mp3s (track_name) VALUES ( '{"first item", "second item"}' );
INSERT 0 1

See the manual: data-types, arrays for more details.

--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL state: 22P02 Error during a COPY FROM a CSV file

Tom Lane-2
In reply to this post by Sherman Brown
Sherman Brown <[hidden email]> writes:
> This is the table structure:
> CREATE TABLE mp3.songs
> (
>   "Name" character varying[] NOT NULL,
>   "Artist" character varying[] NOT NULL,
>   "Composer" character varying[],

I'll bet a large amount of money that what you want is just plain
"character varying", not arrays of character varying.

                        regards, tom lane

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Loading...