PostgreSQL select value where '100%' using bigint

Question

I am trying to select data from a bigint column in PgAdmin using SQL:

SELECT FROM public.addressbaseplus WHERE uprn LIKE '100%'

However, I am getting the following error:

ERROR:  operator does not exist: bigint ~~ unknown
LINE 1: SELECT FROM public.addressbaseplus WHERE uprn LIKE '100%'
HINT:  No operator matches the given name and argument type(s). You might 
need to add explicit type casts.
SQL state: 42883
Character: 47

Can someone explain to me what I am doing wrong and the error appears?

Answer 1

The LIKE keyword in SQL operates on strings not numeric datatypes like bigints. Presumably you want all the items where the uprn field begins '100'.

There are 2 approaches:

1. Select a range of values: WHERE uprn < 200 AND uprn > 99, but this won't find values like 1001...
2. Cast the bigint to a string WHERE uprn::text LIKE '100%'

In the second case the :: is syntax for a type cast.

It's worth noting that issues with SQL that don't specifically link to GIS issues are often better searched for on DBA Stackexchange or stackoverflow.

Answer 2

You seem to compare a BIGINT column (uprn) with a string matching sequence, using the LIKE operator. That´s not going to work. You can cast the uprn column to text (... WHERE uprn::text LIKE '100%') to filter with regex matching.

Doinig this with the used statement, however, will match all values starting with 100 and followed by any character (or none), e.g. 1001, 10098274982374, 100; the % is a reserved wildcard for anything!

If you are looking for the string "100%" in a column (not your BIGINT column) you will have to escape the "%", i.e. ... WHERE <tst_column> LIKE '100\%' ESCAPE '\';