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
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
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, c983, c1256
> based up
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 ge
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 PGPASSWORD parameter on th
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 dat
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
> rebalanced after each insertion.
>
>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)
Bett
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 fillfactor to minimize page sp
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, c
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.
https://www.postgres
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 give it a try later
Do
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 into t02 SELECT ra
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:
https://wiki.pos
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:
> https://wiki.postgresql.org/wiki/Skip32
> F
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, bo
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 i want to link with a
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, not updates on table b.
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
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
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 * from generate_
On Fri, Aug 19, 2016 at 1:31 PM, Sameer Kumar
wrote:
>
>
> 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
22 matches
Mail list logo
