Sql error 22p04 error extra data after last expected column

HI,
When trying to import a zabbix database from mysql to postgres using FromMySqlToPostgreSql, I’m finding the following error:

    -- FromMySqlToPostgreSql::populateTableWorker

    -- PDOException code: 22P04
    -- File: /var/lib/pgsql/FromMySqlToPostgreSql-master/migration/FromMySqlToPostgreSql/FromMySqlToPostgreSql.php
    -- Line: 641
    -- Message: SQLSTATE[22P04]: Bad copy file format: 7 ERROR:  extra data after last expected column

CONTEXT: COPY items, line 13500: «40320,8,,,10292,»Jboss activeSessions»,»grpsum[«»{$SERVER_GROUP}»»,»»jmx[«jboss.web:type=Manager,pa…»
— SQL: SELECT itemid,type,snmp_community,snmp_oid,hostid,name,key_,delay,history,trends,status,value_type,trapper_hosts,units,multiplier,delta,snmpv3_securityname,snmpv3_securitylevel,snmpv3_authpassphrase,snmpv3_privpassphrase,formula,error,lastlogsize,logtimefmt,templateid,valuemapid,delay_flex,params,ipmi_sensor,data_type,authtype,username,password,publickey,privatekey,mtime,flags,interfaceid,port,description,inventory_link,lifetime,snmpv3_authprotocol,snmpv3_privprotocol,state,snmpv3_contextname,evaltype FROM items LIMIT 0, 36445;
COPY «public».»items» FROM ‘/var/lib/pgsql/FromMySqlToPostgreSql-master/temporary_directory/items0.csv’ DELIMITER ‘,’ CSV;

Table definition is as follows:

CREATE TABLE items (
itemid bigint(20) unsigned NOT NULL,
type int(11) NOT NULL DEFAULT ‘0’,
snmp_community varchar(64) NOT NULL DEFAULT »,
snmp_oid varchar(255) NOT NULL DEFAULT »,
hostid bigint(20) unsigned NOT NULL,
name varchar(255) NOT NULL DEFAULT »,
key_ varchar(255) NOT NULL DEFAULT »,
delay int(11) NOT NULL DEFAULT ‘0’,
history int(11) NOT NULL DEFAULT ’90’,
trends int(11) NOT NULL DEFAULT ‘365’,
status int(11) NOT NULL DEFAULT ‘0’,
value_type int(11) NOT NULL DEFAULT ‘0’,
trapper_hosts varchar(255) NOT NULL DEFAULT »,
units varchar(255) NOT NULL DEFAULT »,
multiplier int(11) NOT NULL DEFAULT ‘0’,
delta int(11) NOT NULL DEFAULT ‘0’,
snmpv3_securityname varchar(64) NOT NULL DEFAULT »,
snmpv3_securitylevel int(11) NOT NULL DEFAULT ‘0’,
snmpv3_authpassphrase varchar(64) NOT NULL DEFAULT »,
snmpv3_privpassphrase varchar(64) NOT NULL DEFAULT »,
formula varchar(255) NOT NULL DEFAULT »,
error varchar(2048) NOT NULL DEFAULT »,
lastlogsize bigint(20) unsigned NOT NULL DEFAULT ‘0’,
logtimefmt varchar(64) NOT NULL DEFAULT »,
templateid bigint(20) unsigned DEFAULT NULL,
valuemapid bigint(20) unsigned DEFAULT NULL,
delay_flex varchar(255) NOT NULL DEFAULT »,
params text NOT NULL,
ipmi_sensor varchar(128) NOT NULL DEFAULT »,
data_type int(11) NOT NULL DEFAULT ‘0’,
authtype int(11) NOT NULL DEFAULT ‘0’,
username varchar(64) NOT NULL DEFAULT »,
password varchar(64) NOT NULL DEFAULT »,
publickey varchar(64) NOT NULL DEFAULT »,
privatekey varchar(64) NOT NULL DEFAULT »,
mtime int(11) NOT NULL DEFAULT ‘0’,
flags int(11) NOT NULL DEFAULT ‘0’,
interfaceid bigint(20) unsigned DEFAULT NULL,
port varchar(64) NOT NULL DEFAULT »,
description text NOT NULL,
inventory_link int(11) NOT NULL DEFAULT ‘0’,
lifetime varchar(64) NOT NULL DEFAULT ’30’,
snmpv3_authprotocol int(11) NOT NULL DEFAULT ‘0’,
snmpv3_privprotocol int(11) NOT NULL DEFAULT ‘0’,
state int(11) NOT NULL DEFAULT ‘0’,
snmpv3_contextname varchar(255) NOT NULL DEFAULT »,
evaltype int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (itemid),
UNIQUE KEY items_1 (hostid,key_),
KEY items_3 (status),
KEY items_4 (templateid),
KEY items_5 (valuemapid),
KEY items_6 (interfaceid),
CONSTRAINT c_items_1 FOREIGN KEY (hostid) REFERENCES hosts (hostid) ON DELETE CASCADE,
CONSTRAINT c_items_2 FOREIGN KEY (templateid) REFERENCES items (itemid) ON DELETE CASCADE,
CONSTRAINT c_items_3 FOREIGN KEY (valuemapid) REFERENCES valuemaps (valuemapid),
CONSTRAINT c_items_4 FOREIGN KEY (interfaceid) REFERENCES interface (interfaceid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And the table line where it seems there is a problem follows:

select * FROM items where itemid=40320

*************************** 1. row ***************************
itemid: 40320
type: 8
snmp_community:
snmp_oid:
hostid: 10292
name: Jboss activeSessions
key_: grpsum[«{$SERVER_GROUP}»,»jmx[«jboss.web:type=Manager,path=/{$GRAU},host=localhost»,activeSessions]»,last,0]
delay: 30
history: 90
trends: 365
status: 0
value_type: 3
trapper_hosts:
units:
multiplier: 0
delta: 0
snmpv3_securityname:
snmpv3_securitylevel: 0
snmpv3_authpassphrase:
snmpv3_privpassphrase:
formula: 1
error:
lastlogsize: 0
logtimefmt:
templateid: NULL
valuemapid: NULL
delay_flex:
params:
ipmi_sensor:
data_type: 0
authtype: 0
username:
password:
publickey:
privatekey:
mtime: 0
flags: 0
interfaceid: NULL
port:
description:
inventory_link: 0
lifetime: 30
snmpv3_authprotocol: 0
snmpv3_privprotocol: 0
state: 0
snmpv3_contextname:
evaltype: 0

Any help?
Thanks in advance.

COPY cast_info FROM PROGRAM 'sed s/\\/"/g /private/tmp/cast_info.csv' WITH CSV;

Read More

• ERROR: extra data after last expected column on PostgreSQL while the number of columns is the same
• psycopg2 error while writing data from csv file: extra data after last expected column
• Postgresql COPY CSV ERROR: extra data after last expected column
• ERROR: extra data after last expected column on postgresql
• postgresql Error «extra data after last expected column” while trying to import csv file
• The query can’t be executed!ERROR: 22P04: extra data after last expected column

copy cast_info (id, person_id, movie_id, person_role_id, note, nr_order, role_id) from ... 

copy <table_name> from '<csv_file_path>' csv escape '';

ERROR: extra data after last expected column on PostgreSQL while the number of columns is the same

I am new to PostgreSQL and I need to import a set of csv files, but some of them weren’t imported successfully. I got the same error with these files: ERROR: extra data after last expected column. I have investigated this error report and learned that these errors occur might because the number of columns of the table is not equal to that in the file. But I don’t think I am in this situation.

For example, I create this table:

And then I want to copy the csv file:

Then I got the error:

The complete row in this csv file is as follows:

You can see that there’s exactly 7 columns as the table has.

The strange thing is, I found that the error lines of all these files contain the characters backslash and quotation mark (»). Also, these rows are not the only row that contains » in the files. I wonder why this error doesn’t appear in other rows. Because of that, I am not sure if this is the problem.

After modifying these rows (e.g. replace the » or delete the content while remaining the commas), there are new errors: ERROR: invalid input syntax for line 2 of every file. And the errors occur because the data in the last column of these rows have been added three semicolons(;;;) for no reason. But when I open these csv files, I can’t see the three semicolons in those rows.

For example, after deleting the content in the fifth column of this row:

I got the error:

While the line 2 doesn’t contain three semicolons, as follows:

In principle, I hope the problem can be solved without any modification to the data itself. Thank you for your patience and help!

Источник

ERROR: extra data after last expected column on PostgreSQL while the number of columns is the same

I am new to PostgreSQL and I need to import a set of csv files, but some of them weren’t imported successfully. I got the same error with these files: ERROR: extra data after last expected column. I have investigated this error report and learned that these errors occur might because the number of columns of the table is not equal to that in the file. But I don’t think I am in this situation.

For example, I create this table:

And then I want to copy the csv file:

Then I got the error:

The complete row in this csv file is as follows:

You can see that there’s exactly 7 columns as the table has.

The strange thing is, I found that the error lines of all these files contain the characters backslash and quotation mark (»). Also, these rows are not the only row that contains » in the files. I wonder why this error doesn’t appear in other rows. Because of that, I am not sure if this is the problem.

After modifying these rows (e.g. replace the » or delete the content while remaining the commas), there are new errors: ERROR: invalid input syntax for line 2 of every file. And the errors occur because the data in the last column of these rows have been added three semicolons(;;;) for no reason. But when I open these csv files, I can’t see the three semicolons in those rows.

For example, after deleting the content in the fifth column of this row:

I got the error:

While the line 2 doesn’t contain three semicolons, as follows:

In principle, I hope the problem can be solved without any modification to the data itself. Thank you for your patience and help!

Источник

Re: Error: extra data after last expected column

Subject: Re: Error: extra data after last expected column Date: 2016-05-18 22:04:45 Message-ID: CAKFQuwa7bb=niTuq1W9LuE3s_PXEH2pVF+isZsgBMibO3VtyoQ@mail.gmail.com Views: Raw Message | Whole Thread | Download mbox | Resend email Thread: Lists: pgsql-admin

On Wed, May 18, 2016 at 5:41 PM, Mitra . wrote:

> Hi,
>
> I want to import excel file to postgresql 9.5 but when run the query , got
> error :extra data after last expected column
>
> Mac OS
> 1) downloaded excel file , open with LibreOffice, saved as CSV, UTF-8
> (attachment).
> 2) renamed and moved the file under /Users/ /Library/Application
> Support/Postgres/var-9.5
>

​I’m somewhat surprised the server was able to resolve «test.csv» to above
path. ​

3) created an empty table called mytable
>

​Pertinent also to your subsequent question — I’m not sure where you expect
to place import the data to if the table you’ve created has no columns. ​

> 4) ran the following command:
>
>
> query ran under PSequel:
>

