Why can't I delete a row in Postgresql-Pgadmin when I already followed the format?

Question

I have a table Department with 2 columns, DEPT_CODE the PK and DEPT_NAME and both have data types name. I inserted:

INSERT INTO public."Department"("DEPT_CODE", "DEPT_NAME")
VALUES ('CIS', 'Computer Info Systems');

then I want to delete it so I wrote:

DELETE FROM public."Department"
    WHERE 'DEPT_CODE' = 'CIS';

This didn't delete the row (I used the delete script provided by pgadmin). If I don't use quote for the 'DEPT_CODE', it gave an error:

ERROR:  column "dept_code" does not exist
LINE 2:  WHERE DEPT_CODE = 'CIS';

Idk what to do. Every tutorial I see seemed to be able to delete just fine, why not mine? This is my first time using pgadmin-postgres.

score 0 · Accepted Answer · answered Nov 03 '20 at 08:23

As documented in the manual identifiers (column & table names) need to be enclosed in double quotes. Single quotes are only for string constants.

And because you used the dreaded double quotes for the table and column names when you created the table, you now need to use them always:

DELETE FROM public."Department"
WHERE "DEPT_CODE" = 'CIS';
       ^               ^
       | column name   | string constant

It's strongly recommended to avoid those dreaded quoted identifiers completely. So never use double quotes when you create tables, then you never need to use them when working with the tables

Why can't I delete a row in Postgresql-Pgadmin when I already followed the format?

1 Answers1