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