QueryFailedError: the column "price" contain null values - TypeORM - PostgreSQL

Question

I've created a simple table:

import { Column, Entity, PrimaryGeneratedColumn } from "typeorm"

@Entity()
export class Test {
    @PrimaryGeneratedColumn()
    public id!: number

    @Column({ nullable: false })
    public name!: string

    @Column({ nullable: false, type: "float" })
    public price!: number
}

I generate the migration and run it also. When I have no data in the database and I run the server it succeed. But when I add 1 row in the database and I run it again it appears the following error:

QueryFailedError: the column «price» contain null values

The databse definetely has the row with all the data. I tried a lot of cases and none of them was correct.

Has anybody some idea for it?

Answer 1

I had a similar issue, and I reported it at the bottom of this thread.

You probably have synchronize: true on your ORM configuration. Because of this, every time you run your app Typeorm tries to create the tables. If you have data in your DB, it throws that misleading error.

From here:

synchronize - Indicates if database schema should be auto created on every application launch. Be careful with this option and don't use this in production - otherwise you can lose production data. This option is useful during debug and development. As an alternative to it, you can use CLI and run schema:sync command. Note that for MongoDB database it does not create schema, because MongoDB is schemaless. Instead, it syncs just by creating indices.

Answer 2

The Problem

This line of code right here is the troublemaker:

@Column({ nullable: false, type: "float" })
public price!: number

You have set nullable: false, which means you never want this column to be null.

Why is this giving you trouble?

Because you already have rows in your database that don't have an entry for this column.

Remember:

Prior to this migration, the column didn't exist. But now you're running a migration to add a new column.

But TypeORM needs a way to handle the existing rows...

So, if you were TypeORM, what value would you assign by default if the column didn't exist? Probably NULL. Which is exactly what it does. And that's why the error.

How do you fix this issue?

There are two ways here.

Very easy way

Set a default value in the ColumnOptions

@Column({ nullable: false, type: "float", default: 0.0 })
public price!: number

DANGER: The nuclear option

Delete all data from the table, then re-run the migration.

In Postgres PSQL:

DELETE FROM test;

Answer 3

I had this entity

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity()
export class User {

@PrimaryGeneratedColumn()
id!: number;

@Column('text')
firstName!: string;

}

and I was getting the error "column "firstName" contains null values." So I added {nullable: true} as @pleerock suggested here https://github.com/typeorm/typeorm/issues/845 and it worked.

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity()
export class User {

@PrimaryGeneratedColumn()
id!: number;

@Column('text', {nullable: true})
firstName: string;

}

Answer 4

Set "synchronize": false in ormconfig.json will fix this issue:

{
   ....
   "synchronize": false,
   ....
}