(postgre)sql gotchas

i would like to write about a things that are not really errors by themselves – they are simply situations when database acts in undesired way. a way that's perfectly logical, but where the logic is not always clearly seen “at first sight".

so, don't expect any postgresql-bashing. if you want some, indicate so in comments, i'll find some things to bash postgresql for.

but in here i'd rather bash us – people – for making mistakes. the ones that are not really easy to see as mistakes for database.

first gotcha is the one that bite me some time ago, but was engreved in my brain so hard, that i recently even thought i saw the same problem in situation where it simply wasn't.

let's see this simple sql:

SELECT*FROM x WHERE i ! IN(3,5);

do you see the problem?

if yes – congratulations. you're the lucky one :). i see it clearly now, but i wasn't seeing it as clearly some time ago.

what is the problem, some of you might ask?

technically – it is possible that there is no problem – it all depends on what you wanted to do.

so my query was trying to: select * from x where (i!) in (3,5). there are no such values that would have factorial of 3 or 5.

now – you might say it's obvious, and nobody should write “!" in sql – we should use “not". and you're right. i know. but once in a while you just write this kind of thing, and then you spend some time wondering – what's wrong with pg, that is breaks on such an easy query.

it might get even worse. let's imagine you're doing something like this on a table which holds large numbers. or simply with larger number of rows. oops 🙂

second gotcha for today is even simpler.

i was asked to check for some things and i noticed query (about 5 lines long) with a lot of joins. and this condition:

... where ... and date_field > 2007-01-01 and some_other_field = 1 and ...

again – do you see the problem? i'm sure most of you do. if not, let's see something simple:

does it help? sure it does. it will most probably break some existing application. break in a positive way – they have a code that doesn't really do what they think it does, but in 8.3 it will raise exception.

last gotcha for today is a nasty one. i actually wrote about it some time ago (in polish, as a brain teaser), but lately found exactly the same situation in real-life code.

why? because it become so called ‘correlated subquery' – i'm not good at teaching, so if you don't understand it search google, or ask your sql guru – sorry, but i'm really lousy at explaining things.

technically – postgresql did what it was told to. was it the desired outcome? not really likely. we can't remove correlated subqueries from postgresql because they are highly useful.

how can we protect ourselves from this kind of typos? 2 things:

don't name columns in such a way!

always use table aliasing. then you will have to make 2 typos to get in this situation. aliased query: select f.* from first f where f.text1 in (select s.text1 from second s); will break because now we tell postgresql exactly from which table it should get the field. and in second table there is no text1 field.

so, as i said earlier – nothing really spectacular. nothing to say “postgresql is bad, because it breaks when …". just some “gotchas" 🙂

with some “luck" i will write about next gotchas some other day – life is full of human errors.

3 thoughts on “(postgre)sql gotchas”

I’m surprised no one nitpicked you on this, but in your final query, it will actually error first because you didn’t alias the table first first. If you add an f after first, it would give the error you’re looking for.