CODEWITHSUNDEEP
postgresql
sandeshdaundkar
sandeshdaundkar

Reputation: 903

Postgres Alter Column Datatype & Update Values

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

Upvotes: 0

Views: 2360

Answers (2)

Danial Eskandari
Danial Eskandari

Reputation: 164

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;

Upvotes: 1

user330315
user330315

Reputation:

You can cleanup the string while altering the table:

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

Upvotes: 1

Related Questions