Search code examples
dbeaver

DBeaver does not keep primary keys on import/export

I'm using DBeaver to migrate data from Postgres to Derby. When I use the wizard in DBeaver to go directly from one table to another, the primary key in Derby is being generated instead of inserted. This causes issues on foreign keys for subsequent tables.

If I generate the SQL, the primary key is part of the SQL statement and is properly inserted. However there are too many rows to handle in this way.

Is there a way to have DBeaver insert the primary key instead of letting it be generated when importing / exporting directly to database tables?

Schema of target table

CREATE TABLE APP.THREE_PHASE_MOTOR (
    ID BIGINT NOT NULL DEFAULT GENERATED_BY_DEFAULT,
    VERSION INTEGER NOT NULL,
    CONSTRAINT SQL130812103636700 PRIMARY KEY (ID)
);

CREATE INDEX SQL160416184259290 ON APP.THREE_PHASE_MOTOR (ID);

Schema of source table

CREATE TABLE public.three_phase_motor (
    id int8 NOT NULL DEFAULT nextval('three_phase_motor_id_seq'::regclass),
    "version" int4 NOT NULL,
    CONSTRAINT three_phase_motor_pkey PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
);
Solution

  • I found a trick working with version 6.0.5; do these steps:

    • double click a table name
    • then select Data tab
    • then click the gray table corner (the one on top of row order numbers) in order to select all rows
    • then right click the same gray table corner
    • then select Generate SQL -> INSERT menu

    a window with the INSERT instructions including id (primary key) will popup.

    PS: when selecting a subset of rows the same menu would work for only those too

    enter image description here