Re: [GENERAL] COALESCE woes

[Andy Colson]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general