Postgres Alter Column Datatype & Update Values

Question

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

user330315 · Accepted Answer · 2022-09-14 10:02:56Z

1

You can cleanup the string while altering the table:

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

answered Sep 14, 2022 at 10:02

user330315

Sign up to request clarification or add additional context in comments.

Comments

Danial Eskandari · Accepted Answer · 2022-09-14 10:23:39Z

1

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;

answered Sep 14, 2022 at 10:23

Danial Eskandari

1645 bronze badges

1 Comment

sandeshdaundkar Over a year ago

Thanks, Danial, for the SET SQL_SAFE_UPDATES thing, anyways I got an error while altering the column data type column "price" cannot be cast automatically to type numeric, which is resolved by @a_horse_with_no_name

Collectives™ on Stack Overflow

Postgres Alter Column Datatype & Update Values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related