Thread: 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

On Feb 9, 2015, at 8:53 PM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:
>
> the issue is, while DML its empty string and while SELECT its comparing with NULL

The issue is, empty string is NOT the same as null, and expecting select for null to match empty strings is a bug,
whichyou need to fix. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

sridhar bamandlapally 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

If this is the extent of your problem then you can add table triggers to
change the empty-string input so that the result of the insert/update is
NULL.  Then all of your selects can use IS NULL for their comparisons just
like they do now.

That is as "implicit" as you are going to get without actually fixing the
underlying problem.

David J.






--
View this message in context: http://postgresql.nabble.com/empty-string-Vs-NULL-tp5837188p5837308.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

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

SELECT s.pid, s.client_addr
   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port)
               ,pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
 WHERE s.pid = w.pid;
When i run it as a superuser, everything is ok, when i run it as an ordinary user, the client_addr is NULL. As the function pg_stat_get_wal_senders() returns the result, the problem is in receiving the address from pg_stat_get_activity.

Using/granting pg_stat_get_backend_client_addr() is not solving the problem.

Is there any way to get client_addr value running not as a superuser?


Regards, Mikhail

you can create a function with security differ option as superuser.

On Feb 10, 2015, at 3:40 AM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:
>
> The first contact of database migration/issues is DBA (admin),

This is a SQL usage issue, not a db admin issue, and so is most appropriate for the pgsql-general list. (Anyway, your
questionhas been answered 4 times now.) 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

=> \du zabbix
List of roles
Role name │ Attributes │ Member of
───────────┼─────────────┼───────────
zabbix │ Replication │ {}

[local] zabbix@postgres
=> select client_addr from pg_stat_replication;
client_addr
─────────────
NULL
(1 row)
Seems like for that moment function with security definer is the only solution, though it smells like workaround.


Вторник, 10 февраля 2015, 12:11 -02:00 от Luis Antonio Dias de Sá Junior <luisjunior.sa@gmail.com>: