Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using copy with a file containing blank rows

Using copy with a file containing blank rows

From
"George Weaver"
Date:
Hi All,

I am trying to use COPY to import postgresql logs into a postgresql database
for further review and sorting.

The problem I'm encountering happens when COPY hits a blank row:

development=# COPY log
development-#  FROM
'D:/PostgreSQL/9.1/pg_log/postgresql-2012-03-15_093730.log'
development-#  WITH DELIMITER '^' NULL AS '';

ERROR:  missing data for column "ip_address"
CONTEXT:  COPY log, line 2: "   "

Line 2 is a blank line.

Is there any way to use COPY with blank lines or do the blank lines have to
removed before using COPY?

Thanks,
George

Re: Using copy with a file containing blank rows

From
Adrian Klaver
Date:
On 03/15/2012 09:17 AM, George Weaver wrote:
> Hi All,
>
> I am trying to use COPY to import postgresql logs into a postgresql
> database for further review and sorting.

Are you using the CSV format to log to the Postgres log?:
http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

>
> The problem I'm encountering happens when COPY hits a blank row:
>
> development=# COPY log
> development-# FROM
> 'D:/PostgreSQL/9.1/pg_log/postgresql-2012-03-15_093730.log'
> development-# WITH DELIMITER '^' NULL AS '';
>
> ERROR: missing data for column "ip_address"
> CONTEXT: COPY log, line 2: " "
>
> Line 2 is a blank line.
>
> Is there any way to use COPY with blank lines or do the blank lines have
> to removed before using COPY?

AFAIK, you have to remove blank lines.

>
> Thanks,
> George
>


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Using copy with a file containing blank rows

From
"George Weaver"
Date:
>----- Original Message -----
>From: Adrian Klaver

>On 03/15/2012 09:17 AM, George Weaver wrote:
>> Hi All,
>>
>> I am trying to use COPY to import postgresql logs into a postgresql
>> database for further review and sorting.

>Are you using the CSV format to log to the Postgres log?:
>http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

[life intervenes...]

Thanks Adrian - I wasn't using the CSV format - I am now!  It solves the
blank line problem I was encountering.

George

>>
>> The problem I'm encountering happens when COPY hits a blank row:
>>
>> development=# COPY log
>> development-# FROM
>> 'D:/PostgreSQL/9.1/pg_log/postgresql-2012-03-15_093730.log'
>> development-# WITH DELIMITER '^' NULL AS '';
>>
>> ERROR: missing data for column "ip_address"
>> CONTEXT: COPY log, line 2: " "
>>
>> Line 2 is a blank line.
>>
>> Is there any way to use COPY with blank lines or do the blank lines have
>> to removed before using COPY?

>AFAIK, you have to remove blank lines.

>>
>> Thanks,
>> George
>>


--
Adrian Klaver
adrian.klaver@gmail.com