Re: COALESCE woes

You probably mean ts2.user_id not ts2, user_id, right?

 

Best regards

Holger Friedrich

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Greg Spiegelberg
Sent: Friday, April 24, 2015 3:07 PM
To: pgsql-general@postgresql.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