56

I create a database with a hyphen in the middle of the name with createdb. That successfully creates the database, but within the psql interactive client, I get a syntax error if I try a command like this:

ALTER DATABASE my-database SET SCHEMA = myschema,public;

psql complains of a syntax error at or near "-"

Is there some documentation for what counts as a valid PostgreSQL database name?

Should I just underscores instead of hyphens?

Improve this question

3 Answers 3

Reset to default
49

The documentation you asked about is here:

http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Most people just stick to lowercase letters, numeric digits, and underscores -- to avoid typing the quotes all the time.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This is not exactly the documentation for a database name. If you read it closely, - is not allowed, yet, while CREATE TABLE foo-bar; does indeed fail, createdb foo-bar on the command line works perfectly to create a database named foo-bar.
Sure; most of these shell commands wrap any supplied identifiers in quotes in the commands they send to the database. To me, that makes sense, because otherwise you need to worry about which quotes are consumed by the shell in processing the command at that level.
Yes, but it also means the results will be different between doing the command "out of" the DB through some command, and inside the DB. That's ok but can yield to surprises.
The database service has no control over what quoting gets eaten by command line shells.
The documentation is fairly vague really, the CREATE DATABASE docs just specify a "name" argument, without explicitly saying it should be an identifier, and the identifier docs state that many places identifiers are used are simply referred to as "names".
32

Try putting it in double quotes:

ALTER DATABASE "my-database" SET SCHEMA = myschema,public;

Improve this answer

2 Comments

So it's not frowned upon to put hyphens in database names?
I wouldn't personally, but the debate over naming conventions is well documented elsewhere on stack.
3

I faced one issue and above answers helped me. So sharing scenario on dbname

Scenario:I was tried to change database name using PG admin III. My database name was My_Database

running below queries failed:

ALTER DATABASE My_Database RENAME TO dba;
ALTER DATABASE [My_Database] RENAME TO dba;

ALTER DATABASE 'My_Database' RENAME TO dba;

Then i tried below and its successful

ALTER DATABASE "My_Database" RENAME TO dba;
Improve this answer

1 Comment

Without the double-quotes, any uppercase letters are folded to lowercase, which is why the first statement failed. The next two are just incorrect syntax. The last one the correct way to supply an identifier with any uppercase letter(s).

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.