[GENERAL] Re: Exists subquery in an update ignores the effects of the update itself

2014-09-04 Thread David G Johnston
Jeff Janes wrote > I want to update some data in unique column. Some of the updates would > conflict if applied to eligible rows, and for now I want to skip those > updates, applying only one of a set of conflicting ones. I can use a not > exists subquery to detect when the new value would confli

[GENERAL] Exists subquery in an update ignores the effects of the update itself

2014-09-04 Thread Jeff Janes
I want to update some data in unique column. Some of the updates would conflict if applied to eligible rows, and for now I want to skip those updates, applying only one of a set of conflicting ones. I can use a not exists subquery to detect when the new value would conflict with an existing one,

[GENERAL] CONCAT function

2014-09-04 Thread Vinayak
Hello, The pg_catalog.concat() is defined as STABLE function. As per my understanding a STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. Example: current_timestamp family of functions qualify a

[GENERAL] how to pass tablename to a function

2014-09-04 Thread alecinvan
Hi, All I like to pass the tablename to function but not using execute clause, here is my script CREATE OR REPLACE FUNCTION functions.pgsql_event_unpack_batch(IN _tbl text, IN jobid bigint, IN jobtime timestamp with time zone, IN startid bigint, IN stopid bigint) RETURNS TABLE(events bigint, er

Re: [GENERAL] how to pass tablename to a function

2014-09-04 Thread David G Johnston
alecinvan wrote > I like to pass the tablename to function but not using execute clause, > here is my script > > [...] > > I want to pass the _tbl to the select query integrated in the unpacking(), > how can I make it? There is no way to perform a query with an unknown, at design time, identifie

Re: [GENERAL] how to pass tablename to a function

2014-09-04 Thread Adrian Klaver
On 09/04/2014 04:42 PM, A L. wrote: Hi, All I like to pass the tablename to function but not using execute clause, here is my script CREATE OR REPLACE FUNCTION functions.pgsql_event_unpack_batch(IN _tbl text, IN jobid bigint, IN jobtime timestamp with time zone, IN startid bigint, IN stopid big

[GENERAL] how to pass tablename to a function

2014-09-04 Thread A L .
Hi, All I like to pass the tablename to function but not using execute clause, here is my script CREATE OR REPLACE FUNCTION functions.pgsql_event_unpack_batch(IN _tbl text, IN jobid bigint, IN jobtime timestamp with time zone, IN startid bigint, IN stopid bigint) RETURNS TABLE(events bigi

Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

2014-09-04 Thread Adrian Klaver
On 09/04/2014 08:03 AM, Alanoly Andrews wrote: Thanks, Adrian for the response. Yes, we are using the "large object" as per the specifications in the special "lo" module that we installed on the backend server. The table is created using the "lo" datatype for the image field and the table is bei

Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

2014-09-04 Thread Adrian Klaver
On 09/04/2014 07:15 AM, David G Johnston wrote: Adrian Klaver-4 wrote On 09/04/2014 05:56 AM, Alanoly Andrews wrote: Hi Adrian, Thanks for that explanation of how the "relfilenode" changes after a table reorganization. It is not surprising that this happens because the table rows are being phy

Re: [GENERAL] Employee modeling question

2014-09-04 Thread Robin
Robin St.Clair On 04/09/2014 20:44, Nelson Green wrote: On Thu, Sep 4, 2014 at 9:48 AM, François Beausoleil mailto:franc...@teksol.info>> wrote: Hello Nelson, Le 2014-09-04 à 10:39, Nelson Green mailto:nelsongree...@gmail.com>> a écrit : > Good morning, > > Hopefully this

Re: [GENERAL] Employee modeling question

2014-09-04 Thread Nelson Green
On Thu, Sep 4, 2014 at 9:48 AM, François Beausoleil wrote: > Hello Nelson, > > Le 2014-09-04 à 10:39, Nelson Green a écrit : > > > Good morning, > > > > Hopefully this is the correct place to ask this type of question. > > > > I am in the early stages of designing a system to track employee > >

Re: [GENERAL] free RAM not being used for page cache

2014-09-04 Thread Kevin Goess
This is a super-interesting topic, thanks for all the info. On Thu, Sep 4, 2014 at 7:44 AM, Shaun Thomas wrote: > > Check /proc/meminfo for a better breakdown of how the memory is being > used. This should work: > > grep -A1 Active /proc/meminfo > > I suspect your inactive file cache is larger th

Re: [GENERAL] GiST index question

2014-09-04 Thread Eric Fleming
Thank you both, I will look into alternative data types. I don’t think ltree will work for my purposes but I am going to try out some others that might; like cube. — Eric Fleming On Thu, Sep 4, 2014 at 3:42 AM, Giuseppe Broccolo wrote: > Hi Eric, > As Michael said, path data type does not supp

Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

2014-09-04 Thread Alanoly Andrews
Thanks, Adrian for the response. Yes, we are using the "large object" as per the specifications in the special "lo" module that we installed on the backend server. The table is created using the "lo" datatype for the image field and the table is being regularly used for insertions and retriev

Re: [GENERAL] || operator

2014-09-04 Thread Brett Mc Bride
On Wed, 2014-09-03 at 21:27 -0700, Vinayak wrote: > Hello Pavel, > > Thank you for reply. > >postgres=# select 'abc '::char(7) || 'dbe '::char(6); > >?column? > > > > *abcabc* > >(1 row) > but it gives the result "abcabc". It should be "abcdbe". > > I believe the

Re: [GENERAL] Employee modeling question

2014-09-04 Thread François Beausoleil
Hello Nelson, Le 2014-09-04 à 10:39, Nelson Green a écrit : > Good morning, > > Hopefully this is the correct place to ask this type of question. > > I am in the early stages of designing a system to track employee > information, including some aspects of their payroll, one of which is > the s

Re: [GENERAL] free RAM not being used for page cache

2014-09-04 Thread Shaun Thomas
On 09/03/2014 07:17 PM, Kevin Goess wrote: Debian squeeze, still on 2.6.32. Interesting. Unfortunately that kernel suffers from the newer task scheduler they added to 3.2, and I doubt much of the fixes have been back-ported. I don't know if that affects the memory handling, but it might.

[GENERAL] Employee modeling question

2014-09-04 Thread Nelson Green
Good morning, Hopefully this is the correct place to ask this type of question. I am in the early stages of designing a system to track employee information, including some aspects of their payroll, one of which is the source of the salary funds within the business. I need to make this generic en

[GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

2014-09-04 Thread David G Johnston
Adrian Klaver-4 wrote > On 09/04/2014 05:56 AM, Alanoly Andrews wrote: >> Hi Adrian, >> >> Thanks for that explanation of how the "relfilenode" changes after a >> table reorganization. It is not surprising that this happens because the >> table rows are being physically moved from one location to a

Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

2014-09-04 Thread Adrian Klaver
On 09/04/2014 05:56 AM, Alanoly Andrews wrote: Hi Adrian, Thanks for that explanation of how the "relfilenode" changes after a table reorganization. It is not surprising that this happens because the table rows are being physically moved from one location to another. But such changes at the bac

[GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

2014-09-04 Thread David G Johnston
You are going to have to help us by providing server (and any other) logs with complete error messages and VB/ODBC code, with corresponding schema, that will reliably reproduce the problem. Note that since you are on an ancient 9.1 release it is possible that, if this is indeed a bug, this has alr

Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

2014-09-04 Thread Alanoly Andrews
Hi Adrian, Thanks for that explanation of how the "relfilenode" changes after a table reorganization. It is not surprising that this happens because the table rows are being physically moved from one location to another. But such changes at the backend should be transparent to the end user. The

Re: [GENERAL] || operator

2014-09-04 Thread Pavel Stehule
2014-09-04 11:13 GMT+02:00 Vinayak : > Hi, > > The || operator with arguments (character,character) works fine and even || > operator(character,varchar) also works fine. > but || operator is not working as expected with arguments character data > type and any other data type like integer,smallint,

Re: [GENERAL] || operator

2014-09-04 Thread Vinayak
Hi, The || operator with arguments (character,character) works fine and even || operator(character,varchar) also works fine. but || operator is not working as expected with arguments character data type and any other data type like integer,smallint,date,text. Example: postgres=# select 'ab'::char(

Re: [GENERAL] copymanager question

2014-09-04 Thread Craig Ringer
On 09/02/2014 06:20 PM, swaroop wrote: > To summarize - how do i replace the input strings in java (data is streamed > in and i do a copy to postgres) > so that CSV copy does not fail. > a. words with comma > b. words with double quotes in them > c. words with \ (backslash) Don't write the escapin

Re: [GENERAL] GiST index question

2014-09-04 Thread Giuseppe Broccolo
Hi Eric, As Michael said, path data type does not support for gist operators. Anyway, you could redefine data type using 'ltree' instead of 'path'. Take a look on the following link: http://www.postgresql.org/docs/9.1/static/ltree.html Try to understand if this could be fine for you. Cheers, G

[GENERAL] Merge requirements between offline clients and central database

2014-09-04 Thread Sameer Thakur
Hello, As part of database evaluation one key requirements is as follows: 1. There are multiple thick clients (say 20 ~ 100) with their local databases accepting updates 2. They sync data with a central database which can also receive updates itself. 3. They may not be connected to central databa