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

Question

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.

Solution

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:

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:

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

Comments

[noloman]

is it possible to do this but instead of when creating a table, by editing it?

[Oswaldo Salazar]

@noloman yes, it is possible to add an id column after the table has been created. Right-click on "Columns" -> "Create New Column", then in the popup select the options as in the screenshot above.

[cikatomo]

@OswaldoSalazar what about if the column already exists?

[Byterbit]

This worked for me. I manually added "serial" to the data type (the list box did not show it) and then hit "save" from the "file" menu. After the save the data type is listed as "bigInt unsigned". The key column shows "PRI" and the extra column shows "auto increment".