Insert statement asked me to insert for autoincrement column

Question

I use PostgreSQL, I have created the following table:

CREATE TABLE "Task"
(
  "taskID" serial NOT NULL,
  "taskType" text NOT NULL,
  "taskComment" text NOT NULL,
  "taskDate" date NOT NULL,
  CONSTRAINT "Task_pkey" PRIMARY KEY ("taskID")
)

I put taskID as serial data-type to be incremented automatically. Now I am confused how to use the INSERT statement, as the first column in the table is supposed to increment automatically but INSERT statement asked me to insert a value by myself! Any Idea?

Here is my insert statement:

INSERT INTO "Task" VALUES ('HomeWork', 'No Comment', '3/3/2013');

Answer 1

You should NOT list this auto-incremented column in INSERT statement, and it will work as you expect:

INSERT INTO "Task" (
    "taskType", "taskComment", "taskDate"
) VALUES (
    'abc', 'my comment', '2013-03-17'
)

If you DO list it in INSERT, you will have to make sure that provided value does not conflict with any used values - which in general will be difficult to satisfy, unless you consistently use nextval('some_sequence').

Answer 2

@mvp's answer covers this pretty much. Except for the case where you (have to) add the id column in the column list for some reason. Then you can use the key word DEFAULT to default to the defined default value. Yeah, lots of "default" in this sentence, but it should be correct. :)

INSERT INTO "Task"("taskID", "taskType", "taskComment", "taskDate")
VALUES (DEFAULT, 'HomeWork', 'No Comment', '2013-03-03');

You'd also better use ISO 8601 date format (YYYY-MM-DD), which works independent of locale settings. Your regionally valid format is prone to breakage.

---

However, if you distance yourself from the folly to use mixed case identifiers to begin with, your life in PostgreSQL will be much simpler:

CREATE TABLE task (
  task_id serial NOT NULL PRIMARY KEY,
  task_type text NOT NULL,
  task_comment text NOT NULL,
  task_date date NOT NULL
);

INSERT INTO task
VALUES (DEFAULT, 'HomeWork', 'No Comment', '2013-03-03');

Or better:

INSERT INTO task (task_type, task_comment, task_date)
VALUES ('HomeWork', 'No Comment', '2013-03-03');