PostgreSQL迁移用户REFERENCE列时语法错误

Question

我正在处理一个PostgreSQL数据库。为了在其他很好的特性中加强迁移版本管理，我发现了一个用Go编写的数据库迁移，并将其添加到我的项目中。到目前为止，我还没有遇到任何问题，然而，我遇到了一个问题，如果没有一个hack的解决方案，我就无法克服。所以上下文是这样的，我有一个像下面这样的迁移文件，有4个表:用户、运动、挑战和活动。当我运行migrate up时，migrate抛出以下错误:

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

如果我从表中删除user_id列，migrate工作得很好，所有的表都创建了。但是，就像现在的user_id一样，它不起作用。有人知道这里有什么问题吗?怎样才能使我的迁移正常工作呢?

迁移文件:

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

在这里你有一个docker-compose包含运行迁移和Postgres数据库，如果你的环境中没有一个:):

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:

Answer 1

问题是user是一个保留字，使用时需要双引号。换句话说，它不是标识符的好名字。如果你想继续使用它，那么你必须修改:

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

:

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

和任何你使用它的地方