Color me embarrassed. Must have been the lack of coffee. Thanks to all who responded!
-Greg On Fri, Apr 24, 2015 at 7:09 AM, <holger.friedrich-fa-triva...@it.nrw.de> wrote: > 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 >
