13

I am still relatively new to pgSQL after switching away from mySQL completely.

I am trying to find the character limitations, if any, that pgSQL may or may not have. Specifically I am curious if there is a character limit on the following?

  1. Database Name Length (mySQL is 64 characters)
  2. Username Length (mySQL is 16 characters)
  3. Password Length

I've been searching Google, I've read the pgSQL FAQ, and a few random other posts but I haven't found a solid answer to any of these. Perhaps pgSQL does not have these limitations like mySQL does. If anyone could shed some light on this that would be great!

I am currently using pgSQL 9.3.1

Diemuzi
  • 3,507
  • 7
  • 36
  • 61

1 Answers1

15

The length for any identifier is limited to 63 characters:

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

By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes

As username and database name are identifiers, that limit should apply to them.

I'm not aware of any length limitation on passwords (although I'm sure there is one).

  • I believe the password are hashed, so I've doubts any limit on length apply to them. – Denis de Bernardy Oct 21 '13 at 16:01
  • Yeah I figured there wouldn't be any need to worry about a password length but I tossed that in there just in case. – Diemuzi Oct 21 '13 at 17:18
  • 5
    The `psql` command-line tool truncates passwords to `100` bytes and doesn't work with passwords longer than that, although the server would accept them. – Daniel Vérité Aug 19 '15 at 09:08
  • 6
    99 characters to be exact! I used a 100-character password and spent all day chasing down why it wasn't working. Two details for anyone else runing into this: long passwords can be entered manually but will break when loaded from envar/.pgpass, and it appears that this is not just psql but also libpq (or at least it happens for the node pq library, which I think is based on libpq) – xanderflood Sep 16 '20 at 02:24