Обсуждение: COALESCE woes
mxl_sqr=# \d usersTable "public.users"Column | Type | Modifiers---------+---------+-----------user_id | integer | not nullIndexes:"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_timestampsASSELECT 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_zFROM ts1LEFT JOIN ts2 USING (user_id)LEFT JOIN ts3 USING (user_id)GROUP BY 1;ERROR: COALESCE types integer and ts2 cannot be matchedLINE 3: SELECT COALESCE(ts1.user_id, ts2,user_id, ts3.user_id) AS us...^
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
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
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
> SELECT COALESCE(ts1.user_id, ts2,user_id, ts3.user_id) AS user_id, That should probably be ts2 DOT user_id. Cheers, Andomar
LINE 3: SELECT COALESCE(ts1.user_id, ts2,user_id, ts3.user_id) AS us...
You want ts2.user_id not ts2,user_id