Re: insert with select as value

On Tue, Jun 22, 2004 at 16:22:33 +0200,
Milos Prudek <prudek(at)bvx(dot)cz> wrote:
> I need to insert a value = max(value)+1, where max is a select limited
> by a 'where' clause. Like this:

If your purpose in doing this is just to generate unique keys, you should
be using sequences instead.

>
> INSERT INTO table (idthread, idsection,txt)
> VALUES (
> (SELECT max(idthread)+1 FROM table WHERE idsection = 'CZE'), 'CZE',
> 'sample text')
> );

Note that you probably want to lock the table before doing this or
two transactions running at the same time can generate the same
value for idthread.

>
> This works fine, except when the result of SELECT is empty - which is
> true when the table is empty.
>
> Is it possible to create a "SELECT max(idthread)+1 FROM table WHERE
> idsection = 'CZE';" that will return value 1 instead of value None if
> the SELECT has no results?

You could either right your own max function, or you can use coallesce.
For example:
SELECT coallesce(max(idthread),0)+1 FROM table WHERE idsection = 'CZE';

If there is a compound index on idthread and idsection, then you are probably
better off using something like the following to take advantage of the index:
coallesce((SELECT idthread FROM table WHERE idsection = 'CZE' ORDER BY
idthread DESC, idsection DESC LIMT 1))+1
(You need to list idthread and idsection in the ORDER BY clause in the
same order they are listed in the index.)