Re: [GENERAL] Limit Heap Fetches / Rows Removed by Filter in Index Scans

On Fri, 19 Aug 2016, 1:07 p.m. Victor Blomqvist, wrote: > Hi, > > Is it possible to break/limit a query so that it returns whatever results > found after having checked X amount of rows in a index scan? > > For example: > create table a(id int primary key); > insert into a select

[GENERAL] Limit Heap Fetches / Rows Removed by Filter in Index Scans

Hi, Is it possible to break/limit a query so that it returns whatever results found after having checked X amount of rows in a index scan? For example: create table a(id int primary key); insert into a select * from generate_series(1,10); select * from a where id%2 = 0 order by id limit 10

Re: [GENERAL] foreign key with where clause

On Thu, Aug 18, 2016 at 1:10 PM, Mark Lybarger wrote: > I have two tables that i want to link with a FK where the child table record > is "active". > > some googling shows that i could use a function and a check constraint on > the function, but that only works for inserts,

[GENERAL] Restrict CREATEROLE privilege grant to NOLOGIN only?

Is it possible to create a login user who themselves can CREATE ROLE NOLOGIN but not CREATE ROLE LOGIN? Here’s an example of the behavior I’d like to achieve. $ psql postgres=> CREATE USER admin WITH PASSWORD 'mypassword' CREATEROLE; postgres=> — revoke something? postgres=> \q $ psql -U admin

Re: [GENERAL] foreign key with where clause

My first instinct would be to look into triggers. In addition to an FK a(b_id) -> b(id), you could have an insert and update trigger on a(b_id) and b(active) to ensure the additional constraints. On Thu, Aug 18, 2016 at 1:10 PM, Mark Lybarger wrote: > I have two tables that

[GENERAL] foreign key with where clause

I have two tables that i want to link with a FK where the child table record is "active". some googling shows that i could use a function and a check constraint on the function, but that only works for inserts, not updates on table b. create table a (int id, text name); create table b (int id,

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

Daniel: On Thu, Aug 18, 2016 at 5:24 PM, Daniel Verite wrote: >> unless you know of an easy way to generate a random permutation on the >> fly without using a lot of memory, I do not. > It could be done by encrypting the stream. > For 32 bits integers: >

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

Francisco Olarte wrote: > unless you know of an easy way to generate a random permutation on the > fly without using a lot of memory, I do not. It could be done by encrypting the stream. For 32 bits integers: https://wiki.postgresql.org/wiki/Skip32 For 64 bits integers:

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

Hi: On Thu, Aug 18, 2016 at 1:32 PM, pinker wrote: ... > create table t01 (id bigint); > create index i01 on t01(id); > insert into t01 SELECT s from generate_series(1,1000) as s; > > and random values: > create table t02 (id bigint); > create index i02 on t02(id); > insert

Re: [GENERAL] SQL help - multiple aggregates

CCing to the list ( if you are new to this list, messages come from the sender address, you have to use "reply all" ( at least in my MUA, web gmail ) to make your replies appear in the list ). On Thu, Aug 18, 2016 at 3:03 PM, wrote: > Hi Francisco, > thanks a lot. I will

Re: [GENERAL] SQL help - multiple aggregates

On Thu, Aug 18, 2016 at 4:56 AM, wrote:​ > select custid, count(vendid) as c415 from cv where vendid = 415 group by > custid > ​[...] > > Is there a better way (by creating an aggregate function, perhaps) > ​You may find crosstab in the tablefuncs extension to be of use.

Re: [GENERAL] SQL help - multiple aggregates

Hello. On 18.8.2016 10:56, haman...@t-online.de wrote: > > Hi, > > I have a table cv with custid and vendid columns. Every entry represents the > purchase of a product > available from a specific vendor. > Now, for a set of "interesting" vendors, I would like to select a new table > custid,

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

W dniu 2016-08-18 14:19:25 użytkownik Ilya Kazakevich napisał: > >Thank you. So if that is the reason changing the fillfactor parameter should > >help? > > Fillfactor is not about rebalancing, but about page split. If you have many > insertions you may decrease

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

>Thank you. So if that is the reason changing the fillfactor parameter should >help? Fillfactor is not about rebalancing, but about page split. If you have many insertions you may decrease fillfactor to minimize page splits, but I am not sure it will help in your case. But you should try)

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

W dniu 2016-08-18 14:00:31 użytkownik Ilya Kazakevich napisał: > Hi, > > >What's the reason that postgres needs more index pages to store random > >data > >than sequential ones? > > I assume that is because B-Tree is self-balanced tree, so it needs to be >

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

Hi, >What's the reason that postgres needs more index pages to store random >data >than sequential ones? I assume that is because B-Tree is self-balanced tree, so it needs to be rebalanced after each insertion. Random insertions may go to the head of index where no space left leading to huge

Re: [GENERAL] PGPASSWORD - More than one in a bash script

On Thursday, August 18, 2016, Patrick B wrote: > Hi guys, > > I'm writing a bash script to dump and restore (pg_dump + pg_restore) a > test database. > > However, the username to access the pg_dump server is different of the one > to access pg_restore. > > I'm using the

[GENERAL] Sequential vs. random values - number of pages in B-tree

Hi! After doing a quick test: with sequential values: create table t01 (id bigint); create index i01 on t01(id); insert into t01 SELECT s from generate_series(1,1000) as s; and random values: create table t02 (id bigint); create index i02 on t02(id); insert into t02 SELECT random()*100 from

Re: [GENERAL] SQL help - multiple aggregates

On Thu, Aug 18, 2016 at 10:56 AM, wrote: > I have a table cv with custid and vendid columns. Every entry represents the > purchase of a product > available from a specific vendor. > Now, for a set of "interesting" vendors, I would like to select a new table > custid, c415,

Re: [GENERAL] Re: Easiest way to compare the results of two queries row by row and column by column

On 17/08/16 10:58, gilad905 wrote: > Vik, note that your new suggestion for a query might be more > readable/efficient than Jeff's, but IT STILL DOESN'T SOLVE THE PROBLEM OF > DETECTING DUPLICATE ROWS. Yes, it does. And please don't shout at me. -- Vik Fearing

[GENERAL] SQL help - multiple aggregates

Hi, I have a table cv with custid and vendid columns. Every entry represents the purchase of a product available from a specific vendor. Now, for a set of "interesting" vendors, I would like to select a new table custid, c415, c983, c1256 based upon part queries select custid, count(vendid) as