previous table definition guess no not null of default ''
curreent table definitionCREATE TABLE apps
(
Id int not null unique, -- AppId
Sym varchar(8) not null default '', -- app symbol name
Nam varchar(30) not null default '', -- app full name
BitPriv varchar(10) not null default '', -- app bit priv
NamPriv varchar(255) not null default '', -- app nam priv
AppDir varchar(80) not null default '', -- app dir nam
AppPag varchar(32), -- app page nam
Aka int, -- ptr to real name
Uid int not null default 0, -- ptr to las mod user id
Dlm timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE apps
(
Id int not null unique, -- AppId
Sym varchar(8) not null default '', -- app symbol name
Nam varchar(30) not null default '', -- app full name
BitPriv varchar(10) not null default '', -- app bit priv
NamPriv varchar(255) not null default '', -- app nam priv
AppDir varchar(80) not null default '', -- app dir nam
AppPag varchar(32) not null default '', -- app page nam
Aka int, -- ptr to real name
Uid int not null default 0, -- ptr to las mod user id
Dlm timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);
query on pqsql postgresql7-7.0.2.k62-8 redhat 7.2
select * from apps where (not (apppag = null or apppage = ''));
returns data not sure if current table actualy has not null default ''
on apppag field
but I know that was the reason for the query being as it was
query on pgsql postgresql 7.3.2-3 redhat 9.0 with new table definition
select * from apps where (not (apppag = null or apppag = ''))
returns nothing 0 rows
select * from apps where (not apppag = null);
returns nothing when it should return every thing
select * from apps where (not apppag = '');
returns data as expected
Lessons learned as programer always check database definitions only
check for what is possible. (not my style I like to write paranoid code)
(never trust the dba)
lessens learned as a dba always prevent as much illeagle, unwanted data
as possible. (I try when ever I get the enough time to spend on being a
dba)
and finialy the programer and the dba should talk often to optimize code
espesialy when there the same person.
Arno
