0

Here, I am only going to get CheckListNo (integer) from select statement "Check_No" ( character varying). when am execute this, error showing like this 'operator does not exist: character varying = integer'. So, I want to check without changing datatypes.

For an example, SELECT "Check_No" FROM "Project_CheckList_Options" let we see I defined "check_No" column as a integer if I want to get records in character varying. I can use just "check_No":: character varying then I can see column records in character varying type without affecting integer datatype. So like that Is it possible to convert datatype when alter the table with check constraint in postgresql?

 alter table "Project_Configuration" 
  add check("CheckListNo" in (SELECT  "Check_No"  FROM "Project_CheckList_Options"))
Anthon
  • 69,918
  • 32
  • 186
  • 246
Anand Vignesh A
  • 29
  • 2
  • 8
  • 2
    You can't create a check constraint based on a sub-select. What you are looking for is a foreign key constraint. –  Jun 17 '15 at 10:35
  • i used before this query ,but it shows same error alter table "Project_Configuration" add constraint operator does not exist: character varying = integer'"Fk_ProjTimesheetData_Projworkallocation" foreign key("CheckListNo" ) references "Project_CheckList_Options" ("Check_No") on delete restrict on update cascade – Anand Vignesh A Jun 17 '15 at 11:25
  • 1
    Please don't add code as comments, **edit** your question. If that fails it means that the columns `"CheckListNo"` and `Check_No"` are defined with different data types (one as integer one as varchar). You can't compare apples and oranges, so there is no way you can create such a constraint. You have to change the data type of one of the columns, so that e.g. both are integers. Please show us the complete `create table` statements for both tables (again: **edit** your question and add the code) –  Jun 17 '15 at 11:30

0 Answers0