Re: [GENERAL] Load multiple CSV file in Postgres using COPY

On Feb 17, 2017 06:53, "John R Pierce" wrote: On 2/16/2017 9:43 PM, Magnus Hagander wrote: > Perhaps something like "COPY FROM PROGRAM 'cat /path/*.csv'" would work for you? he's using HEADER, so cat wouldn't work.he's also using MSDOS/WIndows style filenames, so cat

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

On 2/16/2017 9:43 PM, Magnus Hagander wrote: Perhaps something like "COPY FROM PROGRAM 'cat /path/*.csv'" would work for you? he's using HEADER, so cat wouldn't work.he's also using MSDOS/WIndows style filenames, so cat won't work, anyways.. I'd suggest using something like

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

On Fri, Feb 17, 2017 at 6:26 AM, Murtuza Zabuawala < murtuza.zabuaw...@enterprisedb.com> wrote: > Hi, > > Is there any way to load multiple CSV files at once using single COPY > command? > > I have scenario where I have to load multiple files, > > COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH

[GENERAL] Load multiple CSV file in Postgres using COPY

Hi, Is there any way to load multiple CSV files at once using single COPY command? I have scenario where I have to load multiple files, COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV HEADER Y COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV HEADER Y COPY

Re: [GENERAL] PostgreSQL corruption

On 2/16/2017 6:48 PM, James Sewell wrote: Sadly this is for a customer who has 3000 of these in the field, the raid controller is on the motherboard. if thats the usual Intel "Matrix" raid, thats not actually a raid controller. its intel sata in fake raid mode, the raid is entirely done

Re: [GENERAL] PostgreSQL corruption

Sadly this is for a customer who has 3000 of these in the field, the raid controller is on the motherboard. At least they know where to point the finger now! Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P

[GENERAL] updating dup row

Hi all, how can I update a row with newest id from another table if it exists somewhere else? Example: *table test1* - id (primary key) - id_user_bill - clientid *table test2* - item_id - userid (there are duplicated rows here) - clientid - id (primary key) -- finding

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

I guess this doesn't work, latest test run crashed. It still uses the bad plan for the hostid column even after n_distinct is updated. cipafilter=# select attname, n_distinct from pg_stats where tablename cipafilter-# = 'log_raw' and (attname = 'urlid' or attname = 'titleid' or attname =

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Tom Lane wrote: > Also you might want to look into how you got into a situation where > you have an anti-wraparound vacuum that's taking so long to run. If there are ALTERs running all the time, regular (non-anti-wraparound) vacuums would be canceled and never get a chance to run. Eventually,

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Tim Bellis writes: > Even though this is a read only query, is it also expected to be blocked > behind the vacuum? Is there a way of getting indexes for a table which won't > be blocked behind a vacuum? It's not the vacuum that's blocking your read-only queries.

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

Yep, 420ish million records out of 540 million records have a titleid of 1. There are about 880,000 other unique values, but most of the records are 1. Of course, n_distinct is only 292. I'm surprised it's not eliminating the duplicates while it builds that hash table. This is what I'm

Re: [GENERAL] disk writes within a transaction

On 02/16/2017 11:33 AM, 2xlp - ListSubscriptions wrote: Can someone enlighten me to how postgres handles disk writing? I've read some generic remarks about buffers, but that's about it. We have a chunk of code that calls Postgres in a less-than-optimal way within a transaction block. I'm

[GENERAL] disk writes within a transaction

Can someone enlighten me to how postgres handles disk writing? I've read some generic remarks about buffers, but that's about it. We have a chunk of code that calls Postgres in a less-than-optimal way within a transaction block. I'm wondering where to prioritize fixing it, as the traffic on

Re: [GENERAL] application generated an eternal block in the database

