PGSQL Postgres Update based on numeric range on a text column-CodePudding

I have a table with IDs, some have letters, most do not. And associated points with those IDs. The IDs are stored as text. I would like to add points to a given range of IDS that ARE integers and give them all the same points. 535.

I have found a way using a subquery to SELECT the data I need, but it appears that updating it is another matter. I would love to be able to only get data that is CAST-able without a subquery. however since it errors out when it touches something that isn't a number, that doesn't seem to be possible.

select * from (select idstring, amount from members where idstring ~ '^[0-9] $') x
WHERE
CAST(x.idstring AS BIGINT) >= 10137377001
and
CAST(x.idstring AS BIGINT) <= 10137377100

What am I doing ineficiently in the above, and how an I update the records that I want to? In a perfect world my statement would be as simple as:

UPDATE members SET amount = 535 
WHERE idstring >= 10137377001
AND idstring <= 10137377100

But since Idstring both contains entries that contain letters and is stored as text, it complicates things significantly. TRY_CAST would be perfect here, however there is no easy equivalent in postgres.

An example of the ids in the table might be

A52556B
36663256
6363632900B
3000525

ETC.

CodePudding user response：

You can use TO_NUMBER together with your regular expression predicate, like so:

UPDATE members
   SET amount = 535
 WHERE idstring ~ '^[0-9] $'
   AND to_number(idstring, '999999999999') BETWEEN 10137377001 AND 10137377100

Working example on dbfiddle

CodePudding user response：

You can encase the typecast and integer comparison within a CASE statement.

UPDATE members
SET amount = COALESCE(amount, 0)   535
WHERE CASE WHEN idstring ~ '^[0-9] $'
           THEN idstring::BIGINT BETWEEN 10137377001 AND 10137377100
           ELSE FALSE END;

Here I've assumed you might want to add 535 rather than setting it explicitly to 535 based on what you said above, but if my assumption is incorrect then SET amount = 535 is just fine.