1

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?

  • and, btw., SELECT <nothing_here> FROM ... selects no column... – ThingumaBob Oct 11 '18 at 10:07
  • @Hutch you should give DBeaver or DataGrip a try instead of PGAdmin4... a much better user experience for analysis. – DPSSpatial Oct 11 '18 at 17:51
  • Your title has '%100' and the question has '100%'. These are very different problems. – Vince Oct 11 '18 at 17:57
3

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.

1

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 '\';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.