Re: COALESCE woes

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(dot)Friedrich-Fa-Trivadis(at)it(dot)nrw(dot)de>
wrote:

> 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
>