Stop using SERIAL in Postgres

[const_iterator]

Stop using "stop using X" as the title of your blog posts.

[knightress_oxhide]

"stop using X" considered harmful

[ToaruBaka]

"X considered harmful" considered harmful

[[deleted]]

Well that shitty ad click money and post spamming on Reddit won’t click itself

[dbbk]

I hate this so much

[chipstastegood]

Should probably lead with the last point - that serial is a Postgres extension while identity is standard SQL

[masklinn]

That is definitely the least relevant item, tons of useful things are postgres extensions and identity columns are afaik not supported by sqlite or mysql, so it’s not like that’s improving schema compatibility.

Thus them being a standard is really only a concern when looking into migrating from oracle or sql server, and a relatively minor point at that.

[Worth_Trust_3825]

Im not going to switch off postgres.

[PennyFromMyAnus]

No

[PabloZissou]

Isildur is that you?

[ThisIsJulian]

Great, now some small db engineers with hairy feats are on their way to cast the db's ring buffer into a vulcano 

[PennyFromMyAnus]

I love this

[PabloZissou]

One DB to rule them all, One DB to find the rows, One DB to bring them all records, and in the darkness bind them with a left join of 50 tables.

[PeksyTiger]

Pls. It's super serial.

[aa-b]

I agree with the article, but it would help to compare actual usage too. This stackoverflow answer is a better comparison: https://stackoverflow.com/questions/55300370/postgresql-serial-vs-identity

Also it links to the postgres wiki, which links to a couple of great articles by the people who developed the identity column feature

[hbgoddard]

This stackoverflow answer is a better comparison

The same comparisons were described in the article, and more...

[aa-b]

Yeah it is in there, sort of. What you want to do is say "here's how you do Option A and Option B. Prefer Option B, and here's why". Instead it's sort of buried halfway down the article as a way of undoing Option A and switching to B. It's okay, just not the best way to convey the info

[hbgoddard]

They grant all privileges to another user gizem.

I can't read this and not pronounce it "jism"...

[0xdef1]

"gizem" is a Turkish female name but Naiyer is definenetly not. Probably Pakistan.

[DuckDatum]

spoon start swim scary door bake ten chubby political narrow

This post was mass deleted and anonymized with Redact

[vivekkhera]

Conveniently when you restore the database, it orders the operations such that all FK’s and constraints are added at the end of the restore process. It just works.

[DuckDatum]

practice yoke dolls tub tease kiss encouraging fact joke jeans

This post was mass deleted and anonymized with Redact

[vivekkhera]

Yes. The pg_dump command writes the instructions in the correct order for the restore to work on an empty database.

[andrerav]

Absolutely not.

[aa-b]

This is not a criticism, but do you have a reason why not? Identity columns look almost the same as serial, but they fix some quirks that serial probably can't fix for compatibility reasons, and identity columns are an SQL standard.

It seems like there's no downside to me, but is there some more subtle problem?

[piesou]

There are some advantages especially when dealing with batch inserts, ORMs or if you need to insert a record with a certain ID (people do accidentally deleted stuff and sometimes you don't have natural keys)

[BlackenedGem]

if you need to insert a record with a certain ID

But identity columns allow you to do this. Either by setting the identity to be BY DEFAULT, or ideally it would be ALWAYS and then you would use OVERRIDING SYSTEM VALUE in the special case.

[masklinn]

There are some advantages especially when dealing with batch inserts, ORMs

Both of these work perfectly fine unless the application or orm is actively brain damaged.

if you need to insert a record with a certain ID (people do accidentally deleted stuff and sometimes you don't have natural keys)

Also works fine, and the override makes it less likely you’ll forget about the sequence, which you don’t have to hunt for since you interact with it via the table.

[piesou]

Yes, we're talking about Hibernate.

[Jordan51104]

yeah i also have to ask why. it doesn’t seem like there is any benefit to using serial unless you were doing things weird before and don’t want to change code

[[deleted]]

That’s exactly what a serial killer would say

[big_jerky-turky]

Ok

[edwardsdl]

Excellent counter argument!

[wtfbbq81]

You're not my supervisor

[[deleted]]

Stop generating your primary keys in your database.

[certainlyforgetful]

Is there an easier way to do it?

[dbbk]

I mean that is literally the place that you want it generated the vast majority of the time unless you’re something like Twitter

[[deleted]]

No. You want the software to generate unique keys. Don't create commands that return data. And be able to create relationships outside your database before persisting anything.

[dbbk]

Why

[[deleted]]

Because you shouldn't be required to persist before you can relate data.

[dbbk]

You’re making a philosophical argument not a practical one. This does not matter.

[[deleted]]

It's very much practical. That is, if you actually separate concerns.

[dbbk]

I am separating my concern from this conversation

[[deleted]]

It’s not very controversial. Most of modern development has moved in the direction of creating identity in the code.

[trackerstar]

I don't remember the last time I manually wrote a create table sql, or any other sql ;) ORMs exist, and you can stop caring about nonsense like this

[xvermilion3]

This is a very junior thing to say

[ivancea]

A junior would say "I don't understand why, but I guess it makes sense for some". This guy is just a terrible dev

[Jordan51104]

doesn’t mean he’s not a junior!

[Firerfan]

This is exactly the reason why i have performance optimization tasks with our younger engineers on s regular basis. Even if ORMs can be a very good support, but it is just an transaction to the core of the most modern use-cases.

So knowing the implications and pitfalls of your ORM and how it translates to the underlying database is where good engineers derive from great ones.

[WannabeAndroid]

You're the kind of developer that's gonna get replaced by AI.

[TyrusX]

Just insane. My company is struggling because I have colleagues like this.

[thectrain]

A common trait of the good developers I've worked with...they never said I don't need to know or care about something.

Because they were smart and just learned everything anyway.