0

I have this table called transactions. It has two columns id and report_id. id is primary key.

I want to create two new tables transaction_ids and report_ids both with just one column id. Both these new tables are supposed to have the list of unique transaction ids and unique report ids respectively.

The first solution that come to mind is:

Step 1:
insert into transaction_ids (select id from transactions); // as `trasnactions.id` is a primary key, uniqueness is guaranteed

Step 2:
insert into report_ids (select distinct report_id from transactions); // using DISTINCT to make sure uniqueness
-- OR --
create unique index on report_ids(id);
insert into report_ids (select report_id from transactions) on conflict(id) do nothing; // using on conflict to make sure of uniqueness

The problem here is that I am traversing the transactions table twice. I am thinking of this as a for loop where I can just traverse the table once and push both transaction.id and transaction.report_id to the right table on each iteration (assuming I have the on conflict handling present for the table report_ids).

Question 1: Is there a way to do it, without actually ending up writing the for loop. I don't have anything against for loop. It's just I have to write extra lines; the same thing which now I am able to do in just two lines.

Question 2: Also, I am worried, that if I insert the rows one by one in side a for loop, would postgres take much time for this as compared to just asking the whole column in one go. In which case, I would have to compare whether the performance gain by traversing just once is worth the performance loss by inserting rows one by one

I also thought of using CTE:

with _ as (insert into transaction_ids (select id from transactions))
insert into report_ids (select distinct report_id from transactions);

-- OR --

create unique index on report_ids(id);
with _ as (insert into transaction_ids (select id from transactions))
insert into report_ids (select report_id from transactions) on conflict(id) do nothing;

But, IMO even here the problem of traversing twice is present. Question 3: Does this approach has any advantage over first approach

Question 4: Is there a recommended/preferred/standard way to deal with this situation?

P.S.: I am worried because size of the transactions table is quite big:

select pg_size_pretty(pg_total_relation_size('transactions'));
 pg_size_pretty 
----------------
 2282 MB
(1 row)

select count(id) from transactions;
  count  
---------
 1465605
(1 row)

select count(report_id) from transactions;
  count  
---------
 1360204
(1 row)

select count(distinct report_id) from transactions;
 count  
--------
 168508
(1 row)


migrated from stackoverflow.com Jun 6 at 19:22

This question came from our site for professional and enthusiast programmers.

  • for now, I am just continuing with my first approach, where I fill all transactions ids in first traversal and all report ids on second traversal – Vikas Prasad May 31 at 9:34
  • 1
    "I want to create two new tables transaction_ids and report_ids both with just one column id" - why would you want to do that? What are you trying to achieve with that approach? – a_horse_with_no_name May 31 at 9:35
  • 2GB is not big, the insert operations would be fast finished. – Shawn.X May 31 at 9:40
  • @a_horse_with_no_name this question is a sub-problem of a big problem. I removed all the irrelevant stuff to keep it to the point. As you are interested, a short info in the below comments: – Vikas Prasad May 31 at 9:42
  • We have this single table, which is kind of a read-only copy version of multiple source tables(read around 30 tables here) with complex join. Now, we need to keep this table up to date with fresh data. So, at regular intervals(every hour for now) we find the "ids" of interest and then update those rows only. This question is part of the process of finding ids of interest. – Vikas Prasad May 31 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.