Postgres Update table id with sequence

Question

I am trying to connect a sequence for user table to auto incremental value for id field.

I created following sequence,

CREATE SEQUENCE "USER_MGMT"."USER_SEQ"
    INCREMENT 1
    START 1000
    MINVALUE 1000
    MAXVALUE 99999999
    CACHE 1;

ALTER SEQUENCE "USER_MGMT"."USER_SEQ"
    OWNER TO postgres;

following is my table,

-- Table: "USER_MGMT"."USER"

-- DROP TABLE "USER_MGMT"."USER";

CREATE TABLE "USER_MGMT"."USER"
(
    "USER_ID" bigint NOT NULL,
    "FIRST_NAME" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "LAST_NAME" character varying(50) COLLATE pg_catalog."default" NOT NULL,
    "EMAIL_ID" character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "DESK_NUMBER" bigint,
    "MOBILE_NUMBER" bigint,
    "IS_ACTIVE" boolean NOT NULL DEFAULT true,
    "CREATED_BY" character varying(100) COLLATE pg_catalog."default",
    "MODIFIED_BY" character varying(100) COLLATE pg_catalog."default",
    "DATE_CREATED" timestamp without time zone NOT NULL,
    "DATE_MODIFIED" timestamp without time zone,
    CONSTRAINT "USER_ID_PK" PRIMARY KEY ("USER_ID"),
    CONSTRAINT "EMAIL_ID_UK" UNIQUE ("EMAIL_ID"),
    CONSTRAINT "MOBILE_NUMBER_UK" UNIQUE ("MOBILE_NUMBER"),
    CONSTRAINT "USER_ID_UK" UNIQUE ("USER_ID")
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE "USER_MGMT"."USER"
    OWNER to postgres;

I want to connect this sequence to USER_ID column, so it will be auto incremented.

Table name and fields should be in upper case,

I am trying to execute the following query, but its not working

ALTER TABLE USER_MGMT.USER ALTER COLUMN USER_ID SET DEFAULT nextval('USER_MGMT.USER_SEQ');

It says the following error message in console.

ERROR:  schema "user_mgmt" does not exist
********** Error **********

Never use double quotes for identifiers. They are more trouble then they are worth — user330315
– user330315, Commented Oct 3, 2017 at 9:04

Łukasz Kamiński · Accepted Answer · 2017-10-03 09:01:44Z

2

That is because when you use double quotes then you are creating case sensitive object identifier or to be more precise - this object will have identifier with exact case as given in the query during creation. If you do not double quote them, then they are converted to lower case.

So what you need is to either stop using double quotes, create objects in lower case or use double quotes in your alter query:

ALTER TABLE "USER_MGMT"."USER" ALTER COLUMN "USER_ID" SET DEFAULT nextval('"USER_MGMT"."USER_SEQ"');

answered Oct 3, 2017 at 9:01

Łukasz Kamiński

6,0401 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

J Query Over a year ago

Tried this, and getting following error. ERROR: schema "USER_MGMT" does not exist ********** Error **********

Łukasz Kamiński Over a year ago

And you were able to create sequence in said schema? Can you show output of this: SELECT oid::regnamespace FROM pg_namespace WHERE lower(nspname) = 'user_mgmt'

Collectives™ on Stack Overflow

Postgres Update table id with sequence

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related