Postgres Alter Column Datatype & Update Values-CodePudding

I am new to writing postgres queries, I am stuck at a problem where the price is a string having $ prefix, I want to change the data type of column price to float and update the values by removing the $ prefix. Can someone help me do that?

bootcamp=# SELECT * FROM car;
 id |  make   |        model        |   price
---- --------- --------------------- -----------
  1 | Ford    | Explorer Sport Trac | $92075.96
  2 | GMC     | Safari              | $81521.80
  3 | Mercury | Grand Marquis       | $64391.84
(3 rows)

bootcamp=# \d car
                                   Table "public.car"
 Column |         Type          | Collation | Nullable |             Default
-------- ----------------------- ----------- ---------- ---------------------------------
 id     | bigint                |           | not null | nextval('car_id_seq'::regclass)
 make   | character varying(50) |           | not null |
 model  | character varying(50) |           | not null |
 price  | character varying(50) |           | not null |

Thanks

CodePudding user response：

To remove the $ run the following query:

UPDATE car SET price = replace(price, '$', '');

To update the column type run the following one:

ALTER TABLE car ALTER COLUMN price TYPE real;

CodePudding user response：

You can cleanup the string while altering the table:

alter table car
   alter column price type numeric using substr(price, 2)::numeric;

CodePudding user response：

First you have to disable safe update mode to update without WHERE clause:

SET SQL_SAFE_UPDATES=0;

Then remove '$' from all rows:

UPDATE car SET price = replace(price, '$', '');

Then change the column type:

 ALTER TABLE car ALTER COLUMN price TYPE your_desired_type;

If you want to enable safe update mode again:

SET SQL_SAFE_UPDATES=1;