Re: Robust ways for checking allowed values in a column

[Guillaume Lelarge <guillaume@xxxxxxxxxxxx>]

Le mar. 25 janv. 2022 à 14:56, Shaozhong SHI <shishaozhong@xxxxxxxxx> a écrit :

I tried the following:

 select form from mytable  where form ~ '^Canal$|^Drain$|^Foreshore$|^inlandRiver$|^Lake$|^lockOrFlightOfLocks$|^Marsh$|^Researvoir$|^Sea$|^tidalRiver$|^Transfer$'

I used ^ and $ to ensure checking of allowed values.

However, 'Backyard' was selected.

Why is that?

It works for me:

# select 'Backyard' ~ '^Canal$|^Drain$|^Foreshore$|^inlandRiver$|^Lake$|^lockOrFlightOfLocks$|^Marsh$|^Researvoir$|^Sea$|^tidalRiver$|^Transfer$';
┌──────────┐
│ ?column? │
├──────────┤
│ f        │
└──────────┘
(1 row)

So you will probably need a complete and reproducible example so that we could test it.

--

Guillaume.