"Hu, Patricia" writes: > I recently came across an interesting locking/blocking situation in a > Postgres database(9.5.4, RDS but that shouldn't matter). The application is > java/hibernate/springboot with connection pooling. The developers pushed in > some code that

[GENERAL] application generated an eternal block in the database

I recently came across an interesting locking/blocking situation in a Postgres database(9.5.4, RDS but that shouldn't matter). The application is java/hibernate/springboot with connection pooling. The developers pushed in some code that seemed to be doing this: Start a transaction, update

Re: [GENERAL] Service configuration file and password security

On Thu, Feb 16, 2017 at 2:57 PM, JP Jacoupy wrote: > Can the password be stored in an encrypted way inside a service > configuration file? There is not Passwords are not stored in the ( sometimes shared, world readable ) service configuration file ( pg_sevice), but it

Re: [GENERAL] Service configuration file and password security

On 02/16/2017 05:57 AM, JP Jacoupy wrote: Hello, This might seem a pretty novice question but I can't find an answer. Can the password be stored in an encrypted way inside a service configuration file? To be clear you are talking about this, correct?:

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On 02/16/2017 08:45 AM, Tim Bellis wrote: Thank you all - that's really useful :-) The other query that gets blocked behind the vacuum is the below (truncated). This query is generated by jdbc in this method:

[GENERAL] Service configuration file and password security

Hello, This might seem a pretty novice question but I can't find an answer. Can the password be stored in an encrypted way inside a service configuration file? -- Jacoupy Jean-Philippe Sent from [ProtonMail](https://protonmail.ch), encrypted email based in Switzerland.

[GENERAL] Function out there to identify pseudo-empty fields, e.g. "n/a", "--", etc?

Hi all Just wondering if anyone has come across a function which can identify typical user-inputted placeholder values? English language is my scope so typical examples would be "not applicable", "n/a", "na", "none", "--", etc. I know it would be trivial to create but no sense in reinventing

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

David Hinkle writes: > Tom, there are three columns in this table that exhibit the problem, > here is the statistics data after an analyze, and the real data to > compare it to. > attname | n_distinct | most_common_freqs > titleid |292 | {0.767167} Ouch.

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Thank you all - that's really useful :-) The other query that gets blocked behind the vacuum is the below (truncated). This query is generated by jdbc in this method: org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseMetaData.java:4023) Even though this is a

Re: [GENERAL] postgresql how to duplicate rows in result.

On Thu, Feb 16, 2017 at 9:16 AM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > For my enlightenment, why use LATERAL here? I get the same result with a > simple CROSS JOIN (though overall I like the clever solution). > > ​To be explicit, I think. CROSS JOIN function() implies lateral

Re: [GENERAL] postgresql how to duplicate rows in result.

For my enlightenment, why use LATERAL here? I get the same result with a simple CROSS JOIN (though overall I like the clever solution). Cheers, Steve On Thu, Feb 16, 2017 at 12:11 AM, Alessandro Baggi < alessandro.ba...@gmail.com> wrote: > Il 15/02/2017 19:11, Alessandro Baggi ha scritto: > >>

Re: [GENERAL] Using ctid in delete statement

On Thu, Feb 16, 2017 at 7:52 AM, pinker wrote: > Adrian Klaver-4 wrote > > https://www.postgresql.org/docs/9.6/static/sql-truncate.html > > > > https://www.postgresql.org/docs/9.6/static/sql-delete.html > > There is nothing about FOR UPDATE clause on those pages... > ​Both

Re: [GENERAL] Using ctid in delete statement

On 02/16/2017 07:42 AM, pinker wrote: Adrian Klaver-4 wrote Exactly, they do not have it whereas: https://www.postgresql.org/docs/9.6/static/sql-select.html#SQL-FOR-UPDATE-SHARE Still not much. The documentation could be more verbose on this topic. I can only presume that since there is an

Re: [GENERAL] Using ctid in delete statement

Adrian Klaver-4 wrote > Exactly, they do not have it whereas: > > https://www.postgresql.org/docs/9.6/static/sql-select.html#SQL-FOR-UPDATE-SHARE Still not much. The documentation could be more verbose on this topic. I can only presume that since there is an example with select: SELECT * FROM

Re: [GENERAL] Using ctid in delete statement

On 02/16/2017 06:52 AM, pinker wrote: > Adrian Klaver-4 wrote >> https://www.postgresql.org/docs/9.6/static/sql-truncate.html >> >> https://www.postgresql.org/docs/9.6/static/sql-delete.html > > There is nothing about FOR UPDATE clause on those pages... Exactly, they do not have it whereas:

Re: [GENERAL] Using ctid in delete statement

Adrian Klaver-4 wrote > https://www.postgresql.org/docs/9.6/static/sql-truncate.html > > https://www.postgresql.org/docs/9.6/static/sql-delete.html There is nothing about FOR UPDATE clause on those pages... -- View this message in context:

Re: [GENERAL] Using ctid in delete statement

On 02/16/2017 02:04 AM, pinker wrote: Thank you Tom for clarification. Does it mean that FOR UPDATE clause works with other operations as well? i.e. TRUNCATE, DELETE? https://www.postgresql.org/docs/9.6/static/sql-truncate.html https://www.postgresql.org/docs/9.6/static/sql-delete.html

Re: [GENERAL] Alternate way of xpath

On 02/16/2017 04:33 AM, dhaval jaiswal wrote: I have the following situation. PostgreSQL is not configured with the option --with-libxml Having one text column where i am trying to run the following command which is failing as expected its not configure with libxml. However, is there any

Re: [GENERAL] PostgreSQL corruption

On Tue, Feb 14, 2017 at 7:23 PM, James Sewell wrote: > OK, > > So with some help from the IRC channel (thanks macdice and JanniCash) > it's come to light that my RAID1 comprised of 2 * 7200RPM disks is > reporting ~500 ops/sec in pg_test_fsync. > > This is higher than

[GENERAL] Alternate way of xpath

I have the following situation. PostgreSQL is not configured with the option --with-libxml Having one text column where i am trying to run the following command which is failing as expected its not configure with libxml. However, is there any alternate way through which i can achieve this.

Re: [GENERAL] Query with type char

## Egon Frerich (e...@frerich.eu): > Why are there 0 rows? I expect 3 rows: >From your results I guess that sp3 IS NULL on the three rows where it is not 'j'. You should use the correct comparison predicate for this case, i.e. IS DISTINCT FROM instead of <>.

[GENERAL] Query with type char

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Why are there 0 rows? I expect 3 rows: > mydb=# SELECT * FROM sample_char; > sp1 | sp2 | sp3 > -+-+- >1 | Bremen | >2 | Hamburg | >4 | Toronto | >3 | Bern| j >

Re: [GENERAL] Using ctid in delete statement

Thank you Tom for clarification. Does it mean that FOR UPDATE clause works with other operations as well? i.e. TRUNCATE, DELETE? -- View this message in context: http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944658.html Sent from the PostgreSQL - general mailing list

Re: [GENERAL] postgresql how to duplicate rows in result.

Il 15/02/2017 19:11, Alessandro Baggi ha scritto: Il 14/02/2017 21:51, Merlin Moncure ha scritto: On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure wrote: On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi wrote: Hi list, sorry for my english,