Setting auto increment id in Postgres with Navicat

Question

I'm rather new to Postgres and Navicat is the only fully functional GUI that I've come across. That said, I'm trying to implement a simple auto increment for an id. Navicat doesn't seem to support bigserial or serial, or have anything in place to specify a primary key w/ auto increment. Any thoughts?

Mongo

"The only functional GUI that I've come across". PgAdmin-III? It's not amazing, but it does the job. – Team

@CraigRinger Just downloaded it and it's definitely functional... but man, does that make my eyes hurt. Enough to give one a headache if using a retina display. Anyways, thanks for the heads up. Much appreciated. – Mongo

I just use psql, and I think you'll find most regular PostgreSQL users do. So there's less attention paid to GUIs, partly because developing them is really no fun at all. Regular users don't tend to want to use GUIs, and infrequent users are less likely to want to contribute, so ... yeah. – Team

@CraigRinger Great feedback. I used to work with MySQL quite a bit (using Sequel Pro) before switching to MongoDB. I now have an application that requires a relational database so I figured I would give Postgres a shot. Thanks again. – Mongo

What about CREATE SEQUENCE ? That's what a serial-datatype also does. – Timberland

Answer 1

you should make a sequence default value

Navicat Menu -> Others -> Sequence -> (choose table and column) -> save "user_id_plus1"

and set default value for id column with

nextval('user_id_plus1'::regclass)

Torp

I'm using postgresl 12.0. I should use nextval('dbo.user_id_plus1'::regclass) to let it work(But don't know why). – Wirth

Answer 2

Not it does support serial, so it's simply matter of selecting that datatype when you create the table.

When you do that, it will actually be converted into an int4, with a default that looks like this nextval('users_id_seq'::regclass).

This using Navicat 17 under Windows 11.

Piceous