COALESCE woes

From: Greg Spiegelberg <gspiegelberg(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
Subject: COALESCE woes
Date: 2015-04-24 13:06:55
Message-ID: CAEtnbpX-SO3st-8czq3OnEdfg-R2bAabBHv=qy9J28qX+1C25Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Holger.Friedrich-Fa-Trivadis 2015-04-24 13:09:41 Re: COALESCE woes
Previous Message Peter Eisentraut 2015-04-24 12:48:05 Re: Running pg_upgrade under Debian