Re: insert with select as value

You can use the COALESCE function, like this:

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

This function returns the first of its argument that is not null. If your
query returns no value, the second argument (in this case, the number
"1").


Marcelo Soares
Informática - Master Hotéis
ICQ Externo: 19398317
ICQ Interno: 1002
Linux user#: 288006
PGP Key: http://gravatai.ulbra.tche.br/~ringostar/MS-PubOficial.pgp
------------------------------------------------------------------
"Não há limite para a cultura, Watson. O campo de nossas experiências é
uma série de lições das quais a maior é sempre a última." Sherlock Holmes
(A.C.Doyle)
------------------------------------------------------------------

> I need to insert a value = max(value)+1, where max is a select limited
> by a 'where' clause. Like this:
>
> INSERT INTO table (idthread, idsection,txt)
> VALUES (
>    (SELECT max(idthread)+1 FROM table WHERE  idsection = 'CZE'),  'CZE',
> 'sample text')
> );
>
> 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?
>
> --
> Milos Prudek
> _________________
> Most websites are
> confused chintzy gaudy conflicting tacky unpleasant... unusable.
> Learn how usable YOUR website is! http://www.spoxdesign.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>