ERROR: column "dtype" of relation "users" does not exist

Question

I'm creating an API with Spring Boot (3.1.1), Java, PostgreSQL and Flyway. I started doing the security settings with Spring Security, creating a user that could have 3 roles (ADMIN, USER, CUSTOMER) and the functions of authentication, authorization and get all/update/delete of users worked without problems. However, I wanted Customer to have more attributes than I had put in User, so I created a class called Customer and had it inherit from User. I did this in code and in migration. But now, when I try to register a new user (User, not Customer), it returns the error: ERROR: column "dtype" of relation "users" does not exist. I didn't put this attribute in the users table and I couldn't resolve the error. I tried to recreate the migrations and the database, but it didn't work.

Class User

package com.example.auth.domain.user;

import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;

import java.util.Collection;
import java.util.List;

@Table(name = "users")
@Entity(name = "users")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(of = "id")
public class User implements UserDetails {
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private String id;
    private String login;
    private String password;
    private UserRole role;

    public User(String login, String password, UserRole role){
        this.login = login;
        this.password = password;
        this.role = role;
    }

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
        if(this.role == UserRole.ADMIN){
            return List.of(new SimpleGrantedAuthority("ROLE_ADMIN"), new SimpleGrantedAuthority("ROLE_USER"), new SimpleGrantedAuthority("ROLE_CUSTOMER"));
        } 
        else if(this.role == UserRole.USER){
            return List.of(new SimpleGrantedAuthority("ROLE_USER"), new SimpleGrantedAuthority("ROLE_CUSTOMER"));
        }
        else{
            return List.of(new SimpleGrantedAuthority("ROLE_CUSTOMER"));
        }
    }

    @Override
    public String getUsername() {
        return login;
    }

    @Override
    public boolean isAccountNonExpired() {
        return true;
    }

    @Override
    public boolean isAccountNonLocked() {
        return true;
    }

    @Override
    public boolean isCredentialsNonExpired() {
        return true;
    }

    @Override
    public boolean isEnabled() {
        return true;
    }
}

Class Customer:

package com.example.auth.domain.costumer;

import com.example.auth.domain.user.User;
import com.example.auth.domain.user.UserRole;

import jakarta.persistence.Entity;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity(name = "customers")
@Table(name="customers")
@AllArgsConstructor
@Getter
@Setter
@NoArgsConstructor
public class Customer extends User{

    private String name;
    private String email;
    private String phone;
    private Integer relationshipTime;
    private Integer age;
    private CustomerContactRole contactorigin;
    private CustomerAccountTypeRole accounttype;

    public Customer(String login, String password, String name, String email, String phone,
            Integer relationshipTime, Integer age, CustomerContactRole contactOrigin,
            CustomerAccountTypeRole accountType) {
        super(login, password, UserRole.CUSTOMER);
        this.name = name;
        this.email = email;
        this.phone = phone;
        this.relationshipTime = relationshipTime;
        this.age = age;
        this.contactorigin = contactOrigin;
        this.accounttype = accountType;
    }

}

Migration: V1__create-product-user-customer-table.sql

CREATE TABLE product (
    id TEXT PRIMARY KEY UNIQUE NOT NULL,
    name TEXT NOT NULL,
    price INTEGER NOT NULL
);
CREATE TABLE users (
    id TEXT PRIMARY KEY UNIQUE NOT NULL,
    login TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role TEXT NOT NULL
);

CREATE TABLE customers (
    name TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    phone TEXT NOT NULL,
    relationshiptime INTEGER NOT NULL,
    age INTEGER NOT NULL,
    contactorigin TEXT NOT NULL,
    accounttype TEXT NOT NULL
) inherits (users);

Endpoint that generates de error (when I try to register a new User)

@PostMapping("/register")
    public ResponseEntity<String> register(@RequestBody @Valid RegisterDTO data){
        /*if(this.repository.findByLogin(data.login()) != null) {
            String jooj="";
            return ResponseEntity.status(HttpStatus.NOT_FOUND).body("Wrong acess.");
        }*/
        
        String encryptedPassword = new BCryptPasswordEncoder().encode(data.password());
        User newUser = new User(data.login(), encryptedPassword, data.role());

        this.repository.save(newUser);

        return ResponseEntity.status(HttpStatus.OK).body("Account created sucessfully");
    }

Error

Servlet.service() for 
servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement [ERROR: column "dtype" of relation "users" does not exist
  PosiþÒo: 40] [insert into users (login,password,role,dtype,id) values (?,?,?,'users',?)]; SQL [insert into users (login,password,role,dtype,id) values (?,?,?,'users',?)]] with root cause

org.postgresql.util.PSQLException: ERROR: column "dtype" of relation "users" does not exist
  Posição: 40```

Answer 1

The column dtype has been added implicitly to the table users because it is now the base entity for the customers. Hibernate uses it as a discriminator to distinguish between both objects. You should add it to the Flyway script as a TEXT type column.