Re: [GENERAL] How to quote the COALESCE function?

2016-03-28 Thread Jerry Sievers
Roman Scherer writes: > Hello, > > I'm building a DSL in Clojure for SQL and specifically PostgreSQL > [1]. When building a SQL statement that contains a function call > I always quote the function name with \" in case the function > name contains any special characters. Here's an example: > >  

Re: [GENERAL] How to quote the COALESCE function?

2016-03-28 Thread Tom Lane
Roman Scherer writes: > Can someone explain to me what's the difference between quoting > the `upper` and the `coalesce` function? COALESCE is a keyword. > What I found so far is, that the `upper` function can be found in > the `pg_proc` table but not `coalesce`. Yup. > Does this mean that `co

Re: [GENERAL] Unique values on multiple tables

2016-03-28 Thread John Turner
On Mon, Mar 28, 2016 at 2:32 AM, Sterpu Victor wrote: *table1* > id > nr - integer > > *table2* > id > id_table1 - FK in Table 1 > valid_from - timestamp > > There must be unique values for: > - nr - from table1 > and > - YEAR(MIN(valid_from)) from table 2 > Just trying to understand your goal

[GENERAL] How to quote the COALESCE function?

2016-03-28 Thread Roman Scherer
Hello, I'm building a DSL in Clojure for SQL and specifically PostgreSQL [1]. When building a SQL statement that contains a function call I always quote the function name with \" in case the function name contains any special characters. Here's an example: (select db ['(upper "x")]) ;=> ["SEL

Re: [GENERAL] Way to get at parsed trigger 'WHEN' clause expression?

2016-03-28 Thread Igor Neyman
-Original Message- From: James Robinson [mailto:jlrob...@socialserve.com] Sent: Friday, March 25, 2016 11:29 AM To: Igor Neyman Cc: Melvin Davidson ; pgsql-general@postgresql.org Subject: Re: [GENERAL] Way to get at parsed trigger 'WHEN' clause expression? > On Mar 25, 2016, at 11:10 AM

Re: [GENERAL] Way to get at parsed trigger 'WHEN' clause expression?

2016-03-28 Thread Igor Neyman
Did you mean something like SELECT substring(pg_get_triggerdef(tg.oid, TRUE) FROM position('WHEN' in pg_get_triggerdef(tg.oid, TRUE))) AS when_cond FROM pg_trigger tg WHERE tg.tgname = 'your_trigger_name'; -- Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share m

Re: [GENERAL] More correlated (?) index woes

2016-03-28 Thread Geoff Winkless
On 28 March 2016 at 22:01, rob stone wrote: > What does:- > > DELETE FROM pa > WHERE pa.field1 IS NULL > AND pa.sc_id IN (SELECT legs.sc_id FROM legs > WHERE legs.scdate BETWEEN 20160220 AND > 20160222) > > give as a cost when you run ANALYZE over it? > ​Thanks for the suggestion. It's a ​pprox

Re: [GENERAL] More correlated (?) index woes

2016-03-28 Thread rob stone
On Mon, 2016-03-28 at 20:23 +0100, Geoff Winkless wrote: > So I accept that when using MIN(sc_id) against scdate it makes > statistical sense to use the sc_id index for a reasonable percentage > of the full range of scdate, unless we know in advance that scdate is > closely correlated to sc_id (bec

Re: [GENERAL] Table size for partitioned setup

2016-03-28 Thread Rob Sargent
On 03/28/2016 02:55 PM, Mat Arye wrote: This will run on EC2 (or other cloud service) machines and on ssds. Right now runs on m4.4xlarge with 64GiB of ram. Willing to pay for beefy instances if it means better performance. On Mon, Mar 28, 2016 at 4:49 PM, Rob Sargent

Re: [GENERAL] Table size for partitioned setup

2016-03-28 Thread Mat Arye
This will run on EC2 (or other cloud service) machines and on ssds. Right now runs on m4.4xlarge with 64GiB of ram. Willing to pay for beefy instances if it means better performance. On Mon, Mar 28, 2016 at 4:49 PM, Rob Sargent wrote: > > > On 03/28/2016 02:41 PM, Mat Arye wrote: > >> Hi All, >

Re: [GENERAL] Table size for partitioned setup

2016-03-28 Thread Rob Sargent
On 03/28/2016 02:41 PM, Mat Arye wrote: Hi All, I am writing a program that needs time-series-based insert mostly workload. I need to make the system scaleable with many thousand of inserts/s. One of the techniques I plan to use is time-based table partitioning and I am trying to figure out

[GENERAL] Table size for partitioned setup

2016-03-28 Thread Mat Arye
Hi All, I am writing a program that needs time-series-based insert mostly workload. I need to make the system scaleable with many thousand of inserts/s. One of the techniques I plan to use is time-based table partitioning and I am trying to figure out how large to make my time tables. Does anybod

[GENERAL] More correlated (?) index woes

2016-03-28 Thread Geoff Winkless
So I accept that when using MIN(sc_id) against scdate it makes statistical sense to use the sc_id index for a reasonable percentage of the full range of scdate, unless we know in advance that scdate is closely correlated to sc_id (because using MIN means we can stop immediately we hit a value). Ho

Re: [GENERAL] View deleted records in a table

2016-03-28 Thread Adrian Klaver
On 03/26/2016 12:47 PM, Boyan Botev wrote: Thanks, Adrian! That worked great for what I needed. I greatly appreciate your help. Do you know if there is a way to also display system columns like xmin, xmax with this extension. I can see the need for that in some future investigation. You are not

Re: [GENERAL] Unique values on multiple tables

2016-03-28 Thread Jehan-Guillaume de Rorthais
Le Mon, 28 Mar 2016 12:44:51 +0200, Emre Hasegeli a écrit : > > Could I use lock tables to fix this? Is postgres automaticaly locking a > > table while running a trigger on that table? > > You can use LOCK TABLE. See the documentation: > > http://www.postgresql.org/docs/current/static/explicit-

Re: [GENERAL] Unique values on multiple tables

2016-03-28 Thread Emre Hasegeli
> Could I use lock tables to fix this? Is postgres automaticaly locking a > table while running a trigger on that table? You can use LOCK TABLE. See the documentation: http://www.postgresql.org/docs/current/static/explicit-locking.html -- Sent via pgsql-general mailing list (pgsql-general@post

Re: [GENERAL] Unique values on multiple tables

2016-03-28 Thread Sterpu Victor
-- Original Message -- From: "Emre Hasegeli" To: "Sterpu Victor" Cc: "PostgreSQL General" Sent: 28/3/2016 12:06:23 PM Subject: Re: [GENERAL] Unique values on multiple tables I have 2 tables and I must make asure unique values like this. table1 id nr - integer table2 id id_tab

Re: [GENERAL] Multi Master Replication setup

2016-03-28 Thread Pierre Chevalier
Hello, Searching BDR led me to a few surprising results... (trigrams definitely have numerous funny meanings ;o)) This URL will probably help: http://bdr-project.org/docs/stable/index.html Regards, Pierre Andreas Kretschmer a écrit : >Sachin Srivastava wrote: > >> Dear Concern, >> >> Kind

Re: [GENERAL] Unique values on multiple tables

2016-03-28 Thread Emre Hasegeli
> I have 2 tables and I must make asure unique values like this. > > table1 > id > nr - integer > > table2 > id > id_table1 - FK in Table 1 > valid_from - timestamp > > There must be unique values for: > - nr - from table1 > and > - YEAR(MIN(valid_from)) from table 2 In situations like this, I add

Re: [GENERAL] Multi Master Replication setup

2016-03-28 Thread Andreas Kretschmer
Sachin Srivastava wrote: > Dear Concern, > > Kindly inform to me how to setup multi master replication in Postgres. i think, you are looking for BDR. Please use google for more details. Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7

[GENERAL] Multi Master Replication setup

2016-03-28 Thread Sachin Srivastava
Dear Concern, Kindly inform to me how to setup multi master replication in Postgres. Kindly provide the link of step by step setup of this or any video or setup document regarding this. Regards Sachin