Hi,

We are using LLBLGen Pro 5.4 with Npgsql 3.2.7.

We are porting from SQL Server to Postgresql and the code was relying on the support that LLBLGen has for newsequentialid() as a way to specify a default value for uniqueidentifier fields.

**Is there an equivalent for Postgresql (and uuid fields)? **

Details: 1. In Postgresql we have this primary key id that has is configured for default values as follows:

create table item
(
    id uuid default gen_random_uuid() not null
        constraint pk_item_678150057
        primary key
    ...
}

1. In the following code

var transaction = new Transaction(System.Data.IsolationLevel.Unspecified, "insert_my_item");

var insertedItem = new ItemEntity();

transaction.Add(insertedItem);

insertedItem.Save();

var idOfInsertedItem = insertedItem.Id;

the insertedItem.Id is an empty Guid (00000000-0000-0000-0000-000000000000).

As far as I know PostgreSQL doesn't generate values for the UUID, right?

In SQL Server, the database generates the values when NewSequentiaID() function is used as the default value (set in the database). So all LLBLGen does, is to fetch the value set by the SQL Sever after saving the new entity.

As far as I can see, you need to provide the UUID value from your application.

Walaa wrote:

As far as I know PostgreSQL doesn't generate values for the UUID, right?

Hi, thanks for getting back on this one.

We have the uuid-ossp extension installed and we have the gen_random_uuid() function configure to generate a default value for the uuid field. So the uuid is configured to get a default value. More, inserting through sql script populates the uuid field properly.

But in the code, the id is an empty guid after the item is being created.

This is the same thing as with SQL Server and using NEWID(): there's no way we can determine the default value inserted for the PK, so we can't read back the row nor return the PK value.

Newsequentialid() has the advantage that there's a way to retrieve the last inserted guid, so we use that to return the value back to the entity and read back the row (if needed). With your solution this isn't possible.

So to solve this you have to generate the guid on the client: var insertedItem = new ItemEntity(); insertedItem.PkItem = Guid.NewGuid(); //.. proceed as normal.

and remove the default constraint from the PK field.

Otis wrote:

Newsequentialid() has the advantage that there's a way to retrieve the last inserted guid, so we use that to return the value back to the entity and read back the row (if needed). With your solution this isn't possible.

Alright. This makes it more clear.

Thank you Walaa and Otis!