fireproofsocks

fireproofsocks

I’m able to create and migrate my database tables Ok. But when I try to seed it, I’m getting errors as soon as I encounter a table that uses the timestamps() macro in its migration definition:

** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "inserted_at" violates not-null constraint

    table: tenants
    column: inserted_at

My migration looks like this:

defmodule Auth.Repo.Migrations.Tenants do
  use Ecto.Migration

  def change do
    create table(:tenants, primary_key: false) do
      add :id, :string, size: 16, null: false, primary_key: true # Slug
      add :status_id, references("tenants_statuses", type: :string), size: 10, null: false
      add :name, :string, size: 32, null: false, default: ""
      add :description, :text, null: false, default: ""
      timestamps()
    end
  end
end

And my seeds.exs like this:

alias Auth.Repo

Repo.insert_all(
  "tenants_statuses",
  [
    %{id: "on", name: "On", description: "Signups and logins for this tenant are enabled"},
    %{id: "off", name: "Off", description: "Signups and logins for this are disabled"},
  ]
)
Repo.insert_all(
  Auth.Schemas.Tenant,
  [
    %{
      id: "wholesale",
      name: "Wholesale",
      status_id: "on",
      description: "Deny Designs and the Wholesale channel",
    }
  ]
)

I’ve tried using both the string table name as the 1st argument or the schema module, but the error is the same. When I inspect the table inside PostGreSQL, I see that it has not defined a default value:

Column    |              Type              | Collation | Nullable |        Default        | Storage  | Stats target | Description 
-------------+--------------------------------+-----------+----------+-----------------------+----------+--------------+-------------
id          | character varying(16)          |           | not null |                       | extended |              | 
status_id   | character varying(10)          |           | not null |                       | extended |              | 
name        | character varying(32)          |           | not null | ''::character varying | extended |              | 
description | text                           |           | not null | ''::text              | extended |              | 
inserted_at | timestamp(0) without time zone |           | not null |                       | plain    |              | 
updated_at  | timestamp(0) without time zone |           | not null |                       | plain    |              | 

I thought that using timestamps() would set up a default value for both of those columns, but clearly I’m missing something. Can anyone point out what I’m missing? Thanks!

Showing Posts 1 to 7

idi527

idi527

:waving_hand:

The defaults come from your ecto schemas (if you use another timestamps() macro there), they are not generated in the database.

So since *_all functions like Repo.insert_all don’t use ecto schemas, there are no default timestamps being sent to the database thus violating the constraint.

fireproofsocks

fireproofsocks OP

THANK YOU! I did not think of that. Ok, that makes sense: you can set some database-level constraints in PostGreSQL, setting up an “on update CURRENT TIMESTAMP” (or whatever the MySQL equivalent is) is more complicated in PostGreSQL and it requires a trigger. So instead, that functionality is handled by code in the Schema. That makes sense. As soon as I adjusted my seeds to use Repo.insert/1 it worked.

Repo.insert(
    %Auth.Schemas.Tenant{
      id: "wholesale",
      name: "Wholesale",
      status_id: "on",
      description: "Deny Designs and the Wholesale channel",
    }
)
axelson

axelson

Scenic Core Team

Minor clarification, Repo.insert_all does use ecto schemas but does not autogenerate default values. From the docs for Repo.insert_all/3:

If the schema contains an autogenerated ID field, it will be handled either at the adapter or the storage layer. However any other autogenerated value, like timestamps, won’t be autogenerated when using insert_all/3 . This is by design as this function aims to be a more direct way to insert data into the database without the conveniences of insert/2 . This is also consistent with update_all/3 that does not handle timestamps as well.

fireproofsocks

fireproofsocks OP

Further clarification: Repo.insert_all MAY use Ecto Schemas (but it is not required to do so: you can simply pass the name of the table as a string). At various points I have tried removing my schema files altogether to avoid having my seeding operation dependent on them – it works fine (excepting the timestamps as discussed)

axelson

axelson

Scenic Core Team

True! And also true for Repo.insert :slight_smile:

ibarch

ibarch

In order to freely use both schema and schemaless queries and don’t bother yourself manually updating timestamps, you must move autoupdating logic from timestamps macro to the database layer. In PostgreSQL you can use the built-in moddatetime extension and its function with a trigger. Take a look at my examples.

sagastume

sagastume

Another way is to store the timestamp in a variable and pass it with the structure.

Example:

alias Auth.Schemas.Tenant
now = DateTime.utc_now() |> DateTime.truncate(:second)
Repo.insert_all(Tenant, [
  %{name: "wholesale",  inserted_at: now, updated_at: now},
])
— All posts loaded —

Where Next? Top

Trending in Questions Top

katta
I having some trouble figuring out if I have set myself too strict of standards for my production server. Currently I can handle 75% of r...
New
brecabral
Documentation While reading the Scoped Routes section, I noticed that the documentation currently refers to a problem without explainin...
New
achenet
Hello, I’m trying to build a basic Phoenix web-app, and I’d like to use Tailwind. However, when I launch mix phx.server, I get an error...
New
kpanic
Hi everyone, I am toying with the idea of building a “match maker” for giving personal help to people that wants to start coding. I sta...
New
Cxx-mlr
I’m working on a small exercise involving update_in/3, and I came up with this solution: data = %{ name: "Periodic Table", category:...
New
ChrisAmelia
I’ve got trouble wrapping my head around the order in which functions are called in this snippet (from Phoenix’s authentication): toke...
New
dillonoconnor
Is there any way to avoid the Hologram compiler running when using iex? It seems like the front-end code could potentially be disregarded...
New

Other Trending Topics Top

GenericJam
Edit: 2026 May 15 - This post is archived. Mob is alive!! Main docs: mob v0.7.11 — Documentation A bit of explanation for the slightly c...
New
mudasobwa
I am happy to introduce the very α version of the new programming language compiled to BEAM. Welcome Cure. It has literally three kille...
New
garrison
Hobbes is a low-level distributed database for the Elixir programming language. Hobbes provides a simple, safe, and scalable storage lay...
New
budgie
A little off-topic, but I feel like people here have a good head on their shoulders. I used to be quite good at making software. Was luc...
New
KristerV
Hey. Is there anyone here who creates agents in their apps? Not talking about using agents, but creating them. I’m finding it pretty diff...
New
mcass19
ExRatatui lets you cook up rich terminal UIs in Elixir, powered by Rust’s ratatui via Rustler NIFs. Build interactive terminal applicatio...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews