MIME-Version: 1.0
References: <CAEMHB2R1VA09f22WbBOJz5YE6WLC7aK7HYwo7UXSWdyqN5bmpw@mail.gmail.com>
 <ZmIC5LqJhqkaEnS-@sciurus.exwg.net>
In-Reply-To: <ZmIC5LqJhqkaEnS-@sciurus.exwg.net>
From: Muhammad Ikram <mmikram@gmail.com>
Date: Fri, 7 Jun 2024 10:40:38 +0500
Message-ID: <CAGeimVrUeXT287y7CS7JuMUA15WNoB3ZPmf_ZBT2b7yakybMfw@mail.gmail.com>
Subject: Re: Oracle to Postgres - Transform Hash Partition
To: Christoph Moench-Tegeder <cmt@burggraben.net>
Cc: David Barbour <dbarbour@istation.com>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000004393cd061a463e70"
Archived-At: <https://www.postgresql.org/message-id/CAGeimVrUeXT287y7CS7JuMUA15WNoB3ZPmf_ZBT2b7yakybMfw%40mail.gmail.com>
Precedence: bulk

--0000000000004393cd061a463e70
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi David,

Here is another approach. See if it serves your purpose

postgres=3D# create schema idev;
CREATE SCHEMA
postgres=3D#
postgres=3D#
postgres=3D# CREATE TABLE idev.assessment_result_2023_dab (

    district_oid int,
    -- other columns go here
    column1 numeric,
    column2 numeric
);
CREATE TABLE
postgres=3D#
postgres=3D#
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_2023_p1 PARTITION OF
idev.assessment_result_2023_dab_part
    FOR VALUES WITH (modulus 64, remainder 0);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p2 PARTITION OF
idev.assessment_result_2023_dab_part
    FOR VALUES WITH (modulus 64, remainder 1);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p3 PARTITION OF
idev.assessment_result_2023_dab_part
    FOR VALUES WITH (modulus 64, remainder 2);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p4 PARTITION OF
idev.assessment_result_2023_dab_part
    FOR VALUES WITH (modulus 64, remainder 3);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p5 PARTITION OF
idev.assessment_result_2023_dab_part
    FOR VALUES WITH (modulus 64, remainder 4);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p6 PARTITION OF
idev.assessment_result_2023_dab_part
    FOR VALUES WITH (modulus 64, remainder 5);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p7 PARTITION OF
idev.assessment_result_2023_dab_part
    FOR VALUES WITH (modulus 64, remainder 6);

CREATE TABLE idev.assessment_result_2023_dab_part_2023_p8 PARTITION OF
idev.assessment_result_2023_dab_part
    FOR VALUES WITH (modulus 64, remainder 7);
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
postgres=3D#
postgres=3D#
postgres=3D# CREATE TABLE idev.temp_assessment_result_2023_dab AS TABLE
idev.assessment_result_2023_dab WITH NO DATA;
CREATE TABLE AS
postgres=3D#
postgres=3D#
postgres=3D# INSERT INTO idev.temp_assessment_result_2023_dab
SELECT * FROM idev.assessment_result_2023_dab;
INSERT 0 0
postgres=3D#
postgres=3D#
postgres=3D# TRUNCATE TABLE idev.assessment_result_2023_dab;
TRUNCATE TABLE
postgres=3D#
postgres=3D# ALTER TABLE idev.assessment_result_2023_dab_part
ATTACH PARTITION idev.assessment_result_2023_dab
    FOR VALUES WITH (modulus 64, remainder 8);
ALTER TABLE
postgres=3D#
postgres=3D#
postgres=3D# INSERT INTO idev.assessment_result_2023_dab_part
SELECT * FROM idev.temp_assessment_result_2023_dab;
INSERT 0 0
postgres=3D#
postgres=3D# DROP TABLE idev.temp_assessment_result_2023_dab;
DROP TABLE
postgres=3D#
postgres=3D#


