"ERROR: syntax error at or near "INSERT" " PostgreSQL error

Question

I am new to SQL and am attempted to create a table, then alter the table to add some constraints with ALTER and finally insert 2 employees, one managing the other. I have been getting this error in pgAdmin4 "ERROR: syntax error at or near "INSERT" If anyone could let me know any other obvious issues in my code that would be great

Here is the table:

CREATE TABLE employee ( 
    emp_id     integer NOT NULL, 
    first_name varchar(15) NOT NULL, 
    last_name  varchar(15) NOT NULL, 
    manager    integer 
);

And the altering:

ALTER TABLE employee ADD CONSTRAINT unique_emp_id UNIQUE (emp_id);  

ALTER TABLE employee ADD FOREIGN KEY (manager) REFERENCES emp_id;

ALTER TABLE employee ADD CHECK (emp_id != manager)

INSERT INTO employee VALUES
  (1, 'Tom', 'Jones', 2);

INSERT INTO employee (emp_id, first_name, last_name)
  VALUES  (2, 'Dave', 'Smith');

There is a semicolon missing after the last ALTER TABLE statement. — clemens
– clemens, Commented Mar 16, 2018 at 5:42

Gordon Linoff · Accepted Answer · 2018-03-16 03:10:56Z

1

The foreign key reference needs to mention the table, even for a self-reference:

ALTER TABLE employee ADD FOREIGN KEY (manager) REFERENCES employee(emp_id);

In addition, the first insert doesn't work because the table is empty, so the foreign key fails. The inserts are in the wrong order. You should also list all the columns for an insert, even when you think you know them.

Here is a SQL Fiddle with the rest of the statements working.

edited Mar 16, 2018 at 3:10

answered Mar 16, 2018 at 3:02

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Gordon Linoff Over a year ago

@JamesLindsay . . . Yes, you have to add the manager with key 2.

Collectives™ on Stack Overflow

"ERROR: syntax error at or near "INSERT" " PostgreSQL error

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related