Skip to content

Export database as SQL produces invalid SQL for PostgreSQL #1031

Open

Description

@Thx1137

Steps to reproduce this issue

  1. Create a temporary database in PostgreSQL.

  2. Execute the following script against the temporary database:
    CREATE SEQUENCE IF NOT EXISTS driver_id_seq START 1;
    CREATE TABLE IF NOT EXISTS "driver" (
    "id" INTEGER NOT NULL DEFAULT nextval('driver_id_seq'::regclass),
    "first_name" TEXT NULL DEFAULT NULL,
    "last_name" TEXT NULL DEFAULT NULL,
    PRIMARY KEY ("id")
    );
    CREATE INDEX IF NOT EXISTS "driver_database_name_name_idx" ON driver ("first_name", "last_name");

  3. From HeidiSQL, Right click on the database and choose "Export database as SQL".

  4. Ensure that only the driver table is selected in the options;

  5. Ensure that the Table(s) Create option is checked.

  6. Set the Output option as desired.

  7. Press the Export button.

Current behavior

HeidiSQL generates the following SQL:
CREATE TABLE IF NOT EXISTS "driver" (
"id" INTEGER NOT NULL DEFAULT 'nextval(''driver_id_seq''::regclass)',
"first_name" TEXT NULL DEFAULT NULL,
"last_name" TEXT NULL DEFAULT NULL,
INDEX "driver_database_name_name_idx" ("first_name", "last_name"),
PRIMARY KEY ("id")
);

This syntax cannot be executed in PostgreSQL using HeidiSQL.

Expected behavior

The generated SQL must be as show in step 1.
Varitions in syntax.

  • The "IF NOT EXISTS" is optional.
  • The CREATE SEQUENCE IF NOT EXISTS driver_id_seq START 1; can be replaced with SERIAL. EG:
    CREATE TABLE IF NOT EXISTS "driver" (
    "id" SERIAL,
    "first_name" TEXT NULL DEFAULT NULL,
    "last_name" TEXT NULL DEFAULT NULL,
    PRIMARY KEY ("id")
    );
    CREATE INDEX IF NOT EXISTS "driver_database_name_name_idx" ON driver ("first_name", "last_name");

Possible solution

Environment

This has been tried in several version 9 databases.

  • HeidiSQL version:
    11.0.0.5978

  • Database system and version:
    PostgreSQL 9.4

  • Operating system:
    Windows 10.

Activity

  1. mmm8955405 commented on Jul 22, 2022

    @mmm8955405

    I also encountered this problem

  2. epremuz commented on Mar 14, 2024

    @epremuz

    I also encountered this issue when attempting to migrate from MariaDB to PostgreSQL.

  3. ansgarbecker commented on Oct 14, 2025

    @ansgarbecker
    Collaborator

    The whole SQL export is very MySQL focused, due to historical reasons. Also, the high amount of differences in the SQL syntax makes it extremely different to generate valid SQL exports.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugDefective behaviour in HeidiSQLnettype-postgresqlPostgreSQL specific issue

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions