Re: [GENERAL] postgres function

On Thu, Oct 15, 2015 at 8:32 AM, Ramesh T wrote: > select position('-' in '123-987-123') > position > --- > 4 > But I want second occurrence, > position > - > 8 > > plz any help..? > > ​ SELECT length((regexp_matches('123-987-123',

[GENERAL] Installing plpython3u

Hello, I'm having problems installing plpython3u, this is my situation: I have installed postgresql-9.3.5 in my home directory, from source. I used (from my memory, it might not be exact) ./configure --prefix=$HOME; make; make install Now I need to upload a database which requires plpython3u,

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

On 10/12/2015 03:59 PM, Jim Nasby wrote: On 10/6/15 12:18 PM, Olivier Dony wrote: We would happily skip the micro-transactions (as a perf workaround) if there was a way to detect this situation, but we couldn't find a way to do that in 9.3. <9.3 we used SELECT FOR UPDATE NOWAIT to guard

Re: [GENERAL] Understanding "seq scans"

Merlin Moncure writes: > On Tue, Oct 13, 2015 at 11:54 AM, Alvaro Herrera > wrote: >> Be very careful with a % at the left. The index is not going to work at >> all there. It is not the same as looking for stuff without a % at the >> left. > >

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

Tim, Thank you, but I think I already did that. The query is a dollar-quoted string, so there should be no need to do anything with the single quote marks within it, so I would have thought the query engine would already know that it's text. But after seeing the first error message, I

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

On Thu, Oct 15, 2015 at 10:48 AM, Rob Richardson wrote: > By George, I think I've got it! > > When I ran CREATE EXTENSION tablefunc WITH SCHEMA public, I got the > crosstab methods and my sample query worked. > I would suggest learning about search_path(s) instead of

Re: [GENERAL] question

In addition to exactly what you mean by "a long time" to pg_dump 77k of your table, What is your O/S and how much memory is on your system? How many CPU's are in your system? Also, what is your hard disk configuration? What other applications are running simultaneously with pg_dump? What is the

Re: [GENERAL] Installing plpython3u

On 15 October 2015 at 16:23, Tom Lane wrote: > Dario Beraldi writes: > >> It might be worth cd'ing into the src/pl/plpython subdirectory and > >> manually doing "make install" there to see what it prints. > > > Here we go: > > cd > > >

Re: [GENERAL] Installing plpython3u

On 10/15/2015 07:49 AM, Dario Beraldi wrote: It might be worth cd'ing into the src/pl/plpython subdirectory and manually doing "make install" there to see what it prints. By the way, that's what I see in src/pl/plpython: So it does not look like it actually ran the make, I

Re: [GENERAL] Installing plpython3u

On 15 October 2015 at 16:29, Dario Beraldi wrote: > > > > > > On 15 October 2015 at 16:23, Tom Lane wrote: > >> Dario Beraldi writes: >> >> It might be worth cd'ing into the src/pl/plpython subdirectory and >> >> manually

Re: [GENERAL] Installing plpython3u

Dario Beraldi writes: >> It might be worth cd'ing into the src/pl/plpython subdirectory and >> manually doing "make install" there to see what it prints. > Here we go: > cd > /data/sblab-home/berald01/applications/postgresql/postgresql-9.3.5/src/pl/plpython > make

Re: [GENERAL] postgres function

On Thu, Oct 15, 2015 at 10:05 AM, Ramesh T wrote: > '123-987-123' it is not fixed some times it may be '1233-9873-123-098' > as you said it's fixed, > > changes the values in middle of the - > > sometimes times i need 1233 and 098 or 9873,first position i'll find >

[GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

I am trying to learn about crosstab functions in ProgreSQL 9.3, but none of the examples I’ve found are working. I get errors claiming the functions are unknown, but when I try running CREATE EXTENSION tablefunc, I am told that its methods already exist. For example, I am trying to run the

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

Rob Richardson writes: > I am trying to learn about crosstab functions in ProgreSQL 9.3, but none of > the examples I’ve found are working. I get errors claiming the functions > are unknown, but when I try running CREATE EXTENSION tablefunc, I am told > that its

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

I should have mentioned (twice now) that I'm running under Windows 7. RobR -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, October 15, 2015 10:19 AM To: Rob Richardson Cc: pgsql-general General Subject: Re: [GENERAL] How can I use crosstab functons in

Re: [GENERAL] postgres function

On 10/15/2015 07:05 AM, Ramesh T wrote: > '123-987-123' it is not fixed some times it may be '1233-9873-123-098' > as you said it's fixed, > > changes the values in middle of the - > > sometimes times i need 1233 and 098 or 9873,first position i'll find > direct for second variable we don't

Re: [GENERAL] question

On 10/14/2015 06:39 PM, anj patnaik wrote: Hello, I recently downloaded postgres 9.4 and I have a client application that runs in Tcl that inserts to the db and fetches records. For the majority of the time, the app will connect to the server to do insert/fetch. For occasional use, we want to

[GENERAL] pgpool ssl handshake failure

Hi, I am using pgpool-II version 3.4.3 (tataraboshi). Where my database is Postgresql 8.4. I am trying to configure ssl mode from client and between pgpool and database it is non-ssl. I configured as document and now I am getting this in my log: > > *2015-10-13 22:17:58: pid 1857: LOG: new

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

Looks to me like argument types possibly? The article creates various combinations of crosstab() function but you are passing in a query. Wrap your query in quotes (and then escape those within it). Then you'll be passing in a "text" type not an "unknown" as the error clearly shows. Tim Clarke

[GENERAL] Cast hstore type to bytea (and later to hex possibly)

Hello, I would like to achieve something like the following: COPY (select 'a=>x, b=>y'::hstore::bytea) TO STDOUT; I have implemented an hstore value iterator that works with pqxx::result::field.c_str() (which has its own binary serialisation format) and I would like to reuse it to work with

Re: [GENERAL] postgres function

'123-987-123' it is not fixed some times it may be '1233-9873-123-098' as you said it's fixed, changes the values in middle of the - sometimes times i need 1233 and 098 or 9873,first position i'll find direct for second variable we don't know where it's end with - i.e , i need to find second

Re: [GENERAL] postgres function

select position('-' in '123-987-123') position --- 4 But I want second occurrence, position - 8 plz any help..? On Thu, Oct 15, 2015 at 12:54 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Oct 14, 2015 at 9:38 AM, Ramesh T >

[GENERAL] question

Hello, I recently downloaded postgres 9.4 and I have a client application that runs in Tcl that inserts to the db and fetches records. For the majority of the time, the app will connect to the server to do insert/fetch. For occasional use, we want to remove the requirement to have a server db

Re: [GENERAL] Installing plpython3u

On 10/15/2015 07:42 AM, Dario Beraldi wrote: createlang plpython3u sblab ERROR: could not open extension control file "/data/sblab-home/berald01/share/postgresql/extension/plpython3u.control": No such file or directory STATEMENT:

Re: [GENERAL] Installing plpython3u

On 10/15/2015 03:21 AM, Dario Beraldi wrote: Hello, I'm having problems installing plpython3u, this is my situation: I have installed postgresql-9.3.5 in my home directory, from source. I used (from my memory, it might not be exact) ./configure --prefix=$HOME; make; make install Now I need to

Re: [GENERAL] Installing plpython3u

> createlang plpython3u sblab >>> ERROR: could not open extension control file >>> >>> "/data/sblab-home/berald01/share/postgresql/extension/plpython3u.control": >>> No such file or directory >>> STATEMENT: CREATE EXTENSION "plpython3u"; >>> >> >> Hmm, what files *do* you have in that directory

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

By George, I think I've got it! When I ran CREATE EXTENSION tablefunc WITH SCHEMA public, I got the crosstab methods and my sample query worked. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Installing plpython3u

> > It might be worth cd'ing into the src/pl/plpython subdirectory and >> manually doing "make install" there to see what it prints. >> >> >> By the way, that's what I see in src/pl/plpython: ls -l /data/sblab-home/berald01/applications/postgresql/postgresql-9.3.5/src/pl/plpython total 292

Re: [GENERAL] Installing plpython3u

Hi Adrian, Thanks for your answer. Just checking before I screw things up... About "the source tree has to be configured and built twice", does it mean that I have to execute again ./configure --prefix=$HOME; make; make install And should I enable any particular option in ./configure? I see

Re: [GENERAL] Installing plpython3u

Dario Beraldi writes: > Thanks for your answer. Just checking before I screw things up... About > "the source tree has to be configured and built twice", does it mean that I > have to execute again > ./configure --prefix=$HOME; > make; > make install > And should I

Re: [GENERAL] Installing plpython3u

On 15 October 2015 at 14:46, Tom Lane wrote: > Dario Beraldi writes: > > Thanks for your answer. Just checking before I screw things up... About > > "the source tree has to be configured and built twice", does it mean > that I > > have to execute

Re: [GENERAL] Installing plpython3u

Dario Beraldi writes: > Sorry guys... I executed > ./configure --prefix=$HOME --with-python PYTHON=/usr/local/bin/python3 > make > make install That looks sane from here ... > and it completed fine (see also below output from 'grep -i 'PYTHON' > config.log'). Still

Re: [GENERAL] pgpool ssl handshake failure

On 10/15/2015 06:59 AM, AI Rumman wrote: Hi, I am using pgpool-II version 3.4.3 (tataraboshi). Where my database is Postgresql 8.4. Probably already know, but 8.4 is approximately 1.25 years beyond EOL: http://www.postgresql.org/support/versioning/ I am trying to configure ssl mode from

Re: [GENERAL] Installing plpython3u

On 10/15/2015 07:16 AM, Tom Lane wrote: Dario Beraldi writes: Sorry guys... I executed ./configure --prefix=$HOME --with-python PYTHON=/usr/local/bin/python3 make make install That looks sane from here ... and it completed fine (see also below output from 'grep

Re: [GENERAL] postgres function

Well you could use SELECT LENGTH(REGEXP_REPLACE('123-987-123', '(([^-]*-){2}).*', '\1')); Not pretty, but it works. Geoff On 15 October 2015 at 15:05, Ramesh T wrote: > '123-987-123' it is not fixed some times it may be '1233-9873-123-098' > as you said it's

Re: [GENERAL] question

The PostgreSQL default configuration is very conservative so as to insure it will work on almost any system. However, based on your latest information, you should definitely adjust shared_buffers = 4GB maintenance_work_mem = 512MB Note that you will need to restart PostgreSQL for this to take

Re: [GENERAL] postgres function

On 15/10/15 14:32, Ramesh T wrote: > select position('-' in '123-987-123') > position > --- > 4 > But I want second occurrence, > position > - > 8 > > plz any help..? For instance: # select char_length(substring('123-987-123' from '^[^-]*-[^-]*-')); char_length - 8

Re: [GENERAL] using postgresql for session

On 10/14/15 8:57 PM, Tiger Nassau wrote: maybe we will just use beaker with our bottle framework - thought it was duplicative to have redis since we have postgres and lookup speed should be trivial since session only has a couple of small fields like account id and role The problem with

Re: [GENERAL] ID column naming convention

On 10/13/15 2:34 PM, Gavin Flower wrote: My practice is to name the PRIMARY KEY as id, and foreign keys with the original table name plus the sufiix_id. By leaving the table name off the primary key name, and just using id, makes it more obvious that it is a primary key (plus it seems

Re: [GENERAL] ID column naming convention

On 16/10/15 13:09, Jim Nasby wrote: On 10/13/15 2:34 PM, Gavin Flower wrote: My practice is to name the PRIMARY KEY as id, and foreign keys with the original table name plus the sufiix_id. By leaving the table name off the primary key name, and just using id, makes it more obvious that it is

Re: [GENERAL] problems with copy from file

On 10/14/15 11:40 AM, Andreas Kretschmer wrote: test=*# \copy httpd_log (data) from '~/test.log'; ERROR: invalid byte sequence for encoding "UTF8": 0xb1 CONTEXT: COPY httpd_log, line 3: "other-domain bb.243.xx.yyy - - [06/Nov/2014:00:48:22 +0100] "\x16\x03\x01\x01\xb1\x01" 501 10..." I

Re: [GENERAL] pgpool ssl handshake failure

> Hi, > > I am using pgpool-II version 3.4.3 (tataraboshi). > Where my database is Postgresql 8.4. > > I am trying to configure ssl mode from client and between pgpool and > database it is non-ssl. > I configured as document and now I am getting this in my log: > >> >> *2015-10-13 22:17:58: pid

Re: [GENERAL] question

On 10/15/2015 01:35 PM, anj patnaik wrote: Hello all, I will experiment with -Fc (custom). The file is already growing very large. I am running this: ./pg_dump -t RECORDER -Fc postgres | gzip > /tmp/dump Are there any other options for large tables to run faster and occupy less disk space?

[GENERAL] Standby pg_dump Conflict with Recovery

Hell All, I’ve got a confusing issue with dumping data from a standby PostgreSQL 9.4.5 database. At night, on a nearly completely idle server, I run a pg_dump of a database that contains numerous small tables and one 3GB table. The dump consistently fails when reaching the 3GB table with this

Re: [GENERAL] Standby pg_dump Conflict with Recovery

On 10/15/2015 03:03 PM, Louis Battuello wrote: Hell All, I’ve got a confusing issue with dumping data from a standby PostgreSQL 9.4.5 database. At night, on a nearly completely idle server, I run a pg_dump of a database that contains numerous small tables and one 3GB table. The dump

Re: [GENERAL] question

2015-10-15 20:40 GMT+02:00 anj patnaik : > It's a Linux machine with 8 CPUs. I don't have the other details. > > I get archive member too large for tar format. > > Is there a recommended command/options when dealing with very large > tables, aka 150K rows and half of the rows

Re: [GENERAL] Standby pg_dump Conflict with Recovery

> On Oct 15, 2015, at 6:16 PM, Adrian Klaver wrote: > > On 10/15/2015 03:03 PM, Louis Battuello wrote: >> Hello All, >> >> I’ve got a confusing issue with dumping data from a standby PostgreSQL >> 9.4.5 database. >> >> At night, on a nearly completely idle server, I

Re: [GENERAL] question

On Thu, Oct 15, 2015 at 3:55 PM, Guillaume Lelarge wrote: > 2015-10-15 20:40 GMT+02:00 anj patnaik : > >> It's a Linux machine with 8 CPUs. I don't have the other details. >> >> I get archive member too large for tar format. >> >> Is there a recommended

Re: [GENERAL] question

You stated you wanted to dump just one table, but your command is dumping the whole database! So if you truly want to dump just a single table, then change your command to: pg_dump -t RECORDER postgres --format=t -t your_table_name -w > /tmp/dump Also, please explain why you cannot provide the

Re: [GENERAL] pgpool ssl handshake failure

I configured Postgresql 9.4 and still getting the same error. Thanks. On Thu, Oct 15, 2015 at 7:16 AM, Adrian Klaver wrote: > On 10/15/2015 06:59 AM, AI Rumman wrote: > >> Hi, >> >> I am using pgpool-II version 3.4.3 (tataraboshi). >> Where my database is Postgresql

Re: [GENERAL] pgpool ssl handshake failure

On 10/15/2015 09:36 AM, AI Rumman wrote: I configured Postgresql 9.4 and still getting the same error. Configured what? Or more to the point what is ssl_renegotiation_limit set to? Thanks. On Thu, Oct 15, 2015 at 7:16 AM, Adrian Klaver

[GENERAL] Simple way to load xml into table

Hello, For psql 8.3, is there a simple way to load xml file into table please? E.g., True test1 e1 false test2 Results: t1 (c1 text, c2 text, c3 text): c1| c2 | c3 - true| test1 | e1 false | test2 | null ..

Re: [GENERAL] question

It's a Linux machine with 8 CPUs. I don't have the other details. I get archive member too large for tar format. Is there a recommended command/options when dealing with very large tables, aka 150K rows and half of the rows have data being inserted with 22MB? -bash-4.1$ ./pg_dump -t RECORDER

Re: [GENERAL] postgres function

yes David gave correct solution but , the value I'm using and it's column in the table sometimes value may be '123-987-123' or '123-987-123-13-87' if pass like below must return else condiion 0, select case when select split_part('123-987-123','-',4) >0 then 1 else 0 end it's return error

Re: [GENERAL] Simple way to load xml into table

On Thu, Oct 15, 2015 at 1:38 PM, Emi wrote: > Hello, > > For psql 8.3, is there a simple way to load xml file into table please? > > E.g., > > > True > test1 > e1 > > > false > test2 > > > Results: > t1 (c1 text, c2 text, c3 text): > > c1

Re: [GENERAL] postgres function

On Thu, Oct 15, 2015 at 3:15 PM, Ramesh T wrote: > yes David gave correct solution > > but , the value I'm using and it's column in the table sometimes value > may be '123-987-123' or '123-987-123-13-87' > > ​So adapt the answer provided to match your data.​ if

Re: [GENERAL] Simple way to load xml into table

On 10/15/2015 11:38 AM, Emi wrote: Hello, For psql 8.3, is there a simple way to load xml file into table please? E.g., True test1 e1 false test2 Results: t1 (c1 text, c2 text, c3 text): c1| c2 | c3 - true| test1 |