Re: reading cvs logs with pgadmin queries - Mailing list pgsql-general

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.klaver@gmail.com