error when creating an auto increment primary key column on heroku postgres

Question

Trying to set up my postgres table in heroku and I'm getting this error when trying to set up the auto-incrmenting in the primary key on my table

user_id_seq relation does not exist

This is the create statement

CREATE TABLE "public"."user" (
    "id" INTEGER DEFAULT nextval('user_id_seq'::regclass) NOT NULL UNIQUE,
    "uname" CHARACTER VARYING( 255 ) COLLATE "pg_catalog"."default" UNIQUE,
    "description" CHARACTER VARYING( 2044 ) COLLATE "pg_catalog"."default",
    "country" CHARACTER( 3 ) COLLATE "pg_catalog"."default" DEFAULT 'USA'::bpchar NOT NULL,
 PRIMARY KEY ( "id" )
, CONSTRAINT "unique_uname" UNIQUE( "uname" ) );

What does that error mean and do I have to set up that relation beforehand somehow?

Answer 1

You have to create the equence first with something like:

CREATE SEQUENCE user_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;`

Or declaring you column id as a serial:

CREATE TABLE "public"."user" (
    "id" bigserial NOT NULL UNIQUE,
    "uname" CHARACTER VARYING( 255 ) COLLATE "pg_catalog"."default" UNIQUE,
    "description" CHARACTER VARYING( 2044 ) COLLATE "pg_catalog"."default",
    "country" CHARACTER( 3 ) COLLATE "pg_catalog"."default" DEFAULT     'USA'::bpchar NOT NULL,
 PRIMARY KEY ( "id" )
, CONSTRAINT "unique_uname" UNIQUE( "uname" ) );

which is equivalent to:

CREATE SEQUENCE user_id_seq;

CREATE TABLE "public"."user" (
    "id" bigint NOT NULL UNIQUE DEFAULT nextval('user_id_seq'),
    "uname" CHARACTER VARYING( 255 ) COLLATE "pg_catalog"."default" UNIQUE,
    "description" CHARACTER VARYING( 2044 ) COLLATE "pg_catalog"."default",
    "country" CHARACTER( 3 ) COLLATE "pg_catalog"."default" DEFAULT     'USA'::bpchar NOT NULL,
 PRIMARY KEY ( "id" )
, CONSTRAINT "unique_uname" UNIQUE( "uname" ) );


ALTER SEQUENCE user_id_seq OWNED BY public.user.id;