PostgreSQL Migration syntax error with user REFERENCE column-CodePudding

I'm dealing with a PostgreSQL database. And to enforce a migration versioning management among other nice features, I've found Migrate a database migrations written in Go and I've added it to my project. Up until now, I haven't had any issue with it, however, I've encounter an issue that I'm not being able to overcome without a hacky workaround. So the context is the following, I have the migration file like the one below, with 4 tables: user, sport, challenge and activity. When I run migrate up, migrate throws the following error:

(details: pq: syntax error at or near "user")
error: Dirty database version 1. Fix and force version.

If I remove the user_id column from the table, migrate works just fine all the tables are created. But, as it is right now with the user_id it doesn't work. Would somebody know what is the problem here? And what's the fix to make my migration work properly?

Migration up file:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE IF NOT EXISTS "user"(
    "id" UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
    "username" VARCHAR(50) UNIQUE NOT NULL,
    "password" VARCHAR(50) NOT NULL,
    "created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS "sport"(
    "id" UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
    "sport_name" VARCHAR(50) UNIQUE NOT NULL,
    "created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS "challenge"(
    "id" UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
    "challenge_name" VARCHAR(50) UNIQUE NOT NULL,
    "created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    "sport_id" UUID  NOT NULL REFERENCES sport(id),
    "distance_goal" VARCHAR(50),
    "time_goal" VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS "activity"(
    "id" UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
    "created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    "date" VARCHAR(50),
    "time" VARCHAR(50),
    "distance" VARCHAR(50),
    "user_id" UUID NOT NULL REFERENCES user(id),
    "sport_id" UUID NOT NULL REFERENCES sport(id),
    "challenge_id" UUID REFERENCES challenge(id)
);

Here you have a docker-compose containing to run the migrate and Postgres DB if you don't have one in your environment :) :

version: '3.3'

services:
  # Based in https://stackoverflow.com/questions/55779979/how-to-run-golang-migrate-with-docker-compose/55779980
  steelman_db:
    image: postgres:13
    networks:
      database_net:
        aliases:
          - database_host
    environment:
      POSTGRES_DB: steelman_db
      POSTGRES_USER: steelman
      POSTGRES_PASSWORD: steelman
    ports:
      - "5432"
    healthcheck:
      test: pg_isready -U steelman -d steelman_db
      interval: 10s
      timeout: 30s
      retries: 5

  migrate:
    image: migrate/migrate
    networks:
      - database_net
    volumes:
      - ./db/migrations:/migrations
    command: ["-path", "/migrations", "-database", "postgres://steelman:steelman@database_host:5432/steelman_db?sslmode=disable", "up"]
    restart: "on-failure"
    depends_on:
      - steelman_db
    links: 
      - steelman_db
networks:
  database_net:

CodePudding user response：

The issue is user is a Reserved word and needs to be double quoted when used. In other words it is not a good name for an identifier. If you want to keep using it then you have to change:

"user_id" UUID NOT NULL REFERENCES user(id),

to:

"user_id" UUID NOT NULL REFERENCES "user"(id),

and anywhere else you use it.