Inserting into table with a Default column value

Well, in your statement #1, if you omit the list of columns, you must supply values for all three columns, and you're not doing that. That's why it fails if you provide only two of those values.

And your statement #2 should probably be:

INSERT INTO Persons(name, age) values('Bob', 20)

and then you're clearly specifying which columns to insert into (name and age) and you're providing the two values required to fill two columns - that's why it works. The third column will be filled with the configured default, since you didn't specify anything for it.

For these reasons, I would recommend to always explicitly specify the list of columns you want to insert data into - don't just omit that list and assume that you're getting all columns right.....

If you don't want to write the fields, VALUES must match the number of fields in the table.

You can use DEFAULT four your purpose:

 INSERT INTO Persons VALUES('Bob', 20, DEFAULT);

The first one is failing because you are not specifying any of the columns in the INSERT statement. You are then only providing values for two columns instead of all 3 so it does not know what columns to put the values into...this won't work.

The second one works because you are explicitly stating that you want to insert into name and gender and you are specifying one value for each column.

The following will work (but I doubt you want 20 in the gender column):

INSERT INTO Persons(name,gender) values('Bob',20)

Or:

INSERT INTO Persons(name,age) values('Bob',20)

I am guessing that you actually want to be inserting into age not gender. It is very important that you explicitly state what columns you are inserting data into so you get the data in the right columns.