CODEWITHSUNDEEP
dbeaver
Timothy Vogel
Timothy Vogel

Reputation: 1597

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
);

Upvotes: 25

Views: 22666

Answers (3)

Sarke
Sarke

Reputation: 3235

When you go to export, check the Include generated columns option, and the primary key (auto-incremented) will be included in the export.

See this for more details: https://github.com/dbeaver/dbeaver/commit/d1f74ec88183d78c7c6620690ced217a52555262

Personally I think this needs to be more clear, and why they excluded it in the first place was not good data integrity.

Include generated column

Upvotes: 32

mondyfy
mondyfy

Reputation: 534

As of now DBeaver version [22.0.5] you have to select Include generated columns as true, as shown in the following screenshot that will export the primary/generated columns.

DBeaver selecting primary/generated columns [22.0.5]

Upvotes: 13

Adrian
Adrian

Reputation: 3731

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

Upvotes: 26

Related Questions