Re: [GENERAL] Processing data from table using awk.

Your best bet is something like #!/bin/bash get_data () { QRY=$(psql $HOST $PORT $USER $DBNAME <<_QUERY_ \o your_output_file SELECT col1, col2, , coln FROM your_table WHERE ; _QUERY_ ) } awk your_table On Tue, Oct 6, 2015 at 10:04 AM, John McKown

Re: [GENERAL] BDR Rejoin of failed node, hangs.

That was it thanks. Steve Pribyl Sr. Systems Engineer steve.pri...@akunacapital.com Desk: 312-994-4646 From: Craig Ringer Sent: Tuesday, October 6, 2015 12:35 AM To: Steve Pribyl Cc: pgsql-general@postgresql.org Subject: Re:

Re: [GENERAL] Processing data from table using awk.

On Tue, 2015-10-06 at 09:04 -0500, John McKown wrote: > I'm wanting to do some reporting on data which I have an a PostgreSQL table. > For lack of anything better, I've decided to see if I can do it in GNU awk. perhaps... note the 4th extension...

[GENERAL] No funciona WITH con mas de 2 sentencias DML

Hola Lista Estaba realizando un cargue de un archivo Excel con información de clientes bancarios con tarjeta para un call center poblando un modelo maestro, detalle y tabla de llamadas telefónicas. En un principio se implemento por medio de una herramienta de ETL con los controles que ofrece la

Re: [GENERAL] Processing data from table using awk.

Just to throw in an idea. I almost exclusively use PERL for this type of thing. A bunch of examples out on the web using DBI, and the main aspects are portable across many databases, not just POSTGRES. Just my two cents. AWK would work too, I’ve used it myself, and got very complicated with

Re: [GENERAL] Processing data from table using awk.

On Tue, Oct 6, 2015 at 10:15 AM, Melvin Davidson wrote: > Your best bet is something like > > #!/bin/bash > > get_data () > { > QRY=$(psql $HOST $PORT $USER $DBNAME <<_QUERY_ > > \o your_output_file > SELECT col1, col2, , coln > FROM your_table > WHERE ; > > _QUERY_ >

Re: [GENERAL] Processing data from table using awk.

On Tue, Oct 6, 2015 at 9:25 AM, Reid Thompson wrote: > On Tue, 2015-10-06 at 09:04 -0500, John McKown wrote: > > > I'm wanting to do some reporting on data which I have an a PostgreSQL > table. > > For lack of anything better, I've decided to see if I can do it in GNU >

[GENERAL] backup.old

Good Evening, What do I need to do to recover a database server that has a backup.old file in the data_directory. I have see references to a database being stopped during a backup and/or a second backup running and moving the old file aside, but it was not clear to me what needs to be done do

Re: [GENERAL] backup.old

On 10/6/2015 8:28 PM, Steve Pribyl wrote: What do I need to do to recover a database server that has a backup.old file in the data_directory. I have see references to a database being stopped during a backup and/or a second backup running and moving the old file aside, but it was not clear

Re: [GENERAL] backup.old

Honestly I don't know. It was either pg_dump or a recovery script, that would have included a call to pg_start_backup. It does have the start_backup looking format. START WAL LOCATION: F3/128 (file 004500F30001) CHECKPOINT LOCATION: F3/160 BACKUP METHOD: pg_start_backup

Re: [GENERAL] Best practices for aggregate table design

On Tue, Oct 6, 2015 at 2:53 PM, droberts wrote: > > month | city_id | state_id | total_calls_inbound | total_calls_outbound | > total_calls_inbound_encr | total_calls_outbound_encr | > > getting a bit hairy but the alternative seems like it would start growing > too

Re: [GENERAL] Best practices for aggregate table design

On 10/6/2015 11:24 AM, droberts wrote: OR a dimension 'type' with values outbound/inbound and a single measure column 'total' ? that smells a bit too much like an "EAV" (entity-attribute-value) which is considered an antipattern in relational circles -- john r pierce, recycling bits in

Re: [GENERAL] Best practices for aggregate table design

On Tue, Oct 6, 2015 at 2:34 PM, John R Pierce wrote: > On 10/6/2015 11:24 AM, droberts wrote: > >> OR a dimension 'type' with values outbound/inbound and a single measure >> column 'total' ? >> > > that smells a bit too much like an "EAV" (entity-attribute-value) which is >

Re: [GENERAL] Best practices for aggregate table design

