public inbox for [email protected]  

help / color / mirror / Atom feed

Re: Oracle to Postgres - Transform Hash Partition
2+ messages / 2 participants
[nested] [flat]

* Re: Oracle to Postgres - Transform Hash Partition
@ 2024-06-06 18:41 Christoph Moench-Tegeder <[email protected]>
  2024-06-07 05:40 ` Re: Oracle to Postgres - Transform Hash Partition Muhammad Ikram <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Christoph Moench-Tegeder @ 2024-06-06 18:41 UTC (permalink / raw)
  To: David Barbour <[email protected]>; +Cc: [email protected]

## 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.






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Oracle to Postgres - Transform Hash Partition
  2024-06-06 18:41 Re: Oracle to Postgres - Transform Hash Partition Christoph Moench-Tegeder <[email protected]>
@ 2024-06-07 05:40 ` Muhammad Ikram <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Muhammad Ikram @ 2024-06-07 05:40 UTC (permalink / raw)
  To: Christoph Moench-Tegeder <[email protected]>; +Cc: David Barbour <[email protected]>; [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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-06-07 05:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-06 18:41 Re: Oracle to Postgres - Transform Hash Partition Christoph Moench-Tegeder <[email protected]>
2024-06-07 05:40 ` Muhammad Ikram <[email protected]>

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