0

I have a column called still_available with the values of 'Removed' and 'Still_available'. The original table looks like this:

original table

Since there are only two values I want to cast it to boolean e.g 'Removed' => False and 'Still_available' => True. So I did this:

dim_products_set_still_available_to_bool = f"""
UPDATE public.dim_products
SET still_available = 
                CASE
                    WHEN still_available = 'Removed' THEN {False}
                    ELSE {True}
                END;
"""

On row 4 it sets the value to true. I was expecting the rows to be set properly to the new values so I can cast the column datatype to boolean.

I'm not sure why but once I copied the actual string from the database it seems to work completely with:

dim_products_set_still_available_to_bool = f"""
UPDATE public.dim_products
SET still_available = 
                CASE
                    WHEN still_available = 'Removed' THEN {False}
                    WHEN still_available = 'Still_avaliable' THEN {True}
                END;
"""
Swinging Treebranch
  • 21
  • 7
  • Please remember to post table definition and sample data as formatted *text*, never as image. We can't copy/paste from an image. – Erwin Brandstetter Feb 13 '23 at 01:24
  • Yeah that's no problem, it was just a dataframe that i uploaded to the table after cleaning and reading from a pdf, this is all through python so thought explanation, i will remember for the future to have some sample data – Swinging Treebranch Feb 13 '23 at 01:36
  • BTW, I see a column named "uuid" type `text`. Should probably be type `uuid`. See: https://dba.stackexchange.com/q/115271/3684 – Erwin Brandstetter Feb 13 '23 at 07:27
  • Yeah thats all done altered my column types after all of this just couldn't alter the columns all at once since i needed to that column to boolean – Swinging Treebranch Feb 13 '23 at 10:35

1 Answers1

1

I was expecting the rows to be set properly to the new values so I can cast the column datatype to boolean

You should really just ALTER the column type. Don't run a separate UPDATE first, that would double the cost. Do the conversion in a USING clause:

ALTER TABLE public.dim_products
ALTER COLUMN still_available TYPE boolean USING 
                CASE still_available
                    WHEN 'Removed' THEN false
                    WHEN 'Still_avaliable' THEN true
                    -- ELSE null  -- default
                END;

This rewrites the table in pristine condition and takes an exclusive lock for the duration.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This i never knew, you learn a lot by asking questions, yeah theres a few times where i'm doing exactly the same as outlined above, I will edit the question above to include what i mean so you can have a look – Swinging Treebranch Feb 13 '23 at 00:31
  • I just noticed my edit will be hella long lol so i'll just leave it for now but thank you so much furthermore can i ask you another question some of the postgressql statements i am send as a functional string however, from what i read that is prone to sql injection since i'd like to use this as part of my resume what would be the best way to avoid this? i did hear something about parametized sql statements but i'm essentially clueless tbh – Swinging Treebranch Feb 13 '23 at 00:40
  • @SwingingTreebranch: Please ask your new question as question. Comments are not the place. Maybe some existing Q/A help already: https://stackoverflow.com/a/18386034/939860 or https://dba.stackexchange.com/a/159534/3684 or https://stackoverflow.com/a/17355733/939860 – Erwin Brandstetter Feb 13 '23 at 00:57
  • Perfect exactly answers my question will have to read that throughly first and yeah its because i cannot post a question currently – Swinging Treebranch Feb 13 '23 at 01:02
  • So do you have your answer here? – Erwin Brandstetter Mar 21 '23 at 12:24