[SQL] finding (and recycling) holes in sequences
If one has a unique-id generating sequence that sometimes is bound to have holes in it (ie: it could happen that a nextval(seq) happens without a corresponding INSERT in the table), then how could one efficiently scan for these holes to recycle them in subsequent INSERTs? I'm just looking for a "standard" way of doing this if such a thing exists. TIA -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr "For a list of the ways which technology has failed to improve our quality of life, press 3."
[SQL] JOIN syntax
Looking at SQL92, it seems that I ought to be able to do this:
SELECT * FROM invoice INNER JOIN (SELECT * FROM invoice_line WHERE lineno > 1)
ON invoice.invno = invoice_line.invno;
ERROR: parser: parse error at or near "("
(I know it can be successfully expressed in a more complex WHERE clause).
Is there anything wrong with the syntax, or is it a missing feature?
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Honour thy father and mother; which is the first
commandment with promise; That it may be well with
thee, and thou mayest live long on the earth."
Ephesians 6:2,3
Re: [SQL] JOIN syntax
> Looking at SQL92, it seems that I ought to be able to do this:
> SELECT * FROM invoice INNER JOIN
> (SELECT * FROM invoice_line WHERE lineno > 1)
> ON invoice.invno = invoice_line.invno;
> ERROR: parser: parse error at or near "("
Wrong syntax (for Postgres anyway). We don't yet have subselects in the
syntax. Thanks for the example though; it seems like some variant should
be allowed, though perhaps not as written above.
Something like
select * from invoice inner join
(select * from invoice_line where lineno > 1)
as IL (invno, yada)
on invoice.invno = IL.invno;
seems like it could be legal. Your variant of this may have trouble
hooking up invoice_line.invno after the subselect, since the subselect
result may lose the linkage with the underlying input table.
But, all this is theoretical, since Postgres doesn't yet do the right
thing at all.
- Thomas
