Re: [PERFORM] <empty string> Vs NULL

Hi,

Please take this to another list, this has little to do with
PostgreSQL admin or performance.

Florent

On Tue, Feb 10, 2015 at 4:53 AM, sridhar bamandlapally
<sridhar(dot)bn1(at)gmail(dot)com> wrote:
> In application code is
>
> while inserting/updating: INSERT/UPDATE into ... ( '' ) - which is empty
> string in PG, and in Oracle its NULL
>
> while selecting: SELECT ... WHERE column IS NULL / NOT NULL
>
> the issue is, while DML its empty string and while SELECT its comparing with
> NULL
>
>
>
>
>
> On Mon, Feb 9, 2015 at 6:32 PM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
>>
>>
>> >>>Hi
>> >>>
>> >>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally
>> >>> <sridhar(dot)bn1(at)gmail(dot)com>:
>> >>>
>> >>> Hi All
>> >>>
>> >>> We are testing our Oracle compatible business applications on
>> >>> PostgreSQL database,
>> >>>
>> >>> the issue we are facing is <empty string> Vs NULL
>> >>>
>> >>> In Oracle '' (<empty string>) and NULL are treated as NULL
>> >>>
>> >>> but, in PostgreSQL '' <empty string> not treated as NULL
>> >>>
>> >>> I need some implicit way in PostgreSQL where ''<empty string> can
>> >>> be treated as NULL
>> >
>> >>It is not possible in PostgreSQL. PostgreSQL respects ANSI SQL standard
>> >> - Oracle not.
>> >>
>> >>Regards
>> >>
>> >>Pavel
>> >>
>> >>p.s. theoretically you can overwrite a type operators to support Oracle
>> >> behave, but you should not be sure about unexpected negative side effects.
>> >
>> >
>> >A clean way would be to disallow empty strings on the PG side.
>> >This is somewhat combersome depending on how dynamic your model is
>> >and add some last on your db though.
>>
>> hmm, you could also consider disallowing NULLs, i.e. force empty strings.
>> this may result in a better compatibility although unwise from postgres
>> point of view (see null storage in PG)
>> and neither way allow a compatibility out of the box:
>>
>> Postgres ORACLE
>> '' IS NULL false true
>> NULL || 'foo' NULL 'foo'
>>
>> as mention in another post, you need to check/fix your application.
>>
>> >
>> >ALTER TABLE tablename ADD CONSTRAINT tablename_not_empty_ck
>> > CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL
>> > ...) IS NULL)
>>
>> oops, this shold be
>> CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL
>> ...))
>>
>> >
>> >-- and to ensure compatibility with your app or migration:
>> >
>> >CREATE OR REPLACE FUNCTION tablename_setnull_trf()
>> > RETURNS trigger AS
>> >$BODY$
>> >BEGIN
>> >-- for all *string* columns
>> > NEW.colname1 = NULLIF (colname1,'');
>> > NEW.colname2 = NULLIF (colname2,'');
>> > NEW.colname3 = NULLIF (colname3,'');
>> >RETURN NEW;
>> >END;
>> >$BODY$
>> >
>> >CREATE TRIGGER tablename_setnull_tr
>> > BEFORE INSERT OR UPDATE
>> > ON tablename
>> > FOR EACH ROW
>> > EXECUTE PROCEDURE tablename_setnull_trf();
>> >
>> >You can query the pg catalog to generate all required statements.
>> >A possible issue is the order in which triggers are fired, when more than
>> > one exist for a given table:
>> >"If more than one trigger is defined for the same event on the same
>> > relation, the triggers will be fired in alphabetical order by trigger name"
>> >( http://www.postgresql.org/docs/9.3/static/trigger-definition.html )
>> >
>> >regards,
>> >
>> >Marc Mamin
>
>

--
Florent Guillaume, Director of R&D, Nuxeo
Open Source Content Management Platform for Business Apps
http://www.nuxeo.com http://community.nuxeo.com