Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sH9ip-003zkb-V8 for pgsql-general@arkaria.postgresql.org; Tue, 11 Jun 2024 22:12:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sH9in-00Afyz-R3 for pgsql-general@arkaria.postgresql.org; Tue, 11 Jun 2024 22:12:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sH9in-00Afyf-7F for pgsql-general@lists.postgresql.org; Tue, 11 Jun 2024 22:12:06 +0000 Received: from mail-oa1-x2f.google.com ([2001:4860:4864:20::2f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sH9ig-000rKd-8o for pgsql-general@lists.postgresql.org; Tue, 11 Jun 2024 22:12:04 +0000 Received: by mail-oa1-x2f.google.com with SMTP id 586e51a60fabf-254d001d03dso1073032fac.3 for ; Tue, 11 Jun 2024 15:11:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=istation.com; s=google; t=1718143917; x=1718748717; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=KOQbVDInxpiAshBJXmKV+Ss6qABW524RA1IYrRFdNc0=; b=IadOe5zvKvat4VGy1E0lNrwrPfjNMfVXbChvB3+sMSCcprh5x2X5YJtY0YI7swBR7X t6XflBiwbGusKwQeGuJrYUf+aX8sCqUa9hiPcjdUECtfKbgG9QGvBPPs4sctCa5ByC7I X7epih1Yqfnm37HWZUeJKCsLK14hnXHSnfbDs= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718143917; x=1718748717; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=KOQbVDInxpiAshBJXmKV+Ss6qABW524RA1IYrRFdNc0=; b=ecJ92I9EYnfvM/lht8c7ROubfGKBKwqNs7NN+H8OcFdlWWuXZsXwz4f5hoMy9Ps6fp KKcs4mJxkvNRIFOUt0wWQmHlmUca9XYm9LDJJdcAukYn+emuGDcIR9D2oq8Xcrjr2usL KD6FZco7J4KnFIG4thXWuoClROxr8FQm6GCkot7PgqzeNDVEn3Mb+FF1cMUmWodMqcG+ nESBufuygY70WaYQSDNtyZ2CNfmcg5Ur0QGpNA68AUE6Jwli1mB1M9QGcIX2DDdrssys uRGZLA9mBUavp/N9AUoO/VGTjGMuQv7dQs8gWulo7HobPPn8/CrdYPtw6tgOQ58mSkBm DE/A== X-Gm-Message-State: AOJu0YzCwMwWiFp3sm0jYnS6rydCPp1k7IFc4RL12fDKZIv1ti+wVtKk QBXyTUjI4wuHUltKhJCWvFU45DWqgnHuHpoPJnntVVOtMBVchUy8BbOH6qLEvTcxPy6nxuJWrQ/ LS7gdEdkAdrqYyv5bicCMHMnbbJYPwWBn/7t4 X-Google-Smtp-Source: AGHT+IGHaaSeboo2/gv2Tg/y4JIVSohpi+pF7163Ki3bWllAYXk8AJUrukNcrkd+fv8/cndaiu+W/WlZwKPogsGel20= X-Received: by 2002:a05:6870:1694:b0:254:fa3c:97f9 with SMTP id 586e51a60fabf-25514c70279mr139593fac.15.1718143916619; Tue, 11 Jun 2024 15:11:56 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: David Barbour Date: Tue, 11 Jun 2024 17:11:39 -0500 Message-ID: Subject: Re: Oracle to Postgres - Transform Hash Partition - Thanks! To: Laurenz Albe , cmt@burggraben.net, mmikram@gmail.com Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000d2db9b061aa48dd9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d2db9b061aa48dd9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable First post to this list, and am sure it won't be my last, but you guys rock= ! Took a bit from all three replies and got the transformation working seamlessly. Created temp table. Copied data into temp from original (just in case!) Created partitioned table using modulus 8. Imported the data into the partitioned table. Dropped the original table and renamed the partitioned table. Easy, no problems. Also no 'attach'. Thanks. On Fri, Jun 7, 2024 at 3:31=E2=80=AFAM Laurenz Albe wrote: > On Thu, 2024-06-06 at 11:28 -0500, David Barbour wrote: > > Been an Oracle DBA for quite a while and we're moving from Oracle to > Postgres. > > > > I have a table that I need to transform into a hash partitioned table. > > I've gone through all the steps to create a hash partitioned table, wit= h > 8 partitions such as > > > > create table idev.assessment_result_2023_dab_part (like > idev.assessment_result_2023_dab)partition by hash (district_oid); > > > > CREATE TABLE > idev.assessment_result_2023_dab_part_assessment_result_2023_p1 PARTITION = OF > idev.assessment_result_2023_dab_part > > FOR VALUES WITH (modulus 64, remainder 0) > > > > ....etc through partition 2023_p8 FOR VALUES WITH (modulus 64, remainde= r > 7) > > That won't do. If you use a modulus of 64, you need 64 partitions, one f= or > each possible division remainder. > > If you want 8 partitions, you have to use modulus 8. > > > Now I need to 'attach' the original table. The problem I'm running int= o > is > > there are no good examples of how to define the values. > > > > I've tried several iterations of various 'for values', 'values', 'for > values with', > > etc. but they all error out. > > > > Here's an example: > > alter table idev.assessment_result_2023_dab_part > > attach partition idev.assessment_result_2023_dab for values > with(modulus 8, remainder 1) to (modulus 8, remainder 7) > > > > ERROR: syntax error at or near "to" LINE 2: ..._2023_dab for values > with(modulus 8, remainder 1) to (modulu... > > I think you are confused about hash partitioning. > > One partition is only for a single remainder. You cannot have a partitio= n > for > several remainders. > > Hash partitioning is mostly for splitting up a table into several parts o= f > roughly equal size. You decide how many partitions you want; that will > become > the modulus. Then you have to create that many partitions, one for each > remainder. > > If you want to attach an existing table as a partition, that will only > work if > all rows in the table belong into that partition. Otherwise, you will ge= t > an > error. > So you typically won't be able to attach an existing table as a hash > partition. > > To convert an existing table into a hash partitioned table, you have to > > - create a new, empty partitioned table with *all* its partitions > > - transfer the data with "INSERT INTO hash_part_tab SELECT * FROM tab" > > Yours, > Laurenz Albe > --=20 *David A. Barbour* *dbarbour@istation.com * *(214) 292-4096* Istation 8150 North Central Expressway, Suite 2000 Dallas, TX 75206 www.Istation.com CONFIDENTIALITY / PROPRIETARY NOTICE: The information contained in this e-mail, including any attachment(s), is confidential information that may be privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or if you received this message in error, then any direct or indirect disclosure, distribution or copying of this message is strictly prohibited. If you have received this message in error, please notify Istation by calling 866-883-7323 immediately and by sending a return e-mail; delete this message; and destroy all copies, including attachments. Thank you. --000000000000d2db9b061aa48dd9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
First post to this list, and am sure it won't be = my last, but you guys rock!

Took a bit from all th= ree replies and got the transformation working seamlessly.
Create= d temp table.
Copied data into temp from original (just in case!)=
Created partitioned table using modulus 8.
Imported th= e data into the partitioned table.
Dropped the original table and= renamed the partitioned table.

Easy, no problems.= =C2=A0 Also no 'attach'.

Thanks.

=
On Fri, Ju= n 7, 2024 at 3:31=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2024-06-06 at 11:28 -0500, D= avid Barbour wrote:
> Been an Oracle DBA for quite a while and we're moving from Oracle = to Postgres.
>
> I have a table that I need to transform into a hash partitioned table.=
> I've gone through all the steps to create a hash partitioned table= , with 8 partitions such as=C2=A0
>
> create table idev.assessment_result_2023_dab_part (like idev.assessmen= t_result_2023_dab)partition by hash (district_oid);
>
> CREATE TABLE idev.assessment_result_2023_dab_part_assessment_result_20= 23_p1 PARTITION OF idev.assessment_result_2023_dab_part
> =C2=A0 =C2=A0 FOR VALUES WITH (modulus 64, remainder 0)
>
> ....etc through partition 2023_p8 FOR VALUES WITH (modulus 64, remaind= er 7)

That won't do.=C2=A0 If you use a modulus of 64, you need 64 partitions= , one for
each possible division remainder.

If you want 8 partitions, you have to use modulus 8.

> Now I need to 'attach' the original table.=C2=A0 The problem I= 'm running into is
> there are no good examples of how to define the values.=C2=A0=C2=A0 >
> I've tried several iterations of various 'for values', = 9;values', 'for values with',
> etc. but they all error out.
>
> Here's an example:
> =C2=A0alter table idev.assessment_result_2023_dab_part
> =C2=A0attach partition idev.assessment_result_2023_dab for values with= (modulus 8, remainder 1) to (modulus 8, remainder 7)
>
> ERROR: syntax error at or near "to" LINE 2: ..._2023_dab for= values with(modulus 8, remainder 1) to (modulu...

I think you are confused about hash partitioning.

One partition is only for a single remainder.=C2=A0 You cannot have a parti= tion for
several remainders.

Hash partitioning is mostly for splitting up a table into several parts of<= br> roughly equal size.=C2=A0 You decide how many partitions you want; that wil= l become
the modulus.=C2=A0 Then you have to create that many partitions, one for ea= ch remainder.

If you want to attach an existing table as a partition, that will only work= if
all rows in the table belong into that partition.=C2=A0 Otherwise, you will= get an
error.
So you typically won't be able to attach an existing table as a hash pa= rtition.

To convert an existing table into a hash partitioned table, you have to

- create a new, empty partitioned table with *all* its partitions

- transfer the data with "INSERT INTO hash_part_tab SELECT * FROM tab&= quot;

Yours,
Laurenz Albe


--

David A= . Barbour

dbarbour@istation.com

(214) 292-4096

Istation

8150 North Central Expressway, Suite= 2000

Dallas, TX 75206

= www.Istation.com

=C2=A0

CONFIDENT= IALITY / PROPRIETARY NOTICE:

The informat= ion contained in this e-mail, including any attachment(s), is confidential = information that may be privileged and exempt from disclosure under applica= ble law. If the reader of this message is not the intended recipient, or if= you received this message in error, then any direct or indirect disclosure= , distribution or copying of this message is strictly prohibited. If you ha= ve received this message in error, please notify Istation by ca= lling 866-883-7323 immediate= ly and by sending a return e-mail; delete this message; and destroy all cop= ies, including attachments.

Thank you.

--000000000000d2db9b061aa48dd9--