[GENERAL] Text,Citext column and Btree index

Hi, Why is a citext column not using Btree index for like operation even without any wildcard character? It can use Btree index if there is no wildcard character and if it is at the end right? Also a text column is using index when there is no wildcard character,but it is also not using if it is pr

Re: [GENERAL] Text,Citext column and Btree index

On 09/01/2016 09:34 AM, Viswanath wrote: > Hi, > Why is a citext column not using Btree index for like operation even without > any wildcard character? Because it doesn't know how. > It can use Btree index if there is no wildcard character and if it is at the > end right? No; use pg_trgm for th

[GENERAL] Array element foreign keys

Hi, I know a while ago someone posted a patch to support this: http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ but it seems that it has not made it as of 9.5. Is anyone aware of why it wasn't already included / if it is planned for it to be in a (near) future re

[GENERAL] COL unique (CustomerID) plus COL unique (COUNT) inside CustomerID

Hello to all, after a period of silence from Debian, Courier, PHP and PostgreSQL I am half back and running into a problem... :-/ I need a table with an UNIQUE CustomerID which is working fine... ...BUT I need also a second column with a count, which must be UNIQUE inside the CustomerID. I

Re: [GENERAL] COL unique (CustomerID) plus COL unique (COUNT) inside CustomerID

Hi, you can use the UNIQUE constraint with two columns: UNIQUE(CustID, Count). regards, Szymon Lipiński On 1 September 2016 at 11:28, Michelle Konzack wrote: > Hello to all, > > after a period of silence from Debian, Courier, PHP and PostgreSQL I am > half back and running into a problem...

Re: [GENERAL] Do function calls the cached?

Thanks David, Lateral did the trick: CREATE VIEW with_keywords AS SELECT x,y,z, keywords.a, keywords.b, keywords.c FROM large_table l, LATERAL extract_keywords(l.*) keywords(a,b,c) Regards, Daniel On Wed, Aug 31, 2016 at 6:46 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue,

Re: [GENERAL] Array element foreign keys

Emrul writes: > I know a while ago someone posted a patch to support this: > http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ > but it seems that it has not made it as of 9.5. > Is anyone aware of why it wasn't already included / if it is planned for it > to be i

Re: [GENERAL] UPDATE OR REPLACE?

On Thu, Sep 1, 2016 at 12:10 PM, dandl wrote: > Sqlite has options to handle an update that causes a duplicate key. Is > there anything similar in Postgres? > This is not an UPSERT. The scenario is an UPDATE that changes some key > field so that there is now a duplicate key. In Sqlite this handl

Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Nicolas Grilly Sent: Wednesday, August 31, 2016 6:32 PM To: Kenneth Marshall Cc: Vick Khera ; pgsql-general Subject: Re: [GENERAL] Clustered index to preserve data locality in a multitenant applica

Re: [GENERAL] COL unique (CustomerID) plus COL unique (COUNT) inside CustomerID

On Thu, 2016-09-01 at 12:28 +0300, Michelle Konzack wrote: > Hello to all, > > after a period of silence from Debian, Courier, PHP and PostgreSQL > I  am > half back and running into a problem...  :-/ > > I need a table with an UNIQUE CustomerID which is working fine... > > ...BUT I need also a

Re: [GENERAL] COL unique (CustomerID) plus COL unique (COUNT) inside CustomerID

Michelle Konzack wrote: > Hello to all, > > after a period of silence from Debian, Courier, PHP and PostgreSQL I am > half back and running into a problem... :-/ > > I need a table with an UNIQUE CustomerID which is working fine... > > ...BUT I need also a second column with a count, which

Re: [GENERAL] UPDATE OR REPLACE?

> > Sqlite has options to handle an update that causes a duplicate key. > Is > > there anything similar in Postgres? > > This is not an UPSERT. The scenario is an UPDATE that changes some > key > > field so that there is now a duplicate key. In Sqlite this handled > as: > > UPDATE OR IGNORE table S

