Re: reading cvs logs with pgadmin queries

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