​Its nice to use actual names, and not phonetic spellings, when writing.

> COPY mytable
>
> FROM ‘test.csv’
>
>
> Do I have to create columns inside mytable ? I also tried «Freeze header
> and footer» before converting to CSV , same error.
>
> What am I missing here?
>
>
>
​Try:

COPY mytable FROm ‘test.csv’ WITH (FORMAT csv);

​Then read the following while paying special attention to what the
defaults are:

PostgreSQL doesn’t try to figure things out on its own — you have to tell
it what format your data is in.

Источник

Re: ERROR: extra data after last expected column

Bummer. Best to bottom post or in-line comment on this forum.

I’m trying to use the postgres copy command and getting, «extra data
after last expected column».

All items in the DB are currently set to varchar(255) to make it
simple. I’ve checked for hidden characters in the file and don’t see

Simpler yet is to make the columns «text»

any. All the other files I’ve processed with this exact command worked
perfectly. I’ve processed 10 other’s so far. The only difference I
notice is this one has significantly more columns.

The number of columns in the DB (25) exactly match the number of
columns in the csv (25), which exactly match the number of columns
defined in my COPY command (25). I’ve read practically every post on
the internet over the last two days containg this error and cannot
resolve it. I am completely stumped at this point.

It pukes after the 9th column every time no matter what I change.