Re: [GENERAL] UPDATE OR REPLACE?

On 09/01/2016 07:37 AM, dandl wrote: Sqlite has options to handle an update that causes a duplicate key. Is there anything similar in Postgres? This is not an UPSERT. The scenario is an UPDATE that changes some key field so that there is now a duplicate key. In Sqlite this handled as: UPDAT

Re: [GENERAL] Rackspace to RDS using DMS (Postgres 9.2)

On 08/31/2016 03:41 PM, Patrick B wrote: Hi guys, I posted this question on the ADMIN list but will post here as well so more people can comment... https://www.postgresql.org/message-id/CAJNY3it_AfxJhmwMHtpiAbHG47GS5rJOAUgfHw%2BGm5OXCbUm7w%40mail.gmail.com I've got a 2.3TB Database running at Ra

Re: [GENERAL] Rackspace to RDS using DMS (Postgres 9.2)

From: Joshua D. Drake Sent: Thursday, September 01, 2016 9:29 AM On 08/31/2016 03:41 PM, Patrick B wrote: > Hi guys, I posted this question on the ADMIN list but will post here > as well so more people can comment... >

Re: [GENERAL] COL unique (CustomerID) plus COL unique (COUNT) inside CustomerID

On Thu, Sep 1, 2016 at 4:28 AM, Michelle Konzack wrote: > I need a table with an UNIQUE CustomerID which is working fine... > > ...BUT I need also a second column with a count, which must be UNIQUE > inside the CustomerID. Just to be clear, you probably have a customer table with "CustIomerId

Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?

On Wed, 31 Aug 2016 17:33:18 +0200 Nicolas Grilly wrote: > Eduardo Morras wrote: > > > > Check BRIN indexs, they are "designed for handling very large > > tables in which certain columns have some natural correlation with > > their physical location within the table", I think they fit your > >

Re: [GENERAL] Rackspace to RDS using DMS (Postgres 9.2)

On Thu, Sep 1, 2016 at 1:44 PM, Mike Sofen wrote: > $100k/ $620 = 161 months of operation before cost parity. You ought to maybe compare what you can buy from an outfit like Silicon Mechanics for ~$15k. I suspect you could get 16-core, 256GB RAM, and several TB of disk. And then your parity would

Re: [GENERAL] Rackspace to RDS using DMS (Postgres 9.2)

> > > > It is 2.3TB, it is going to take a long time no matter what service you > are running. > > I know that Joshua! I just wanna get an idea from someone who has used DMS service... > > > No, I have not used DMS. Frankly, with all respect to AWS/RDS the idea of > running a 2.3TB instance that

Re: [GENERAL] UPDATE OR REPLACE?

> > In my particular situation the case I care about is when the result > of an UPDATE is two identical rows. All I really want is a DISTINCT > option. > > Assuming I am following correctly what you want is that the result of > an UPDATE not be two identical rows. Correct. In practice I don't car

[GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

Hi guys, I'll be performing a migration on my production master database server, which is running PostgreSQL 9.2 atm, from SATA disks to SSD disks. I've got some questions about it, and it would be nice if u guys could share your experiences/thoughts: *SCENARIO:* I currently have one MASTER and

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

On Fri, Sep 2, 2016 at 12:48 PM, Patrick B wrote: > Hi guys, > > I'll be performing a migration on my production master database server, > which is running PostgreSQL 9.2 atm, from SATA disks to SSD disks. > I've got some questions about it, and it would be nice if u guys could > share your exper

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

2016-09-02 15:36 GMT+12:00 Venkata B Nagothi : > > On Fri, Sep 2, 2016 at 12:48 PM, Patrick B > wrote: > >> Hi guys, >> >> I'll be performing a migration on my production master database server, >> which is running PostgreSQL 9.2 atm, from SATA disks to SSD disks. >> I've got some questions about