Re: reading cvs logs with pgadmin queries

OK,

I have a little more information.

Yes, in isolation I can import these lines, however something happens after
69000 lines. These lines cause an error.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Sep 20, 2013 at 9:59 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> On 09/19/2013 06:04 PM, Dave Cramer wrote:
>
>> Adrian,
>>
>
>
>> 2) The exact error message is ERROR: extra data after last expected
>> column
>>
>>
>> considering ~39000 lines go in before this line I am fairly certain it is
>> the line.
>>
>
> New day, new start. I am not sure now that the line you showed is the
> problem. I isolated that line in a file and did a COPY into postgres log.
> Note, I changed the time zone because my installation did not recognize WST:
>
> test=# COPY postgres_log FROM '/home/postgres/pg_csv_test.**csv' WITH csv;
> COPY 1
> test=# \x
> Expanded display is on.
> test=# SELECT * from postgres_log ;
> -[ RECORD 1 ]----------+------------------**------------------------------
> **----------------------------
> log_time | 2013-09-12 10:33:19.145-07
> user_name | user
> database_name | dbname
> process_id | 14581
> connection_from | 192.168.1.22:58840
> session_id | 523126d3.38f5
> session_line_num | 1
> command_tag | SELECT
> session_start_time | 2013-09-12 10:28:35-07
> virtual_transaction_id | 6/503023
> transaction_id | 0
> error_severity | ERROR
> sql_state_code | 42P01
> message | relation "dds_stores" does not exist
>
> detail |
> hint |
> internal_query |
> internal_query_pos |
> context |
> query | WITH RECURSIVE recursive_stores(id, name) AS (
>
> | SELECT id, name FROM customer_store WHERE
> id IN (1, 280, 864, 2376)
> | UNION ALL
> | SELECT ss.id, ss.name FROM
> recursive_stores sss, customer_store
> | ss WHERE sss.id = ss.parent_store_id
> | ) SELECT
> | to_char(i.timestamp, 'Mon-YY') AS "Month
> Name",
> | s.name AS "Target",
> | COUNT(DISTINCT i.id) / (SELECT COUNT(
> dds_stores.id) FROM
> | dds_stores) AS "Ticket Count"
> | FROM
> | customer_store s
> | INNER JOIN printdata_workstation w ON
> s.id = w.store_id AND s.id
> | IN (SELECT recursive_stores.id FROM
> recursive_stores)
> | INNER JOIN printdata_report r ON w.id =
> r.workstation_id AND
> | r.package_id IS NOT NULL
> | INNER JOIN printdata_page p ON r.id =
> p.report_id
> | INNER JOIN printdata_item i ON p.id =
> i.page_id
> | WHERE
> | r.timestamp >= '2012-09-01' AND
> r.timestamp <= '2013-08-31'
> | GROUP BY
> | "Month Name",
> | "Target"
> query_pos | 367
> location |
> application_name | pgAdmin III - Query Tool
>
>
>
>>
>>
>> Dave
>>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>