Conditional update statement in PSQL

Question

I'm trying to do a conditional update on my website. Whenever a user clicks a certain link, a global counter variable in the database increments itself.

The complication is that when the counter number exceeds the number of websites I have on record, I want the counter to go back to 1. I have an update statement for this, but its not working and I cannot see why.

#Increment global counter, set it back to one when it exceeds the number of websites on record
cursor.execute("UPDATE url_reroute_counter SET counter_num = "+
                    " CASE"+
                        " WHEN counter_num>(SELECT count(*) FROM url_reroute_targeturl)"+ 
                            " THEN counter_num = 1"+
                        " ELSE"+
                            " counter_num = counter_num+1"+
                    " END"+
                    " WHERE name_of_counter = 'url_count'")`

Running this code gets me the following exception:

django.db.utils.ProgrammingError: column "counter_num" is of type integer but expression is of type boolean
    LINE 1: UPDATE url_reroute_counter SET counter_num =  CASE WHEN coun...
                                                          ^
    HINT:  You will need to rewrite or cast the expression.

I'm not used to using conditionals in any SQL language so any hlep here is appreciated.

I think the more appropriate tag is postgresql – e4c5 Jun 10 '16 at 12:22 — e4c5, Jun 10 '16 at 12:22

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

Your query should be as follows

cursor.execute("UPDATE url_reroute_counter SET counter_num = "+
                " CASE"+
                    " WHEN counter_num>(SELECT count(*) FROM url_reroute_targeturl)"+ 
                        " THEN 1"+
                    " ELSE"+
                        " counter_num+1"+
                " END"+
                " WHERE name_of_counter = 'url_count'")`

And BTW, in a multi line python string, you don't need all those + signs. The this: https://stackoverflow.com/a/10660443/267540

edited May 23 '17 at 12:31

Community

1
1

answered Jun 10 '16 at 12:25

e4c5

52,766
11
101
134

happens to everyone\ – e4c5 Jun 10 '16 at 13:15

Conditional update statement in PSQL

1 Answers1