Convert a type to another in Postgres

Question

I have a table of cars in my Postgres database, but a created the price column with varchar.

I want to convert this type to numeric or integer.

Are you sure about *integer* part? Some of the prices have decimal places. — PM 77-1, Feb 06 '23 at 20:06
What do you mean by "*convert*"? Are you creating a new column? — PM 77-1, Feb 06 '23 at 20:07
Please your sample data as text, never as image. Ideally, add a fiddle. How many rows are populated? Any NULL values? A column default? Your version of Postgres? — Erwin Brandstetter, Feb 06 '23 at 20:08

Erwin Brandstetter · Answer 1 · 2023-02-06T20:16:09.673

You can change the table definition with ALTER TYPE, but you'll need a USING clause with your particular conversion. Related:

If your data sample tells the full story, this should work:

ALTER TABLE tbl
ALTER COLUMN price TYPE numeric USING right(price, -1)::numeric

right(price, -1) trims the first character, which is always $ in your sample. Remaining leading and trailing white space is no problem. Related:

For anything else, adapt the expression.

The operation triggers a table rewrite, taking an exclusive lock on the table for the duration.

I chose numeric to cover fractional digits. See:

1 Answers1