英文:
What is there difference between Identity vs Generated in Postgres / pgAdmin4?
问题
在pgAdmin4中提供的UI中,Identity
和 Generated
这两个选项有什么区别:
Identity
Generated
我来自SQL Server背景,所以我习惯于设置INT
的IDENTITY
,用于自动生成该列的值。
在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
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?
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论