0

I have query. There is a construction like this example: (online demo)

You will see the in result created_at field. I have to use query the created_at field. So I have to use it in select created_at. I don't want to use it created_at field in select. Because, there are millions of records in the deposits table. How can i escape this problem?

(Note: I have many table to query, like "deposits" table. this is just a short example.)

create table payment_methods
(
    payment_method_id bigserial not null
        constraint payment_methods_pkey
            primary key
);

create table currencies_of_payment_methods
(
    copm_id bigserial not null
        constraint currencies_of_payment_methods_pkey
            primary key,
    payment_method_id integer not null
    
);
 
create table deposits
(
    deposit_id bigserial not null
        constraint deposits_pkey
            primary key,
    amount numeric(18,2) not null,
    copm_id integer not null,
    created_at timestamp(0)
);

INSERT INTO payment_methods (payment_method_id) VALUES (1);
INSERT INTO payment_methods (payment_method_id) VALUES (2);

INSERT INTO currencies_of_payment_methods (copm_id, payment_method_id) VALUES (1, 1);

INSERT INTO deposits (amount, copm_id, created_at) VALUES (100, 1, '2020-09-10 08:49:37');
INSERT INTO deposits (amount, copm_id, created_at) VALUES (200, 1, '2020-09-10 08:49:37');
INSERT INTO deposits (amount, copm_id, created_at) VALUES (40, 1, '2020-09-10 08:49:37');

Query:

SELECT     payment_methods.payment_method_id,
           deposit_copm_id.deposit_copm_id,
           manuel_deposit_amount.manuel_deposit_amount,
           manuel_deposit_amount.created_at
FROM       payment_methods
CROSS JOIN lateral
           (
                  SELECT currencies_of_payment_methods.copm_id AS deposit_copm_id
                  FROM   currencies_of_payment_methods
                  WHERE  currencies_of_payment_methods.payment_method_id = payment_methods.payment_method_id) deposit_copm_id
CROSS JOIN lateral
           (
                  SELECT sum(deposits.amount) AS manuel_deposit_amount,
                         array_agg(deposits.created_at) AS created_at
                  FROM   deposits
                  WHERE  deposits.copm_id = deposit_copm_id.deposit_copm_id) manuel_deposit_amount
WHERE      payment_methods.payment_method_id = 1
omero
  • 93
  • 1
  • 4
  • 10
  • There were many people who followed postgresq-9.4 tag. I am not using 9.4 or 9.1. – omero Sep 22 '20 at 19:34
  • 1
    you should choose the tags based on what your question is about and what you really use, not what you think would attract more readers. –  Sep 22 '20 at 19:34
  • What is the problem? You want it there, and also want it not there? That is not a problem to which there is a technical solution. You have to decide which is more important, having it there or having it not there. – jjanes Sep 23 '20 at 00:56

0 Answers0