PGSQL Postgres Update based on numeric range on a text column

Question

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.

I found out that you don't need to do any conversion with postgresql. It just natively converts text to numbers where possible when you use the < and > operands. Much simpler but completely hidden. — Terst Mann
– Terst Mann, Commented Mar 5, 2022 at 21:19

MarcinJ · Accepted Answer · 2022-02-20 23:56:27Z

1

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

answered Feb 20, 2022 at 23:56

MarcinJ

3,6592 gold badges16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eli Johnson · Accepted Answer · 2022-02-20 23:58:59Z

0

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.

answered Feb 20, 2022 at 23:58

Eli Johnson

3602 silver badges11 bronze badges

Collectives™ on Stack Overflow

PGSQL Postgres Update based on numeric range on a text column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related