无法向medico表插入数据：passwordDoctor列不存在问题求助

错误信息 #

Error:
Error connecting to database error: column "passworddoctor" of relation "medico" does not exist
    at C:\Users\willa\OneDrive\Área de Trabalho\Projetos e cursos\projeto-agenda\agenda-back-end\node_modules\pg\lib\client.js:526:17
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async exports.query (C:\Users\willa\OneDrive\Área de Trabalho\Projetos e cursos\projeto-agenda\agenda-back-end\src\database\index.js:34:24) 
    at async DoctorsRepository.create (C:\Users\willa\OneDrive\Área de Trabalho\Projetos e cursos\projeto-agenda\agenda-back-end\src\app\repositories\doctorsRepository.js:16:18)
    at async store (C:\Users\willa\OneDrive\Área de Trabalho\Projetos e cursos\projeto-agenda\agenda-back-end\src\app\controllers\doctorController.js:44:20) {
  length: 133,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: '60',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_target.c',
  line: '1061',
  routine: 'checkInsertTargets'
}

表结构Schema #

CREATE TABLE IF NOT EXISTS medico (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    nome VARCHAR NOT NULL,
    especialidade VARCHAR(50) NOT NULL,
    crm VARCHAR(20) NOT NULL,
    email VARCHAR,
    passwordDoctor VARCHAR,
    authLevel smallint
);

插入代码 #

async create({
    nome, especialidade, crm, email, passwordDoctor, authLevel,
  }) {
    const rows = await database.query(`
      INSERT INTO medico(nome, especialidade, crm, email, passwordDoctor, authlevel)
      VALUES($1, $2, $3, $4, $5, $6)
      RETURNING *
    `, [nome, especialidade, crm, email, passwordDoctor, authLevel]);

    return rows;
  }

问题描述 #

尝试向medico表插入数据时，无论将passwordDoctor字段改为大写或全小写，均报错提示该列不存在，请求解决方法。

解决方案 #

1. 先确认表的实际列名 #

PostgreSQL会自动将未加双引号的标识符（表名、列名）转换为小写。在PostgreSQL客户端执行以下命令，查看medico表的实际列名：

SELECT column_name FROM information_schema.columns WHERE table_name = 'medico';

2. 针对不同情况修复 #

情况一：表中实际无passworddoctor列

如果查询结果显示没有passworddoctor列，说明建表语句未生效（比如CREATE TABLE IF NOT EXISTS因为表已存在而跳过执行），可以执行以下语句添加该列：

ALTER TABLE medico ADD COLUMN passworddoctor VARCHAR;

或者删除旧表后重新执行建表语句。

情况二：需要保留驼峰式列名

如果你想保留passwordDoctor这种驼峰命名，建表和插入时都必须给列名加上双引号，PostgreSQL才会保留大小写：

• 修正后的建表语句：

CREATE TABLE IF NOT EXISTS medico (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    nome VARCHAR NOT NULL,
    especialidade VARCHAR(50) NOT NULL,
    crm VARCHAR(20) NOT NULL,
    email VARCHAR,
    "passwordDoctor" VARCHAR,
    "authLevel" smallint
);

• 修正后的插入代码：

async create({
    nome, especialidade, crm, email, passwordDoctor, authLevel,
  }) {
    const rows = await database.query(`
      INSERT INTO medico(nome, especialidade, crm, email, "passwordDoctor", "authLevel")
      VALUES($1, $2, $3, $4, $5, $6)
      RETURNING *
    `, [nome, especialidade, crm, email, passwordDoctor, authLevel]);

    return rows;
  }

3. 额外注意 #

PostgreSQL推荐使用小写标识符并以下划线分隔（比如password_doctor），可以避免大小写相关的问题，后续开发建议遵循这个规范。

内容的提问来源于stack exchange，提问作者Willames da S. Barbosa