I am coming from MySQL, and in MySQL you can use AUTOINCREMENT for a row’s unique id as the primary key.
I find that there is no AUTOINCREMENT in Postgresql, only SEQUENCE or UUID.I have read somewhere that we can use UUID as the primary key of a table. This has the added advantage of masking other user’s id (as I want to build APIs that take the ID in as a parameter). Which should I use for Postgresql?
sequence in PostgreSQL does exactly the same as
AUTOINCREMENT in MySQL. A
sequence is more efficient than a
uuid because it is 8 bytes instead of 16 for the
uuid. You can use a
uuid as a primary key, just like most any other data type.
However, I don’t see how this relates to masking of an user ID. If you want to mask the ID of a certain user from other users, you should carefully manage the table privileges and/or hash the ID using – for instance –
If you want to protect a table with user data from snooping hackers that are trying to guess other IDs, the the
uuid type is an excellent choice. The version 4 is then the best choice as it has 122 random bits (the other 6 are used for identification of the version). You can create a primary key like this:
id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
and then you will never have to worry about it anymore.