Thread: Cryptic Error Message Importing Table Dump

   Now that I fixed the rows that had the inadvertent newlines in one column,
I'm trying to read in the fixed table from the .sql file produced by
pg_dump. I know there are duplicate rows now that I removed the newlines,
and those are easily fixed (although the reported line numbers don't match
what I see in emacs). There is, however, one problem that I don't understand
so I can't find the row and fix it. Here's what psql reports:

     \i /<full-path-to-file>/chemistry.sql
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
psql:/<full-path-to-file>/chemistry.sql:47475:
ERROR:  invalid input syntax for type real: "     "
CONTEXT:  COPY chemistry, line 47363, column quant: "     "

   Line 47475 is below the last line with content in the file. Line 47363
contains:

96-A000890    SC    1996-04-23    Conductance, Specific    394
uS/cm    t    \N    \N    \N

(which is wrapped here, but not in the emacs buffer). There are 10 columns,
which is how many there should be. When I go to the end of the line there's
no space or other extraneous character. The column 'quant' contains the
number 394. While that's an integer, the column domain is real and psql
doesn't complain about other whole numbers in that column.

   Please help me understand what the error message and context are telling
me because I just don't see it.

Rich

On 15 Sep 2011, at 3:42, Rich Shepard wrote:

>  Line 47475 is below the last line with content in the file. Line 47363
> contains:
>
> 96-A000890    SC    1996-04-23    Conductance, Specific    394
> uS/cm    t    \N    \N    \N
>
> (which is wrapped here, but not in the emacs buffer). There are 10 columns,
> which is how many there should be. When I go to the end of the line there's
> no space or other extraneous character. The column 'quant' contains the
> number 394. While that's an integer, the column domain is real and psql
> doesn't complain about other whole numbers in that column.
>
>  Please help me understand what the error message and context are telling
> me because I just don't see it.


The text in the error is a tab character, so maybe you have an extra tab somewhere?

If not, perhaps the error is about the line after the one you showed us. If there isn't a terminator (\.) there, then
readingthe first 4 columns would probably succeed; they are type text from the looks of it,  so '    ' (tab) is a valid
characterfor those fields, but not for the real type column after those. 

Alban Hertroys

--
The scale of a problem often equals the size of an ego.

On Thu, 15 Sep 2011, Alban Hertroys wrote:

> The text in the error is a tab character, so maybe you have an extra tab
> somewhere?

Alban,

   The column separators are tabs. I've checked a few rows above and below
the cited one and find only a single tab between columns.

> If not, perhaps the error is about the line after the one you showed us.
> If there isn't a terminator (\.) there, then reading the first 4 columns
> would probably succeed; they are type text from the looks of it, so ' '
> (tab) is a valid character for those fields, but not for the real type
> column after those.

   The table schema is:

CREATE TABLE chemistry (
     lab_nbr character varying(12),
     site_id character varying(16) NOT NULL,
     sample_date date NOT NULL,
     param character varying(32) NOT NULL,
     quant real,
     units character varying(12),
     qual boolean,
     easting numeric(13,6),
     northing numeric(13,6),
     remark character varying(2)
);

   Each row terminates with a \n; when the cursor is at the row end and I
press ctrl-f (move one character forward), the cursor is at the beginning of
the next line.

   I'm confused about your statement that a tab is not valid for the 'real'
column. There are tabs between that column and adjacent ones, but no tabs
within any text column (only one of which has multiple strings).

   Could the error be far from the reported line 47363 but that's where psql
stopped? I've not been able to think of a way to check the entire file for
an extra tab as the length of each column's content varies.

Still perplexed,

Rich

On Thursday, September 15, 2011 6:24:42 am Rich Shepard wrote:
> On Thu, 15 Sep 2011, Alban Hertroys wrote:

>
>    Each row terminates with a \n; when the cursor is at the row end and I
> press ctrl-f (move one character forward), the cursor is at the beginning
> of the next line.
>
>    I'm confused about your statement that a tab is not valid for the 'real'
> column. There are tabs between that column and adjacent ones, but no tabs
> within any text column (only one of which has multiple strings).
>
>    Could the error be far from the reported line 47363 but that's where
> psql stopped? I've not been able to think of a way to check the entire
> file for an extra tab as the length of each column's content varies.
>
> Still perplexed,

In your editing of the file did you happen to edit out the \.
that is at the end of the  COPY data?

>
> Rich

--
Adrian Klaver
adrian.klaver@gmail.com

On Thu, 15 Sep 2011, Adrian Klaver wrote:

> In your editing of the file did you happen to edit out the \. that is at
> the end of the COPY data?

Adrian,

   Ah, shoot! I did ... based on an earlier message.

   That was the problem.

Many thanks, once again,

Rich