A recent change in Postgresql or Ecto regarding the ID column of a table

Questions / Help
,
1

I’m using the latest version of:

% psql --version
psql (PostgreSQL) 9.6.5

So far my Phoenix application has worked fine. The last time I reseted my db was about 2-3 weeks ago.

Now after I’ve reseted it, my custom psql function has started throwing an exception related “integer vs bigint for ID/primary key column”.

 DETAIL:  Returned type bigint does not match expected type integer in column 1.

But it’s always been integer in my app with no problem.

The thing is that I’ve not changed anything in the migrations related to ID columns.

Has there been any breaking changes in Ecto or Postgresql related to ID/primary key datatypes?.

P.S.
In all my old phoenix applications all ID columns are now integers – it’s how they were generated by Ecto or Phoenix. I’ve not reseted a db in these apps. However, in this app they’re now generated bigint. Why? Where can I read about this?

2

It’s not that recent, but this did change in Ecto 2.0:

https://github.com/elixir-ecto/ecto/issues/1879

1 Like
3

How I setup the type of an ID key in Phoenix/Ecto? Make it integer or bigint or UUID …

4

You can use @primary_key/@foreign_key_type attributes

@primary_key {:id, :binary_id, autogenerate: true}
@foreign_key_type :binary_id
schema "users" do
    ...

to set the GUID type of Primary key for Ecto Schema.

Also for the GUID primary key You will need to override the default id generation in Migration like this:

  def change do
    create table(:users, primary_key: false) do
      add :id, :binary_id, primary_key: true
      ...

and I think You will need to manually set the GUID key type for @belongs_to associations:

    belongs_to :type, Type
    belongs_to :user, User, foreign_key: :user_id, type: :binary_id
1 Like
5

You could also migrate all the affected tables to the new datatype. PostgreSQL supports this with the following syntax:

ALTER TABLE <tablename> ALTER COLUMN id TYPE bigint;

Similar would need to be done on foreign keys in other tables. Due to foreign keys, you may need to drop relations first (not sure off-hand, would have to try it out to see what breaks and I’m away from anything with a usable database on it at this particular moment…).

So would be a bit of a pain, but a one time thing, and probably at least somewhat automatable given a list of tables with ids that need updating and a list of foreign key relationships that depend on them?