Postgresql - few issues

[MikeKry]

Hi, we have encountered some issues when testing schemareader and generating migrations on PostgreSQL

• when dropping primary key with FK constraint - commands are in wrong order and migration does not go through. Same applies for unique key
• Type names are different in postgres and schema reader. e.g. decimal is numeric in postgresql, so dbschemareader always creates migration because it detects change.
• Identity cannot be set on existing column - there is problem with default value

[martinjw]

First issue: are you using migrationGenerator directly or via CompareSchemas?
I'm trying to work out a test for it, but I'm not sure how...

            //CREATE TABLE employees (
            //    employee_id SERIAL PRIMARY KEY,
            //    name VARCHAR(100),
            //    manager_id INTEGER,
            //    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
            //);
            var schema = new DatabaseSchema(null, SqlType.PostgreSql);
            schema.AddTable("employees")
                .AddColumn<int>("employee_id").AddIdentity().AddPrimaryKey("pkEmployees")
                .AddColumn<string>("name").AddLength(100)
                .AddColumn<int>("manager_id")
                .AddForeignKey("manager_id", "employees", "employee_id");

            var schema2 = new DatabaseSchema(null, SqlType.PostgreSql);
            schema2.AddTable("employees")
                .AddColumn<int>("employee_id")
                .AddColumn<string>("name").AddLength(100)
                .AddColumn<int>("manager_id");

            var comparer = new CompareSchemas(schema,schema2);
            var migration = comparer.Execute();
            Console.WriteLine(migration);

[martinjw]

Second issue: if you're using the .AddColumn("x") it's mapped using a class called PostgreSqlDataTypeMapper to the pgsql type decimal. This is of course just an alias of NUMERIC, and with different precisions you could map to different types. In that case you have to define it manually (set the column DataType and DbDataType)- or create an extension that does it for you (eg AddPgSqlNumeric).

If you're using compare between two actual schemas read from databases, the DataTypes should be correct

[martinjw]

Third issue is tough, and occurs in other databases too. You have to set the next value of the serial sequence/identity to the max(id)+1 of the column. The last time I did that we used a bigger round number, for business reasons so we could tell the old defaults from the new identity- writing the code automatically may be dangerous here.