Home PostgreSQL: Updating data on a column of a table with a counter
Reply: 1

PostgreSQL: Updating data on a column of a table with a counter

p4x
1#
p4x Published in 2018-01-12 10:33:37Z

I want to change a column of a table in our database to UNIQUE NOT NULL and first I need to write a postgres sql script to fix current data.

What I need to do basically is find all the instances with column 'code' being NULL or ' ' and change it with a text like this: (1), (2), (3), (4)..

Sameway, I have to find all instances with repeated code and change them into something like this: 'sameCode(1)', 'sameCode(2)', 'equalCode(1)', 'equalCode(2)', 'equalCode(3)'.

Example input       Expected Output
╔════╤═══════╗      ╔════╤══════════╗
║ id │ code  ║      ║ id │ code     ║
╠════╪═══════╣      ╠════╪══════════╣
║ 1  │ null  ║      ║ 1  │ (1)      ║
╟────┼───────╢      ╟────┼──────────╢
║ 2  │ ' '   ║      ║ 2  │ (2)      ║
╟────┼───────╢      ╟────┼──────────╢
║ 3  │ 'FOO' ║      ║ 3  │ 'FOO(1)' ║
╟────┼───────╢      ╟────┼──────────╢
║ 4  │ 'FOO' ║      ║ 4  │ 'FOO(2)' ║
╟────┼───────╢      ╟────┼──────────╢
║ 5  │ 'BAR' ║      ║ 5  │ 'BAR(1)' ║
╟────┼───────╢      ╟────┼──────────╢
║ 6  │ 'BOB' ║      ║ 6  │ 'BOB'    ║
╟────┼───────╢      ╟────┼──────────╢
║ 7  │ 'BAR' ║      ║ 7  │ 'BAR(2)' ║
╚════╧═══════╝      ╚════╧══════════╝

Writing loops with counters and updates using SQL is not one of my strengths and I'm finding it a bit difficult.

Conceptually it is plain an simple. Obtain an ordered list of ids and their codes ordered by code (and NULL and ' ') and then loop over it adding '(++counter)' to the current code text, but restarting the counter every time the code changes. This is going to be slow but it doesn't matter, it is only a one time thing and we're not talking millions of instances here.

DECLARE
    list_of_instances tablename[]; #dunno how to declare an instance
    counter INTEGER;
    oldcode CHARACTER VARYING;
BEGIN
    list_of_instances:=(select id, code from tablename 
                       where code is null or code = ' ' or code in 
                        (select code from tablename group by code having count(*) > 1) 
                       order by codi desc);
    counter:=1;
    oldcode:=tablename[0].code;
    for i in list_of_instances LOOP
        UPDATE tablename SET code=i.code + '(' + cast(counter as text) +')'
        WHERE id=i.id;
        counter:= counter+1;
        IF i.code <> oldcode THEN
            oldcode:=i.code;
            counter:=1;
        END IF;
    END LOOP;
END
$$ language 'plpgsql';

Needless to say this code isn't working.

a_horse_with_no_name
2#
a_horse_with_no_name Reply to 2018-01-12 11:37:51Z

You can generate the number you want using window functions. Apparently you want to treat null and strings with only whitespace as the same thing, so you need to normalize those.

The following query generates the numbers you want, and also supplies the information if a single string occurs more than once:

select id, code, 
       row_number() over (partition by coalesce(nullif(trim(code),''),'') order by id) as counter,
       count(*) over (partition by coalesce(nullif(trim(code),''),'')) as num_rows
from tablename
order by id;

With your sample input, this returns:

id | code | counter | num_rows
---+------+---------+---------
 1 |      |       1 |        2
 2 |      |       2 |        2
 3 | FOO  |       1 |        2
 4 | FOO  |       2 |        2
 5 | BAR  |       1 |        2
 6 | BOB  |       1 |        1
 7 | BAR  |       2 |        2

The expression coalesce(nullif(trim(code),''),'') "normalizes" NULL values and strings that only consist of whitespace, e.g. ' ' to an empty string ''.

This can now be used to update the table. As you only want to add the counter to values that occur more than once you need to restrict the update to those:

update tablename as tn
  set code = concat(trim(tn.code), '(', x.counter, ')')
from (
  select id, code, 
         row_number() over (partition by coalesce(nullif(trim(code),''),'') order by id) as counter,
         count(*) over (partition by coalesce(nullif(trim(code),''),'')) as num_rows
  from tablename
) x
where x.id = tn.id
  and x.num_rows > 1;

After the update, your sample looks like this:

id | code  
---+-------
 1 | (1)   
 2 | (2)   
 3 | FOO(1)
 4 | FOO(2)
 5 | BAR(1)
 6 | BOB   
 7 | BAR(2)

Online example: http://rextester.com/WGAL85544

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.324828 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO