Обсуждение: Vs NULL

Its been a while since I really worked with Postgres, but could you write a trigger to convert empty string to null on save?  You'd have to carefully apply it everywhere but it'd get you the searching for null finds empty. If that is all you do the you've got it.

Essentially, there isn't a switch for it but you can do it with some mechanisms.

Nik

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.bn1@gmail.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.Mamin@intershop.de> wrote:
>>
>>
>> >>>Hi
>> >>>
>> >>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally
>> >>> <sridhar.bn1@gmail.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