CODEWITHSUNDEEP
postgresqldbeaver
jwwnz
jwwnz

Reputation: 843

How to create auto incrementing / SERIAL id columns using DBeaver with PostgreSQL?

I am a new user for both PostgreSQL and DBeaver (Community edition ver. 5.1.6) and was looking for a way to create an auto incrementing ID column in a table through the DBeaver GUI.

From my research I can see that:

  1. You can set this up easily using SQL eg. id SERIAL NOT_NULL

  2. The underlying problem is that there is no such thing as a 'Serial data type', and that SERIAL equates to nextval('table_name_id_seq').

  3. When I create a table using the SERIAL command in SQL the resulting id column has a nextval('exampletable_id_seq'::regclass') value in the 'Default' attribute.

I have attempted to manually input the nextval() command within the 'Default' attribute for the column in DBeaver in a new table, for example. nextval('mytable_id_seq') with and without the '::regclass;. However this is not working.

I appreciate that doing this in SQL would be easier, and that there is a previously asked question at: Problems de Serial data type in DBeaver & PostgreSQL.

However, I could not find a satisfactory answer and the option of being able to do this through the GUI would be useful, especially if other setup is being done through the DBeaver GUI.

Specifically, my question is:

  1. Is there a functionality for DBeaver to add auto incrementing id's through the GUI?

  2. If so, what would be the steps to do this.

Upvotes: 25

Views: 83136

Answers (2)

cassepipe
cassepipe

Reputation: 653

If your table has already been created

In the Database Navigator window, expand your table by clicking ⏵

Right-click Columns

At the top of the context menu click Create Column

Now you should be able to create an id autoincrement column You can also make it a primary/unique

Upvotes: 0

jwwnz
jwwnz

Reputation: 843

After reviewing the very helpful comments to my question above, I have realized that the question was asked due to my lack of experience with DBeaver.

For completeness I have set out the answer to my question below:

  1. Yes there is a way to auto-increment directly through the DBeaver GUI.

  2. This can be done by setting up an id variable when a table is created by setting a column with a type of serial and "not null" ticked, then setting the id as a primary key through constraints.

Screenshot attached below:

enter image description here

Due to the way PostgreSQL works, the 'Serial' type is a pseudotype that allows a sequence of numbers to be used (http://www.postgresqltutorial.com/postgresql-serial/).

So, in DBeaver, once you set up the tables this will be displayed as the following rather than with a 'serial' type:

enter image description here

I appreciate that this is a very obvious answer to anyone who is familiar with PostgreSQL and/ DBeaver, thank you for your understanding :)

Upvotes: 50

Related Questions