Postgresql – How to easily show very long “canonical” name for each column in all reports

aliaspostgresqlreporting

Related:

For query writing purposes I want to have nice short actual column names.

However, from my actual data source, I have an extremely long "canonical" name for each field. (This was the column name in the source.)

When showing my query results, I would like to show these long column names.

How can I do this?

I considered a couple ways of approaching this:

  • I could COMMENT ON each column, which would at least store the long names directly in the table definition, but this wouldn't show the comments with each query result. (Unless there's some way to do that…?)
  • I could make a big column alias block of code that I could copy and paste for each query, but that would only work if I'm returning ALL the columns, which isn't likely in most cases.
  • I could try some fancy magic with the information schema (or system tables) and use PL/pgSQL to run every query so it gives the desired column names. (I wouldn't know how to approach this, actually, though I have a decent grasp of PL/pgSQL.)
  • There must be some better way I haven't thought of…can you help me here?

Best Answer

Identifiers are limited to 63 bytes by default, which doesn't qualify as "extremely long". Quote from the manual:

By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes. If this limit is problematic, it can be raised by changing the NAMEDATALEN constant in src/include/pg_config_manual.h

(and recompiling)

Besides the need to recompile that might give pause, increasing NAMEDATALEN by a large margin is not very good idea, because every single identifier stored in the catalog will consume that size, no matter its length.

To me, the only reasonable way to show arbitrary long column names would be to implement it in the client-side presentation layer. Storing the names themselves could be done in the SQL column comments as you suggest, or in your own custom metadata table. The presentation layer could retrieve that data at the start of the session and cache it so that it would be essentially transparent for the rest of the SQL.

Related Question