Styjun

Anatomically Correct Strange Women In Ponds Distributing Swords

Is a stroke of luck acceptable after a series of unfavorable events?

Why do professional authors make "consistency" mistakes? And how to avoid them?

Does the Brexit deal have to be agreed by both Houses?

Increase performance creating Mandelbrot set in python

WOW air has ceased operation, can I get my tickets refunded?

How do spells that require an ability check vs. the caster's spell save DC work?

Is it okay to store user locations?

Too much space between section and text in a twocolumn document

Is it my responsibility to learn a new technology in my own time my employer wants to implement?

What can we do to stop prior company from asking us questions?

Inappropriate reference requests from Journal reviewers

How to make a variable always equal to the result of some calculations?

What's the point of interval inversion?

Return the Closest Prime Number

How to get regions to plot as graphics

Customer Requests (Sometimes) Drive Me Bonkers!

Trouble understanding the speech of overseas colleagues

How to make a software documentation "officially" citable?

Would this house-rule that treats advantage as a +1 to the roll instead (and disadvantage as -1) and allows them to stack be balanced?

What is the point of a new vote on May's deal when the indicative votes suggest she will not win?

Implement the Thanos sorting algorithm

If the heap is initialized for security, then why is the stack uninitialized?

Are there languages with no euphemisms?

Numeric=character varying :operator does not exist based on postgres database

0

I have a query like this :

Select office_name, ofc_code 
from table1 
where ofc_code in (select ofc_institution from table2 where id ='2')

If i run this i get an error:

Operator does not exist:numeric = character varying
Hint : no operator matches the given name and argument types

In the above query: ofc_code is numeric and ofc_institution is a character varying

sql postgresql

share|improve this question

edited Mar 21 at 16:44

a_horse_with_no_name

306k46468565

asked Mar 21 at 16:41

Stevie Weedy LyngdohStevie Weedy Lyngdoh

1

add a comment | 

0

I have a query like this :

Select office_name, ofc_code 
from table1 
where ofc_code in (select ofc_institution from table2 where id ='2')

If i run this i get an error:

Operator does not exist:numeric = character varying
Hint : no operator matches the given name and argument types

In the above query: ofc_code is numeric and ofc_institution is a character varying

sql postgresql

share|improve this question

edited Mar 21 at 16:44

a_horse_with_no_name

306k46468565

asked Mar 21 at 16:41

Stevie Weedy LyngdohStevie Weedy Lyngdoh

1

add a comment | 

I have a query like this :

Select office_name, ofc_code 
from table1 
where ofc_code in (select ofc_institution from table2 where id ='2')

If i run this i get an error:

Operator does not exist:numeric = character varying
Hint : no operator matches the given name and argument types

In the above query: ofc_code is numeric and ofc_institution is a character varying

sql postgresql

share|improve this question

edited Mar 21 at 16:44

a_horse_with_no_name

306k46468565

asked Mar 21 at 16:41

Stevie Weedy LyngdohStevie Weedy Lyngdoh

1

Select office_name, ofc_code 
from table1 
where ofc_code in (select ofc_institution from table2 where id ='2')

Operator does not exist:numeric = character varying
Hint : no operator matches the given name and argument types

share|improve this question

edited Mar 21 at 16:44

a_horse_with_no_name

306k46468565

asked Mar 21 at 16:41

Stevie Weedy LyngdohStevie Weedy Lyngdoh

1

share|improve this question

edited Mar 21 at 16:44

a_horse_with_no_name

306k46468565

asked Mar 21 at 16:41

Stevie Weedy LyngdohStevie Weedy Lyngdoh

1

edited Mar 21 at 16:44

a_horse_with_no_name

306k46468565

edited Mar 21 at 16:44

a_horse_with_no_name

306k46468565

asked Mar 21 at 16:41

Stevie Weedy LyngdohStevie Weedy Lyngdoh

1

asked Mar 21 at 16:41

Stevie Weedy LyngdohStevie Weedy Lyngdoh

1

1 Answer
1

active

oldest

votes

1

Don't compare apples to oranges. '2' is a string value, 2 is a number.

You also need to convert ofc_institution to a number if you are certain that it only contains numbers (the question then is: why on earth are you storing numbers in a varchar column?):

Select office_name, ofc_code 
from table1 
where ofc_code in (select ofc_institution::numeric
 from table2 
 where id = 2);

If you can't be certain that ofc_institution is always a number, then cast the ofc_code to a string - but that then begs the question why are you comparing those columns to begin with:

Select office_name, ofc_code 
from table1 
where ofc_code::varchar in (select ofc_institution
 from table2 
 where id = 2);

share|improve this answer

edited Mar 21 at 16:47

answered Mar 21 at 16:44

a_horse_with_no_namea_horse_with_no_name

306k46468565

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yeah i try but still not working... the error is where ofc_code in ...
  
  – Stevie Weedy Lyngdoh
  Mar 21 at 16:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @StevieWeedyLyngdoh: see my edit. You seem to have a horrible table design if you are comparing apples with oranges
  
  – a_horse_with_no_name
  Mar 21 at 16:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yeah i know ..actually the query was written by someone else...i came to fixed those errors... thats y im having a lots of errors in those queries... and i have to do it..its my job... neway thank you for your help..really appreciate it..thanks
  
  – Stevie Weedy Lyngdoh
  Mar 21 at 16:53
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Before properly fixing the queries, please be sure to look at the table structure and understand, that relational databases are not dynamically typed. If you have a psql session, just do a d+ table1 and d+ table2 and look at the data types of columns table1.ofc_code, table2.ofc_institution and table2.id. That should make you understand what is wrong with this query and others.
  
  – Ancoron
  Mar 21 at 16:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @StevieWeedyLyngdoh: If that answer solved your question then please accept it, so that your question is marked as resolved.
  
  – a_horse_with_no_name
  Mar 22 at 6:56
  

  
  
  
  

 | 
