Secret Santa List

On 23 December 2015 at 16:49, Lou Duchez <lou@paprikash.com> wrote:

I have a company with four employees who participate in a Secret Santa
program, where each buys a gift for an employee chosen at random. (For
now, I do not mind if an employee ends up buying a gift for himself.) How
can I make this work with an SQL statement?

Here is my Secret Santa table:

--
create table secretsanta
(giver text,
recipient text,
primary key (giver));

insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'),
('Earl');
--

Here is the SQL statement I am using to populate the "recipient" column:

--
update secretsanta set recipient =
( select giver from secretsanta s2 where not exists (select * from
secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );
--

The problem: every time I run this, a single name is chosen at random and
used to populate all the rows. So all four rows will get a recipient of
"Steve" or "Earl" or whatever single name is chosen at random.

Why not generate the required results in a SELECT then update from that.
row_number() could allow you to generate a random number to each giver,
then we can generate another random number and join to each random number.
That'll give you a giver and recipient combination.

e.g:

select giver,recipient from
(select row_number() over (order by random()) rn, giver from secretsanta) g
inner join
(select row_number() over (order by random()) rn, giver recipient from
secretsanta) r on g.rn = r.rn

You can then wrap that up in a CTE, something along the lines of:

with cte (giver, recipient) as (
select giver,recipient from
(select row_number() over (order by random()) rn, giver from secretsanta) g
inner join
(select row_number() over (order by random()) rn, giver recipient from
secretsanta) r on g.rn = r.rn
)
update secretsanta set recipient = cte.recipient from cte WHERE cte.giver =
secretsanta.giver;

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 23 December 2015 at 16:49, Lou Duchez <lou@paprikash.com> wrote:

I have a company with four employees who participate in a Secret Santa
program, where each buys a gift for an employee chosen at random. (For
now, I do not mind if an employee ends up buying a gift for himself.) How
can I make this work with an SQL statement?

Here is the SQL statement I am using to populate the "recipient" column:

--
update secretsanta set recipient =
( select giver from secretsanta s2 where not exists (select * from
secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );
--

The problem: every time I run this, a single name is chosen at random and
used to populate all the rows. So all four rows will get a recipient of
"Steve" or "Earl" or whatever single name is chosen at random.

Of course: you can't UPDATE a field with a query returning more than one
result, as you can check easily trying:

update secretsanta set recipient=(select giver from secretsanta);

You could get a list of givers in no particular order (e. g. "select giver
from secretsanta order by md5(concat(giver,current_time))") then setting
each employee as next's employee giver.

--
Alberto Cabello Sánchez
Universidad de Extremadura

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Of course: you can't UPDATE a field with a query returning more than one
result, as you can check easily trying:

I understand that, and my query does not return more than one result. The problem is that it returns THE SAME result each time, most likely because the subquery is evaluated exactly once and then the main query uses that single result over and over.

update secretsanta set recipient =
( select giver from secretsanta s2 where not exists (select * from
secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );

My hope is to somehow persuade PostgreSQL to re-evaluate the subquery each time, and see that the set of available recipients has changed. If "Steve" was picked for the first row, "Steve" shouldn't be available for any subsequent row. If "Fred" was picked for the second row, neither "Steve" nor "Fred" should be available for any subsequent row.

You could get a list of givers in no particular order (e. g. "select giver
from secretsanta order by md5(concat(giver,current_time))") then setting
each employee as next's employee giver.

As in, write a loop in some programming language to update the table one row at a time, or did you envision a way to do this with an SQL statement? I can certainly write a loop, if that's the only solution.

Thanks!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Dec 23, 2015 at 04:32:34AM -0500, Lou Duchez wrote:

Of course: you can't UPDATE a field with a query returning more than one
result, as you can check easily trying:

I understand that, and my query does not return more than one result. The
problem is that it returns THE SAME result each time, most likely because
the subquery is evaluated exactly once and then the main query uses that
single result over and over.

update secretsanta set recipient =
( select giver from secretsanta s2 where not exists (select * from
secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );

My hope is to somehow persuade PostgreSQL to re-evaluate the subquery each
time, and see that the set of available recipients has changed.

I see. As in most programming languages, the () clause is evaluated once
and the result used instead.

You could get a list of givers in no particular order (e. g. "select giver
from secretsanta order by md5(concat(giver,current_time))") then setting
each employee as next's employee giver.

As in, write a loop in some programming language to update the table one row
at a time, or did you envision a way to do this with an SQL statement? I can
certainly write a loop, if that's the only solution.

I'm not aware of a SQL statement to do that. Maybe you will be able do it with
a CTE, as you can make a table with a field (1, 2, 3, 4) and take advantage of
the integer arithmetic (one problem with your original question is I don't know
how to ask for the next employee in plain SQL when the ID is a given name).

--
Alberto Cabello Sánchez
Universidad de Extremadura

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Lou Duchez schrieb am 23.12.2015 um 04:49:

I have a company with four employees who participate in a Secret
Santa program, where each buys a gift for an employee chosen at
random. (For now, I do not mind if an employee ends up buying a gift
for himself.) How can I make this work with an SQL statement?

Here is my Secret Santa table:

-- create table secretsanta (giver text, recipient text, primary key
(giver));

insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'),
('Earl'); --

Here is the SQL statement I am using to populate the "recipient"
column:

-- update secretsanta set recipient = ( select giver from secretsanta
s2 where not exists (select * from secretsanta s3 where s3.recipient
= s2.giver) order by random() limit 1 ); --

The problem: every time I run this, a single name is chosen at random
and used to populate all the rows. So all four rows will get a
recipient of "Steve" or "Earl" or whatever single name is chosen at
random.

I suppose the problem is that the "exists" subquery does not
re-evaluate for each record. How do I prevent this from happening?
Can I use a "lateral" join of some kind, or somehow tell PostgreSQL
to not be so optimized?

You can populate the table with a single statement:

with people (name) as (
values ('Frank'), ('Joe'), ('Steve'), ('Earl')
)
insert into secretsanta (giver, recipient)
select distinct on (n1.name) n1.name, n2.name
from people n1
join people n2 on n1.name <> n2.name
order by n1.name;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Dec 22, 2015 at 9:49 PM, Lou Duchez <lou@paprikash.com> wrote:

I have a company with four employees who participate in a Secret Santa
program, where each buys a gift for an employee chosen at random. (For now,
I do not mind if an employee ends up buying a gift for himself.) How can I
make this work with an SQL statement?

Here is my Secret Santa table:

--
create table secretsanta
(giver text,
recipient text,
primary key (giver));

insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'),
('Earl');
--

with
g as (select giver, row_number() over () as rownum from secretsanta),
r as (select giver, row_number() over () as rownum from (select
giver from secretsanta order by random()) as x)
update secretsanta
set recipient = r.giver
from g join r on g.rownum = r.rownum
where secretsanta.giver = g.giver;

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Why not generate the required results in a SELECT then update from
that. row_number() could allow you to generate a random number to each
giver, then we can generate another random number and join to each
random number. That'll give you a giver and recipient combination.

e.g:

select giver,recipient from
(select row_number() over (order by random()) rn, giver from
secretsanta) g
inner join
(select row_number() over (order by random()) rn, giver recipient from
secretsanta) r on g.rn = r.rn

You can then wrap that up in a CTE, something along the lines of:

with cte (giver, recipient) as (
select giver,recipient from
(select row_number() over (order by random()) rn, giver from
secretsanta) g
inner join
(select row_number() over (order by random()) rn, giver recipient from
secretsanta) r on g.rn = r.rn
)
update secretsanta set recipient = cte.recipient from cte WHERE
cte.giver = secretsanta.giver;

Hey, I think that works! Thanks!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general