ERROR: relation "students" already EXISTS

Question

When I am executing this query:

CREATE TABLE public.students (
  id INTEGER PRIMARY KEY NOT NULL DEFAULT NEXTVAL('students_id_seq'::regclass),
  first_name CHARACTER VARYING(20) NOT NULL,
  last_name CHARACTER VARYING(20) NOT NULL,
  major CHARACTER VARYING(20) NOT NULL
);
CREATE UNIQUE INDEX "Students_ID_uindex" ON students USING BTREE (id);

SELECT * FROM public.students;

I get the following error:

[2016-03-12 22:16:54] Run postgres.public.students [PostgreSQL - postgres@localhost]
[2016-03-12 22:16:54] Connecting TO PostgreSQL - postgres@localhost...
CREATE TABLE public.students (
  id INTEGER PRIMARY KEY NOT NULL DEFAULT NEXTVAL('students_id_seq'::regclass),
  first_name CHARACTER VARYING(20) NOT NULL,
  last_name CHARACTER VARYING(20) NOT NULL,
  major CHARACTER VARYING(20) NOT NULL
)
[2016-03-12 22:16:54] [42P07] ERROR: relation "students" already EXISTS
CREATE UNIQUE INDEX "Students_ID_uindex" ON students USING BTREE (id)
[2016-03-12 22:16:54] [42P07] ERROR: relation "Students_ID_uindex" already EXISTS
SELECT * FROM public.students
[2016-03-12 22:16:54] Executed IN 14ms ms

[2016-03-12 22:16:54] Summary: 3 OF 3 statements executed, 2 failed IN 68ms (338 symbols IN file)

I have made the table using DataGrip generate:

Any idea what am I doing wrong?

UPDATE: Just to clarify my question, when I first run the code with a new table name, I get now error but when I run it again I get the above error. How can this be fixed?

Answer 1

You cannot create more tables with the same name - so statement CREATE should fail if there is a table with the same name already.

You can run the statement DROP TABLE before - but be aware! - it drops the table with all it's data, and undo is not possible. Second alternative is using the clause IF NOT EXISTS in CREATE statement:

DROP TABLE IF EXISTS foo;
CREATE TABLE foo(a int);

or

CREATE TABLE IF NOT EXISTS foo(a int);  

Answer 2

You don't need to use an index name, just allow to PG to made it by itselves:

CREATE INDEX ON public.students ("id");