CODEWITHSUNDEEP
sqlpostgresql
YulePale
YulePale

Reputation: 7686

Does postgres automatically generate an id for every row?

I am new to postgres and SQL in general. I am coming from NoSQL. More specific mongodb. In mongodb every document had a unique id automatically generated by mongodb.

Do postgres rows automatically have a unique id or do you have to generate them yourself?

Upvotes: 0

Views: 4504

Answers (4)

Morris de Oryx
Morris de Oryx

Reputation: 2183

@YulePale, moving to an environment with different rules is always kind of...tiring. They claim it's good for your brain, but learning a new paradigm is not a one day thing.

I'm responding because you've been asking variations of "in PostgreSQL you can have rows which cannot be uniquely identified if the data is the same?" Fair question, but it's important to think of this as a feature. Think of it in plain language terms, how do you distinguish things the are the same? They're indistinguishable, that's what being the same means.

Kind of the whole point in a relational database is to not have identical rows. Imagine that you have two documents that are exactly the same and you store them in two rows. In what way are they different? There's nothing about them that is different. So why do you have two rows? If there's additional information to distinguish them, then that's likely something to have in the row too. Maybe they came from different sources, so you store a path along with each document.

Figuring out what in the data uniquely identifies each row is the starting point for table design. It might be one "natural" field, but that's pretty rare. It might be a combination of fields.

It is commonplace to generate an ID (either a sequential number or a UUID) as a convenience. But bolting a unique number onto identical rows is a bad plan.

1 Don Perkins 123 Main St Waltham MA
2 Don Perkins 123 Main St Waltham MA

That ID at the front makes the rows unique, but the data isn't unique. Pretend that there really is only one Dan Perkins at that address. You've now got bad data. This kind of thing always ends in tears.

The Mongo paradigm and relational database design are different. You'll do yourself a favor to try and come to grips with relational design. Then, you can really exploit the power of a tool such as Postgres. Foreign keys, JOIN, and CHECK constraints are likely going to be your friends.

I don't know where you will find them, but checking in with people who have moved from Mongo to an RDBMS might be pretty helpful for you during the transition. I don't know Mongo, but there are lots of people who know both Mongo and Postgres. In fact, an important Postgres contributor is also behind a tool you might find of interest?

https://www.torodb.com/

I just looked it up, and it sounds like MongoDB generates a base-12 semi-random number for row IDs:

https://docs.mongodb.com/manual/reference/method/ObjectId/

If you like that sort of thing, a good bet in Postgres would be a 16 byte UUID. Note that there is no timestamp data recoverable from a V4 UUID. If you need timestamps, you should add a column for those too.

Both of these columns can be set to auto-generate values when you create your record, right in the table's definition. You've already had a bunch of answers with samples.

Upvotes: 2

Vesa Karjalainen
Vesa Karjalainen

Reputation: 1107

You don't have to generate an artificial id for your table if there is a natural primary key. You can use them also as foreign keys.

User id, email address, government issued personal id number or a nanosecond resolution timestamp are examples of such keys.

Upvotes: 0

user330315
user330315

Reputation:

You need to define one for yourself:

create table foo
(
  id integer generated always as identity primary key,
  .... other columns
);

In a relational database you have to define the primary key yourself, the database does not make assumptions about that. e.g. a link table for a many-to-many relationship usually doesn't have a generated PK at all:

create table many_to_many
(
  one_id integer not null references table_one,
  other_id integer not null references other_table,
  primary key (one_id, other_id)
);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Every database has unique identifiers for each row. Such identifiers are needed, for instance, for indexes on other columns so they can be referenced.

Postgres actually has a variety of internal ids. Here is a reference page for them.

That said, I think your question is whether Postgres automatically makes such an id available for use by users. Let me answer that with a "no", although you can access some of the ids.

However, it is very simple to create one for any table where you need it. Here are two common methods:

table_id serial primary key
table_id int generated always as identity primary key

Upvotes: 1

Related Questions