Regrads,
Muhammad Ikram
bitnine


On Thu, Jun 6, 2024 at 11:41=E2=80=AFPM Christoph Moench-Tegeder <cmt@burgg=
raben.net>
wrote:

> ## David Barbour (dbarbour@istation.com):
>
> > 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.
>
> The syntax is the same as with CREATE TABLE ... PARTITION OF, e.g.
> ALTER TABLE parent ATTACH TABLE part FOR VALUES WITH (MODULUS m, REMAINDE=
R
> r);
>
> > Here's an example:
> >  alter table idev.assessment_result_2023_dab_part
> > attach partition idev.assessment_result_2023_dab for values with(modulu=
s
> 8,
> > remainder 1) to (modulus 8, remainder 7)
>
> There's only one (modulus, remainder) tuple in the partition bound
> definition for hash partitions, and always only one partition bound
> specification per partition.
> Maybe what you actually want is a DEFAULT partition (specified as
> PARTITION OF parent DEFAULT), or maybe a completely different approach?
>
> Regards,
> Christoph
>
> --
> Spare Space.
>
>
>

--=20
Muhammad Ikram

--0000000000004393cd061a463e70
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi David,<div><br></div><div>Here is another approach. See=
 if it serves your purpose</div><div><br></div><div>postgres=3D# create sch=
ema idev;<br>CREATE SCHEMA<br>postgres=3D# <br>postgres=3D# <br>postgres=3D=
# CREATE TABLE idev.assessment_result_2023_dab ( =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <br>=C2=A0 =C2=A0 district_oid int,<=
br>=C2=A0 =C2=A0 -- other columns go here<br>=C2=A0 =C2=A0 column1 numeric,=
<br>=C2=A0 =C2=A0 column2 numeric<br>);<br>CREATE TABLE<br>postgres=3D# <br=
>postgres=3D# <br>CREATE TABLE idev.assessment_result_2023_dab_part (<br>=
=C2=A0 =C2=A0 LIKE idev.assessment_result_2023_dab<br>) PARTITION BY HASH (=
district_oid);<br><br>CREATE TABLE idev.assessment_result_2023_dab_part_202=
3_p1 PARTITION OF idev.assessment_result_2023_dab_part<br>=C2=A0 =C2=A0 FOR=
 VALUES WITH (modulus 64, remainder 0);<br><br>CREATE TABLE idev.assessment=
_result_2023_dab_part_2023_p2 PARTITION OF idev.assessment_result_2023_dab_=
part<br>=C2=A0 =C2=A0 FOR VALUES WITH (modulus 64, remainder 1);<br><br>CRE=
ATE TABLE idev.assessment_result_2023_dab_part_2023_p3 PARTITION OF idev.as=
sessment_result_2023_dab_part<br>=C2=A0 =C2=A0 FOR VALUES WITH (modulus 64,=
 remainder 2);<br><br>CREATE TABLE idev.assessment_result_2023_dab_part_202=
3_p4 PARTITION OF idev.assessment_result_2023_dab_part<br>=C2=A0 =C2=A0 FOR=
 VALUES WITH (modulus 64, remainder 3);<br><br>CREATE TABLE idev.assessment=
_result_2023_dab_part_2023_p5 PARTITION OF idev.assessment_result_2023_dab_=
part<br>=C2=A0 =C2=A0 FOR VALUES WITH (modulus 64, remainder 4);<br><br>CRE=
ATE TABLE idev.assessment_result_2023_dab_part_2023_p6 PARTITION OF idev.as=
sessment_result_2023_dab_part<br>=C2=A0 =C2=A0 FOR VALUES WITH (modulus 64,=
 remainder 5);<br><br>CREATE TABLE idev.assessment_result_2023_dab_part_202=
3_p7 PARTITION OF idev.assessment_result_2023_dab_part<br>=C2=A0 =C2=A0 FOR=
 VALUES WITH (modulus 64, remainder 6);<br><br>CREATE TABLE idev.assessment=