Thanks for your response. One more follow-up question. Is there a best practice when to create a measure that includes a property/dimension? Let me give an example, say in my example where I have outbound and inbound calls. Is is best to have measures: -total_inbound -total_outbound

Re: [GENERAL] Selecting pairs of numbers

Charles Clavadetscher wrote: >> aklaver@test=> create table pr_test(x int, y int); >> >> aklaver@test=> select * from pr_test where (x, y) between (1, 3) and >> (3,2) order by x,y; >> x | y >> ---+--- >> 1 | 3 >> 1 | 4 >> 2 | 1 >> 2 | 2 >> 2 | 3 >> 2 | 4 >> 3 | 1 >> 3 | 2 > >

[GENERAL] dubious optimization of the function in SELECT INTO target list

Hello, I have an issue with a function that is called as a part of the SELECT INTO target list in pl/pgSQL. I'd like to illustrate it with a simple example: DO $$ DECLARE l_id integer; BEGIN SELECT test(id) INTO l_id FROM generate_series(1,10) t(id); END; $$ LANGUAGE

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

> On 06 Oct 2015, at 22:40, Adrian Klaver wrote: > > On 10/06/2015 01:13 PM, Oleksii Kliukin wrote: >> >> Basically, if we invoke the first example, the foo table with have only >> 1 row and not 10, as supplied by the generate_series. >> However, when ORDER BY is

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

On 10/06/2015 01:13 PM, Oleksii Kliukin wrote: Hello, I have an issue with a function that is called as a part of the SELECT INTO target list in pl/pgSQL. I'd like to illustrate it with a simple example: DO $$ DECLARE l_id integer; BEGIN SELECT test(id) INTO l_id FROM

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

On 10/06/2015 01:48 PM, Oleksii Kliukin wrote: On 06 Oct 2015, at 22:40, Adrian Klaver > wrote: On 10/06/2015 01:13 PM, Oleksii Kliukin wrote: Basically, if we invoke the first example, the foo table with have only 1 row and not

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

Oleksii Kliukin writes: > This should work, but I'm interested in finding out why the original > statement behaves the way I’ve described. plpgsql's SELECT INTO is only capable of storing a single result row, so it only executes the statement far enough to obtain one row,

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

> On 06 Oct 2015, at 22:50, Adrian Klaver wrote: > > On 10/06/2015 01:48 PM, Oleksii Kliukin wrote: >> >>> On 06 Oct 2015, at 22:40, Adrian Klaver >> >>>

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

On 10/06/2015 02:00 PM, Oleksii Kliukin wrote: On 06 Oct 2015, at 22:50, Adrian Klaver > wrote: On 10/06/2015 01:48 PM, Oleksii Kliukin wrote: On 06 Oct 2015, at 22:40, Adrian Klaver

Re: [GENERAL] Best practices for aggregate table design

Okay, so is it safe to say I should use loosely use these guidelines when deciding whether to model an attribute as a dimension (type=[inbound,outbound]) vs. bundling with a measure (total_inbound) ? If you know the number of values for a dimension are fixed (e.g. boolean), then creating a

[GENERAL] Recording exceptions within function (autonomous transactions?)

I am porting several stored procedures from Oracle to Postgres. In the Oracle code, if an exception is thrown within a stored procedure, the exception is caught and details are written to a database table using an autonomous transaction (as the main transaction is rolled back). As far as I can

Re: [GENERAL] Recording exceptions within function (autonomous transactions?)

> On Oct 6, 2015, at 05:38, Steve Pritchard wrote: > > I am porting several stored procedures from Oracle to Postgres. In the Oracle > code, if an exception is thrown within a stored procedure, the exception is > caught and details are written to a database table

Re: [GENERAL] [Q] Serializable

David G. Johnston wrote: > On Thu, Sep 24, 2015 at 12:15 PM, Ladislav Lenart wrote: >> Suppose I have two (or more) concurrent DB transactions: >> * Each runs in SERIALIZABLE. >> * Each updates (insert / update / delete) different rows in the >>

Re: [GENERAL] How to reduce pg_dump backup time

> On Oct 6, 2015, at 00:52, Sachin Srivastava wrote: > > Dear Team, > > > > I am using PostgreSQL 9.1 on Redhat Linux on my Production environment. My > database size is 680 GB and it take 7 hour for completion the pg_dump backup. > > > > I want that my pg_dump

[GENERAL] Processing data from table using awk.

