Why is the column not altering when I try to convert it to UUID?

Question

I have a primary key column in my SQL table in PostgreSQL named "id". It is a "bigseries" column. I want to convert the column to a "UUID" column. It entered the below command in the terminal:

alter table people alter column id uuid;

and

alter table people alter column id uuid using (uuid_generate_v4());

but neither of them worked.

In both tries I got the error message

ERROR: syntax error at or near "uuid"

LINE 1: alter table people alter column id uuid using (uuid_generate...

What is the correct syntax?

Answer 1

CREATE TABLE IF NOT EXISTS people (
id uuid NOT NULL CONSTRAINT people_pkey PRIMARY KEY,
address varchar,
    city varchar(255),
    country varchar(255),
    email varchar(255),
    phone varchar(255)
); 

This is the correct syntax to create table in postgres SQL, it's better to do these constraints at beginning to avoid any error. For using alter command you would do the following:

ALTER TABLE customer ADD COLUMN cid uuid PRIMARY KEY;

Most of errors that you could find while writing command either lower case or undefined correct the table name or column.

Answer 2

First of all uuid_generate_v4() is a function which is provided by an extension called uuid-ossp. You should have install that extension by using;

CREATE EXTENSION uuid-ossp;

Postgresql 13 introduced a new function which does basically the same without installing extension. The function is called gen_random_uuid()

Suppose that we have a table like the one below;

CREATE TABLE people (
   id bigserial primary key,
   data text
);

The bigserial is not a real type. It's a macro which basically creates bigint column with default value and a sequence. The default value is next value of that sequence.

For your use case, to change data type, you first should drop the old default value. Then, alter the type and finally add new default value expression. Here is the sample:

ALTER TABLE people
  ALTER id DROP DEFAULT,  
  ALTER id TYPE uuid using (gen_random_uuid() /* or uuid_generate_v4() */ ), 
  ALTER id SET DEFAULT gen_random_uuid() /* or uuid_generate_v4() */ ;