Identity vs Generated 在 Postgres / pgAdmin4 中的区别是什么?

huangapple go评论122阅读模式
英文:

What is there difference between Identity vs Generated in Postgres / pgAdmin4?

问题

在pgAdmin4中提供的UI中,IdentityGenerated 这两个选项有什么区别:

  • Identity
  • Generated

我来自SQL Server背景,所以我习惯于设置INTIDENTITY,用于自动生成该列的值。

在Postgres中,这与SQL Server有什么不同/相似?

有趣的是,当我查看GENERATED选项时,我得到了以下菜单:

IDENTITY选项看起来就好像我需要指定“一切”。所以,区别是IDENTITY非常硬编码具体,而GENERATED是“让我自动设置一切,除了某些表达式的部分”吗?

注意:使用Postgres的最新Docker镜像,所以让我们假设是v10+(关于serial现在不在讨论范围内)。

类似的问题:

英文:

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

  • Identity
  • Generated

Identity vs Generated 在 Postgres / pgAdmin4 中的区别是什么?

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?

Identity vs Generated 在 Postgres / pgAdmin4 中的区别是什么?

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:

答案1

得分: 1

Identity在mssql或一般SQL中的工作方式与PostgreSQL 10+中的generated as identity相比较老的serial更符合SQL标准。我认为这个答案在这里解释得很好。

对于GENERATED,这是一个始终将作为其他列的计算值创建的列。

假设 monthly_salary GENERATED ALWAYS AS (salary/12) STORED 将使用salary列的值进行计算,并存储为monthly_salary。详见文档

就语法而言,两者相似,但有一点不同。

  • 这是IDENTITY
    GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

  • 这是从其他列生成的
    GENERATED ALWAYS AS ( generation_expr ) STORED

英文:

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 

huangapple
  • 本文由 发表于 2023年3月12日 11:36:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/75710924.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定