show 1 more 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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55285326%2fnumeric-character-varying-operator-does-not-exist-based-on-postgres-database%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Don't compare apples to oranges. '2' is a string value, 2 is a number.

You also need to convert ofc_institution to a number if you are certain that it only contains numbers (the question then is: why on earth are you storing numbers in a varchar column?):

Select office_name, ofc_code 
from table1 
where ofc_code in (select ofc_institution::numeric
 from table2 
 where id = 2);

If you can't be certain that ofc_institution is always a number, then cast the ofc_code to a string - but that then begs the question why are you comparing those columns to begin with:

Select office_name, ofc_code 
from table1 
where ofc_code::varchar in (select ofc_institution
 from table2 
 where id = 2);

share|improve this answer

edited Mar 21 at 16:47

answered Mar 21 at 16:44

a_horse_with_no_namea_horse_with_no_name

306k46468565

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yeah i try but still not working... the error is where ofc_code in ...
  
  – Stevie Weedy Lyngdoh
  Mar 21 at 16:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @StevieWeedyLyngdoh: see my edit. You seem to have a horrible table design if you are comparing apples with oranges
  
  – a_horse_with_no_name
  Mar 21 at 16:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yeah i know ..actually the query was written by someone else...i came to fixed those errors... thats y im having a lots of errors in those queries... and i have to do it..its my job... neway thank you for your help..really appreciate it..thanks
  
  – Stevie Weedy Lyngdoh
  Mar 21 at 16:53
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Before properly fixing the queries, please be sure to look at the table structure and understand, that relational databases are not dynamically typed. If you have a psql session, just do a d+ table1 and d+ table2 and look at the data types of columns table1.ofc_code, table2.ofc_institution and table2.id. That should make you understand what is wrong with this query and others.
  
  – Ancoron
  Mar 21 at 16:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @StevieWeedyLyngdoh: If that answer solved your question then please accept it, so that your question is marked as resolved.
  
  – a_horse_with_no_name
  Mar 22 at 6:56
  

  
  
  
  

 | 
show 1 more comment

1

Don't compare apples to oranges. '2' is a string value, 2 is a number.

You also need to convert ofc_institution to a number if you are certain that it only contains numbers (the question then is: why on earth are you storing numbers in a varchar column?):

Select office_name, ofc_code 
from table1 
where ofc_code in (select ofc_institution::numeric
 from table2 
 where id = 2);

If you can't be certain that ofc_institution is always a number, then cast the ofc_code to a string - but that then begs the question why are you comparing those columns to begin with:

Select office_name, ofc_code 
from table1 
where ofc_code::varchar in (select ofc_institution
 from table2 
 where id = 2);

share|improve this answer

edited Mar 21 at 16:47

answered Mar 21 at 16:44

a_horse_with_no_namea_horse_with_no_name

306k46468565

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yeah i try but still not working... the error is where ofc_code in ...
  
  – Stevie Weedy Lyngdoh
  Mar 21 at 16:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @StevieWeedyLyngdoh: see my edit. You seem to have a horrible table design if you are comparing apples with oranges
  
  – a_horse_with_no_name
  Mar 21 at 16:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yeah i know ..actually the query was written by someone else...i came to fixed those errors... thats y im having a lots of errors in those queries... and i have to do it..its my job... neway thank you for your help..really appreciate it..thanks
  
  – Stevie Weedy Lyngdoh
  Mar 21 at 16:53
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Before properly fixing the queries, please be sure to look at the table structure and understand, that relational databases are not dynamically typed. If you have a psql session, just do a d+ table1 and d+ table2 and look at the data types of columns table1.ofc_code, table2.ofc_institution and table2.id. That should make you understand what is wrong with this query and others.
  
  – Ancoron
  Mar 21 at 16:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @StevieWeedyLyngdoh: If that answer solved your question then please accept it, so that your question is marked as resolved.
  
  – a_horse_with_no_name
  Mar 22 at 6:56
  

  
  
  
  

 | 
show 1 more comment

Don't compare apples to oranges. '2' is a string value, 2 is a number.

You also need to convert ofc_institution to a number if you are certain that it only contains numbers (the question then is: why on earth are you storing numbers in a varchar column?):

Select office_name, ofc_code 
from table1 
where ofc_code in (select ofc_institution::numeric
 from table2 
 where id = 2);

If you can't be certain that ofc_institution is always a number, then cast the ofc_code to a string - but that then begs the question why are you comparing those columns to begin with:

Select office_name, ofc_code 
from table1 
where ofc_code::varchar in (select ofc_institution
 from table2 
 where id = 2);

share|improve this answer

edited Mar 21 at 16:47

answered Mar 21 at 16:44

a_horse_with_no_namea_horse_with_no_name

306k46468565

Select office_name, ofc_code 
from table1 
where ofc_code in (select ofc_institution::numeric
 from table2 
 where id = 2);

Select office_name, ofc_code 
from table1 
where ofc_code::varchar in (select ofc_institution
 from table2 
 where id = 2);

share|improve this answer

edited Mar 21 at 16:47

answered Mar 21 at 16:44

a_horse_with_no_namea_horse_with_no_name

306k46468565

answered Mar 21 at 16:44

a_horse_with_no_namea_horse_with_no_name

306k46468565

answered Mar 21 at 16:44

a_horse_with_no_namea_horse_with_no_name

306k46468565