From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | Greg Spiegelberg <gspiegelberg(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: COALESCE woes |
Date: | 2015-04-24 13:15:01 |
Message-ID: | 553A41D5.7000708@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andomar | 2015-04-24 13:18:30 | Re: COALESCE woes |
Previous Message | Greg Spiegelberg | 2015-04-24 13:12:02 | Re: COALESCE woes |