Postgresql return row with default value if no data found

user_4519487 0
Viewed 982

I'm currently facing a problem with Postgresql. I have a query that searches for values in the database and sums them up. The query looks like this:

SELECT
  t.username,
  Coalesce(m.disposal,0)
FROM
  (values('user1'),( 'user2'),('user3'),('user4')) as t (username)
  left join my_table m on t.username=m.username      
WHERE
  disposal_time BETWEEN 2021-03-01 07:00:00+00 AND 2021-03-31 07:00:00+00

Now I want to get 0 as default if there is no data returned for a user.
I tried something with subqueries but it did not work out.
I hope somebody can help me with this.

The table look like this:

DROP TABLE IF EXISTS "my_table";
DROP SEQUENCE IF EXISTS my_table_id_seq;
CREATE SEQUENCE my_table_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "public"."my_table" (
    "id" integer DEFAULT nextval('my_table_id_seq') NOT NULL,
    "username" character varying NOT NULL,
    "startdate" timestamptz NOT NULL,
    "disposal" numeric NOT NULL,
    CONSTRAINT "my_table_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

INSERT INTO "my_table" ("id", "username", "startdate", "disposal") VALUES
(1, 'user1',    '2021-03-22 07:00:00+00',   1234),
(2, 'user2',    '2021-03-23 07:00:00+00',   123456),
(3, 'user3',    '2021-04-02 07:00:00+00',   987),
(4, 'user4',    '2021-04-12 07:00:00+00',   456);

And the output should look like this.

username disposal
user1 1234
user2 123456
user3 0
user4 0
postgresql sql
2 Answers

Your WHERE clause turns the outer join back into an inner join because rows included due to the outer join will have a null value in m.startdate and thus the WHERE clause removes them again.

You need to move that into the join condition:

SELECT t.username,
       coalesce(m.disposal,0) as disposal
FROM (
  values('user1'),( 'user2'),('user3'),('user4')
) as t (username)
  left join my_table m 
    on t.username=m.username  
   and startdate BETWEEN '2021-03-01 07:00:00+00' AND '2021-03-31 07:00:00+00'
;

Online demo

user_3303150

If this query is returing Please try this:

SELECT
  t.username,
  Coalesce(m.disposal,0)
FROM
 (values('user1'),( 'user2'),('user3'),('user4')) as t (username)
  left join my_table m on t.username=m.username      
WHERE
  disposal_time BETWEEN start_date AND end_date AND

The table look like this:

DROP TABLE IF EXISTS "my_table";
DROP SEQUENCE IF EXISTS my_table_id_seq;
CREATE SEQUENCE my_table_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "public"."my_table" (
    "id" integer DEFAULT nextval('my_table_id_seq') NOT NULL,
    "username" character varying NOT NULL,
    "startdate" timestamptz NOT NULL,
    "disposal" numeric NOT NULL,
    CONSTRAINT "my_table_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

INSERT INTO "my_table" ("id", "username", "startdate", "disposal") VALUES
(1, 'user1',    '2021-03-22 07:00:00+00',   1234),
(2, 'user2',    '2021-03-23 07:00:00+00',   123456),
(3, 'user3',    '2021-04-02 07:00:00+00',   987),
(4, 'user4',    '2021-04-12 07:00:00+00',   456);
user_86516010
Related