syntax error at or near "-" in PostgreSQL

Question

I'm trying to run a query to update the user password using.

alter user dell-sys with password 'Pass@133';

But because of - it's giving me error like,

ERROR:  syntax error at or near "-"
LINE 1: alter user dell-sys with password 'Pass@133';
                       ^

Can Anyone shade a light on it?

Sergiosergipe

postgresql.org/docs/current/static/… – Brassie

Answer 1

I have reproduced the issue in my system,

postgres=# alter user my-sys with password 'pass11';
ERROR:  syntax error at or near "-"
LINE 1: alter user my-sys with password 'pass11';
                       ^

Here is the issue,

psql is asking for input and you have given again the alter query see postgres-#That's why it's giving error at alter

postgres-# alter user "my-sys" with password 'pass11';
ERROR:  syntax error at or near "alter"
LINE 2: alter user "my-sys" with password 'pass11';
        ^

Solution is as simple as the error,

postgres=# alter user "my-sys" with password 'pass11';
ALTER ROLE

Animatism

Answer 2

Wrap it in double quotes

alter user "dell-sys" with password 'Pass@133';

Notice that you will have to use the same case you used when you created the user using double quotes. Say you created "Dell-Sys" then you will have to issue exact the same whenever you refer to that user.

I think the best you do is to drop that user and recreate without illegal identifier characters and without double quotes so you can later refer to it in any case you want.

Terrellterrena

Tried this one, it's giving ERROR: syntax error at or near "alter" LINE 2: alter user "dell-sys" with password 'Pass@133'; – Sergiosergipe

@Heliconia are you sure your previous line is ended with semicolon? – Ganesha

Answer 3

i was trying trying to GRANT read-only privileges to a particular table to a user called walters-ro. So when i ran the sql command # GRANT SELECT ON table_name TO walters-ro; --- i got the following error..`syntax error at or near “-”

The solution to this was basically putting the user_name into double quotes since there is a dash(-) between the name.

# GRANT SELECT ON table_name TO "walters-ro";

That solved the problem.

Hymettus

Answer 4

I got the similar error below:

ERROR: syntax error at or near ")"
LINE 4: );

Because I put a trailing comma mistakenly as shown below:

CREATE TABLE person(
  id SERIAL PRIMARY KEY,
  name VARCHAR(20),
               -- ↑ A trailing comma
);

So, I removed the a trailing comma as shown below, then the error was solved:

CREATE TABLE person(
  id SERIAL PRIMARY KEY,
  name VARCHAR(20)
               -- ↑ No trailing comma
);

Impinge

Answer 5

In case you're in 2024 and facing this issue while resetting the password All you need to do is just wrap your password in single quotes it'll work

alter user postgres with password 'Pass@133';

Whip