Thread: 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"
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)
--------------------------------------------------------------------------------------------------------
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
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"
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"
ERROR: data type bit has no default operator class for access method "hash"
Regards,
Tianyi Gao
Tianyi Gao
John Naylor <john.naylor@enterprisedb.com> 于2023年3月22日周三 22:13写道：
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)."
"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)."