CODEWITHSUNDEEP
postgresql
nskalis
nskalis

Reputation: 2382

how-to modify a column type and set its default and current value in postgresql

I would like to perform the following change, from

CREATE TABLE IF NOT EXISTS scheme.xxx (
    id SERIAL PRIMARY KEY,

to

CREATE TABLE IF NOT EXISTS scheme.xxx (
    id UUID DEFAULT uuid_generate_v4(),

in an existing table with records, but I fail to achieve it.

An example that doesn't work is:

ALTER TABLE scheme.xxx
    ALTER COLUMN id TYPE UUID SET DEFAULT uuid_generate_v4()
        USING id::uuid_generate_v4() ;

Upvotes: 3

Views: 2123

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246228

You'll have to remove the default value first, then change the type, then add the new default value.

But first find out the sequence:

SELECT pg_get_serial_sequence('scheme.xxx', 'id');

 pg_get_serial_sequence 
------------------------
 scheme.xxx_id_seq
(1 row)

Now do it:

ALTER TABLE scheme.xxx
   ALTER id DROP DEFAULT,
   ALTER id TYPE uuid USING uuid_generate_v4(),
   ALTER id SET DEFAULT uuid_generate_v4();

All in one statement!

Now get rid of the sequence:

DROP SEQUENCE public.xxx_id_seq;

Upvotes: 3

Related Questions