COPY option_details(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y)
FROM ‘/home/dump/my_csv.csv’ WITH (FORMAT CSV, DELIMITER ‘|’, ENCODING
‘UTF8’);

You’ve verified the encoding is UTF8?

Row one data in file is below:
item a | item b | item c | item d | item e | item f | item g | item h |
item i | item j | item k | item l | item m | item n | item o | item p |
item q | item r | item s | item t | item u | item v | item w | item x |
item y
— Line two would normally start here but no reason to show since it’s
failing above. —

I get the following error:
ERROR: extra data after last expected column
CONTEXT: COPY option_details, line 1: «item a|item b|item c|item
d|item e|item f|item g|item h|item i|. «

Источник

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

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:

Using the SQL command: I’ve previously established a blank table in the database named «raw data.»

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

(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:

Fastest Entity Framework Extensions

Accepted Answer

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

There’s no need to proclaim tab as DELIMITER because that is the standard:

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

Automation

A simple raw data assumption

Establish a different DELIMITER (one that is entirely absent from the import data) and import to a temporary staging table with a single text column:

Using this query, the CREATE TABLE script:

A safer and more general question:

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

Then INSERT data relating to this inquiry:

Simpler still using translate()

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

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.

Источник

I’m working on items for migrating my database class from Oracle to PostgreSQL. I ran into an interesting limitation when I tried using the COPY command to read an external CSV file.

I had prepared the system by creating a new directory hierarchy owned by the postgres user on top of a /u01/app mount point. I set the ownership of the directories and files with the following command from the /u01/app mount point:

chown -R postgres:postgres postgres

After running the following command:

COPY transaction_upload
FROM '/u01/app/upload/postgres/transaction_upload_postgres.csv' DELIMITERS ',' CSV;

The command raised the following error:

COPY transaction_upload FROM '/u01/app/upload/postgres/transaction_upload_postgres.csv' DELIMITERS ',' CSV;
ERROR:  must be superuser or a member of the <code>pg_read_server_files</code> role to COPY from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's copy command also works for anyone.

The two options for fixing the problem are: Changing the student user to a superuser, and granting the pg_read_server_files role to the student user. Changing the student user to a superuser isn’t really a practical option. So, I connected as the postgres superuser and granted the pg_read_server_files role to the student user. It is a system level role and therefore doesn’t limit the role to only the videodb database.

As the postgres user, type the following command to grant the pg_read_server_files role to the system user:

GRANT pg_read_server_files TO student;

After granting the role to the student user, I created a small test case. The test table definition is:

CREATE TABLE test
( id          INTEGER
, first_name  VARCHAR(20)
, last_name   VARCHAR(20));

I created a test.csv file in the /u01/app/upload/postgres directory, like:

1,Simon,Bolivar
2,Peter,Davenport
3,Michael,Swan

The test.csv file requires the following permissions and ownerships:

-rw-r--r--. 1 postgres postgres 49 Nov 13 10:56 test.csv

The permissions are user read-write, groups read, and others read. The ownership should be granted to postgres and the primary group for the postgres user, which should also be postgres.

You can then connect to psql as the student user with the database set to videodb and run the following copy command:

COPY test
FROM '/u01/app/upload/postgres/test.csv' DELIMITERS ',' CSV;

If you put a comma at the end of each line, like you would do in MySQL, it raises an error. The trailing comma raises the following error:

ERROR:  extra data after last expected column

If you forget a delimiting commas somewhere on a line, the copy command raises the following error:

ERROR:  missing data for column "last_name"
CONTEXT:  COPY tester, line 3: "3,Michael Swan"

The error points to the column after the missing column. The context points to the line number while displaying the text.

You should take careful note that the copy command is an appending command. If you run it a second time, you insert a duplicate set of values in the target table.

After experimenting, its time to fix my student instance. The transaction_upload_mysql.csv file has two critical errors that need to be fixed. They are:

1. A comma terminates each line, which would raise an extra data after last expected column error.
2. A comma terminates each line followed by some indefinite amount of whitespace, which would also raise an extra data after last expected column error.

Since I have students with little expertise in Unix or Linux commands, I must provide a single command that they can use to convert the file with problems to one without problems. However, they should copy the transaction_upload_mysql.csv file to ensure they don’t disable the equivalent functionality for the MySQL solution space.

They should copy two files as the root user from the mysql directory to the postgres directory, as follows:

cp /u01/app/mysql/upload/transaction_upload_mysql.csv /u01/app/postgres/upload/transaction_upload_postgres.csv
cp /u01/app/mysql/upload/transaction_upload2_mysql.csv /u01/app/postgres/upload/transaction_upload2_postgres.csv

As the root user in the /u01/app/upload/postgres directory, run the following command:

cat transaction_upload_postgres.csv | sed -e 's/,$//g' > x; cat x | sed -e 's/,[[:space:]]*$//g' > y; mv y transaction_upload_postgres.csv; rm x

Please check the file permissions and ownerships with the ll (long list) command. If the file isn’t like this:

-rw-r--r--. 1 postgres postgres 49 Nov 13 10:56 transaction_upload_postgres.csv

Then, they should be able to change it as the root user with these commands:

chown postgres:postgres transaction_upload_postgres.csv
chmod 544 transaction_upload_postgres.csv

Lastly, they should connect to the psql as the student user, using the videodb database and run the following command:

COPY transaction_upload
FROM '/u01/app/postgres/upload/transaction_upload_postgres.csv' DELIMITERS ',' CSV;

A query of the import table with this:

SELECT COUNT(*) FROM transaction_upload;

should return:

 count 
-------
 11520
(1 row)

As always, I hope this helps those looking for some explanation and example on the copy feature of PostgreSQL.

History

Error logging in COPY was a proposed feature developed by Aster Data against the PostgreSQL 9.0 code base. It was submitted and reviewed (1) but not accepted into the core product for that or any other version so far.

Overview

The purpose of error logging in COPY is to prevent the backend from erroring out if a malformed tuple is encountered during a COPY operation. Bad tuples can either be skipped or logged into an error logging table.

The format of the error logging table is as follows:

 CREATE TABLE error_logging_table(
   tupletimestamp TIMESTAMP WITH TIME ZONE,
   targettable    VARCHAR,
   dmltype        CHAR(1),
   errmessage     VARCHAR,
   sqlerrcode     CHAR(5),
   label          VARCHAR,
   key            BIGINT,
   rawdata        BYTEA
 );

The COPY command returns the number of successfully copied tuples only.

COPY options

Error logging is set by adding options to the COPY command. Here is the list of the available options:

Bad tuples can be rejected for a number of reasons (extra or missing column, constraint violation, …). The error table tries to capture as much context as possible about the error. If the table does not exist it is created automatically. The format of the error logging table is as follows:

 CREATE TABLE error_logging_table(
   tupletimestamp TIMESTAMP WITH TIME ZONE,
   targettable    VARCHAR,
   dmltype        CHAR(1),
   errmessage     VARCHAR,
   sqlerrcode     CHAR(5),
   label          VARCHAR,
   key            BIGINT,
   rawdata        BYTEA
 );

tupletimestamp stores the time at which the error occured. targettable describes the table in which the row was inserted when the error occured. The exact error message and sql error code are recorded in errmessage and sqlerrcode, respectively. The original data of the row can be found in rawdata.

Example

 CREATE TEMP TABLE foo (a bigint, b text);

— input_file.txt —

 1	one
 2	
 3	three	111
 four    4
 5	five

— end of input_file.txt —

error logging off

 COPY foo FROM 'input_file.txt';

 ERROR:  missing data for column "b"
 CONTEXT:  COPY foo, line 2: "2"

skip bad rows

 --skip bad rows
 COPY foo FROM 'input_file.txt' (ERROR_LOGGING, ERROR_LOGGING_SKIP_BAD_ROWS);
 SELECT * from foo;

  a |  b   
 ---+------
  1 | one
  5 | five
 (2 rows)

turn error logging on (default logs in error_logging_table)

 --turn error logging on (default logs in error_logging_table)
 COPY foo FROM 'input_file.txt' (ERROR_LOGGING);
 SELECT * from foo;

  a |  b   
 ---+------
  1 | one
  5 | five
 (2 rows)

 SELECT * FROM error_logging_table;

  key |           tupletimestamp            |              label              |  targettable  | dmltype |                errmessage                | sqlerrcode |         rawdata          
 -----+-------------------------------------+---------------------------------+---------------+---------+------------------------------------------+------------+--------------------------
    2 | Thu Sep 10 07:09:17.869521 2009 PDT | COPY foo FROM 'input_file.txt'; | pg_temp_2.foo | C       | missing data for column "b"              | 22P04      | x32
    3 | Thu Sep 10 07:09:17.86953 2009 PDT  | COPY foo FROM 'input_file.txt'; | pg_temp_2.foo | C       | extra data after last expected column    | 22P04      | x3309746872656509313131
    4 | Thu Sep 10 07:09:17.869538 2009 PDT | COPY foo FROM 'input_file.txt'; | pg_temp_2.foo | C       | invalid input syntax for integer: "four" | 22P02      | x666f75720934
 (3 rows)

Redirect to another table with a specific label

 -- Redirect to another table with a specific label
 COPY foo FROM 'input_file.txt' (ERROR_LOGGING, ERROR_LOGGING_SCHEMA_NAME 'error', ERROR_LOGGING_TABLE_NAME 'table1', ERROR_LOGGING_LABEL 'batch1');
 SELECT * FROM error.table1;

  key |           tupletimestamp            | label  |  targettable  | dmltype |                errmessage                | sqlerrcode |         rawdata          
 -----+-------------------------------------+--------+---------------+---------+------------------------------------------+------------+--------------------------
    2 | Thu Sep 10 07:09:17.869521 2009 PDT | batch1 | pg_temp_2.foo | C       | missing data for column "b"              | 22P04      | x32
    3 | Thu Sep 10 07:09:17.86953 2009 PDT  | batch1 | pg_temp_2.foo | C       | extra data after last expected column    | 22P04      | x3309746872656509313131
    4 | Thu Sep 10 07:09:17.869538 2009 PDT | batch1 | pg_temp_2.foo | C       | invalid input syntax for integer: "four" | 22P02      | x666f75720934
 (3 rows)

Limit to 2 bad rows:

 -- Limit to 2 bad rows:  
 COPY foo FROM 'input_file.txt' (ERROR_LOGGING, ERROR_LOGGING_MAX_ERRORS 2);

 ERROR:  invalid input syntax for integer: "four"
 CONTEXT:  COPY foo, line 4, column a: "four"

 SELECT count(*) from error_logging_table;

  count 
  -------
       0
  (1 row)

MacLochlainns Weblog

Michael McLaughlin’s Technical Blog

Postgres Reading Files

I’m working on items for migrating my database class from Oracle to PostgreSQL. I ran into an interesting limitation when I tried using the COPY command to read an external CSV file.

I had prepared the system by creating a new directory hierarchy owned by the postgres user on top of a /u01/app mount point. I set the ownership of the directories and files with the following command from the /u01/app mount point:

chown -R postgres:postgres postgres

After running the following command:

COPY transaction_upload FROM ‘/u01/app/upload/postgres/transaction_upload_postgres.csv’ DELIMITERS ‘,’ CSV;

The command raised the following error:

COPY transaction_upload FROM ‘/u01/app/upload/postgres/transaction_upload_postgres.csv’ DELIMITERS ‘,’ CSV; ERROR: must be superuser or a member of the pg_read_server_files role to COPY from a file HINT: Anyone can COPY to stdout or from stdin. psql’s copy command also works for anyone.

The two options for fixing the problem are: Changing the student user to a superuser, and granting the pg_read_server_files role to the student user. Changing the student user to a superuser isn’t really a practical option. So, I connected as the postgres superuser and granted the pg_read_server_files role to the student user. It is a system level role and therefore doesn’t limit the role to only the videodb database.

As the postgres user, type the following command to grant the pg_read_server_files role to the system user:

GRANT pg_read_server_files TO student;

After granting the role to the student user, I created a small test case. The test table definition is:

CREATE TABLE test ( id integer , first_name varchar(20) , last_name varchar(20));

I created a test.csv file in the /u01/app/upload/postgres directory, like:

1,Simon,Bolivar 2,Peter,Davenport 3,Michael,Swan

The test.csv file requires the following permissions and ownerships:

-rw-r—r—. 1 postgres postgres 49 Nov 13 10:56 test.csv

The permissions are user read-write, groups read, and others read. The ownership should be granted to postgres and the primary group for the postgres user, which should also be postgres .

You can then connect to psql as the student user with the database set to videodb and run the following copy command:

COPY test FROM ‘/u01/app/upload/postgres/test.csv’ DELIMITERS ‘,’ CSV;

If you put a comma at the end of each line, like you would do in MySQL, it raises an error. The trailing comma raises the following error:

ERROR: extra data after last expected column

If you forget a delimiting commas somewhere on a line, the copy command raises the following error:

ERROR: missing data for column «last_name» CONTEXT: COPY tester, line 3: «3,Michael Swan»

The error points to the column after the missing column. The context points to the line number while displaying the text.

You should take careful note that the copy command is an appending command. If you run it a second time, you insert a duplicate set of values in the target table.

After experimenting, its time to fix my student instance. The transaction_upload_mysql.csv file has two critical errors that need to be fixed. They are:

1. A comma terminates each line, which would raise an extra data after last expected column error.
2. A comma terminates each line followed by some indefinite amount of whitespace, which would also raise an extra data after last expected column error.

Since I have students with little expertise in Unix or Linux commands, I must provide a single command that they can use to convert the file with problems to one without problems. However, they should copy the transaction_upload_mysql.csv file to ensure they don’t disable the equivalent functionality for the MySQL solution space.

They should copy two files as the root user from the mysql directory to the postgres directory, as follows:

cp /u01/app/mysql/upload/transaction_upload_mysql.csv /u01/app/postgres/upload/transaction_upload_postgres.csv cp /u01/app/mysql/upload/transaction_upload2_mysql.csv /u01/app/postgres/upload/transaction_upload2_postgres.csv

As the root user in the /u01/app/upload/postgres directory, run the following command:

cat transaction_upload_postgres.csv | sed -e ‘s/,$//g’ > x; cat x | sed -e ‘s/,[[:space:]]*$//g’ > y; mv y transaction_upload_postgres.csv; rm x

Please check the file permissions and ownerships with the ll (long list) command. If the file isn’t like this:

-rw-r—r—. 1 postgres postgres 49 Nov 13 10:56 transaction_upload_postgres.csv

Then, they should be able to change it as the root user with these commands:

chown postgres:postgres transaction_upload_postgres.csv chmod 544 transaction_upload_postgres.csv

Lastly, they should connect to the psql as the student user, using the videodb database and run the following command:

COPY transaction_upload FROM ‘/u01/app/postgres/upload/transaction_upload_postgres.csv’ DELIMITERS ‘,’ CSV;

A query of the import table with this:

SELECT COUNT(*) FROM transaction_upload;

count ——- 11520 (1 row)

As always, I hope this helps those looking for some explanation and example on the copy feature of PostgreSQL.

Источник

«ОШИБКА: дополнительные данные после последнего ожидаемого столбца» при использовании PostgreSQL COPY

Пожалуйста, подождите, это мой первый пост.

Я пытаюсь запустить команду COPY в PostgreSQL-9.2 для добавления таблицы с разделителями табуляции из файла .txt в базу данных PostgreSQL, например:

Я уже создал в базе данных пустую таблицу с именем «raw_data» с помощью команды SQL:

При попытке запустить команду COPY я получаю следующее сообщение об ошибке:

(Числа здесь должны быть заголовками столбцов)

Я не уверен, что это потому, что я не указывал столбцы таблицы при создании таблицы db, но я пытаюсь избежать необходимости вручную вводить 800 или столбцы.

Есть предложения, как это исправить?

Вот пример того, как выглядит файл .txt:

2 ответа

Пустой стол не годится. Вам нужна таблица, соответствующая структуре входных данных. Что-то вроде:

Вам не нужно объявлять tab как DELIMITER , поскольку это значение по умолчанию:

800 столбцов говорите? Такое количество столбцов обычно указывает на проблему с вашим дизайном. В любом случае, есть способы полуавтоматизировать скрипт CREATE TABLE .

Автоматизация

Предполагая упрощенные необработанные данные

Определите другой DELIMITER (тот, который вообще не встречается в данных импорта) и импортируйте во временную промежуточную таблицу с одним столбцом text :

Этот запрос создает скрипт CREATE TABLE :

Более общий и безопасный запрос:

Выполнить после проверки действительности — или выполнить динамически, если вы доверяете результату:

Затем INSERT данные с этим запросом:

Строка преобразуется в литерал строки, приводится к новому типу строки таблицы и разлагается с помощью (row).* .

Вы можете поместить все это в функцию plpgsql, но вам нужно будет защититься от SQL-инъекций. (Здесь, на SO, есть несколько связанных решений. Попробуйте поискать.

Источник

Re: Error: extra data after last expected column

Subject: Re: Error: extra data after last expected column Date: 2016-05-18 22:04:45 Message-ID: CAKFQuwa7bb=niTuq1W9LuE3s_PXEH2pVF+isZsgBMibO3VtyoQ@mail.gmail.com Views: Raw Message | Whole Thread | Download mbox | Resend email Thread: Lists: pgsql-admin

On Wed, May 18, 2016 at 5:41 PM, Mitra . wrote:

> Hi,
>
> I want to import excel file to postgresql 9.5 but when run the query , got
> error :extra data after last expected column
>
> Mac OS
> 1) downloaded excel file , open with LibreOffice, saved as CSV, UTF-8
> (attachment).
> 2) renamed and moved the file under /Users/ /Library/Application
> Support/Postgres/var-9.5
>

