Lior Amsalem

When we use supabase (or postgresql) we might want the id to be automatic managed and either increment or be created uniqley whenever we insert content to our database. for example, see the following code where we try to insert a new post to “public.posts” table in supabase/postgresSQL:

    const { data, error } = await supabase
      .from('posts')
      .insert({
        title: formValues.title,
        body: formValues.body,
        author_name: formValues.authorName,
        author: user.id,
      })
      .select()
      .single();

give the above code we’ll get an error “NOT NULL VIOLATION – error code 23502” since the post id in Supabase postgreSQL might not be configured as we expected. a good approach to avoid error code 23502 or not full violation is to define the UID(or ID of the post) to have a uniquely generated id automatically whenever we insert post to the database.

We can do so as such when we create the posts database:

create table "public"."posts" (
  id uuid not null default uuid_generate_v4() primary key,
  "title" text not null,
  "body" text not null,
  "created_at" timestamp with time zone default now(),
  "author" uuid not null
);

CREATE UNIQUE INDEX posts_pkey ON public.posts USING btree (id);

alter table "public"."posts" add constraint "posts_pkey" PRIMARY KEY using index "posts_pkey";
alter table "public"."posts" add constraint "posts_author_fkey" FOREIGN KEY (author) REFERENCES auth.users(id) not valid;
alter table "public"."posts" validate constraint "posts_author_fkey";

or if the database already exists an we want to know how to add automatic newly uniq generated id for a column in the database:

ALTER TABLE public.posts
ADD COLUMN id uuid not null default uuid_generate_v4() primary key constraint;

Now whenever we add uniq id to the column “uuid_generate_v4” function wil lbe used to generate automatic new id that does not exists in the table for that new item/post that we’ve added. and avoiding the “NOT NULL VIOLATION – error code 23502”!

Lior Amsalem

Lior Amsalem embarked on his software engineering journey in the early 2000s, Diving into Pascal with a keen interest in creating, developing, and working on new technologies. Transitioning from his early teenage years as a freelancer, Lior dedicated countless hours to expanding his knowledge within the software engineering domain. He immersed himself in learning new development languages and technologies such as JavaScript, React, backend, frontend, devops, nextjs, nodejs, mongodb, mysql and all together end to end development, while also gaining insights into business development and idea implementation.

Through his blog, Lior aims to share his interests and entrepreneurial journey, driven by a desire for independence and freedom from traditional 9-5 work constraints.