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