Search code examples
sqlpostgresqlalter-table

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.

Solution

  • 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