Issue with Postgres not recognizing CAST on join
I'm trying to join two tables together based on an ID column. The join is not working successfully because I cannot join a varchar column on an integer column, despite using cast().
In the first table, the ID column is character varying, in the format of: XYZA-123456.
In the second table, the ID column is simply the number: 123456.
-- TABLE 1
create table fake_receivers(id varchar(11));
insert into fake_receivers(id) values
('VR2W-110528'),
('VR2W-113640'),
('VR4W-113640'),
('VR4W-110528'),
('VR2W-110154'),
('VMT2-127942'),
('VR2W-113640'),
('V16X-110528'),
('VR2W-110154'),
('VR2W-110528');
-- TABLE 2
create table fake_stations(receiver_sn integer, station varchar);
insert into fake_stations values
('110528', 'Baff01-01'),
('113640', 'Baff02-02'),
('110154', 'Baff03-01'),
('127942', 'Baff05-01');
My solution is to split the string at the dash, take the number after the dash, and cast it as an integer, so that I may perform the join:
select cast(split_part(id, '-', 2) as integer) from fake_receivers; -- this works fine, seemingly selects integers
However, when I actually attempt to perform the join, I'm getting the following error, despite using an explicit cast:
select cast(split_part(id, '-', 2) as integer), station
from fake_receivers
inner join fake_locations
on split_part = fake_locations.receiver_sn -- not recognizing split_part cast as integer!
>ERROR: operator does not exist: character varying = integer
>Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Strangely enough, I can perform this join with my full dataset (a queried result set shows up) but I then can't manipulate it at all (e.g. sorting, filtering it) - I get an error saying ERROR: invalid input syntax for integer: "UWM". The string "UWM" appears nowhere in my dataset or in my code, but I strongly suspect it has to do with the split_part cast from varchar to integer going wrong somewhere.
-- Misc. info
select version();
>PostgreSQL 10.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 9.0.0 (clang-900.0.39.2), 64-bit
EDIT: dbfiddle exhibiting behavior
sql postgresql join casting
asked Nov 20 '18 at 2:24
spopsspops
236215
add a comment |
I'm trying to join two tables together based on an ID column. The join is not working successfully because I cannot join a varchar column on an integer column, despite using cast().
In the first table, the ID column is character varying, in the format of: XYZA-123456.
In the second table, the ID column is simply the number: 123456.
-- TABLE 1
create table fake_receivers(id varchar(11));
insert into fake_receivers(id) values
('VR2W-110528'),
('VR2W-113640'),
('VR4W-113640'),
('VR4W-110528'),
('VR2W-110154'),
('VMT2-127942'),
('VR2W-113640'),
('V16X-110528'),
('VR2W-110154'),
('VR2W-110528');
-- TABLE 2
create table fake_stations(receiver_sn integer, station varchar);
insert into fake_stations values
('110528', 'Baff01-01'),
('113640', 'Baff02-02'),
('110154', 'Baff03-01'),
('127942', 'Baff05-01');
My solution is to split the string at the dash, take the number after the dash, and cast it as an integer, so that I may perform the join:
select cast(split_part(id, '-', 2) as integer) from fake_receivers; -- this works fine, seemingly selects integers
However, when I actually attempt to perform the join, I'm getting the following error, despite using an explicit cast:
select cast(split_part(id, '-', 2) as integer), station
from fake_receivers
inner join fake_locations
on split_part = fake_locations.receiver_sn -- not recognizing split_part cast as integer!
>ERROR: operator does not exist: character varying = integer
>Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Strangely enough, I can perform this join with my full dataset (a queried result set shows up) but I then can't manipulate it at all (e.g. sorting, filtering it) - I get an error saying ERROR: invalid input syntax for integer: "UWM". The string "UWM" appears nowhere in my dataset or in my code, but I strongly suspect it has to do with the split_part cast from varchar to integer going wrong somewhere.
-- Misc. info
select version();
>PostgreSQL 10.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 9.0.0 (clang-900.0.39.2), 64-bit
EDIT: dbfiddle exhibiting behavior
sql postgresql join casting
asked Nov 20 '18 at 2:24
spopsspops
236215
NOTE to anybody reading this at the moment: minor errors in my code above which I will fix tomorrow, but Tim's answer below uses correct code that correctly reproduces & then subsequently fixes the issue.
– spops
Nov 20 '18 at 3:08
"Smart keys"--your fake_receivers id values--are a design antipattern.
– philipxy
Nov 20 '18 at 3:50
Not sure I understand, can you explain? If you're referring to the fact that there are letters & dash before the number - nothing I can do about that, that's the way the sensor hardware spits out the data; in the second table, we don't always know the letter code associated with a serial number, but always know the serial number and that the serial numbers will always line up. Not ideal but splitting the string in the first table is the only option I have.
– spops
Nov 20 '18 at 17:15
1
Whatever raw sensor data we log, we can extract appropriate data into a more suitable schema. Re "smart keys".
– philipxy
Nov 21 '18 at 8:02
add a comment |
I'm trying to join two tables together based on an ID column. The join is not working successfully because I cannot join a varchar column on an integer column, despite using cast().
In the first table, the ID column is character varying, in the format of: XYZA-123456.
In the second table, the ID column is simply the number: 123456.
-- TABLE 1
create table fake_receivers(id varchar(11));
insert into fake_receivers(id) values
('VR2W-110528'),
('VR2W-113640'),
('VR4W-113640'),
('VR4W-110528'),
('VR2W-110154'),
('VMT2-127942'),
('VR2W-113640'),
('V16X-110528'),
('VR2W-110154'),
('VR2W-110528');
-- TABLE 2
create table fake_stations(receiver_sn integer, station varchar);
insert into fake_stations values
('110528', 'Baff01-01'),
('113640', 'Baff02-02'),
('110154', 'Baff03-01'),
('127942', 'Baff05-01');
My solution is to split the string at the dash, take the number after the dash, and cast it as an integer, so that I may perform the join:
select cast(split_part(id, '-', 2) as integer) from fake_receivers; -- this works fine, seemingly selects integers
However, when I actually attempt to perform the join, I'm getting the following error, despite using an explicit cast:
select cast(split_part(id, '-', 2) as integer), station
from fake_receivers
inner join fake_locations
on split_part = fake_locations.receiver_sn -- not recognizing split_part cast as integer!
>ERROR: operator does not exist: character varying = integer
>Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Strangely enough, I can perform this join with my full dataset (a queried result set shows up) but I then can't manipulate it at all (e.g. sorting, filtering it) - I get an error saying ERROR: invalid input syntax for integer: "UWM". The string "UWM" appears nowhere in my dataset or in my code, but I strongly suspect it has to do with the split_part cast from varchar to integer going wrong somewhere.
-- Misc. info
select version();
>PostgreSQL 10.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 9.0.0 (clang-900.0.39.2), 64-bit
EDIT: dbfiddle exhibiting behavior
sql postgresql join casting
asked Nov 20 '18 at 2:24
spopsspops
236215
I'm trying to join two tables together based on an ID column. The join is not working successfully because I cannot join a varchar column on an integer column, despite using cast().
In the first table, the ID column is character varying, in the format of: XYZA-123456.
In the second table, the ID column is simply the number: 123456.
-- TABLE 1
create table fake_receivers(id varchar(11));
insert into fake_receivers(id) values
('VR2W-110528'),
('VR2W-113640'),
('VR4W-113640'),
('VR4W-110528'),
('VR2W-110154'),
('VMT2-127942'),
('VR2W-113640'),
('V16X-110528'),
('VR2W-110154'),
('VR2W-110528');
-- TABLE 2
create table fake_stations(receiver_sn integer, station varchar);
insert into fake_stations values
('110528', 'Baff01-01'),
('113640', 'Baff02-02'),
('110154', 'Baff03-01'),
('127942', 'Baff05-01');
My solution is to split the string at the dash, take the number after the dash, and cast it as an integer, so that I may perform the join:
select cast(split_part(id, '-', 2) as integer) from fake_receivers; -- this works fine, seemingly selects integers
However, when I actually attempt to perform the join, I'm getting the following error, despite using an explicit cast:
select cast(split_part(id, '-', 2) as integer), station
from fake_receivers
inner join fake_locations
on split_part = fake_locations.receiver_sn -- not recognizing split_part cast as integer!
>ERROR: operator does not exist: character varying = integer
>Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Strangely enough, I can perform this join with my full dataset (a queried result set shows up) but I then can't manipulate it at all (e.g. sorting, filtering it) - I get an error saying ERROR: invalid input syntax for integer: "UWM". The string "UWM" appears nowhere in my dataset or in my code, but I strongly suspect it has to do with the split_part cast from varchar to integer going wrong somewhere.
-- Misc. info
select version();
>PostgreSQL 10.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 9.0.0 (clang-900.0.39.2), 64-bit
EDIT: dbfiddle exhibiting behavior
sql postgresql join casting
sql postgresql join casting
asked Nov 20 '18 at 2:24
spopsspops
236215
asked Nov 20 '18 at 2:24
spopsspops
236215
edited Nov 20 '18 at 3:06
spops
asked Nov 20 '18 at 2:24
spopsspops
236215
asked Nov 20 '18 at 2:24
spopsspops
236215
asked Nov 20 '18 at 2:24
spopsspops
236215
236215
NOTE to anybody reading this at the moment: minor errors in my code above which I will fix tomorrow, but Tim's answer below uses correct code that correctly reproduces & then subsequently fixes the issue.
– spops
Nov 20 '18 at 3:08
"Smart keys"--your fake_receivers id values--are a design antipattern.
– philipxy
Nov 20 '18 at 3:50
Not sure I understand, can you explain? If you're referring to the fact that there are letters & dash before the number - nothing I can do about that, that's the way the sensor hardware spits out the data; in the second table, we don't always know the letter code associated with a serial number, but always know the serial number and that the serial numbers will always line up. Not ideal but splitting the string in the first table is the only option I have.
– spops
Nov 20 '18 at 17:15
1
Whatever raw sensor data we log, we can extract appropriate data into a more suitable schema. Re "smart keys".
– philipxy
Nov 21 '18 at 8:02
add a comment |
NOTE to anybody reading this at the moment: minor errors in my code above which I will fix tomorrow, but Tim's answer below uses correct code that correctly reproduces & then subsequently fixes the issue.
– spops
Nov 20 '18 at 3:08
"Smart keys"--your fake_receivers id values--are a design antipattern.
– philipxy
Nov 20 '18 at 3:50
Not sure I understand, can you explain? If you're referring to the fact that there are letters & dash before the number - nothing I can do about that, that's the way the sensor hardware spits out the data; in the second table, we don't always know the letter code associated with a serial number, but always know the serial number and that the serial numbers will always line up. Not ideal but splitting the string in the first table is the only option I have.
– spops
Nov 20 '18 at 17:15
1
Whatever raw sensor data we log, we can extract appropriate data into a more suitable schema. Re "smart keys".
– philipxy
Nov 21 '18 at 8:02
NOTE to anybody reading this at the moment: minor errors in my code above which I will fix tomorrow, but Tim's answer below uses correct code that correctly reproduces & then subsequently fixes the issue.
– spops
Nov 20 '18 at 3:08
NOTE to anybody reading this at the moment: minor errors in my code above which I will fix tomorrow, but Tim's answer below uses correct code that correctly reproduces & then subsequently fixes the issue.
– spops
Nov 20 '18 at 3:08
"Smart keys"--your fake_receivers id values--are a design antipattern.
– philipxy
Nov 20 '18 at 3:50
"Smart keys"--your fake_receivers id values--are a design antipattern.
– philipxy
Nov 20 '18 at 3:50
Not sure I understand, can you explain? If you're referring to the fact that there are letters & dash before the number - nothing I can do about that, that's the way the sensor hardware spits out the data; in the second table, we don't always know the letter code associated with a serial number, but always know the serial number and that the serial numbers will always line up. Not ideal but splitting the string in the first table is the only option I have.
– spops
Nov 20 '18 at 17:15
Not sure I understand, can you explain? If you're referring to the fact that there are letters & dash before the number - nothing I can do about that, that's the way the sensor hardware spits out the data; in the second table, we don't always know the letter code associated with a serial number, but always know the serial number and that the serial numbers will always line up. Not ideal but splitting the string in the first table is the only option I have.
– spops
Nov 20 '18 at 17:15
1
1
Whatever raw sensor data we log, we can extract appropriate data into a more suitable schema. Re "smart keys".
– philipxy
Nov 21 '18 at 8:02
Whatever raw sensor data we log, we can extract appropriate data into a more suitable schema. Re "smart keys".
– philipxy
Nov 21 '18 at 8:02
add a comment |
1 Answer
1
active
oldest
votes
You need to include your current logic directly in the join condition:
select *
from fake_receivers r
inner join fake_stations s
on split_part(r.id, '-', 2)::int = s.receiver_sn;
Demo
answered Nov 20 '18 at 2:31
Tim BiegeleisenTim Biegeleisen
224k1391143
That definitely helps. I'm still getting the mysteriousERROR: invalid input syntax for integer: "UWM"in my full db - will update the above fiddle with more complex data to see if I can recreate it.
– spops
Nov 20 '18 at 3:07
2
Then most likely your data has some problem.
– Tim Biegeleisen
Nov 20 '18 at 3:18
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53385332%2fissue-with-postgres-not-recognizing-cast-on-join%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need to include your current logic directly in the join condition:
select *
from fake_receivers r
inner join fake_stations s
on split_part(r.id, '-', 2)::int = s.receiver_sn;
Demo
answered Nov 20 '18 at 2:31
Tim BiegeleisenTim Biegeleisen
224k1391143
That definitely helps. I'm still getting the mysteriousERROR: invalid input syntax for integer: "UWM"in my full db - will update the above fiddle with more complex data to see if I can recreate it.
– spops
Nov 20 '18 at 3:07
2
Then most likely your data has some problem.
– Tim Biegeleisen
Nov 20 '18 at 3:18
add a comment |
You need to include your current logic directly in the join condition:
select *
from fake_receivers r
inner join fake_stations s
on split_part(r.id, '-', 2)::int = s.receiver_sn;
Demo
answered Nov 20 '18 at 2:31
Tim BiegeleisenTim Biegeleisen
224k1391143
That definitely helps. I'm still getting the mysteriousERROR: invalid input syntax for integer: "UWM"in my full db - will update the above fiddle with more complex data to see if I can recreate it.
– spops
Nov 20 '18 at 3:07
2
Then most likely your data has some problem.
– Tim Biegeleisen
Nov 20 '18 at 3:18
add a comment |
You need to include your current logic directly in the join condition:
select *
from fake_receivers r
inner join fake_stations s
on split_part(r.id, '-', 2)::int = s.receiver_sn;
Demo
answered Nov 20 '18 at 2:31
Tim BiegeleisenTim Biegeleisen
224k1391143
You need to include your current logic directly in the join condition:
select *
from fake_receivers r
inner join fake_stations s
on split_part(r.id, '-', 2)::int = s.receiver_sn;
Demo
answered Nov 20 '18 at 2:31
Tim BiegeleisenTim Biegeleisen
224k1391143
answered Nov 20 '18 at 2:31
Tim BiegeleisenTim Biegeleisen
224k1391143
answered Nov 20 '18 at 2:31
Tim BiegeleisenTim Biegeleisen
224k1391143
answered Nov 20 '18 at 2:31
Tim BiegeleisenTim Biegeleisen
224k1391143
224k1391143
That definitely helps. I'm still getting the mysteriousERROR: invalid input syntax for integer: "UWM"in my full db - will update the above fiddle with more complex data to see if I can recreate it.
– spops
Nov 20 '18 at 3:07
2
Then most likely your data has some problem.
– Tim Biegeleisen
Nov 20 '18 at 3:18
add a comment |
That definitely helps. I'm still getting the mysteriousERROR: invalid input syntax for integer: "UWM"in my full db - will update the above fiddle with more complex data to see if I can recreate it.
– spops
Nov 20 '18 at 3:07
2
Then most likely your data has some problem.
– Tim Biegeleisen
Nov 20 '18 at 3:18
That definitely helps. I'm still getting the mysterious
ERROR: invalid input syntax for integer: "UWM" in my full db - will update the above fiddle with more complex data to see if I can recreate it.– spops
Nov 20 '18 at 3:07
That definitely helps. I'm still getting the mysterious
ERROR: invalid input syntax for integer: "UWM" in my full db - will update the above fiddle with more complex data to see if I can recreate it.– spops
Nov 20 '18 at 3:07
2
2
Then most likely your data has some problem.
– Tim Biegeleisen
Nov 20 '18 at 3:18
Then most likely your data has some problem.
– Tim Biegeleisen
Nov 20 '18 at 3:18
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53385332%2fissue-with-postgres-not-recognizing-cast-on-join%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
NOTE to anybody reading this at the moment: minor errors in my code above which I will fix tomorrow, but Tim's answer below uses correct code that correctly reproduces & then subsequently fixes the issue.
– spops
Nov 20 '18 at 3:08
"Smart keys"--your fake_receivers id values--are a design antipattern.
– philipxy
Nov 20 '18 at 3:50
Not sure I understand, can you explain? If you're referring to the fact that there are letters & dash before the number - nothing I can do about that, that's the way the sensor hardware spits out the data; in the second table, we don't always know the letter code associated with a serial number, but always know the serial number and that the serial numbers will always line up. Not ideal but splitting the string in the first table is the only option I have.
– spops
Nov 20 '18 at 17:15
1
Whatever raw sensor data we log, we can extract appropriate data into a more suitable schema. Re "smart keys".
– philipxy
Nov 21 '18 at 8:02