"ERRORE: dati extra dopo l'ultima colonna prevista" quando si utilizza COPIA PostgreSQL

database-design dynamic-sql postgresql sqlbulkcopy

Domanda

Per favore, sopportami visto che questo è il mio primo post.

Sto cercando di eseguire il comando COPY in PostgreSQL-9.2 per aggiungere una tabella delimitata da un file .txt a un database PostgreSQL come:

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

Ho già creato una tabella vuota chiamata "raw_data" nel database utilizzando il comando SQL:

CREATE TABLE raw_data ();

Continuo a ricevere il seguente messaggio di errore quando si tenta di eseguire il comando COPY :

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  ..."

(I numeri qui dovrebbero essere le intestazioni delle colonne)

Non sono sicuro se è perché non ho specificato le colonne della tabella durante la creazione della tabella db, ma sto cercando di evitare di dover inserire manualmente 800 o colonne.

Qualche suggerimento su come risolvere questo problema?

Ecco un esempio di come appare il file .txt:

        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

Risposta accettata

Un tavolo vuoto non lo farà.

CREATE TABLE raw_data ();

È necessaria una tabella che corrisponda alla struttura dei dati di importazione. Qualcosa di simile a

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

Non è necessario dichiarare la tab come DELIMITER , poiché è l'impostazione predefinita:

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

800 colonne che dici? In 9 casi su 10 questo indica un problema con il tuo progetto. Normalmente non avresti tante colonne. Ad ogni modo, ci sono certamente dei modi per automatizzare a metà la creazione dello script CREATE TABLE .

Automazione

Supponendo dati grezzi semplificati

1   2   3   4  -- first row is meant as "column name"
1   1   0   1  -- tab separated
1   0   0   1
1   0   1   1

Definire un DELIMITER diverso (uno che non si verifica affatto nei dati di importazione) e importarlo in una tabella temporanea di staging con una singola colonna di text :

CREATE TEMP TABLE tmp_data (raw text);

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

Questa query crea lo script CREATE TABLE:

SELECT 'CREATE TABLE tbl (col' || replace (raw, E'\t',' bool, col') || ' bool)'
FROM   tmp_data
WHERE  raw ~~ E'1\t2\t3\t4%';   -- criteria to identify row with col names

Ritorna:

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

Esegui questo (dopo aver verificato la validità).
Quindi INSERT i dati con questa query:

INSERT INTO tbl
SELECT (('(' || replace(replace(replace(
                  raw
                , '1',   't')
                , '0',   'f')
                , E'\t', ',')
             || ')')::tbl).*
FROM   tmp_data
WHERE  raw !~~ E'1\t2\t3\t4%';  -- criteria to exclude row with col names

Oppure, più breve / più veloce con translate() :

INSERT INTO tbl
SELECT (('(' || translate(raw, E'10\t', 'tf,') || ')')::tbl).*
FROM   tmp_data
WHERE  raw !~~ E'1\t2\t3\t4%';

Converte la stringa in un formato compatibile per lanciarla sul tipo di tabella appena creato e poi la esplode con (row).* .

Tutto fatto.

Potresti inserire tutto ciò in una funzione plpgsql, ma dovresti proteggerti dall'iniezione SQL. Ho pubblicato un certo numero di soluzioni simili qui su SO. Prova una ricerca.
Dovrò lasciare qualcosa per risolverlo ...

-> Demo di SQLfiddle


Risposta popolare

puoi creare direttamente la tabella dal comando copia, controlla l'opzione HEADER in COPY come: COPY FROM '/path/to/csv/SourceCSVFile.csv' DELIMITERS ',' CSV HEADER



Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché