From: | <Holger(dot)Friedrich-Fa-Trivadis(at)it(dot)nrw(dot)de> |
---|---|
To: | <gspiegelberg(at)gmail(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: COALESCE woes |
Date: | 2015-04-24 13:09:41 |
Message-ID: | C5DBACC6DCC7604C9E4875FD9C7968B112D9295CB9@ITXS01EVS.service.it.nrw.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
You probably mean ts2.user_id not ts2, user_id, right?
Best regards
Holger Friedrich
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Greg Spiegelberg
Sent: Friday, April 24, 2015 3:07 PM
To: pgsql-general(at)postgresql(dot)org >> PG-General Mailing List
Subject: [GENERAL] COALESCE woes
Hi PG List,
I'm missing something or haven't had enough coffee yet. What gives with the COALESCE in the view below?
mxl_sqr=# \d users
Table "public.users"
Column | Type | Modifiers
---------+---------+-----------
user_id | integer | not null
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
CREATE TABLE ts1 (
user_id int references users(user_id),
ts timestamptz default now()
);
CREATE TABLE ts2 (
user_id int references users(user_id),
ts timestamptz default now()
);
CREATE TABLE ts3 (
user_id int references users(user_id),
ts timestamptz default now()
);
CREATE OR REPLACE VIEW user_timestamps
AS
SELECT COALESCE(ts1.user_id, ts2,user_id, ts3.user_id) AS user_id,
max(ts1.ts) AS ts_x,
max(ts2.ts) AS ts_y,
max(ts3.ts) AS ts_z
FROM ts1
LEFT JOIN ts2 USING (user_id)
LEFT JOIN ts3 USING (user_id)
GROUP BY 1;
ERROR: COALESCE types integer and ts2 cannot be matched
LINE 3: SELECT COALESCE(ts1.user_id, ts2,user_id, ts3.user_id) AS us...
^
All types match from start to finish.
Thanks,
-Greg
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Spiegelberg | 2015-04-24 13:12:02 | Re: COALESCE woes |
Previous Message | Greg Spiegelberg | 2015-04-24 13:06:55 | COALESCE woes |