Question 114634 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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

Tags:

2

Answers


  1. 0

    You can cleanup the string while altering the table:

    alter table car
   alter column price type numeric using substr(price, 2)::numeric;
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search