CODEWITHSUNDEEP
sqlpostgresqlalter-table
Pyrejkee
Pyrejkee

Reputation: 337

Change column type from text to integer

I have column with type text I need to change column type to integer.

I try to do it with the following script

ALTER TABLE table ALTER COLUMN column TYPE integer

Obviously I get an error.

But how can I modify my script if my previous values were like "Word1", "Word2" and now I want to keep them like 0, 1

If it possible I would be Ok if this script will set 0 to all rows when processing this script.

Upvotes: 1

Views: 1920

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6140

If your column contains only integer values then try this way:

ALTER TABLE test ALTER COLUMN id  TYPE integer USING (id::integer);

If your column contains non-numeric values then try this way:

ALTER TABLE test ALTER COLUMN id  TYPE integer USING (CASE
        WHEN id~E'^\\d+$' THEN
            CAST (id AS INTEGER)
        ELSE
            0
        END);

DEMO

Upvotes: 3

Related Questions