postgres partition duplicated data

Question

I have a transactions table:

CREATE TABLE transactions
(
  id                      BIGSERIAL        NOT NULL,
  amount                  BIGINT,
  value                   VARCHAR(200)     NOT NULL,
  created_date            TIMESTAMP WITH TIME ZONE DEFAULT now()
) PARTITION BY RANGE (created_date);

Now i am creating two partitions:

CREATE TABLE transactions_y2021s01 PARTITION of transactions
    FOR VALUES FROM ('2021-01-01') to ('2021-07-01')
;


CREATE TABLE transactions_y2021s02 PARTITION of transactions
    FOR VALUES FROM ('2021-07-01') to ('2022-01-01')
;

Now i am inserting values in the table

insert into transactions (amount , value)
values (1,'xxxxxxxx'),
 (2,'yyyyyyyyy'),
 (3,'zzzzzzz');

Now i am do a select:

 select * from transactions;
 select * from transactions_y2021s02;

But I have the 3 rows in both tables, I have the information in parent and child table, this is the correct flow in a partition?, i don't think so.

select * from transactions;

1 , 1 , 'xxxxxxxx'  , 2021-07-16 18:52:13
2 , 2 , 'yyyyyyyyy' , 2021-07-16 18:52:13
3 , 3 , 'zzzzzzz'   , 2021-07-16 18:52:13

select * from transactions_y2021s02

1 , 1 , 'xxxxxxxx'  , 2021-07-16 18:52:13
2 , 2 , 'yyyyyyyyy' , 2021-07-16 18:52:13
3 , 3 , 'zzzzzzz'   , 2021-07-16 18:52:13

What is my problem with the partition.

and i create a rule but i have the same result, the insertion in both tables, i think that the data must be appear in child tables and not in parent table.

i use this rule, but not works:

CREATE or replace RULE transactions_insert_y2021s02 AS ON INSERT TO transactions
    DO INSTEAD
    INSERT INTO data.transactions_y2021s02 VALUES (NEW.*);

Thaks a lot

That looks correct to me. What about it looks wrong? What do you think it should do instead? — jjanes
– jjanes, Commented Jul 17, 2021 at 1:55
Only insert data in child, because i have the same data in both tables, child and parent: transactions and transactions_y2021s02 — Code Geas Coder
– Code Geas Coder, Commented Jul 17, 2021 at 4:12
I tried with a rule too with a insert instead but happens the same, the insertion in both tables CREATE or replace RULE transactions_insert_y2021s02 AS ON INSERT TO data.transactions DO INSTEAD INSERT INTO data.transactions_y2021s02 VALUES (NEW.*); — Code Geas Coder
– Code Geas Coder, Commented Jul 17, 2021 at 4:14
The data physically resides in the child table, but the parent also sees it. This is what partitioning is. If you don't want partitioning, why are you using partitioning? You could specify select * from only transactions if want to see only rows physically in the parent, (which will be none when using declarative partitioning). — jjanes
– jjanes, Commented Jul 17, 2021 at 14:26
because is a report development, then i need partition in data ranges for better performance, but i was confused when i check the tables and the query show me data in both, because i thought that the data was only in child and not in parent table — Code Geas Coder
– Code Geas Coder, Commented Jul 18, 2021 at 22:12

user330315 · Accepted Answer · 2021-07-17 06:49:15Z

3

This is how partitioning works.

The partitioned table transactions always contains (or more precisely: pretends to contain) all rows from all partitions. So a select from the partitioned table will display all rows from all tables while a select from a specific partition will only display those stored in that partition.

The partitioned table acts like a virtual container for all rows in all partitions.

answered Jul 17, 2021 at 6:49

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Code Geas Coder Over a year ago

ah ok, i understand, and to search i need build the query over the transaction table and the query redirect to the specific partition table, right?

user4660595 · Accepted Answer · 2021-07-17 00:09:29Z

0

you can display the result of query :

select * from transactions;

answered Jul 17, 2021 at 0:09

user4660595

Collectives™ on Stack Overflow

postgres partition duplicated data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related