Search code examples
sqldatabasepostgresqldelete-rowsql-delete

cant delete data from postgresql

I'm quite new to PostgreSQL, so maybe I'm getting some noob error I cannot recognize, so please, tell me why I cannot get this data deleted.

This is what is happening:

DELETE FROM userprofile WHERE user LIKE (SELECT User FROM User WHERE username LIKE 'testuser');
ERROR:  column "username" does not exist
LINE 1: ...file WHERE user LIKE (SELECT User FROM User WHERE username L...

As I'm using django user, I know that username does exists, so, how can I get it deleted?

Solution

  • Chances are (stab in the dark for lack of information) you didn't think of lower-case names.

    Start by reading the manual about identifiers.

    What do you get for: 

    SELECT n.nspname, c.relname, a.attnum, a.attname
FROM   pg_class c
JOIN   pg_attribute a ON a.attrelid = c.oid
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname ilike 'user'
AND    NOT a.attisdropped  -- no dropped (dead) columns
AND    a.attnum > 0        -- no system cols
ORDER  BY 1,2,3

    Pay attention to capitalization of identifiers. Why? Follow the link to the manual above.

    You also seem to be confusing the name of a column with a value stored in this column. The error message complains about the name, because it does not exist.

    And you should never use reserved words like user as identifiers to avoid problems like the one at hand. Those need to be enclosed in double-quotes at all times.