Question 217550 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Split and sum in some cases

I need you infinite knowledge. I have some kind of data in a column (text)

state
0
0,0
13
13,27,0
0,0,0

Is a text column, but it can have a number, or many numbers separate by commas (not defined how long). The number 0 means OK and any other number is a code error. When it has more than one number is part of the business (patrons).

Anyway, i need to know if any file is OK (0) or it has some error (one number different of 0). I thought if i can do a split i could converter it to number and sum, if sum is 0, means all are 0. If it sum different so it is error code. But i have problem doing the split, i try with split_part but i must choose the position and string_to_array too i have problem. It table has other column but this is the key for the problem.

SELECT split_part(state, ',', position)

So in this example (the table is just the example and not the result of sql in that way. Just the i need to know):

state result sum
0 OK 0 (0)
0,0 OK 0 (0+0)
13 NOK 13 (13)
13,27,0 NOK 40 (13+27+0)
0,0,0 OK 0 (0+0+0)

1.- Like i said, i need to know if is 0 ok or not, because i must work with other columna of the "zero" result.

2.- Sum is only an idea, maybe could be other way.

Please help, thanks a lot.

Tags:

2

Answers


  1. 0

    A normalized table without delimiter, would save the unnest, which costs time

    CREATE TABLE ExTable 
    (ID  SERIAL PRIMARY KEY,"state" varchar(7))
;
    
INSERT INTO ExTable 
    ("state")
VALUES
    ('0'),
    ('0,0'),
    ('13'),
    ('13,27,0'),
    ('0,0,0')
;


    WITH CTE AS ( SELECT
ID,state,unnest(string_to_array(state, ','))::int val
FROM Extable)
SELECT state,CASE WHEN SUM(val) = 0 THEn 'OK' ELSE 'NOK' END result, SUM(val) sum
FROM CTE GROUP BY ID,state
ORDER By ID
    state result sum
    0 OK 0
    0,0 OK 0
    13 NOK 13
    13,27,0 NOK 40
    0,0,0 OK 0 
    SELECT 5

    fiddle

    Login or Signup to reply.
  2. 0

    It’s text data, so you could just use a simple regular expression query. The following query would list ONLY states that contain at least one error.

    SELECT * FROM your_table WHERE state ~ '[1-9]';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search