Incorrect SQL Generation for PostgreSQL: Uses AUTO_INCREMENT

[alecdesjardins]

Environment

• DbSchema Version: 9.5.2 build 240323
• Operating System: Windows 11 Pro 23H2
• Database: PostgreSQL 16.2

Description

When converting a logical model to a physical model or exporting a schema in DbSchema, the tool does not correctly convert integer or biginteger fields that should auto-increment for PostgreSQL databases. It incorrectly uses AUTO_INCREMENT, which is not supported by PostgreSQL. Instead, PostgreSQL uses SERIAL or BIGSERIAL (other methods too). This issue manifests when exporting a schema model designed specifically for PostgreSQL, resulting in syntax errors if the script is executed in a PostgreSQL environment.

Additionally, there is a minor formatting error in the generated SQL script where an extra space is inserted between 'CREATE' and 'TABLE'.

Steps to Reproduce

1. Open DbSchema and load a schema model configured for PostgreSQL.
2. Navigate to Schema > Export Schema and Data.
3. Leave the export settings at their default values and export the schema.
4. Review the generated SQL script.

Current Behavior

The generated script incorrectly uses AUTO_INCREMENT, and there is an unnecessary space between 'CREATE' and 'TABLE':

CREATE SCHEMA IF NOT EXISTS lookups;

CREATE  TABLE lookups.packed_by_types (
    packed_by_id INTEGER NOT NULL AUTO_INCREMENT,
    packed_by_name VARCHAR(100) NOT NULL,
    created_date TIMESTAMP NOT NULL,
    CONSTRAINT pk_packed_by_types PRIMARY KEY (packed_by_id)
);

COMMENT ON TABLE lookups.packed_by_types IS 'Lookups table for moves packed by column';

[alecdesjardins]

Once you are have converted to a physical model of Postgresql, you are able to use bigserial, serial, smallserial.

Although, it is not efficient to go through each table and manually convert the auto incrementing primary keys.

[wise-coders]

Please install this beta: https://dbschema.com/beta.php also drop the .DbSchema folder from the user home directory, and restart DbSchema.

I tried to reproduce the issue using the attached model file ( please rename it to .dbs ). If the issue persists, it helps to use the same model to reproduce the issue. Logical4Pg.txt