Inserting into table with a Default column value

Question

I have created a table with a default column value male

CREATE TABLE Persons 
 (name varchar(20), 
  age int, 
  sex varchar(5) default 'male')

Insert statement #1:

INSERT INTO Persons values('Bob', 20)

Insert statement #2:

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

When I try to insert the values into Persons table using the insert statement #1, I get the error shown below

column name or number of supplied values does not match table definition

But when I insert into Persons using statement #2, it successfully executes.

Can some please explain why this happens?

Answer 1

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