I can't understand why the following NOT LIKE query removes not just the ones that end in '% Lake' but blank records too for certain data formats within both PostgreSQL with PostGIS and QGIS. This happens to the original FileGDB and my converted PostGIS table but works when the dataset is converted to Shapefile format.

I first noticed this when trying to run the query on the PostGIS table from pgAdmin4 and psql but also tried the from DBManager (in QGIS)

SELECT * FROM fwa.fwa_assessment_watersheds_poly
WHERE "gnis_name_1" NOT LIKE '% Lake'

So I tested in QGIS as a definition query.

# FileGDB
# 11252 records returned (incorrect)

# PostGIS
# 11252 records returned (incorrect)
"gnis_name_1" NOT LIKE '% Lake'

# Shapefile
# 19358 records returned (correct!)

You can see in the QGIS image below that the PostGIS dataset in orange removes additional blank records when on top of the Shapefile in light brown.

showing results of PostGIS and shapefile with the same definition query applied in qgis

The source dataset is available at ftp://ftp.geobc.gov.bc.ca/sections/outgoing/bmgs/FWA_Public/FWA_BC.gdb.zip - 2.3 GB

I've uploaded some additional test data for testing at a smaller size - the first two are the entire datasets and the last two are subsets:
fwa_assessment_watersheds_poly.gdb.zip - 116.13 MB
fwa_assessment_watersheds_full.shp.zip - 95.47 MB fwa_assessment_watersheds_selection.shp.zip - 2.38 MB fwa_assessment_watersheds_selection.gdb.zip - 3.24 MB

System info:
Windows 10 64-bit
PostgreSQL 9.6.1
PostGIS 2.3.1
QGIS 2.18.1

Tried from ArchLinux with same software versions as above.

  • 1
    I think it's due to the special treatment of NULL values in databases.
    – underdark
    Jan 20, 2017 at 20:09
  • A NULL cannot be compared in a LIKE or NOT LIKE statement, so will always be excluded. To specifically include them you'd need to use something like "gnis_name_1" NOT LIKE '% Lake' OR "gnis_name_1" IS NULL
    – Midavalo
    Jan 20, 2017 at 20:17
  • see my answer here gis.stackexchange.com/a/203475/64785
    – Midavalo
    Jan 20, 2017 at 20:21
  • @Midavalo Thanks! I saw that when searching but it specifically mentioned the = and != operators so thought it didn't apply to LIKE searches.
    – SaultDon
    Jan 20, 2017 at 20:26
  • @underdark In my mind I kept seeing them as simply blank because the attribute table in QGIS apparently shows NULL when it's an integer column, but not for text/varchar columns.
    – SaultDon
    Jan 20, 2017 at 20:26