Re: problem with inserting a generated seq no into not null field

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: mike <mike(at)bristolreccc(dot)co(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: problem with inserting a generated seq no into not null field
Date: 2004-06-08 12:48:52
Message-ID: a4dbc0hqsk2l1khvhp3qd37rt44hij2cd3@email.aon.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, 08 Jun 2004 12:37:48 +0100, mike <mike(at)bristolreccc(dot)co(dot)uk>
wrote:
>INSERT INTO tb_files (subj, area, file_no) select '4', '8',(SELECT CASE
>WHEN (4||8) NOT IN (SELECT (subj||area) FROM tb_files) THEN '1' ELSE
>max(file_no)+1 END FROM tb_files GROUP BY (subj||area),subj,area HAVING
>subj = '4' AND area = '8');

If there is no row with subj = '4' AND area = '8', then SELECT ...
HAVING returns no rows and therefore max(file_no) is NULL. And NULL+1
is NULL. Try

INSERT INTO tb_files (subj, area, file_no)
SELECT '4', '8', coalesce(max(file_no), 0)+1
FROM tb_files
WHERE subj = '4' AND area = '8';

Servus
Manfred

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2004-06-08 13:08:24 Re: Index on points
Previous Message Jan Wieck 2004-06-08 12:46:59 Re: [GENERAL] The pgreplication project