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

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

user3191
1#
user3191 Published in April 26, 2018, 5:21 pm

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO