id not Auto Incrementing on Postgres 12.2 using SQL Inserts

Question

On PostgresSQL 12.2, running on macOS Catalina (10.15.4), have the following DB Schema:

CREATE TABLE public.reservation
(
    id integer,
    name character varying(255) COLLATE pg_catalog."default"
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.reservation
    OWNER to postgres;

Here's listing the relations:

orders=# \d
            List of relations
 Schema |    Name     | Type  |  Owner   
--------+-------------+-------+----------
 public | reservation | table | postgres
(1 row)

After conducting the following inserts:

INSERT INTO reservation(name) VALUES('Michael');
INSERT INTO reservation(name) VALUES('Vito');
INSERT INTO reservation(name) VALUES('Sonny');
INSERT INTO reservation(name) VALUES('Fredo');

Then, checking the results:

SELECT * FROM reservation;

Receive the following output:

 id |  name  
----+--------
    | Michael
    | Vito
    | Sonny
    | Fredo
(4 rows)

How can I modify this table's structure (using DDL) to have it auto-increment ids with values?

Are you aware that a limit of 255 for a varchar column has no performance or storage advantage over 250, 263 or 3876 — user330315
– user330315, Commented May 10, 2020 at 19:02
@a_horse_with_no_name - Nope, I was not aware... I am coming from a MySQL background and transitioning to PostgreSQL and am following an online tutorial. — PacificNW_Lover
– PacificNW_Lover, Commented May 10, 2020 at 19:11

user330315 · Accepted Answer · 2020-05-10 19:01:19Z

3

Just because you name a column id, doesn't mean it's an "auto increment" column. If you want that, you need to declare it as an identity column:

CREATE TABLE public.reservation
(
    id integer not null generated always as identity,
    name character varying(255) COLLATE pg_catalog."default"
);

answered May 10, 2020 at 19:01

user330315

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

Comments

Prateek Sharma · Accepted Answer · 2020-05-10 19:36:13Z

0

In Postgres, use SERIAL datatype for auto-increment integer id.

Run the below queries

CREATE SEQUENCE public.reservation_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1;

ALTER SEQUENCE public.reservation_id_seq
    OWNER TO postgres;

AND THEN

CREATE TABLE public.reservation
(
    id integer NOT NULL DEFAULT 
    nextval('public.reservation_id_seq'::regclass),
    name character varying(255) COLLATE pg_catalog."default"
)

TABLESPACE pg_default;

ALTER TABLE public.reservation
    OWNER to postgres;

Thank you !!

answered May 10, 2020 at 19:36

Prateek Sharma

4125 silver badges5 bronze badges

5 Comments

user330315 Over a year ago

With modern Postgres version using identity columns is preferred over serial: wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial

PacificNW_Lover Over a year ago

@Prateek Sharma - Thank you but can you kind of explain why I needed to use sequence for this? Sorry, I come from a MySQL background.

Prateek Sharma Over a year ago

sequence is to track the latest id in an incremental order. It always +1 in the current id.

user330315 Over a year ago

@PacificNW_Lover: you don't have to use sequences explicitly. An identity (or serial) column will do that automatically for you in the background.

PacificNW_Lover Over a year ago

@a_horse_with_no_name - How can I use identity`` instead of serial```? Can you provide an example?

Collectives™ on Stack Overflow

id not Auto Incrementing on Postgres 12.2 using SQL Inserts

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related