help with inserting into the same table and changing a field

[bobm]

I am trying to update a table by copying existing rows and change one field in the new row but getting various errors when doing so. The latest is:
ERROR: column "diagnosis_id" is of type integer but expression is of type boolean
LINE 2: ...region_char_code, procedure_id, hide, code_type, (diagnosis_...
^
HINT: You will need to rewrite or cast the expression.

Code: Select all

INSERT INTO diagnosis_procedures (flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id)
SELECT flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, (diagnosis_id = '14742')
FROM diagnosis_procedures
WHERE diagnosis_id = '13783'

Any help would be much appreciated. I am using postgresql for my database.
Thank you,
Bob

[Bernd.N]

It is as postgres says, your last field diagnosis_id is of type integer, but the expression below results into a boolean, because of the brackets around the expression: (diagnosis_id = '14742').
That evaluates into a true or false.
In case you want to put the diagnosis_id 14742 into the new record(s), just write
14742 AS diagnosis_id
That should do it.

I guess that your WHERE clause will also complain, when the diagnosis_id is really an integer, because then the 13783 may not have quotation marks ( ' ) around it.
Tip: In those cases, I always align the fields in the first INSERT INTO line to the fields in the second line (with blanks), so that the field pairs can be easily seen and checked.
And I think you do not need the AS diagnosis_id, just try.

Code: Select all

INSERT INTO diagnosis_procedures
      (flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id)
SELECT flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, 14742
FROM diagnosis_procedures
WHERE diagnosis_id = 13783

[bobm]

Thank you Bernd,
This is getting me closer. I guess something I forgot to mention is I have a primary key field set to not-null -
so when I run this I am getting the following message:

ERROR: null value in column "diagnosis_procedure_id" violates not-null constraint

How can I set the diagnosis_procedure_id to auto increment as new records are added?
Thanks again,
Bob

[Bernd.N]

That depends on the data type of your primary key.
We use for example only UUIDs, and we would have to create a new UUID with the expression
UPPER(uuid_generate_v4()::varchar)

So in case you use UUIDs, that would be

Code: Select all

INSERT INTO diagnosis_procedures
      (flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id, diagnosis_procedure_id)
SELECT flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, 14742,        UPPER(uuid_generate_v4()::varchar)
FROM diagnosis_procedures
WHERE diagnosis_id = 13783

In case you use integers as primary keys, I do not know at the moment how to do that in such an INSERT case, but I guess other users here can easily answer that.
You can also google with a smart word combination, like "postgres insert primary key autoincrement", I learned a lot with that strategy.
You will find for example
http://dba.stackexchange.com/questions/65662/postgres-how-to-insert-row-with-autoincrement-id

Servoy is using the same strategy for integers as far as I know, when you have autoincrement switched on for the table and Servoy adds a record.

[bobm]

Thanks Bernd,

Yes, my primary key is an integer. I'll google this as well and see if I can figure it out from there. But if anyone has suggestions, please let me know.

Thanks,
Bob