operator class "xxx" does not exist for access method "yyy"
Hello, I encountered some errors while using PostgreSQL. It seems that the
information in the pg_opclass cannot be found by syscache. Can you please
let me know if this is a bug and whether this syntax will be supported in
the future?
error:
*postgres=# CREATE TEMP TABLE issue(c0 FLOAT , c1 money NULL) PARTITION BY
HASH((issue.c0)cdbhash_float8_ops);ERROR: operator class
"cdbhash_float8_ops" does not exist for access method "hash"*
version:
*
version--------------------------------------------------------------------------------------------------------
PostgreSQL
16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit(1 row)*
Regards,
Tianyi Gao
On Wed, Mar 22, 2023 at 8:15 PM Tianyi Gao <gtydoit@gmail.com> wrote:
postgres=# CREATE TEMP TABLE issue(c0 FLOAT , c1 money NULL) PARTITION BY
HASH((issue.c0)cdbhash_float8_ops);
ERROR: operator class "cdbhash_float8_ops" does not exist for access
method "hash"
cdbhash_float8_ops seems specific to Greenplum, so not found in community
Postgres.
--
John Naylor
EDB: http://www.enterprisedb.com
Thanks for your reply, I have added some SQL statements, such as the
following example when I use the BIT or BIT VARYING type, similar problems
will occur.
postgres=# CREATE TABLE t(c BIT varying(10)) PARTITION BY HASH((t.c));
ERROR: data type bit varying has no default operator class for access
method "hash"
postgres=# CREATE TABLE t(c BIT) PARTITION BY HASH((t.c));
ERROR: data type bit has no default operator class for access method "hash"
Regards,
Tianyi Gao
John Naylor <john.naylor@enterprisedb.com> 于2023年3月22日周三 22:13写道：
Show quoted text
On Wed, Mar 22, 2023 at 8:15 PM Tianyi Gao <gtydoit@gmail.com> wrote:
postgres=# CREATE TEMP TABLE issue(c0 FLOAT , c1 money NULL) PARTITION
BY HASH((issue.c0)cdbhash_float8_ops);
ERROR: operator class "cdbhash_float8_ops" does not exist for access
method "hash"
cdbhash_float8_ops seems specific to Greenplum, so not found in community
Postgres.--
John Naylor
EDB: http://www.enterprisedb.com
On Thu, Mar 23, 2023 at 11:38 AM Tianyi Gao <gtydoit@gmail.com> wrote:
Thanks for your reply, I have added some SQL statements, such as the
following example when I use the BIT or BIT VARYING type, similar problems
will occur.
postgres=# CREATE TABLE t(c BIT varying(10)) PARTITION BY HASH((t.c));
ERROR: data type bit varying has no default operator class for access
method "hash"
postgres=# CREATE TABLE t(c BIT) PARTITION BY HASH((t.c));
ERROR: data type bit has no default operator class for access method
"hash"
According to the documentation:
"Range and list partitioning require a btree operator class, while hash
partitioning requires a hash operator class. If no operator class is
specified explicitly, the default operator class of the appropriate type
will be used; if no default operator class exists, an error will be raised.
When hash partitioning is used, the operator class used must implement
support function 2 (see Section 38.16.3 for details)."
https://www.postgresql.org/docs/devel/sql-createtable.html
--
John Naylor
EDB: http://www.enterprisedb.com