PostgreSQL: Create Tables with Auto-Increment IDs

This article walks you through 2 different approaches to creating tables with auto-generated primary keys in PostgreSQL. Without any further ado, let’s get in action.

Using generated as identity

This syntax is available from PostgreSQL 10 and it is compliant with the SQL standard.

Example

Create a new table named “users” and insert some data into it:

/* create the table */
CREATE TABLE users(
  id Integer Primary Key Generated Always as Identity,
  name VARCHAR(50),
  email VARCHAR(50)
);

/* adding some data */
INSERT INTO users(name, email)
VALUES
  ('Goodman', '[email protected]'),
  ('Badman', '[email protected]'),
  ('Invisible Man', '[email protected]');

Now when selecting all things from that table, you will get output like this:

For more detailed information, see the official documentation.

Using Serial

Using serial syntax is a way that has been around for a long time with PostgreSQL. It’s shorter and easier to memorize.

Example

Create a table named “photos” and add some dummy data into it:

/* create the table */
CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  url VARCHAR(200)
);

/* insert some dummy data */
INSERT INTO photos(url)
VALUES
  ('https://www.kindacode.com/1fdfafdfdf.jpg'),
  ('https://www.kindacode.com/2fdfdfd.jpg'),
  ('https://www.kindacode.com/eerda3.jpg'),
  ('https://www.kindacode.com/bbafd4.jpg'),
  ('https://www.kindacode.com/1aaaa.jpg');

And here’s what you have:

Further reading:

• SQLite: Select Random Records from a Table
• How to see your MySQL version in macOS
• Node + Mongoose + TypeScript: Defining Schemas and Models
• How to Create/Delete a Database with MongoDB Shell
• MongoDB: Renaming a Database with Command Line

I have made every effort to ensure that every piece of code in this article works properly, but I may have made some mistakes or omissions. If so, please send me an email: [email protected] or leave a comment to report errors.