public inbox for [email protected]  

help / color / mirror / Atom feed

From: Muhammad Ikram <[email protected]>
To: Christoph Moench-Tegeder <[email protected]>
Cc: David Barbour <[email protected]>
Cc: [email protected]
Subject: Re: Oracle to Postgres - Transform Hash Partition
Date: Fri, 7 Jun 2024 10:40:38 +0500
Message-ID: <CAGeimVrUeXT287y7CS7JuMUA15WNoB3ZPmf_ZBT2b7yakybMfw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAEMHB2R1VA09f22WbBOJz5YE6WLC7aK7HYwo7UXSWdyqN5bmpw@mail.gmail.com>
	<[email protected]>

Hi David,

Here is another approach. See if it serves your purpose

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

    district_oid int,
    -- other columns go here
    column1 numeric,
    column2 numeric
);
CREATE TABLE
postgres=#
postgres=#
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=#
postgres=#
postgres=# CREATE TABLE idev.temp_assessment_result_2023_dab AS TABLE
idev.assessment_result_2023_dab WITH NO DATA;
CREATE TABLE AS
postgres=#
postgres=#
postgres=# INSERT INTO idev.temp_assessment_result_2023_dab
SELECT * FROM idev.assessment_result_2023_dab;
INSERT 0 0
postgres=#
postgres=#
postgres=# TRUNCATE TABLE idev.assessment_result_2023_dab;
TRUNCATE TABLE
postgres=#
postgres=# 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=#
postgres=#
postgres=# INSERT INTO idev.assessment_result_2023_dab_part
SELECT * FROM idev.temp_assessment_result_2023_dab;
INSERT 0 0
postgres=#
postgres=# DROP TABLE idev.temp_assessment_result_2023_dab;
DROP TABLE
postgres=#
postgres=#


Regrads,
Muhammad Ikram
bitnine


On Thu, Jun 6, 2024 at 11:41 PM Christoph Moench-Tegeder <[email protected]>
wrote:

> ## David Barbour ([email protected]):
>
> > Now I need to 'attach' the original table.  The problem I'm running into
> 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, REMAINDER
> r);
>
> > 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)
>
> 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.
>
>
>

-- 
Muhammad Ikram

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Oracle to Postgres - Transform Hash Partition
  In-Reply-To: <CAGeimVrUeXT287y7CS7JuMUA15WNoB3ZPmf_ZBT2b7yakybMfw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox