PostgreSQL naming conventions

by

Regarding tables names, case, etc, the prevalent convention is:

  • SQL keywords: UPPER CASE
  • identifiers (names of databases, tables, columns, etc): lower_case_with_underscores

For example:

UPDATE my_table SET name = 5;

This is not written in stone, but the bit about identifiers in lower case is highly recommended, IMO. Postgresql treats identifiers case insensitively when not quoted (it actually folds them to lowercase internally), and case sensitively when quoted; many people are not aware of this idiosyncrasy. Using always lowercase you are safe. Anyway, it’s acceptable to use camelCase or PascalCase (or UPPER_CASE), as long as you are consistent: either quote identifiers always or never (and this includes the schema creation!).

I am not aware of many more conventions or style guides. Surrogate keys are normally made from a sequence (usually with the serial macro), it would be convenient to stick to that naming for those sequences if you create them by hand (tablename_colname_seq).

See also some discussion here, here and (for general SQL) here, all with several related links.

Note: Postgresql 10 introduced identity columns as an SQL-compliant replacement for serial.

More Related Contents:

  1. What is PostgreSQL equivalent to autoincrement?
  2. How do I specify a password to ‘psql’ non-interactively?
  3. INSERT with dynamic table name in trigger function
  4. psql: FATAL: Ident authentication failed for user “postgres”
  5. Use multiple conflict_target in ON CONFLICT clause
  6. How to increase the max connections in postgres?
  7. Run a PostgreSQL .sql file using command line arguments
  8. Where does PostgreSQL store configuration/conf files?
  9. How to thoroughly purge and reinstall postgresql on ubuntu? [closed]
  10. PostgreSQL function returning multiple result sets
  11. How to add a new Column in a table after the 2nd or 3rd column in the Table using postgres?
  12. How do you change the character encoding of a postgres database?
  13. Difference between GiST and GIN index
  14. Test for null in function with varying parameters
  15. PostgreSQL: Why psql can’t connect to server?
  16. Postgres error on insert – ERROR: invalid byte sequence for encoding “UTF8”: 0x00
  17. Can I use return value of INSERT…RETURNING in another INSERT?
  18. Changing a postgres containers server port in Docker Compose
  19. How do you find the disk size of a Postgres / PostgreSQL table and its indexes
  20. no pg_hba.conf entry for host
  21. PostgreSQL – disabling constraints
  22. PostgreSQL authentication method 10 not supported
  23. Heroku database restore issue
  24. user postgres launches process that takes all CPUs 100% usage
  25. PostgreSQL array_agg order
  26. Postgres table column name restrictions?
  27. How do I get min, median and max from my query in postgresql?
  28. PostgreSQL jsonb, `?` and JDBC
  29. Syntax error at end of input in PostgreSQL
  30. Error when pushing data to Heroku: time zone displacement out of range

Leave a Comment