0

Given this UI in pgAdmin4, what's the difference between these two options:

  • Identity
  • Generated

enter image description here

I'm from a SQL Server background, so I'm used to setting the IDENTITY of an INT, for those MSSql tables to auto-generate that column value.

How is this different/similar in Postgres?

What is interesting is when I check out the GENERATED option, I get this menu, now?

enter image description here

While the IDENTITY option looks like I need to specify everything. So is the difference that IDENTITY is very hard-coded specific, while the GENERATED is 'let me auto set everything up, except for some expression thingy?'

NOTE: Using the latest Docker image of Postgres, so let's assume v10+ (with regards to serial now being off the table in this discussion).

Similar questions:

halfer
  • 19,824
  • 17
  • 99
  • 186
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647

1 Answers1

1

Identity works the same way as in mssql or SQL in general, PostgreSQL 10+ used generated as identity more as a compliant on SQL standard, compared to the older serial. I think This answer explained it well here.
For GENERATED, It's a column that will always be created as a computed value from other columns.
Let's say monthly_salary GENERATED ALWAYS AS (salary/12) STORED will use value from salary column, calculate, and stored as monthly_salary. see The documentation here.

For syntax purposes, both are similar. But there is a little difference.\

# This one is IDENTITY
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] 

# This one is generated from other column
GENERATED ALWAYS AS ( generation_expr ) STORED
Ponsakorn30214
  • 267
  • 4
  • 11
  • AH! awesome - so `GENERATED isn't "I'll _generate_ an auto-incremental value". It's "I'll generate some value based on _existing columns and some other math crap u ask me to do". Where as `IDENTITY` is the auto-incrementing value. Cheers! – Pure.Krome Mar 12 '23 at 04:31