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'm 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');

How can a programmer ask this question *without* showing the `INSERT` statement? — Erwin Brandstetter, Mar 17 '13 at 19:03
Now it's a good question. While mostly irrelevant in this case, PostgreSQL *version* should always be included. — Erwin Brandstetter, Mar 17 '13 at 19:31

Erwin Brandstetter · Accepted Answer · 2023-01-10T01:50:27.037

@mvp's answer covers this pretty much. Except for the case where you (have to) add the auto-incremented column in the column list for some reason. Then you can use the key word DEFAULT to default to the defined default value.

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

Also, 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 of mixed case identifiers, living with Postgres becomes much easier:

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');

I just want to reinforce the opinion that listing the columns in all INSERT statements is much better practice than relying on the order they were defined. It makes code both more readable and more robust. — IMSoP, Mar 17 '13 at 22:06

score 5 · Answer 2 · answered Mar 17 '13 at 19:01

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').

score 0 · Answer 3 · answered Sep 30 '22 at 00:18

0

You can use DEFAULT for the serial column(field) and insert rows without column(field) names as shown below:

INSERT INTO Task VALUES 
(DEFAULT, 'HomeWork1', 'No Comment', '3/3/2013'), 
(DEFAULT, 'HomeWork2', 'No Comment', '4/4/2014');

answered Sep 30 '22 at 00:18

Super Kai - Kazuya Ito

22,221
10
124
129

Insert statement asked me to insert for autoincrement column

3 Answers3