CODEWITHSUNDEEP
sqlpostgresqlddl
Aristotelis
Aristotelis

Reputation: 21

Converting varchar to int in psql

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;

Upvotes: 0

Views: 2070

Answers (3)

Julius Tuskenis
Julius Tuskenis

Reputation: 1630

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.

Upvotes: 1

Joma
Joma

Reputation: 3869

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"

calendar

After alter the table

id  price   adjusted_price   
100 8000    8001  
101 7000    7355

calendar

References

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

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247950

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;

Upvotes: 0

Related Questions