My understanding is that Postgres supports UUID data type out-of-the-box. I'm running Postgres on Amazon RDS with Engine version 10.6 and am scratching my head as to why the following commands are not behaving as expected:
CREATE TABLE IF NOT EXISTS "Tenants" ( "id" UUID NOT NULL , "domain" VARCHAR(255) NOT NULL UNIQUE, "name" VARCHAR(255) NOT NULL, "schema" VARCHAR(255) NOT NULL UNIQUE, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id") );
The critical part here is that I'm creating
"id" UUID NOT NULL. So far, so good. Or so I thought.
Next, I'm trying to add an entry:
INSERT INTO "Tenants" ("id","domain","name","schema","createdAt","updatedAt") VALUES ( '3b6b220b-2690-4279-8343-cbe6167f7596', 'test1.stage-domain.io', 'Test1','test1', '2019-02-25 14:29:33.475 +00:00', '2019-02-25 14:29:33.475 +00:00') RETURNING *;
and surprisingly the following error shows up:
SequelizeDatabaseError: invalid input syntax for integer: "3b6b220b-2690-4279-8343-cbe6167f7596
Sure enough, if I go to PGAdmin, I'm seeing that the column has not been set to UUID, rather it has been set to an integer:
The dropdown shown in this picture does not even contain a UUID type.
The weirdest part is that I created a different Postgresql server on RDS last night and everything actually worked just fine in that instance.
My question: How can I get my database to recognize the UUID type when creating the Tenants table?