I'm trying to delete just one data from my DB, but, when I write the command I keep getting that there's some syntax error, could you tell me where is the error?
This are the commands I've tried:
DELETE FROM database_userprofile WHERE user.username = 'some';
ERROR: syntax error at or near "."
LINE 1: DELETE FROM database_userprofile WHERE user.username = 'some'...
DELETE FROM database_userprofile USING database_user WHERE user.username="some";
ERROR: syntax error at or near "."
LINE 1: ... database_userprofile USING database_user WHERE user.username=...
Hope you can help me
Your query doesn't make any sense.
DELETE FROM database_userprofile WHERE user.username = 'some';
^^^^
Where'd user
come from? It isn't referenced in the query. Is it a column of database_userprofile
? If so, you can't write user.username
(unless it's a composite type, in which case you would have to write (user).username
to tell the parser that; but I doubt it's a composite type).
The immediate cause is that user
is a reserved word. You can't use that name without quoting it:
DELETE FROM database_userprofile WHERE "user".username = 'some';
... however, this query still makes no sense, it'll just give a different error:
regress=> DELETE FROM database_userprofile WHERE "user".username = 'some';
ERROR: missing FROM-clause entry for table "user"
LINE 1: DELETE FROM database_userprofile WHERE "user".username = 'so...
My wild guess is that you're trying to do a delete over a join. I'm assuming that you have tables like:
CREATE TABLE "user" (
id serial primary key,
username text not null,
-- blah blah
);
CREATE TABLE database_userprofile (
user_id integer references "user"(id),
-- blah blah
);
and you're trying to do delete with a condition across the other table.
If so, you can't just write user.username
. You must use:
DELETE FROM database_userprofile
USING "user"
WHERE database_userprofile.user_id = "user".id
AND "user".username = 'fred';
You'll notice that I've double-quoted "user". That's because it's a keyword and shouldn't really be used for table names or other user defined identifiers. Double-quoting it forces it to be intepreted as an identifier not a keyword.
Due to documentation, the syntax for delete in PostgreSQL 9.1 is:
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table [ * ] [ [ AS ] alias ]
[ USING using_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
So you need to specify the "table_name" after DELETE command, not the "database_name".
You can delete data only if you are logged into the database.
You got
ERROR: syntax error at or near "."
because in the WHERE section you can specify the target table or the tables in the usinglist.
You may also get this error when copy-pasting a query from Eclipse to pgadmin. Somehow, a strange symbol may be inserted. To avoid this error, paste it in a simple text editor first (like notepad), then cut it from there and paste it in pgadmin.