Migrations throwing syntax errors after switching from Mariaex to Postgrex

I had started a phoenix project with --database mysql, then I decided that I want to use postgresql. After the switch to postgrex my ecto migrations scripts which were running fine with mariaex are now throwing syntax errors in postgrex.

I have updated my config Repo adapters and am able to create a database via ecto.create, is there anything else am missing?

The exception raised is as below:

[wogembo@hostname myapp] $ mix ecto.migrate

13:59:40.504 [info] == Running Contrebate.Repo.Migrations.CreateSubscriber.change/0 forward

13:59:40.504 [info] create table subscribers
** (Postgrex.Error) ERROR 42601 (syntax_error): syntax error at or near “(”
(ecto) lib/ecto/adapters/sql.ex:195: Ecto.Adapters.SQL.query!/5
(ecto) lib/ecto/adapters/postgres.ex:86: anonymous fn/4 in Ecto.Adapters.Postgres.execute_ddl/3
(elixir) lib/enum.ex:1623: Enum."-reduce/3-lists^foldl/2-0-"/3

Below is the migration script raising this error:

defmodule Contrebate.Repo.Migrations.CreateSubscriber do
  use Ecto.Migration

  def change do
    create table(:subscribers) do
      add :msisdn, :bigint, size: 12
      add :first_name, :string
      add :middle_name, :string
      add :last_name, :string
      add :account_id, :bigint

      timestamps()
    end
    create unique_index(:subscribers, [:msisdn])

  end
end

okay, so I suspect it’s the line:

add :msisdn, :bigint, size: 12

I think in Postgres the bigint is up to 8 bytes in size and you attempt to use 12.

If I am reading this correctly, it is also a mobile/ID number of sort in this field. If this is true, it most likely should not be an integer type field, but a :string.

Integers should be used for stuff you will make mathematical operations on (and foreign keys if you choose so). You may have leading zeroes here, and also it is a large number so you may reach some limits (your size: 12 overrides this), can be slow when parsing/displaying etc as text. As a rule of thumb use :string for phone numbers, ISDNs, barcode numbers, EINs etc.

Thanks Hubert.

I changed the msisdn data type from bigint to string as advised, and the migrations run successfully.
Also thanks for your suggestion on how to determine whether to use integers or not for a particular field. Really helpful. :slight_smile:

1 Like

Glad I could be of assistance.