Обсуждение: COALESCE woes

On 04/24/2015 08:06 AM, Greg Spiegelberg wrote:
> 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

Maybe dot instead of comma?  (ts2.user_id instead of ts2,user_id)

-Andy

>     SELECT COALESCE(ts1.user_id, ts2,user_id, ts3.user_id) AS user_id,

That should probably be ts2 DOT user_id.

Cheers,
Andomar