[SQL] difference between EXCEPT and NOT IN?
Hi, The 2 following statements don't give the same result. I expected the second ti give the exact same result as the first one. What am I missing? development= SELECT id FROM entrees except select entree_id from postes ORDER BY id desc; id -- 3651 (1 row) development= SELECT id FROM entrees WHERE id not in (select entree_id from postes) ORDER BY id desc; id (0 rows) thanks in advance for the help. Raph -- Web database: http://www.myowndb.com Free Software Developers Meeting: http://www.fosdem.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] difference between EXCEPT and NOT IN?
On Tue, 1 Apr 2008, Raphael Bauduin wrote: The 2 following statements don't give the same result. I expected the second ti give the exact same result as the first one. If any entree_id can be NULL they aren't defined to give the same result. EXCEPT is defined in terms of duplicates based on distinctness, and for example (1 is distinct from 1) is false, (1 is distinct from NULL) is true and (NULL is distinct from NULL) if false. NOT IN is defined in terms of equality, and for example, (1=1) is true, (1=NULL) is unknown and (NULL=NULL) is unknown. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] difference between EXCEPT and NOT IN?
On Tue, Apr 1, 2008 at 6:04 PM, Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 1 Apr 2008, Raphael Bauduin wrote: The 2 following statements don't give the same result. I expected the second ti give the exact same result as the first one. If any entree_id can be NULL they aren't defined to give the same result. EXCEPT is defined in terms of duplicates based on distinctness, and for example (1 is distinct from 1) is false, (1 is distinct from NULL) is true and (NULL is distinct from NULL) if false. NOT IN is defined in terms of equality, and for example, (1=1) is true, (1=NULL) is unknown and (NULL=NULL) is unknown. My problem came from 2 entries in the table postes that had an entree_id NULL Thanks for your fast answer, it has helped me spot the problem! Raph -- Web database: http://www.myowndb.com Free Software Developers Meeting: http://www.fosdem.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Sequential non unique IDs
I have the following scenario:
A 'task' table that has the fields:
id = primary key, updated on each insert using a sequence
customerid = integer
localid = integer
I need the localid to be sequential and unique per unique customerid.
The data needs to look like this:
1, 92, 1
2, 92, 2
3, 93, 1
4, 93, 2
5, 93, 3
6, 92, 3
and so on
I am presently doing this on the INSERT using an INNER SELECT, like
this:
INSERT INTO task (id, customerid, localid) VALUES (nextval('task_id'),
92, (SELECT MAX(localid) + 1 FROM task WHERE customerid = 92));
The problem with this query is that if two INSERTS are performed at the
same time for the same customerid (which is happening more than you
would think), than both records end up with the same localid.
I've already considered:
1. I can't lock the table, because there are too many inserts happening
and it will slow down the app.
2. I can't use temporary sequence tables because they only exist per
session, and each insert statement belongs to a seperate session.
3. I could create a sequence table for every customerid (i.e. CREATE
SEQUENCE task_id_92) but then I would end up with thousands of sequence
tables.
4. Sequence tables wont' rollback with the transaction, so any db error
would create a gap in the localid sequence. This is not ideal, but I
suppose I could live with it (but would prefer not to)
Anyone know a solution for this? This can't be the first time anyone has
ever tried to do this. Thanks!
- John
Re: [SQL] Sequential non unique IDs
John Reeve wrote:
I've already considered:
1. I can't lock the table, because there are too many inserts happening
and it will slow down the app.
In a locking approach you may not need to lock the whole table. You
should only need to lock the entry for the customer being altered, eg:
BEGIN;
SELECT 1 FROM task WHERE customerid = 92 FOR UPDATE;
INSERT INTO task (id, customerid, localid) VALUES (nextval('task_id'),
92, (SELECT MAX(localid) + 1 FROM task WHERE customerid = 92));
If I'm not mistaken, that should ensure that for any given customer ID
there's only one transaction holding the locks on that customer.
It won't prevent SELECTs from reading the customer's records, but you
don't mind that so long as they're not using the customer's records to
determine the least free localid. That'll help reduce the hit on your
app's performance, too. If you do mind the , use SELECT ... FOR SHARE
and you'll wait on the FOR UPDATE lock if one is active (however, it
might prove hard to obtain a FOR UPDATE lock if there are lots of FOR
SHARE operations active).
I *think* that'll work, but you should of course test and investigate
before doing anything as crazy as taking my word for it.
Anyone know a solution for this? This can't be the first time anyone has
ever tried to do this. Thanks!
If I'm not mistaken about the similarity, you might want to search the
archives for the thread Primary Key with serial. It might be informative.
--
Craig Ringer
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Sequential non unique IDs
On Tue, 1 Apr 2008, John Reeve [EMAIL PROTECTED] writes:
I have the following scenario:
A 'task' table that has the fields:
id = primary key, updated on each insert using a sequence
customerid = integer
localid = integer
I need the localid to be sequential and unique per unique customerid. The
data needs to look like this:
1, 92, 1
2, 92, 2
3, 93, 1
4, 93, 2
5, 93, 3
6, 92, 3
and so on
I am presently doing this on the INSERT using an INNER SELECT, like this:
INSERT INTO task (id, customerid, localid) VALUES (nextval('task_id'),
92, (SELECT MAX(localid) + 1 FROM task WHERE customerid = 92));
Why not creating a separate serial for localid field? It won't
contradict with your making localid to be sequential and unique per
unique customerid restriction.
CREATE TABLE task (
idserialPRIMARY KEY,
customeridinteger,
localid serial
);
CREATE UNIQUE INDEX task_customerid_localid_idx
ON task (customerid, localid);
INSERT INTO task (customerid) VALUES (92);
If I didn't get you wrong, this should solve your problem.
Regards.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
