I just downloaded 5.3.2 a few days ago. (I was previously using 3.5) .net 4.5 Postgres 9.4 Adapter

I am trying to use the Model First approach with a new project, and I cannot find a way to end up with an unspecified numeric (e.g. ALTER TABLE x ADD y numeric; )

My old database first project (v3.5) created entity fields from the relational database as decimal with a precision and scale (0, 0).

For the Model First with this new project (v5.3), I am trying to accomplish the same db field by creating a an entity field with decimal with 0,0. But the ddl script is creating the fields with (0,0) instead of omitting the precision and scale which is required to accomplish the goal of an unrestricted decimal number.

This is the sql generated by the designer:

ALTER TABLE "lc"."lcbidhistory" 
    ADD COLUMN "discountmargin" numeric(0, 0) NULL;

Postgres returns this error:

ERROR: NUMERIC precision 0 must be between 1 and 1000 LINE 3: ADD COLUMN "allocationpct" numeric(0, 0) NULL;

So what I need is the ddl generator to recognize the (0,0) and omit it entirely:

ALTER TABLE "lc"."lcbidhistory" 
    ADD COLUMN "discountmargin" numeric NULL;

Hopefully, there is a setting I overlooked or a template file that can can be patched to get this moving, but I'd prefer if the solution become part of the base install of LLBLGen. I prefer not to have to maintain a set of custom templates.

I finally got the approval to upgrade to 5.3 because 3.5 didn't seem to generate the ddl the way I wanted (i.e. case insensitive, using the "Relational model data element name casing case sensitive dbs" project setting). That worked! But I need to get this numeric issue resolved. I took on a project to prove that I can spin up an app in 3 weeks using .net and llblgen, and it result in a better product than a 3rd party business rules engine that we're currently being sold on.

Indeed, it's not possible to not specify the scale/precision in your numeric type. I checked the postgres documentation. It says that explicitly, you can specify up to 1000 in scale. If not specify, the db takes a very big number as the limit of digits (131072).

Do you have a explicitly number of digits for your scale? Anyway we will look into this.

If you really need this, you could override the involved template that generates the field definition. To do so, follow these steps:

1. Copy [LLBLGenInstallFolder]\Frameworks\DDL SQL\Templates\PostgreSqlSpecific\FieldCreationInclude.lpt into [yourCustomTemplatesFolder]

2. Modify the file so the Precision/Scale is not emitted if they are both 0:

    case PostgreSqlDbTypes.Numeric:
                if (field.TypeDefinition.Precision > 0 || field.TypeDefinition.Precision > 0 )
                {
                    toReturn.AppendFormat("({0}, {1})", field.TypeDefinition.Precision, field.TypeDefinition.Scale);
                }
                break;
        ...

1. Create a .templatebindings file in [yourCustomTemplatesFolder] to map your new template. You need to put it the exact TemplateID so it will override the built-in one. Save it as [yourCustomTemplatesFolder]\My.TemplateBindings.PostgreSqlSpecific.DDLSQL.templatebindings:

<templateBindings name="My.TemplateBindings.PostgreSqlSpecific.DDLSQL" description="PostgreSql specific template bindings for DDL SQL generation" 
                        precedenceLevel="10" databaseDriverID="88EBFD8C-CBDD-4452-88AF-1C99E41A123F" xmlns="http://sd/llblgen/pro/templateBindingsDefinition.xsd">
    <supportedPlatforms>
        <platform name="RDBMS"/>
    </supportedPlatforms>
    <supportedFrameworks>
        <framework name="DDL SQL" />
    </supportedFrameworks>
    <language name="SQL">
        <templateBinding templateID="SD_DDLSQL_FieldCreationInclude" filename="FieldCreationIncludeCustom.lpt" templateLanguage="C#" includeOnly="true" />      
    </language>
</templateBindings>

1. Go to your project. Select Project->Settings->General and set the Additional Templates folder to your own [yourCustomTemplatesFolder].

2. Click Project->Generate Create DDL Script. Then click Advance and make sure that your custom binding (My.TemplateBindings.PostgreSqlSpecific.DDLSQL.templatebindings) is above the built-in one (SD.TemplateBindings.PostgreSqlSpecific.DDLSQL.templatebindings). When the system generates the DDL code, and it will look for the SD_DDLSQL_FieldCreationInclude template, it will use yours.

Hope that helps as a workaround.

Looking into altering the DDL templates to emit just 'numeric' if precision and scale are both 0. It will currently create bad DDL SQL anyway so changing this won't break anything. We'll look into rolling this into v5.3.3.

(edit) there's another problem. Creating an entity field of type 'decimal' with precision and scale set to 0 will not result in a valid table field as there's no mapping found in the driver for decimal(0, 0). you added the table field by hand using 'new field' in the table mappings?

Will add the mapping directive for the driver too

Please download the v5.3.3 hotfix build from the website. With that update you'll get what you requested:

• create an entity field with decimal(0,0) as type
• sync using model first, which will create a numeric(0, 0) field for you
• generating DDL SQL will now omit (0,0) for these fields for postgresql

Sorry I took so long to get back to testing this. Works perfectly! Thanks again for your amazing service!