How to add timestamps to an existing table with Ec

问题:

Since inserted_at and updated_at can not be null this won't work:

def change do
  alter table(:channels) do
    timestamps
  end
end

** (Postgrex.Error) ERROR (not_null_violation): column "inserted_at" contains null values

Is there an easy way to accomplish this without copying timestamps' functionality?

回答1:

The timestamps/1 function accepts an options keyword list, you can set the default value with it.

def change do
  alter table(:channels) do
    timestamps default: "2016-01-01 00:00:01", null: false
  end
end

UPDATE Ecto >= 2.1
You'll need to use the new type NaiveDateTime

def change do
  alter table(:channels) do
    timestamps default: ~N[2017-01-01 00:00:01], null: false
  end
end

If you have more doubts take a look at the documentation

回答2:

I used following migration to add timestamps to existing table and fill them with current time:

defmodule MyApp.AddTimestampsToChannels do
  use Ecto.Migration

  def up do
    alter table(:channels) do
      timestamps null: true
    end

    execute """
    UPDATE channels
    SET updated_at=NOW(), inserted_at=NOW()
    """

    alter table(:channels) do
      modify :inserted_at, :utc_datetime, null: false
      modify :updated_at, :utc_datetime, null: false
    end
  end

  def down do
    alter table(:channels) do
      remove :inserted_at
      remove :updated_at
    end
  end
end

And there are other ways to do it. For example, if you have some related table, you can borrow initial timestamps from it:

execute """
UPDATE channels
SET inserted_at=u.inserted_at,
    updated_at=u.updated_at
FROM
  (SELECT id,
          inserted_at,
          updated_at
   FROM accounts) AS u
WHERE u.id=channels.user_id;
"""

回答3:

I guess you get this when you are trying to update a record, I can think 2 possible solutions you could either touch the inserted_at column in your table by running a UPDATE query or adding a function to your ecto model like so

  def create_changeset(model, attrs) do
    model
    |> cast(attrs, @required_fields, @optional_fields)
    |> update_inserted_at
  end

  defp update_inserted_at(changeset) do
    # check if the updated field is null set a new date
  end