Postgresql SmallSerial issue

Toad Data Modeler
1

When I create a relation in a table with a SmallSerial field, the field created in the child table is also a SmallSerial instead of a SmallInt. For the Serial and BigSerial this issue does not occurs.

How can I solve this issue?

Is there a way to create sequences (using script) for all tables?

Regards

2

Hi,

unfortunately it is a bug that needs to be fixed in the application (modification of system scripts won’t help in this case). Change request #111519 created. The next version will work properly.

Regarding sequences: what would be the result, please? Do you wish to create sequence for all existing tables and use it in attribute DEFAULT? (as “nextval(‘newsequence’::text)”)) Do you plan to use some naming convention?

Thank you,

Vaclav

3

Why would you need a sequence for each table when you define the columns as serial?

Postgres will automatically create a sequence for each serial column and make it the default value for that column.

4

I want a sequence for the tables which have the SmallInt field as my primary key. I cannot use the SmallSerial due this bug.

I solve the problem using a script to change the datatypes of the child tables to SmallInt instead of SmallSerial.

    function main() {
var app = System.GetInterface('Application');
var Model = app.Models.GetObject(2); // Change the model, 0=first
var Entity, Attribute;
var i, j;
var DataType_SmallInt = Model.ModelDef.DataTypes.GetObjectByName("Smallint");
if (DataType_SmallInt == null)
return;
Model.Lock();
for (i = 0; i < Model.Entities.Count; i++) {
Entity = Model.Entities.GetObject(i);
Entity.Lock();
for (j = 0; j < Entity.Attributes.Count; j++) {
Attribute = Entity.Attributes.GetObject(j);
if (Attribute.Datatype.Caption == 'Smallserial' && Attribute.FKForeignKeys.Count > 0) {
Log.Information("Change datatype of " + Entity.Caption + '.' + Attribute.Caption + ' from ' + Attribute.Datatype.Caption + ' to Smallint');
Attribute.SetLinkedObject("DataType", DataType_SmallInt);
}
}
Entity.Unlock();
}
Model.Unlock();
Log.Information("DONE");
}

Thanks for yours help.

5

Hi,

See the script below.

  • SmallSerial in FK attribute set to SmallInt (that’s what your script does as well)

  • SmallSerial in PK attribute set to SmallInt + default of the attribute set to sequence + the sequence is created with correct Owned By parameters set

The script aims to do it the same way as if PostgreSQL did it itself.

function main()
{
var app = System.GetInterface(‘Application’);
var Model = app.Models.GetObject(4); // Change the model, 0=first
var Entity, Attribute;
var i, j;

var DataType_SmallInt = Model.ModelDef.DataTypes.GetObjectByName(“Smallint”);
if (DataType_SmallInt == null)
return;

Model.Lock();
for (i = 0; i < Model.Entities.Count; i++)
{
Entity = Model.Entities.GetObject(i);
Entity.Lock();
for (j = 0; j < Entity.Attributes.Count; j++)
{
Attribute = Entity.Attributes.GetObject(j);
if (Attribute.Datatype.Caption = ‘Smallserial’)
{
if (Attribute.IsPrimaryKey || Attribute.FKForeignKeys.Count > 0)
{
Log.Information(“Change datatype of " + Entity.Caption + ‘.’ + Attribute.Caption + ’ from ’ + Attribute.Datatype.Caption + ’ to Smallint’);
Attribute.SetLinkedObject(“DataType”, DataType_SmallInt);
}
if (Attribute.IsPrimaryKey)
{
Log.Information(“Change default of " + Entity.Caption + ‘.’ + Attribute.Caption);
var SeqName = Entity.Name+”_”+Attribute.Name+"_seq";
Attribute.DefaultValue = “nextval(’”"+SeqName+""’::regclass)";
var Seq = Model.CreateNewObject( 51000 ); //object type of PEREntity
Seq.Name = SeqName;
Seq.SetLinkedObject(“OwnedByTable”, Entity);
Seq.OwnedByColumn = Attribute.Name;
}
}
}
Entity.Unlock();
}
Model.Unlock();

Log.Information(“DONE”);
}

Regards,

Lukas