Null value in column "created_at" violates not-null constraint when using upsert

Question

Why is created_at null and how would I resolve this?

 ↳ app/controllers/projects_controller.rb:28:in `create'
  Tag Upsert (5.6ms)  INSERT INTO "tags" ("category","name") VALUES ('topic', 'career') ON CONFLICT ("id") DO UPDATE SET "type"=excluded."type","name"=excluded."name" RETURNING "id"
  ↳ app/controllers/projects_controller.rb:32:in `block in create'
Completed 500 Internal Server Error in 62ms (ActiveRecord: 31.0ms | Allocations: 22999)



ActiveRecord::NotNullViolation (PG::NotNullViolation: ERROR:  null value in column "created_at" violates not-null constraint
DETAIL:  Failing row contains (5, topic, career, null, null).
):

app/controllers/projects_controller.rb:32:in `block in create'
app/controllers/projects_controller.rb:31:in `each'
app/controllers/projects_controller.rb:31:in `create'

# projects_controller.rb
def create
    @project = Project.create(project_params)
    if @project.valid?
      # tags
      params[:tags].each do |tag|
        @tag = Tag.upsert({ category: 'topic', name: tag })
        ProjectTag.create(tag: @tag, project: @project)
      end
      respond_to do |format|
        format.json { render json: { "message": "success!", status: :ok } }
      end
    end
  end

mu is too short · Accepted Answer · 2021-09-22T20:55:21.093

4

upsert works in straight SQL with very little ActiveRecord involvement:

Updates or inserts (upserts) a single record into the database in a single SQL INSERT statement. It does not instantiate any models nor does it trigger Active Record callbacks or validations.

so AR won't touch updated_at or created_at like it usually does.

The easiest thing to do would be add a migration to add defaults in the database for created_at and updated_at:

change_column_default :tags, :created_at, from: nil, to: ->{ 'now()' }
change_column_default :tags, :updated_at, from: nil, to: ->{ 'now()' }

or you could use current_timestamp as the default (which will work with both PostgreSQL and MySQL):

change_column_default :tags, :created_at, from: nil, to: ->{ 'current_timestamp' }
change_column_default :tags, :updated_at, from: nil, to: ->{ 'current_timestamp' }

Then the database will take care of those columns.

Two things to note in the migration:

Passing the :from and :to options instead of just the new default gives you a reversible migration.
You have to use a lambda for the :to value so that the PostgreSQL now() function will be used rather than the string 'now()'. Similarly if you use current_timestamp instead of now().

edited Sep 22 '21 at 20:55

answered Jan 03 '21 at 03:29

mu is too short

426,620
70
833
800

Thank you! I somehow fail to understand upsert's functionality. Shouldn't it update columns that were previously set to `nil`? Somehow I ended up with a new error after migrating: `NoMethodError (undefined method 'keys' for [:category, "topic"]:Array)` (for the upsert) – Daniel Jan 03 '21 at 10:02
Upserts are done in straight SQL using the SQL you see in the log, AR does very little so you have to arrange everything, all your `created_at` and `updated_at` columns should have defaults in the database anyway (IMO). You've changed your code from `Tag.upsert` to `Tag.upsert_all`, right? – mu is too short Jan 03 '21 at 17:02
Changed it back, didn't help anything. I feel super out of my depth here and aren't sure where to even start looking. I assume it's best to open a new thread from here on out. Thanks again. – Daniel Jan 03 '21 at 19:00
1

Unfortunately, this is a DB-specific solution, so MySQL would have to use [something different](https://stackoverflow.com/a/5818452/41688), ditto for Sqlite, etc. – BryanH Feb 19 '21 at 15:47
@BryanH `->{ 'current_timestamp' }` as a default should work for both PostgreSQL and MySQL, not sure about SQLite though. – mu is too short Feb 19 '21 at 17:56

Null value in column "created_at" violates not-null constraint when using upsert

1 Answers1