PostgreSQL remove numbers before first comma

Question

I am new to SQL however, I am looking to update a table where column called "scrap_value" and rows contains a set of numbers in an array e.g. {100000,125000,150000,175000,200000}

I want to remove the first number and comma of all rows in "scrap_value", so the end result looks like this {125000,150000,175000,200000}

How would I got about doing this?

Answer 1

That looks like an array. If that is the case, you can remove the first element of the array using an UPDATE statement:

update the_table
  scrap_value = scrap_value[2:];

[2:] selects all elements of the array starting with the second. The result of that is then used to override the existing array.

Answer 2

column called "scrap_value" ... contains a set of numbers e.g. {100000,125000,150000,175000,200000}

Whilst many DBMSs will support this sort of thing, it breaks basic Database Normalisation rules and, since you say you're just starting out with this sort of thing, let's set you on the Right Road to being with:

These values should be split out into a separate table containing the key of the original table, plus the new value, one per row. This makes it very easy to remove an item, using only a standard Delete statement:

select * from scraps; 
+-----------+--------+
| parent_id | value  | 
+-----------+--------+
|    123456 | 100000 | 
|    123456 | 125000 | 
|    123456 | 150000 | 
|    123456 | 175000 | 
|    123456 | 200000 | 
+-----------+--------+

delete 
from scraps 
where id = 123456 
and value = 175000 ;  

select * from scraps; 
+-----------+--------+
| parent_id | value  | 
+-----------+--------+
|    123456 | 100000 | 
|    123456 | 125000 | 
|    123456 | 150000 | 
|    123456 | 200000 | 
+-----------+--------+

This also makes it far, far easier to search for scrap values.

If you keep to the current "Lots-of-Values-all-Smooshed-Together" model, you have to do all sorts of weird, String manipulation to find an item that might be at the start of the string, or somewhere in the middle or at the end, all with different delimiters before and after the thing you're looking for. Yuck!.

Using the properly normalised structure, you can just use a standard where clause :

select * 
from scraps 
where parent_id = 123456
and value = '150000 ; 

select * 
from scraps; 

+-----------+--------+
| parent_id | value  | 
+-----------+--------+
|    123456 | 150000 | 
+-----------+--------+

To match more than scrap, things get a little more exciting:

select * 
from scraps
where parent_id = 123456 
and value in ( 100000, 200000 ); 

+-----------+--------+
| parent_id | value  | 
+-----------+--------+
|    123456 | 200000 | 
|    123456 | 100000 | 
+-----------+--------+

Regards, Phill W.