You probably mean ts2.user_id not ts2, user_id, right? Best regards Holger Friedrich
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Greg Spiegelberg Sent: Friday, April 24, 2015 3:07 PM To: pgsql-general@postgresql.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
