"ERROR: extra data after last expected column" when using PostgreSQL COPY

database-design dynamic-sql postgresql sqlbulkcopy

Question

Since this is my first post, kindly be patient with me.

I'm attempting to use PostgreSQL 9.2's COPY command to add a tab-delimited table from a.txt file to a PostgreSQL database such as:

COPY raw_data FROM '/home/Projects/TestData/raw_data.txt' WITH (DELIMITER ' ');

Using the SQL command: I've previously established a blank table in the database named "raw data."

CREATE TABLE raw_data ();

When attempting to run the, I continue to get theCOPY command:

ERROR:  extra data after last expected column
CONTEXT:  COPY raw_data, line 1: "  1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34  35  36  ..."

(The column heads are shown by the numbers here.)

I'm attempting to avoid having to manually input 800 columns, but I'm not sure whether that's because I didn't specify table columns while building the database table.

Any ideas on how to resolve this?

The.txt file looks like this as an illustration:

        1   2   3   4   5   6   7   8   9
binary1 1   1   0   1   1   1   1   1   1
binary2 1   0   0   1   0   1   1   0   0
binary3 1   0   1   1   1   0   0   1   0
binary4 1   1   1   1   0   1   0   1   0
1
7
5/3/2013 11:42:47 PM

Accepted Answer

A table that is empty won't do. You need a table with the same structure as the incoming data. the following

CREATE TABLE raw_data (
  col1 int
, col2 int
  ...
);

There's no need to proclaimtab as DELIMITER because that is the standard:

COPY raw_data FROM '/home/Projects/TestData/raw_data.txt';

What, 800 columns? That many columns would normally suggest that your design is flawed. However, there are methods to partially automate theCREATE TABLE script.

Automation

A simple raw data assumption

1   2   3   4  -- first row contains "column names"
1   1   0   1  -- tab separated
1   0   0   1
1   0   1   1

Establish a differentDELIMITER (one that is entirely absent from the import data) and import to a temporary staging table with a singletext column:

CREATE TEMP TABLE tmp_data (raw text);

COPY tmp_data FROM '/home/Projects/TestData/raw_data.txt' WITH (DELIMITER '§');

Using this query, theCREATE TABLE script:

SELECT 'CREATE TABLE tbl (col' || replace (raw, E'\t', ' bool, col') || ' bool)'
FROM   (SELECT raw FROM tmp_data LIMIT 1) t;

A safer and more general question:

SELECT 'CREATE TABLE tbl('
    ||  string_agg(quote_ident('col' || col), ' bool, ' ORDER  BY ord)
    || ' bool);'
FROM  (SELECT raw FROM tmp_data LIMIT 1) t
     , unnest(string_to_array(t.raw, E'\t')) WITH ORDINALITY c(col, ord);

Returns:

CREATE TABLE tbl (col1 bool, col2 bool, col3 bool, col4 bool);

Execute after validating, or do so dynamically if you are confident in the outcome:

DO
$$BEGIN
EXECUTE (
   SELECT 'CREATE TABLE tbl (col' || replace(raw, ' ', ' bool, col') || ' bool)'
   FROM  (SELECT raw FROM tmp_data LIMIT 1) t
   );
END$$;

Then INSERT data relating to this inquiry:

INSERT INTO tbl
SELECT (('(' || replace(replace(replace(
                  raw
                , '1',   't')
                , '0',   'f')
                , E'\t', ',')
             || ')')::tbl).*
FROM   (SELECT raw FROM tmp_data OFFSET 1) t;

Simpler still using translate()

INSERT INTO tbl
SELECT (('(' || translate(raw, E'10\t', 'tf,') || ')')::tbl).*
FROM   (SELECT raw FROM tmp_data OFFSET 1) t;

The string is deconstructed with, cast to the newly formed table row type, and turned into a row literal.(row).* .

I'm done.

All of that could be included in a plpgsql function, but you would need to protect against SQL injection. (On SO, there are a lot of relevant solutions. Try searching.

fiddle db> 114-114-zzz
Old SQL Game

12
4/18/2019 4:30:55 PM

Popular Answer

Check out the HEADER option in COPY and use commands like: COPY FROM "/path/to/csv/SourceCSVFile.csv" DELIMITERS "," CSV HEADER to generate the table directly.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow