0

I'm using PostgresSQL 10 and RPostgreSQL package in R.

I create a new table: 

CREATE TABLE people (
    id integer NOT NULL,
    name character varying,
    birthdate date,
    deathdate date
);

Then attempt to copy the file :

COPY people (id, name, birthdate, deathdate) 
  FROM '\people.sql'
  ENCODING 'UTF-8';

Then get the following error:

Error in postgresqlExecStatement(conn, statement, ...) :    RS-DBI
driver: (could not Retrieve the result : ERROR:  invalid input syntax
for integer: "1" CONTEXT:  COPY people, line 1, column id: "1"

I did wonder if this was an encoding error, so ran and got:

readr::guess_encoding("\people.sql")
# A tibble: 4 x 2
      encoding confidence
         <chr>      <dbl>
1        UTF-8       1.00
2 windows-1252       0.37
3 windows-1254       0.37
4 windows-1250       0.28

I've tried the other, less probable windows encodings, but this do not work either and throws similar issues with line 1, for instance windows-1252 shows ERROR: invalid input syntax for integer: "1"

The first few lines of the file are:

1   50 Cent 1975-07-06  \N
2   A. Michael Baldwin  1963-04-04  \N

and the last two lines :

8396    Zubaida Sahar   \N  \N
8397    Zuhair Haddad   \N  \N 
\.

Any idea what could be causing this?

Thank you

user27612
  • 55
  • 1
  • 7
  • 3
    Maybe there is a BOM (Byte Order Mark) in the file? You can check this by hexdumping the file and looking at the first three characters. – wildplasser Jan 14 '18 at 19:03
  • Thanks - I've used Notepad++ to Convert to UTF-8, to remove any BOM. This now reads the file, however it throws an error of "RS-DBI driver: (could not Retrieve the result : ERROR: end-of-copy marker corrupt CONTEXT: COPY people, line 8398". Line 8398 is the end of file which is \. – user27612 Jan 14 '18 at 19:15
  • A new error or that same one? – mu is too short Jan 14 '18 at 19:16
  • "RS-DBI driver: (could not Retrieve the result : ERROR: end-of-copy marker corrupt CONTEXT: COPY people, line 8398". Line 8398 is the end of file which is \. – user27612 Jan 14 '18 at 19:19
  • 1
    Maybe remove the `\.` ? – wildplasser Jan 14 '18 at 19:22

1 Answers1

0

Thanks to wildplasser in the comments for this one:

1 - Remove the Byte Order Mark - I used Notepad++ and convert to UTF-8 (see this post)

2 - Completely removed the erroneous end of file marker which was 

\.
user27612
  • 55
  • 1
  • 7