CODEWITHSUNDEEP
databasepostgresqlinsertionpostgresql-9.1
wingedrhino
wingedrhino

Reputation: 162

PostgreSQL: Creating a .sql file to insert data into a table the fastest way

I'm working on a project where I have to parse a bunch of .csv files, all of different formats and containing different kinds of data through some C++ functions. After that I extract data from the files and create a .sql file that can be imported in psql to insert the data into a PostgreSQL database at a later stage.

But I am not able to figure out the correct syntax for the .sql file. Here is a sample table and a sample .sql file reproducing the same errors I am getting:

Table Creation Code:

CREATE TABLE "Sample_Table"
(
  "Col_ID" integer NOT NULL,
  "Col_Message" character varying(50),
  CONSTRAINT "Sample_Table_pkey" PRIMARY KEY ("Col_ID" )
)

insertion.sql (after the copy line, fields separated by a single tab character)

copy Sample_Table (Col_ID, Col_Message) from stdin;
1   This is Spaaarta
2   Why So Serious
3   Baazinga
\.

Now if I execute the above sql file, I get the following error:

ERROR:  syntax error at or near "1"
LINE 2: 1 This is Spaaarta
        ^


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

If it can help, I'm running a PostgreSQL 9.1 release, and all the above queries were executed through PGAdmin III Software.

Upvotes: 0

Views: 4401

Answers (2)

A.H.
A.H.

Reputation: 66243

Three things to check:

  • Is there actually exactly one tab characters between the columns? Spaces are a no-go.

  • Are there more error messages? I'm missing at least one. (See below)

  • When you force case sensitive table and column names you have to do this consequently. Therefore you must write this:

copy "Sample_Table" ("Col_ID", "Col_Message") from stdin;

Otherwise you will get theese errors:

psql:x.sql:1: ERROR:  relation "sample_table" does not exist
psql:x.sql:5: invalid command \.
psql:x.sql:5: ERROR:  syntax error at or near "1"
LINE 1: 1 This is Spaaarta
        ^

With these things in place I can use your example data successfully.

EDIT Bug change: The questioner now has

ERROR: invalid input syntax for integer: "1 'This is Spaaarta'"

So something with the 1 is not OK.

My guess is, that this is an encoding problem. Windows with it's UTF-16 stuff might be the culprit here.

Debugging these kind of problems other the web is not easy, because to many semi-intelligent programs are in the line, most of them like to adjust "a few" things.

But first check a few things in psql:

\encoding
show client_encoding;
show server_encoding;

According to the pastebin data these should be the same and one of "SQL_ASCII", "LATIN1" or "UTF-8".

If they already are or if adjusting them does not help: Unix/Linux/cygwin has a hexdump -C x.sql program, post its output to pastebin. DO NOT USE the hexdump from any Windows editor like ultraedit - they have fooled me several times. When transferring the file to Linux be sure to use binary transfer.

Upvotes: 1

araqnid
araqnid

Reputation: 133462

PgAdmin doesn't support executing COPY commands in the same way that psql does (or at least, it didn't the last time I tried it with version 1.14). Use psql to execute the script, or use INSERT statements.

Upvotes: 1

Related Questions