I can't insert data into the table

Question

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

Query:

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

Even the fields independent of all uppercase or lowercase, it does not find the passwordDoctor column in the medico table.

I've tried putting passwordDoctor all in lowercase, but it's no use.

The column's case will be ignored if not enclosed in double quotes. So your column ```passwordDoctor``` will become ```passworddoctor``` — vicki, Sep 02 '23 at 01:02
can you pease run https://stackoverflow.com/a/34518648/5193536 and see if your table gas actually that column, or if you are connected to a different database. also don't forget to **hash your password**. because there is nithing wrong with your query https://dbfiddle.uk/ia4gP2iv — nbk, Sep 02 '23 at 01:49

porrokynoa · Answer 1 · 2023-09-02T01:41:41.410

The error message you're encountering, "column 'passworddoctor' of relation 'medico' does not exist," suggests that there is a mismatch in the case sensitivity of the column name in your SQL query and the actual column name in your database table.

PostgreSQL is case-sensitive by default for column names unless you explicitly double-quote them during table creation. If you created the medico table with the column name "passwordDoctor" (with double quotes), you must use double quotes and the exact same casing when referring to it in SQL queries. If it was created without double quotes, PostgreSQL converts the column name to lowercase.

If the column name was not created with double quotes, ensure that both the column name in your query and your table match case exactly (e.g., "passwordDoctor" in the table creation, and "passwordDoctor" in your query, or both "passworddoctor" in both places without double quotes).

Here's an example based on the assumption that the column name was created with double quotes:

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

Edit

The error message 'column 'passworddoctor' of relationship 'doctor' does not exist' indicates that PostgreSQL is still looking for the column with the lowercase name, even though you changed it in the table schema and query.

After making changes to your table schema, it's essential to ensure that the database schema is reloaded and updated. You can try restarting your PostgreSQL server or using the pg_reload_conf() function to reload the configuration.

SELECT pg_reload_conf();

-- I've tried to recreate the table with all lowercase letters, even so it keeps giving error -- Schema Definition: 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 ); — Willames da S. Barbosa, Sep 02 '23 at 01:31
If you have recreated the table with all lowercase letters in your schema definition and continue to receive the error, there may be a caching or data persistence issue in your database. To ensure that the table has been effectively recreated with the new lowercase schema. — porrokynoa, Sep 02 '23 at 01:36
Apparently it must have been one of the problems you mentioned. I deleted the medico table and recreated it with another name, when I do the query, it says that the table does not exist. So I assume it must be one of the problems you mentioned, correct? — Willames da S. Barbosa, Sep 02 '23 at 01:53
If you created the table within a transaction, make sure that the transaction was committed successfully before attempting to query the table. If you created the table within a transaction, make sure that the transaction was committed successfully before attempting to query the table. — porrokynoa, Sep 02 '23 at 02:00

I can't insert data into the table

1 Answers1