[BUGS] BUG #4586: Supporting of Binary instead Bytea for Primary Keys
The following bug has been logged online: Bug reference: 4586 Logged by: Miroslav Nachev Email address: m...@space-comm.com PostgreSQL version: 8.3 Operating system: Windows Description:Supporting of Binary instead Bytea for Primary Keys Details: Most of the popular databases (Oracle, MySQL, MS SQL, etc.) support binary column type which can be used as primary key. For example UUID algorithm need of exact 16 bytes and is very useful for Primary Key. Of course it can be presented with characters in Hex format but in that case this will take 32 bytes which is 2 times bigger. It will be very helpful if in PostgreSQL binary/varbinary type is supported and can be used for indexes and primary keys. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4586: Supporting of Binary instead Bytea for Primary Keys
Miroslav Nachev wrote: Most of the popular databases (Oracle, MySQL, MS SQL, etc.) support binary column type which can be used as primary key. For example UUID algorithm need of exact 16 bytes and is very useful for Primary Key. Of course it can be presented with characters in Hex format but in that case this will take 32 bytes which is 2 times bigger. It will be very helpful if in PostgreSQL binary/varbinary type is supported and can be used for indexes and primary keys. There's a built-in UUID datatype that you might be interested in. It's 16 bytes, fixed-length. Also, what's wrong with bytea? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4586: Supporting of Binary instead Bytea for Primary Keys
(please keep the mailing list cc'd. (or take this to a more relevant list, actually)). Miroslav Nachev wrote: Thank you very much. How can I map PostgreSQL UUID to JPA Hibernate with annotations? Serializable or BigInteger or byte[] or java.util.UUID? No idea. The big disadvantage of bytea is that it is not possible to use that type in indexes and primary keys. Probably this restriction is because is very slow column type because instead to keep the data this column keeps pointer to the data. Huh? Of course it's possible: postgres=# CREATE TABLE foo (id bytea PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE postgres=# CREATE INDEX i_foo ON foo (id); CREATE INDEX postgres=# -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4586: Supporting of Binary instead Bytea for Primary Keys
Miroslav Nachev wrote: And how to specify that bytea MUST be exact 16 bytes? Well, you could put a constraint on the column, or create a domain with such a constraint. But if you're dealing with uuids, you really want to use the UUID data type anyway. What about the speed because this is very important? 1st of all when the length is variable then the speed is low. The overhead is small enough that you'd never notice. 2nd when the column is with bigger length which require pointer to the data instead data, then the speed will be extremely low. Again you're just assuming some overhead that's completely insignificant in the big scheme of things. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4586: Supporting of Binary instead Bytea for Primary Keys
On Wed, 17 Dec 2008, Miroslav Nachev wrote: Thank you very much. How can I map PostgreSQL UUID to JPA Hibernate with annotations? Serializable or BigInteger or byte[] or java.util.UUID? The server uuid type is already mapped to java.util.UUID in recent JDBC driver versions: http://archives.postgresql.org/pgsql-jdbc/2008-09/msg00102.php Kris Jurka -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4586: Supporting of Binary instead Bytea for Primary Keys
And how to specify that bytea MUST be exact 16 bytes? What about the speed because this is very important? 1st of all when the length is variable then the speed is low. 2nd when the column is with bigger length which require pointer to the data instead data, then the speed will be extremely low. Miro. On Wed, Dec 17, 2008 at 11:19 AM, Heikki Linnakangas < heikki.linnakan...@enterprisedb.com> wrote: > (please keep the mailing list cc'd. (or take this to a more relevant list, > actually)). > > Miroslav Nachev wrote: > >> Thank you very much. How can I map PostgreSQL UUID to JPA Hibernate with >> annotations? Serializable or BigInteger or byte[] or java.util.UUID? >> > > No idea. > > The big disadvantage of bytea is that it is not possible to use that type >> in >> indexes and primary keys. Probably this restriction is because is very >> slow >> column type because instead to keep the data this column keeps pointer to >> the data. >> > > Huh? Of course it's possible: > > postgres=# CREATE TABLE foo (id bytea PRIMARY KEY); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" > for table "foo" > CREATE TABLE > postgres=# CREATE INDEX i_foo ON foo (id); > CREATE INDEX > postgres=# > > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com >
Re: [BUGS] BUG #4586: Supporting of Binary instead Bytea for Primary Keys
Dear Heikki, Thank you very much. How can I map PostgreSQL UUID to JPA Hibernate with annotations? Serializable or BigInteger or byte[] or java.util.UUID? The big disadvantage of bytea is that it is not possible to use that type in indexes and primary keys. Probably this restriction is because is very slow column type because instead to keep the data this column keeps pointer to the data. Miro. On Wed, Dec 17, 2008 at 10:17 AM, Heikki Linnakangas < heikki.linnakan...@enterprisedb.com> wrote: > Miroslav Nachev wrote: > >> Most of the popular databases (Oracle, MySQL, MS SQL, etc.) support binary >> column type which can be used as primary key. For example UUID algorithm >> need of exact 16 bytes and is very useful for Primary Key. Of course it >> can >> be presented with characters in Hex format but in that case this will take >> 32 bytes which is 2 times bigger. >> It will be very helpful if in PostgreSQL binary/varbinary type is >> supported >> and can be used for indexes and primary keys. >> > > There's a built-in UUID datatype that you might be interested in. It's 16 > bytes, fixed-length. > > Also, what's wrong with bytea? > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com >
Re: [BUGS] BUG #4586: Supporting of Binary instead Bytea for Primary Keys
Miroslav Nachev wrote: I try to use it but I have the following exception: java.lang.IllegalArgumentException: Unknown entity: java.util.UUID at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:223) at psqluuidtest.Main.persist(Main.java:33) at psqluuidtest.Main.main(Main..java:25) Surely you want to persist a TestTable1 instance, not the uuid itself. Kris Jurka -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
