19

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.

jwwnz
  • 703
  • 2
  • 7
  • 13
  • It's unclear to me what your problem is: Can you create a table through the GUI using `SERIAL` but DBeaver doesn't _display_ the `serial` "type"? Or can't you select `serial` to begin with? –  Sep 05 '18 at 07:03
  • 1
    @a_horse_with_no_name `Serial` can be selected during table creation in DBeaver and it does use `serial` for its `CREATE` statement (in preview at least). I think he wants DBeaver to detect that column meets requirements of `serial` "type" and display it as such and does not understand that most clients do not do this, including DBeaver. – Łukasz Kamiński Sep 05 '18 at 08:31
  • @ŁukaszKamiński: that's what I _think_ as well. But the part "*is there a functionality to add auto incrementing IDs*" seems to indicate that DBeaver does not offer such a possibility –  Sep 05 '18 at 08:34
  • @a_horse_with_no_name I think he is just a bit confused with how serial is usually displayed by clients and didn't actually test if table created by DBeaver will auto increment on column with type set to `serial`. It works for me just fine in DBeaver. – Łukasz Kamiński Sep 05 '18 at 08:39
  • @a_horse_with_no_name Thanks so much for your answers and sorry about the confusion with the question. I was essentially wanting to know can you select serial to begin with through DBeaver without typing SQL. – jwwnz Sep 05 '18 at 20:49
  • After looking at your and @ŁukaszKamiński comments I had another play around with DBeaver and as ŁukaszKamiński rightly pointed out, there is a type of serial you can set up as a column Data Type. I am almost ashamed of the noobness of my question in hindsight, thanks so much for both of you for taking the time to answer despite this :) – jwwnz Sep 05 '18 at 20:50

1 Answers1

43

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

Andhi Irawan
  • 456
  • 8
  • 15
jwwnz
  • 703
  • 2
  • 7
  • 13
  • 11
    is it possible to do this but instead of when creating a table, by editing it? – noloman May 28 '19 at 12:06
  • 1
    @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. – Oswaldo Salazar Jul 23 '20 at 23:40
  • @OswaldoSalazar what about if the column already exists? – cikatomo Apr 17 '21 at 22:08
  • 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". – Byterbit Feb 28 '22 at 20:56
  • @OswaldoSalazar I right clicked on an existing table, chose "View Table". There I could double-click on "Auto Increment Checkbox" of the existing Id column. – Beyto A. Sep 25 '22 at 12:35