1

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:

enter image description here

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?

closed as off-topic by dezso, mustaccio, MDCCL, Colin 't Hart, Marco May 5 at 16:38

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, mustaccio, MDCCL, Colin 't Hart, Marco
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Are you saying that you manually ran that CREATE TABLE statement and the column was created as an integerin the backround? How and where did you run that CREATE TABLE statement? – a_horse_with_no_name Feb 25 at 14:58
  • 3
    Unrelated, but: you should really avoid those dreaded quoted identifiers. They are much more trouble in the long run than they are worth it. – a_horse_with_no_name Feb 25 at 14:58
  • 2
    Please try to drop your table before to execute CREATE TABLE IF NOT EXISTS or remove the IF NOT EXIST part – McNets Feb 25 at 15:04
  • 3
    Maybe your obfuscation layer (sequelize) changed that somehow. – a_horse_with_no_name Feb 25 at 15:13
  • 1
    You used CREATE TABLE IF NOT EXISTS ... , so the obvious explanation would be that a table of the same name already existed, just with integer instead of uuid, hence the statement did nothing, as instructed. – Erwin Brandstetter Feb 25 at 17:26
0

just typecast id value like this

postgres@localhost:test> create table "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")              
 );                                                                                 
CREATE TABLE                                                                        
Time: 0.048s                                                                        


postgres@localhost:test> INSERT INTO "Tenants" ("id","domain","name","schema","createdAt","updatedAt")
 VALUES ('3b6b220b-2690-4279-8343-cbe6167f7596'::UUID, 'test1.stage-domain.io', 'Test1','test1', '2019-02-25 14:29:33.475 +00:00','2019-02-25 14:29:33.475 +00:00') ;
INSERT 0 1
Time: 0.004s

hope this helps

Not the answer you're looking for? Browse other questions tagged or ask your own question.