Why does the Postgres position() function give different results?

Question

I am baffled at to why the Postgresql position function gives different results for what seems to be a straightforward test.

Here is query #1:

SELECT count(*)
FROM 
    dnasample D, ibg_studies ST, subjects S
WHERE 
    D.studyindex=ST.studyindex
    AND ST.studyabrv='CONGER'
    AND D.subjectidkey=S.id
    AND D.projectindex IS NULL
    AND POSITION('Previous subjectid:' in D.comment) IS NULL

which returns a result of 246.

Then here is query #2:

SELECT count(*)
FROM 
    dnasample D, ibg_studies ST, subjects S
WHERE 
    D.studyindex=ST.studyindex
    AND ST.studyabrv='CONGER'
    AND D.subjectidkey=S.id
    AND D.projectindex IS NULL
    AND POSITION('Previous subjectid:' in D.comment)=0

I do not see why these return such different results?

I've tried reading the Postgres documentation to clarify the difference between zero and the null string, but not much luck there yet...

Thanks in advance, --Rick

mu is too short · Accepted Answer · 2011-12-19 23:09:53Z

5

The position(needle in haystack) function will return:

Zero if needle is not NULL and not in haystack and haystack is not NULL.
A positive value if needle is in haystack with one being the beginning of haystack.
NULL if needle or haystack are NULL.

For example:

=> select position('a'  in 'a' ) as a,
          position('a'  in 'b' ) as b,
          position('a'  in null) as n1,
          position(null in 'a' ) as n2,
          position(null in null) as n3;
 a | b | n1 | n2 | n3 
---+---+----+----+----
 1 | 0 |    |    |

That empty spots under n1 through n3 are NULLs.

So this condition:

POSITION('Previous subjectid:' in D.comment) IS NULL

is equivalent to:

D.comment IS NULL

whereas this:

POSITION('Previous subjectid:' in D.comment)=0

will be true if and only if D.comment IS NOT NULL and D.comment does not contain 'Previous subjectid:'.

So the two conditions are quite different.

answered Dec 19, 2011 at 23:09

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rixter Over a year ago

Thanks; I understand the difference now; great answer.

rixter Over a year ago

This question has been answered...no more are needed!

DonCallisto · Accepted Answer · 2011-12-19 22:46:44Z

1

NULL is actually a no value. 0 is a pool of bit data.

This is the difference.

answered Dec 19, 2011 at 22:46

DonCallisto

30k9 gold badges74 silver badges104 bronze badges

Collectives™ on Stack Overflow

Why does the Postgres position() function give different results?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related