Re: reading cvs logs with pgadmin queries

[Dave Cramer <pg@xxxxxxxxxxxxx>]

The errors are extra lines after .... with a query like this

2013-09-12 10:33:19.145 WST,"user","dbname",14581,"192.168.1.22:58840",523126d3.38f5,1,"SELECT",2013-09-12
10:28:35 WST,6/503023,0,ERROR,42P01,"relation ""dds_stores"" does not
exist",,,,,,"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""",367,,"pgAdmin III - Query Tool"

Dave Cramer

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

On Thu, Sep 19, 2013 at 10:14 AM, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote:

On 09/19/2013 05:47 AM, Dave Cramer wrote:

pgadmin uses multi line queries and copy from 'cvslogs' cannot read them.

Does anyone have a work around or other solution ?

It worked here for me:

test=# INSERT INTO
    big_int_test
VALUES
    (6.7);

COPY postgres_log FROM '/usr/local/pgsql/data/pg_log/postgresql-2013-09-19_070152.csv' WITH csv;


-[ RECORD 7 ]----------+-----------------------------------------------
log_time               | 2013-09-19 07:04:42.422-07
user_name              | aklaver
database_name          | test
process_id             | 8281
connection_from        | [local]
session_id             | 523b0401.2059
session_line_num       | 4
command_tag            | idle
session_start_time     | 2013-09-19 07:02:41-07
virtual_transaction_id | 2/30
transaction_id         | 0
error_severity         | LOG
sql_state_code         | 00000
message                | statement: INSERT INTO
                       |     big_int_test
                       | VALUES
                       |     (6.7);
detail                 |
hint                   |
internal_query         |
internal_query_pos     |
context                |
query                  |
query_pos              |
location               |
application_name       | psql



What are the errors you are seeing?

Dave Cramer

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

--
Adrian Klaver
adrian.klaver@xxxxxxxxx