Talk:Java Persistence/Identity and Sequencing

PostgreSQL and Identity Columns
RTFM on PostgreSQL.

To JPA, Oracle and Postgres behave the same. If you create your tables with the appropriate mechanism you can treat some columns LIKE an identity column. But in fact, like Oracle, those columns are Integers datatypes supported by sequence objects. Identity columns use an Identity or Generated datatype. Further, in PostgreSQL OR Oracle, if someone accidentally deletes the sequence for example, the incrementing on the column will stop but there will be nothing wrong with the table and no error reported. Identity columns become part of the table. Postgres does NOT have identity columns.

In fact, they decided not to pursue implementing identity columns: http://archives.postgresql.org/pgsql-hackers/2008-02/msg00617.php. As well, this is the last item on their general todo list relating to to identity/generated columns: http://wiki.postgresql.org/wiki/Todo#Data_Types

However Postgres does not use or have Identity columns or datatypes. Nor does it support them. Just because it emulates an identity column, and you can create a sequenced column more easily than in Oracle, does not mean that it is the same to JPA as an Identity column. It isn't. Postgres still uses a sequence object to do the work.

By implying that PostgreSQL has a mechanism that is the closer to being or the same as an Identity datatype, and is not similar to how Oracle uses sequences to autoincrment columns to to provide false and misleading information. Something a book or encyclopedia should never do.

Postgres does use pseudo datatypes called Serial for Integer primary key columns and BigSerial for BigInt columns. The 'serial' datatype is used in the definition of the table (the create table statement) but once the table is created it relies on a database sequence object to populate the primary key field; just as Oracle does. With Oracle you need to set up the sequence and trigger manually, and PostgreSQL automates this. But in no way means Postgres is supporting identity columns. They will act almost identically to an Identity datatype but they are **not** an Identity datatype.

Identity datatypes are an emerging SQL Standard and in systems that support it, it is a datatype in its own right. Once a table is created, the Identity datatype used to create the column remains an Identity datatype. Serial and Big Serial being pseudo datatypes (really they are like a named macro) are changed to Int and BigInt respectively once the table is created and have no special properties such as auto-increment.

Identity columns have methods like next curval, no column in Postgres has those methods associated with them. Those methods belong to the sequence object associated to the table/column. To say it belonged to the table is too strong since it can be referenced and changed without doing anything to the table. In fact you can change a column associated to the sequence without affecting the sequence at all.

Further, as far as up to Postgres 8.4 the developers have rejected the Identity datatype for PostgreSQL since the standard was not standard at that time.

http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL

http://www.postgresql.org/docs/9.1/static/functions-sequence.html


 * Thank you for the detailed information on PostreSQL. From the perspective of JPA, JPA IDENTITY support does work and is supported on PostgreSQL (depending on the JPA provider of coarse), so it is correct to say that JPA IDENTITY is supported on PostgreSQL, how PostgreSQL implements the support or not very relevant to the JPA user.  It is not supported on Oracle, as Oracle does not provide a column type for this, macro or otherwise.  You could define your own trigger, but this would be application specific, not database platform specific, there would be no standard way to support this in JPA.
 * James (discuss • contribs) 13:33, 10 May 2012 (UTC)


 * On the contrary, Postgres is treated no differently than Oracle. Postgres if you read DOES NOT provide a column type. It provides a macro that essentially does the same thing as Oracle. IDENTITY will work the same on Postgres and Oracle. You are simply wrong in how you think about this. Just because Postgres provides a tool that build the sequence object for and then puts a default on the column (it is not something built into the system like an Identity column... it is a default on a column, something that any decent RDBMS provides. JPA Identity supports both Postgres AND Oracle. So you don't know what you are talking about if you continue saying Oracle isn't supported just because you manually have to set it up. Please, please don't edit stuff on databases if you can't get this concept through your head.

No Primary Key
RE: (according to the book Enterprise JavaBeans 3.1, this statement is not true: "Every entity bean must have a primary key, and it must be unique.").


 * What the "No Primary Key" section what stating, was two part. First the columns that are defined on the database using a PRIMARY KEY constraint do not necessarily need to match what is defined in the Entity using the @Id annotation (although this is normally the case, and the best choice).  You can use different fields, and you can map data in JPA even if the database does not define a PRIMARY KEY constraint.  If the JavaBeans 3.1 book says otherwise, then it is wrong, try it and see for yourself, it will work.
 * In regard to not having a unique set of fields at all, this is more difficult. JPA does require you mark some set of fields with @Id, unless you class in an Embeddable.  However, if no set of field is unique, you can mark them all with @Id.  Although this is not ideal, it will work for reads, delete and insert at least.  You will not be able to performance updates, as you cannot change the id of an object.
 * James (discuss • contribs) 14:38, 5 November 2012 (UTC)