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?

You are missing the `type` keyword before the data type [as documented in the manual](https://www.postgresql.org/docs/current/sql-altertable.html) but you can't cast an integer to a UUID, so even with the correct syntax this wouldn't work if you have rows in the table. — , Oct 08 '21 at 05:32

score 3 · Answer 1 · answered Oct 07 '21 at 23:51

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() */ ;

Plus, drop the now orphaned sequence: `DROP SEQUENCE public.people_id_seq;` Or find the actual sequence name with `SELECT pg_get_serial_sequence('public.people', 'id');` See: https://stackoverflow.com/a/59233169/939860 — Erwin Brandstetter, Oct 08 '21 at 02:25
Starting with Postgres 13 there is no need to install the extension any more. That version introduce the built-in method [gen_random_uuid](https://www.postgresql.org/docs/13/functions-uuid.html) — , Oct 08 '21 at 12:43

Abdullah Al masri · Answer 2 · 2021-10-08T12:12:33.200

0

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.

edited Oct 08 '21 at 12:12

answered Oct 07 '21 at 22:36

Abdullah Al masri

1
2

I tried this and got the error message "ERROR: syntax error at or near "id" LINE 1: alter table people alter column add id uuid using (uuid_gene..." – Oct 07 '21 at 22:45
ALTER TABLE people ADD COLUMN id uuid people_pkey; – Abdullah Al masri Oct 07 '21 at 22:53
you can see that in website of postgres here : https://www.postgresqltutorial.com/postgresql-alter-table/ try that command – Abdullah Al masri Oct 07 '21 at 22:54
for me i have database called people under it there is table called customer i did try this command and it work well ALTER TABLE customer ADD COLUMN cid uuid PRIMARY KEY; try it and tell if you stiil at this error – Abdullah Al masri Oct 07 '21 at 23:08
sorry ,yes i can't do that, i edit it so no one could try the second one, thanks to alert me :D. – Abdullah Al masri Oct 08 '21 at 12:11

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

2 Answers2