_result_2023_dab_part_2023_p8 PARTITION OF idev.assessment_result_2023_dab_=
part<br>=C2=A0 =C2=A0 FOR VALUES WITH (modulus 64, remainder 7);<br>CREATE =
TABLE<br>CREATE TABLE<br>CREATE TABLE<br>CREATE TABLE<br>CREATE TABLE<br>CR=
EATE TABLE<br>CREATE TABLE<br>CREATE TABLE<br>CREATE TABLE<br>postgres=3D# =
<br>postgres=3D# <br>postgres=3D# CREATE TABLE idev.temp_assessment_result_=
2023_dab AS TABLE idev.assessment_result_2023_dab WITH NO DATA;<br>CREATE T=
ABLE AS<br>postgres=3D# <br>postgres=3D# <br>postgres=3D# INSERT INTO idev.=
temp_assessment_result_2023_dab<br>SELECT * FROM idev.assessment_result_202=
3_dab;<br>INSERT 0 0<br>postgres=3D# <br>postgres=3D# <br>postgres=3D# TRUN=
CATE TABLE idev.assessment_result_2023_dab;<br>TRUNCATE TABLE<br>postgres=
=3D# <br>postgres=3D# ALTER TABLE idev.assessment_result_2023_dab_part<br>A=
TTACH PARTITION idev.assessment_result_2023_dab<br>=C2=A0 =C2=A0 FOR VALUES=
 WITH (modulus 64, remainder 8);<br>ALTER TABLE<br>postgres=3D# <br>postgre=
s=3D# <br>postgres=3D# INSERT INTO idev.assessment_result_2023_dab_part<br>=
SELECT * FROM idev.temp_assessment_result_2023_dab;<br>INSERT 0 0<br>postgr=
es=3D# <br>postgres=3D# DROP TABLE idev.temp_assessment_result_2023_dab;<br=
>DROP TABLE<br>postgres=3D# <br>postgres=3D# <br><div><br></div><div><br></=
div><div>Regrads,</div><div>Muhammad Ikram</div><div>bitnine</div><div><br>=
</div></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"=
gmail_attr">On Thu, Jun 6, 2024 at 11:41=E2=80=AFPM Christoph Moench-Tegede=
r &lt;<a href=3D"mailto:cmt@burggraben.net">cmt@burggraben.net</a>&gt; wrot=
e:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">## David Barb=
our (<a href=3D"mailto:dbarbour@istation.com" target=3D"_blank">dbarbour@is=
tation.com</a>):<br>
<br>
&gt; Now I need to &#39;attach&#39; the original table.=C2=A0 The problem I=
&#39;m running into is<br>
&gt; there are no good examples of how to define the values.<br>
<br>
The syntax is the same as with CREATE TABLE ... PARTITION OF, e.g.<br>
ALTER TABLE parent ATTACH TABLE part FOR VALUES WITH (MODULUS m, REMAINDER =
r);<br>
<br>
&gt; Here&#39;s an example:<br>
&gt;=C2=A0 alter table idev.assessment_result_2023_dab_part<br>
&gt; attach partition idev.assessment_result_2023_dab for values with(modul=
us 8,<br>
&gt; remainder 1) to (modulus 8, remainder 7)<br>
<br>
There&#39;s only one (modulus, remainder) tuple in the partition bound<br>
definition for hash partitions, and always only one partition bound<br>
specification per partition.<br>
Maybe what you actually want is a DEFAULT partition (specified as<br>
PARTITION OF parent DEFAULT), or maybe a completely different approach?<br>
<br>
Regards,<br>
Christoph<br>
<br>
-- <br>
Spare Space.<br>
<br>
<br>
</blockquote></div><br clear=3D"all"><div><br></div><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><d=
iv dir=3D"ltr"><div>Muhammad Ikram<br><br></div></div></div>

--0000000000004393cd061a463e70--