PostgreSQL primary key auto increment crashes in C++

Question

What is the correct syntax to create an integer primary key auto incremental field in PostgreSQL using C++?

I started with

db->ExecuteSQL("CREATE TABLE mytable (\"mytableid\" INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,

This compiles but the process crashes and no field is created.

db->ExecuteSQL("CREATE TABLE mytable (\"mytableid\" serial PRIMARY KEY NOT NULL,

This works and does create the field correctly.

Do I need the NOT NULL or is this not necessary with serial?
Is this the best syntax and method in Postgres for primary key field creation?

Answer 1

You do not need the NOT NULL. It is implied when you define the column PRIMARY KEYS. Per documentation:

Technically, a primary key constraint is simply a combination of a unique constraint and a not-null constraint.

In addition, serial also implies NOT NULL. It's not a data type per se, just a notational convenience for integer NOT NULL with an attached sequence.

So this is perfect syntax:

CREATE TABLE mytable (mytableid serial PRIMARY KEY);

You don't need to double quote the column name as long as you don't want to use mixed case identifiers, reserved words or "illegal" characters. I would advise to use legal, lower case identifiers exclusively to make your code less error-prone (and your life simpler).