TIL: PostgreSQL Insert ID Only Works With id as Primary Key

I’ve created a database table for my PostgreSQL database with Nim:

import db_postgres, os

## some code

proc setup*(database: Database) =
  database.db.exec(sql"""
    CREATE TABLE IF NOT EXISTS Url(
      shortcode SERIAL PRIMARY KEY,
      orig_url VARCHAR(255) NOT NULL
    );
  """)

## more code

My primary key is shortcode as a SERIAL data type. That means the shortcode column automatically increments.

I want to insert data into the database and return the generated ID for the row.

## convert uint64 type to a string
proc `$` *(i: uint): string {.inline.} =
  $uint64(i)

proc shorten*(database: Database, orig_url: string): string =
      $database.db.insertID(
          sql"INSERT INTO Url (orig_url) VALUES (?)", orig_url)

The code throws an error: returning id column name “id” does not exist.

If you check the documentation for insertID you’ll see that “INSERT” in PostgreSQL solely works if the primary key’s name is id.

proc insertID*(db: DbConn, query: SqlQuery,
               args: varargs[string, `$`]): int64 {.
               tags: [WriteDbEffect].} =

executes the query (typically “INSERT”) and returns the generated ID for the row. For Postgre this adds RETURNING id to the query, so it only works if your primary key is named id.

In my example, the primary key was shortcode. I thought it would be more descriptive than id. If you use SQLite3 that works, but not with PostgreSQL.

If you rename the primary key to id, your program will function.

TIL: PostgreSQL Insert ID Only Works With id as Primary Key

Further Reading