Postgres create table error

Question

I am trying to create my very first table in postgres, but when I execute this SQL:

create table public.automated_group_msg (
  automated_group_msg_idx integer NOT NULL DEFAULT nextval ('automated_group_msg_idx'::regclass),
  group_idx integer NOT NULL,
  template_idx integer NOT NULL,
  CONSTRAINT automated_group_msg_pkey PRIMARY KEY (automated_group_msg_idx),
  CONSTRAINT automated_group_msg_group_idx_fkey FOREIGN KEY (group_idx)
  REFERENCES public.groups (group_idx) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT automated_msg_template_idx_fkey FOREIGN KEY (template_idx)
  REFERENCES public.template (template_idx) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
OIDS = FALSE
);

I get the following error:

ERROR: relation "automated_group_msg_idx" does not exist

Do you just want automated_group_msg_idx to be an auto incrementing id? — Bohemian
– Bohemian ♦, Commented Nov 6, 2017 at 19:16

Bohemian · Accepted Answer · 2017-11-06 22:00:38Z

2

Your error is (likely) because the sequence you're trying to use doesn't exist yet.

But you can create a sequence on the fly using this syntax:

create table public.automated_group_msg (
  id serial primary key,
  ... -- other columns
)

Not directly related to your question, but naming columns with the table name in the name of the column is generally speaking an anti-pattern, especially for primary keys for which id is the industry standard. It also allows for app code refactoring using abstract classes whose id column is always id. It's crystal clear what automated_group_msg.id means and also crystal clear that automated_group_msg.automated_group_msg_id is a train wreck and contains redundant information. Attribute column names like customer.birth_date should also not be over-decorated as customer.customer_birth_date for the same reasons.

edited Nov 6, 2017 at 22:00

answered Nov 6, 2017 at 19:22

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

6 Comments

user8895989 Over a year ago

looking at postgres documentation and seems like nextval should also work like serial. Actually I was looking in the database that I am working with and thats how the schema is defined for some tables, so think syntactically the way i have written should be right.

Bohemian Over a year ago

@user8895989 No - mostly incorrect. In postgres, auto increment is implemented using a sequence object. A table definition that refers to the nextval of a sequence requires that the sequence object be already created. Like many SQL inspections of objects, the SQL shown is postgres's internal representation of the SQL, not the SQL originally entered by the DBA/dev. However, there is no advantage whatsoever in doing this manually - just use the convenience syntax of serial and everything will be OK. Defining sequences separately gives you no advantage whatsoever, except typing practice.

user8895989 Over a year ago

Thank you, this is really helpful. Also, the naming convention that you suggested makes complete sense, it is a overkill and probably makes things more confusing. Thanks for your amazing feedback.

jcaron Over a year ago

Of course the alternative is to create the sequence before creating the table, which is probably what'll you find in a database dump. It would probably also include setting the sequence's current value.

Bohemian Over a year ago

@jcaron that's exactly what serial is converted to before execution: a create sequence and a create table that references the sequence. serial` is just a super convenient short hand for that - it's not really an "alternative", it's an abbreviation.

|

ckroutz · Accepted Answer · 2022-10-30 08:49:39Z

0

You just need to create the sequence before creating the table

CREATE SEQUENCE automated_group_msg_idx;

answered Oct 30, 2022 at 8:49

ckroutz

258 bronze badges

Collectives™ on Stack Overflow

Postgres create table error

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related