# Does CREATE TABLE syntax need HASH keyword explicitly?

**URL:** <https://forum.yugabyte.com/t/does-create-table-syntax-need-hash-keyword-explicitly/1565>\
**Category:** General\
**Created:** [April 7, 2022, 11:19pm UTC](https://forum.yugabyte.com/t/does-create-table-syntax-need-hash-keyword-explicitly/1565 "2022-04-07T23:19:52Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![sham\_yuga](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@sham\_yuga](https://forum.yugabyte.com/u/sham_yuga)\
**Post date:** [April 7, 2022, 11:19pm UTC](https://forum.yugabyte.com/t/does-create-table-syntax-need-hash-keyword-explicitly/1565/1 "2022-04-07T23:19:52Z")

</div>

We are using YSQL on yugabyte DB

Below table syntax create table with hash sharding:

```auto
CREATE TABLE customers (
    customer_id bpchar NOT NULL,
    company_name character varying(40) NOT NULL,
    PRIMARY KEY (customer_id HASH)
);

```

Does below table syntax creates table with range sharding or hash sharding?

```auto
CREATE TABLE customers (
    customer_id bpchar NOT NULL,
    company_name character varying(40) NOT NULL,
    PRIMARY KEY (customer_id)
);

```

---

<div class="post-metadata">

**Author:** ![Alan\_Caldera](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/alan_caldera/32/91_2.png) [@Alan\_Caldera](https://forum.yugabyte.com/u/Alan_Caldera)\
**Post date:** [April 7, 2022, 11:50pm UTC](https://forum.yugabyte.com/t/does-create-table-syntax-need-hash-keyword-explicitly/1565/2 "2022-04-07T23:50:02Z")

</div>

@sham_yuga –  
By default, the first column of a PRIMARY KEY or INDEX is taken to be the HASH column unless it is denoted as ASC or DESC which would indicate a RANGE column. The default RANGE column order is ASC if unspecified after the first column.

Alan

---

<div class="post-metadata">

**Author:** ![sham\_yuga](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@sham\_yuga](https://forum.yugabyte.com/u/sham_yuga)\
**Post date:** [April 7, 2022, 11:55pm UTC](https://forum.yugabyte.com/t/does-create-table-syntax-need-hash-keyword-explicitly/1565/3 "2022-04-07T23:55:47Z")

</div>

@Alan_Caldera

If the primary key is as shown in below table:

```auto
CREATE TABLE IF NOT EXISTS public.table1
(
    customer_id uuid NOT NULL,
    item_id uuid NOT NULL,
    kind character varying(100) NOT NULL,
    details character varying(100) NOT NULL,
    CONSTRAINT table1_pkey PRIMARY KEY (customer_id, kind, item_id)
);

```

then table is created with  
first column(`customer_id`) of PRIMARY KEY indicate a HASH column  
and  
second & third column(i.e., `kind`, `item_id`) of PRIMARY KEY indicate a RANGE column

1. Is my understanding(above) correct?

2. Which sharding is applied on below two indexes? hash or range

```auto
CREATE UNIQUE INDEX IF NOT EXISTS unique_customer_id ON table1(customer_id);

CREATE UNIQUE INDEX IF NOT EXISTS unique_item ON table1(customer_id, kind) WHERE kind='BAD' OR kind='SLOW';

```

---

<div class="post-metadata">

**Author:** ![Alan\_Caldera](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/alan_caldera/32/91_2.png) [@Alan\_Caldera](https://forum.yugabyte.com/u/Alan_Caldera)\
**Post date:** [April 8, 2022, 12:16am UTC](https://forum.yugabyte.com/t/does-create-table-syntax-need-hash-keyword-explicitly/1565/4 "2022-04-08T00:16:52Z")

</div>

@sham_yuga -

Yes, in the example above, the PRIMARY KEY will be sharded on customer\_id, and it will be sorted in `kind, item_id` order ascending.

In the first index (`unique_customer_id`), that index will be sharded on the customer\_id field. In the second index (`unique_item`), it will be sharded on `customer_id`, with the `kind` field being sorted in ascending order within each `customer_id`. Your sample query will be slow/bad because you have to scan all `customer_id` shards for the specified `kind` field.

Alan

---

<div class="post-metadata">

**Author:** ![sham\_yuga](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@sham\_yuga](https://forum.yugabyte.com/u/sham_yuga)\
**Post date:** [April 8, 2022, 12:36am UTC](https://forum.yugabyte.com/t/does-create-table-syntax-need-hash-keyword-explicitly/1565/5 "2022-04-08T00:36:04Z")

</div>

@Alan_Caldera

So,

1. for `table1`, `yb_hash_code()` with SELECT query can only be applied on `customer_id` column:

`select * from table1 WHERE yb_hash_code(customer_id)>=0 AND yb_hash_code(customer_id) < 4095;`

1. For index `unique_customer_id`, yb\_hash\_code() with SELECT query can only be applied on `customer_id` column:

`select * from unique_customer_id WHERE yb_hash_code(customer_id)>=0 AND yb_hash_code(customer_id) < 4095;`

1. For index `unique_item `, `yb_hash_code()` with SELECT query can only be applied on `item_id` column:

`select * from unique_item WHERE yb_hash_code(item_id)>=0 AND yb_hash_code(item_id) < 4095;`

Correct me

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [April 8, 2022, 8:56am UTC](https://forum.yugabyte.com/t/does-create-table-syntax-need-hash-keyword-explicitly/1565/6 "2022-04-08T08:56:13Z")

</div>

> [@sham\_yuga](#):
>
> Correct me

`yb_hash_code()` can only be applied on column(s) that are used for partitioning. You’re correct.

---

<div class="post-metadata">

**Author:** ![sham\_yuga](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@sham\_yuga](https://forum.yugabyte.com/u/sham_yuga)\
**Post date:** [April 8, 2022, 6:28pm UTC](https://forum.yugabyte.com/t/does-create-table-syntax-need-hash-keyword-explicitly/1565/7 "2022-04-08T18:28:46Z")

</div>

@dorian_yugabyte

In YCQL, we can find hash ranges through `select start_range, end_range from system.partitions;`

What is the query to find hash range in postgres(yugabyte DB 2.8.3) using YSQL?

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [May 6, 2022, 3:25pm UTC](https://forum.yugabyte.com/t/does-create-table-syntax-need-hash-keyword-explicitly/1565/8 "2022-05-06T15:25:14Z")

</div>

> [@sham\_yuga](#):
>
> What is the query to find hash range in postgres(yugabyte DB 2.8.3) using YSQL?

It’s not possible yet in YSQL [[YSQL] Make queries partition aware & expose tablet info in hash sharding · Issue #2480 · yugabyte/yugabyte-db · GitHub](https://github.com/yugabyte/yugabyte-db/issues/2480)