problem with inserting a generated seq no into not null field

[mike]

I have the following generic query which I am trying to use to insert
into a 3 field table

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');

However the first entry will not go in with a

'null value in column "file_no" violates not-null constraint'

message

Anyone any ideas on this - the CASE statement does not seem to be being
evaluated

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Manfred Koizar]

On Tue, 08 Jun 2004 12:37:48 +0100, mike <mi**@bristolreccc.co.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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Manfred Koizar]

On Tue, 08 Jun 2004 12:37:48 +0100, mike <mi**@bristolreccc.co.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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Stephan Szabo]

I have the following generic query which I am trying to use to insert
into a 3 field table

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

That's not really a good way to detect the existance of a row with those
values. If either subj or area is allowed to be NULL, wierdness occurs
(*). Even if neither is allowed to be null, if the values can ever have
more than one digit wierdness occurs.

(*) - IIRC, even if no row exists with (4,8) as (subj,area), if there's a
row where subj || area is NULL, you will never get true from the NOT IN
clause and the second alternative will always be chosen. NULLs and IN
don't play very well together.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

I have the following generic query which I am trying to use to insert
into a 3 field table

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

That's not really a good way to detect the existance of a row with those
values. If either subj or area is allowed to be NULL, wierdness occurs
(*). Even if neither is allowed to be null, if the values can ever have
more than one digit wierdness occurs.

(*) - IIRC, even if no row exists with (4,8) as (subj,area), if there's a
row where subj || area is NULL, you will never get true from the NOT IN
clause and the second alternative will always be chosen. NULLs and IN
don't play very well together.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings