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

Color me embarrassed. Must have been the lack of coffee.

Thanks to all who responded!

-Greg

On Fri, Apr 24, 2015 at 7:09 AM, <Holger.Friedrich-Fa-Trivadis@it.nrw.de>
wrote:

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

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

That should probably be ts2 DOT user_id.

Cheers,
Andomar

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

On Fri, Apr 24, 2015 at 9:06 AM, Greg Spiegelberg <gspiegelberg@gmail.com>
wrote:

*LINE 3: SELECT COALESCE(ts1.user_id, ts2,user_id, ts3.user_id) AS us...*

You want ts2.user_id not ts2,user_id