I'm wanting to do some reporting on data which I have an a PostgreSQL table. For lack of anything better, I've decided to see if I can do it in GNU awk. OK, using Perl with DBI might be a better idea, I'll grant you that. Or maybe Python or Ruby (which I don't know). But out of shear cussedness,

Re: [GENERAL] How to reduce pg_dump backup time

On 10/05/2015 09:52 PM, Sachin Srivastava wrote: Dear Team, I am using PostgreSQL 9.1 on Redhat Linux on my Production environment. My database size is 680 GB and it take 7 hour for completion the pg_dump backup. I want that my pg_dump backup should be fast and take less time. In PostgresQL

Re: [GENERAL] md5(large_object_id)

Karsten Hilbert wrote: > On Mon, Oct 05, 2015 at 03:27:26PM +, Kevin Grittner wrote: > > > Karsten Hilbert wrote: > > > > > I am dealing with radiology studies aka DICOM data) one would > > > want an md5 function which streams in parts of a large object > >

Re: [GENERAL] Recording exceptions within function (autonomous transactions?)

On 10/06/2015 02:38 AM, Steve Pritchard wrote: I am porting several stored procedures from Oracle to Postgres. In the Oracle code, if an exception is thrown within a stored procedure, the exception is caught and details are written to a database table using an autonomous transaction (as the main

Re: [GENERAL] Recording exceptions within function (autonomous transactions?)

Scott Mead writes: >> On Oct 6, 2015, at 05:38, Steve Pritchard wrote: >> [ how to fake an autonomous transaction? ] > It's hacky, and, I haven't tried it in a few years. Setup a foreign table > that resides in the same database. When you write to

Re: [GENERAL] How to reduce pg_dump backup time

On Tue, Oct 6, 2015 at 8:54 AM, Adrian Klaver wrote: > On 10/05/2015 09:52 PM, Sachin Srivastava wrote: > >> Dear Team, >> >> I am using PostgreSQL 9.1 on Redhat Linux on my Production environment. >> My database size is 680 GB and it take 7 hour for completion the

Re: [GENERAL] Best practices for aggregate table design

On Tue, Oct 6, 2015 at 11:59 AM, droberts wrote: > Hi, > I'm trying to construct an agg table to capture phone call data and group > by > state, city and time but also want just general measures by month. I'm > thinking to have this: > > month | city_id | state_id |

[GENERAL] Best practices for aggregate table design

Hi, I'm trying to construct an agg table to capture phone call data and group by state, city and time but also want just general measures by month. I'm thinking to have this: month | city_id | state_id | total_calls_inbound | total_calls_outbound | total_calls 2015-01 12 2 54 2 56 2015-01 10

Re: [GENERAL] Processing data from table using awk.

On Tue, Oct 6, 2015 at 10:38 AM, Basques, Bob (CI-StPaul) < bob.basq...@ci.stpaul.mn.us> wrote: > Just to throw in an idea. > > I almost exclusively use PERL for this type of thing. A bunch of examples > out on the web using DBI, and the main aspects are portable across many > databases, not

Re: [GENERAL] Recording exceptions within function (autonomous transactions?)

On 10/6/2015 2:38 AM, Steve Pritchard wrote: I am porting several stored procedures from Oracle to Postgres. In the Oracle code, if an exception is thrown within a stored procedure, the exception is caught and details are written to a database table using an autonomous transaction (as the main

Re: [GENERAL] Serialization errors despite KEY SHARE/NO KEY UPDATE

On 10/05/2015 11:17 PM, Kevin Grittner wrote: Jim Nasby wrote: On 10/2/15 11:44 AM, Olivier Dony wrote: On 10/02/2015 12:28 AM, Jim Nasby wrote: On 9/29/15 9:47 AM, Olivier Dony wrote: -- Setup tables CREATE TABLE users ( id serial PRIMARY KEY,

Re: [GENERAL] Processing data from table using awk.

On Tue, Oct 6, 2015 at 10:04 AM, John McKown wrote: > I'm wanting to do some reporting on data which I have an a PostgreSQL > table. For lack of anything better, I've decided to see if I can do it in > GNU awk. OK, using Perl with DBI might be a better idea, I'll

Re: [GENERAL] [pgsql-es-ayuda] No funciona WITH con mas de 2 sentencias DML

Hellmuth Vargas escribió: > Hola Lista > > Estaba realizando un cargue de un archivo Excel con información de clientes > bancarios con tarjeta para un call center poblando un modelo maestro, > detalle y tabla de llamadas telefónicas. En un principio se implemento por > medio de una herramienta