Converting varchar to int in psql

Question

so I am trying to convert a varchar to an int. I started without the numeric type and I got an error probably because of the . in the varchar. I searched online and found that I should add the numeric type. Now I have another error which is probably because of the , which is used as the thousands separator. Any suggestions?

I would like to use the alter table command if possible not cast or anything else because we have not learned it yet and it's for a school assignment. I have also added a screenshot of the query.

enter image description here

ALTER TABLE table_name
ALTER COLUMN column_name TYPE type USING column_name::type::type,
ALTER COLUMN column_name TYPE type USING column_name::type::type;

I just realised how badly my attempt to write the query under the screenshot came out.Sorry for that, at least you can see it from the screenshot. — Aristotelis, Apr 07 '21 at 19:16
Please do **not** post code as images. See here for more details why: http://meta.stackoverflow.com/questions/285551 — a_horse_with_no_name, Apr 10 '21 at 19:44

Julius Tuskenis · Answer 1 · 2021-04-10T18:29:17.287

1

You can use a number of ways to convert your text value to integer (assuming the number in text field is actually an integer). For example:

REPLACE(price, ',', '')::numeric::int
TO_NUMBER(price, translate(price, '1234567890', '9999999999'))::int

Your alter table statement should look like this:

ALTER TABLE calendar 
ALTER COLUMN price TYPE integer USING REPLACE(price , ',', '')::numeric::integer,
ALTER COLUMN adjusted_price TYPE integer USING REPLACE(adjusted_price, ',', '')::numeric::integer;

I've chosen the shorter way to cast, but TO_NUMBER case would work as well.

edited Apr 10 '21 at 18:29

answered Apr 07 '21 at 20:50

Julius Tuskenis

1,168
7
12

Well, slap me silly, I didn't know that was possible. I've learned something new today. – Tim Roberts Apr 09 '21 at 17:59

score 0 · Answer 2 · answered Apr 08 '21 at 02:52

Use to_number, that can understand group separators:

ALTER TABLE calendar
   ALTER price TYPE integer
      USING to_number(price, '999,999,999,999.99')::integer,
   ALTER adjusted_price TYPE integer
      USING to_number(adjusted_price, '999,999,999,999.99')::integer;

Joma · Answer 3 · 2021-04-08T05:23:08.717

My example/test script.

-- █ Droping and creating the table for test purposes. Don't do this with table with production data.
DROP TABLE IF EXISTS calendar;
CREATE TABLE calendar
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 100 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    price character varying(10) COLLATE pg_catalog."default" NOT NULL,
    adjusted_price character varying(10) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT pk_calendar_id PRIMARY KEY (id)
);

-- █ For test purposes, creating example data if table exists.
DO $$ -- DO executes an anonymous code block
BEGIN
    IF EXISTS(SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'calendar') THEN
        INSERT INTO calendar (price, adjusted_price) VALUES('8,000.00', '8,001.00');
        INSERT INTO calendar (price, adjusted_price) VALUES('7,000.00', '7,355.00');
    END IF;
END;
$$;

-- █ Alter table columns from varchar to int.
ALTER TABLE calendar
ALTER COLUMN price TYPE int USING SPLIT_PART(REPLACE(price, ',', ''), '.', 1)::int, 
ALTER COLUMN adjusted_price TYPE int USING SPLIT_PART(REPLACE(adjusted_price, ',', ''), '.', 1)::int;

-- REPLACE(source, old_text, new_text ) comma is replaced by empty string '8,000.00' -> '8000.00'
-- SPLIT_PART(string, delimiter, position) '8000.00' is splitted in 2 parts ['8000', '00'] we need the part 1 ->'8000'
-- ::int using cast operator ::, convert from varchar to int.

-- █ Select all columns with new types.
select * from calendar;

Example data

id  price       adjusted_price  
100 "8,000.00"  "8,001.00"
101 "7,000.00"  "7,355.00"

After alter the table

id  price   adjusted_price   
100 8000    8001  
101 7000    7355

References

PostgreSql SPLIT_PART
PostgreSql REPLACE
PostgreSql CAST
PostgreSql DO
Check if table exists

Converting varchar to int in psql

3 Answers3