Thread: BUG #13747: Like clause ignoring raws with null value
The following bug has been logged on the website: Bug reference: 13747 Logged by: Denys Tyshetskyy Email address: dtyshecky@gmail.com PostgreSQL version: 9.2.0 Operating system: ubuntu Description: I am executing select with like clause on the field X (select * from table where x like '%ABC%') In result I don't receive the raws with null in X column so I have to manually state (where X is null or X like ...) I would expect to receive columns with null.
On Fri, Oct 30, 2015 at 12:08 AM, <dtyshecky@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 13747 > Logged by: Denys Tyshetskyy > Email address: dtyshecky@gmail.com > PostgreSQL version: 9.2.0 > Operating system: ubuntu > Description: > > I am executing select with like clause on the field X (select * from table > where x like '%ABC%') > > In result I don't receive the raws with null in X column so I have to > manually state (where X is null or X like ...) > > I would expect to receive columns with null. > But why? I would understand if your pattern was '%', but it's really strange to expect that with the pattern you've shown. -- Alex
On Fri, Oct 30, 2015 at 12:08 AM, <dtyshecky@gmail.com> wrote:
--
I am executing select with like clause on the field X (select * from table
where x like '%ABC%')
In result I don't receive the raws with null in X column so I have to
manually state (where X is null or X like ...)
I would expect to receive columns with null.
I don't understand your reasons either, but you can achieve what you want with:
select * from table where coalesce(x, 'ABC') like '%ABC%'
select * from table where coalesce(x, 'ABC') like '%ABC%'
but I find that less clear (and maybe a little bit less performant than) than:
select * from table where x like '%ABC%' or x is null
select * from table where x like '%ABC%' or x is null
--
Félix