​I’m somewhat surprised the server was able to resolve «test.csv» to above
path. ​

3) created an empty table called mytable
>

​Pertinent also to your subsequent question — I’m not sure where you expect
to place import the data to if the table you’ve created has no columns. ​

> 4) ran the following command:
>
>
> query ran under PSequel:
>

​Its nice to use actual names, and not phonetic spellings, when writing.

> COPY mytable
>
> FROM ‘test.csv’
>
>
> Do I have to create columns inside mytable ? I also tried «Freeze header
> and footer» before converting to CSV , same error.
>
> What am I missing here?
>
>
>
​Try:

COPY mytable FROm ‘test.csv’ WITH (FORMAT csv);

​Then read the following while paying special attention to what the
defaults are:

PostgreSQL doesn’t try to figure things out on its own — you have to tell
it what format your data is in.

Источник

extra data after last expected column #50

HI,
When trying to import a zabbix database from mysql to postgres using FromMySqlToPostgreSql, I’m finding the following error:

CONTEXT: COPY items, line 13500: «40320,8. 10292,»Jboss activeSessions»,»grpsum[«»<$SERVER_GROUP>«»,»»jmx[«jboss.web:type=Manager,pa. »
— SQL: SELECT itemid , type , snmp_community , snmp_oid , hostid , name , key_ , delay , history , trends , status , value_type , trapper_hosts , units , multiplier , delta , snmpv3_securityname , snmpv3_securitylevel , snmpv3_authpassphrase , snmpv3_privpassphrase , formula , error , lastlogsize , logtimefmt , templateid , valuemapid , delay_flex , params , ipmi_sensor , data_type , authtype , username , password , publickey , privatekey , mtime , flags , interfaceid , port , description , inventory_link , lifetime , snmpv3_authprotocol , snmpv3_privprotocol , state , snmpv3_contextname , evaltype FROM items LIMIT 0, 36445;
COPY «public».»items» FROM ‘/var/lib/pgsql/FromMySqlToPostgreSql-master/temporary_directory/items0.csv’ DELIMITER ‘,’ CSV;

Table definition is as follows:

CREATE TABLE items (
itemid bigint(20) unsigned NOT NULL,
type int(11) NOT NULL DEFAULT ‘0’,
snmp_community varchar(64) NOT NULL DEFAULT »,
snmp_oid varchar(255) NOT NULL DEFAULT »,
hostid bigint(20) unsigned NOT NULL,
name varchar(255) NOT NULL DEFAULT »,
key_ varchar(255) NOT NULL DEFAULT »,
delay int(11) NOT NULL DEFAULT ‘0’,
history int(11) NOT NULL DEFAULT ’90’,
trends int(11) NOT NULL DEFAULT ‘365’,
status int(11) NOT NULL DEFAULT ‘0’,
value_type int(11) NOT NULL DEFAULT ‘0’,
trapper_hosts varchar(255) NOT NULL DEFAULT »,
units varchar(255) NOT NULL DEFAULT »,
multiplier int(11) NOT NULL DEFAULT ‘0’,
delta int(11) NOT NULL DEFAULT ‘0’,
snmpv3_securityname varchar(64) NOT NULL DEFAULT »,
snmpv3_securitylevel int(11) NOT NULL DEFAULT ‘0’,
snmpv3_authpassphrase varchar(64) NOT NULL DEFAULT »,
snmpv3_privpassphrase varchar(64) NOT NULL DEFAULT »,
formula varchar(255) NOT NULL DEFAULT »,
error varchar(2048) NOT NULL DEFAULT »,
lastlogsize bigint(20) unsigned NOT NULL DEFAULT ‘0’,
logtimefmt varchar(64) NOT NULL DEFAULT »,
templateid bigint(20) unsigned DEFAULT NULL,
valuemapid bigint(20) unsigned DEFAULT NULL,
delay_flex varchar(255) NOT NULL DEFAULT »,
params text NOT NULL,
ipmi_sensor varchar(128) NOT NULL DEFAULT »,
data_type int(11) NOT NULL DEFAULT ‘0’,
authtype int(11) NOT NULL DEFAULT ‘0’,
username varchar(64) NOT NULL DEFAULT »,
password varchar(64) NOT NULL DEFAULT »,
publickey varchar(64) NOT NULL DEFAULT »,
privatekey varchar(64) NOT NULL DEFAULT »,
mtime int(11) NOT NULL DEFAULT ‘0’,
flags int(11) NOT NULL DEFAULT ‘0’,
interfaceid bigint(20) unsigned DEFAULT NULL,
port varchar(64) NOT NULL DEFAULT »,
description text NOT NULL,
inventory_link int(11) NOT NULL DEFAULT ‘0’,
lifetime varchar(64) NOT NULL DEFAULT ’30’,
snmpv3_authprotocol int(11) NOT NULL DEFAULT ‘0’,
snmpv3_privprotocol int(11) NOT NULL DEFAULT ‘0’,
state int(11) NOT NULL DEFAULT ‘0’,
snmpv3_contextname varchar(255) NOT NULL DEFAULT »,
evaltype int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY ( itemid ),
UNIQUE KEY items_1 ( hostid , key_ ),
KEY items_3 ( status ),
KEY items_4 ( templateid ),
KEY items_5 ( valuemapid ),
KEY items_6 ( interfaceid ),
CONSTRAINT c_items_1 FOREIGN KEY ( hostid ) REFERENCES hosts ( hostid ) ON DELETE CASCADE,
CONSTRAINT c_items_2 FOREIGN KEY ( templateid ) REFERENCES items ( itemid ) ON DELETE CASCADE,
CONSTRAINT c_items_3 FOREIGN KEY ( valuemapid ) REFERENCES valuemaps ( valuemapid ),
CONSTRAINT c_items_4 FOREIGN KEY ( interfaceid ) REFERENCES interface ( interfaceid )
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And the table line where it seems there is a problem follows:

select * FROM items where itemid=40320

*************************** 1. row ***************************
itemid: 40320
type: 8
snmp_community:
snmp_oid:
hostid: 10292
name: Jboss activeSessions
key_: grpsum[«<$SERVER_GROUP>«,»jmx[«jboss.web:type=Manager,path=/<$GRAU>,host=localhost»,activeSessions]»,last,0]
delay: 30
history: 90
trends: 365
status: 0
value_type: 3
trapper_hosts:
units:
multiplier: 0
delta: 0
snmpv3_securityname:
snmpv3_securitylevel: 0
snmpv3_authpassphrase:
snmpv3_privpassphrase:
formula: 1
error:
lastlogsize: 0
logtimefmt:
templateid: NULL
valuemapid: NULL
delay_flex:
params:
ipmi_sensor:
data_type: 0
authtype: 0
username:
password:
publickey:
privatekey:
mtime: 0
flags: 0
interfaceid: NULL
port:
description:
inventory_link: 0
lifetime: 30
snmpv3_authprotocol: 0
snmpv3_privprotocol: 0
state: 0
snmpv3_contextname:
evaltype: 0

Any help?
Thanks in advance.

The text was updated successfully, but these errors were encountered:

Источник

Пожалуйста, подождите, это мой первый пост.

Я пытаюсь запустить команду COPY в PostgreSQL-9.2 для добавления таблицы с разделителями табуляции из файла .txt в базу данных PostgreSQL, например:

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

Я уже создал в базе данных пустую таблицу с именем «raw_data» с помощью команды SQL:

CREATE TABLE raw_data ();

При попытке запустить команду 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  ..."

(Числа здесь должны быть заголовками столбцов)

Я не уверен, что это потому, что я не указывал столбцы таблицы при создании таблицы db, но я пытаюсь избежать необходимости вручную вводить 800 или столбцы.

Есть предложения, как это исправить?

Вот пример того, как выглядит файл .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

Пустой стол не годится. Вам нужна таблица, соответствующая структуре входных данных. Что-то вроде:

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

Вам не нужно объявлять tab как DELIMITER, поскольку это значение по умолчанию:

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

800 столбцов говорите? Такое количество столбцов обычно указывает на проблему с вашим дизайном. В любом случае, есть способы полуавтоматизировать скрипт CREATE TABLE.

Автоматизация

Предполагая упрощенные необработанные данные

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

Определите другой DELIMITER (тот, который вообще не встречается в данных импорта) и импортируйте во временную промежуточную таблицу с одним столбцом text:

CREATE TEMP TABLE tmp_data (raw text);

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

Этот запрос создает скрипт CREATE TABLE:

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

Более общий и безопасный запрос:

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);

Возврат:

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

Выполнить после проверки действительности — или выполнить динамически, если вы доверяете результату:

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

Затем INSERT данные с этим запросом:

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

Или проще с translate() :

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

Строка преобразуется в литерал строки, приводится к новому типу строки таблицы и разлагается с помощью (row).*.

Все сделано.

Вы можете поместить все это в функцию plpgsql, но вам нужно будет защититься от SQL-инъекций. (Здесь, на SO, есть несколько связанных решений. Попробуйте поискать.

db <> fiddle здесь
Старый скрипт SQL