0

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?
Improve this question
4
  • 1
    Why don't you create a view with the long names? – a_horse_with_no_name Nov 30 '17 at 6:48
  • Why do you want extremely long canonical column names anyway? That's never a good idea. Just put the description in the COMMENT and put the extended names in the rendering engine. – Evan Carroll Nov 30 '17 at 6:52
  • @EvanCarroll, could I pull the comments out to the rendering engine so the comments would be the "source of truth"? – Wildcard Nov 30 '17 at 6:54
  • 1
    @Wildcard Yes, you can however you have to specify their position SELECT col_description('foo'::regclass, 1); for the first column of the table foo. And the downside is internationalization is hard in this fashion unless you store keys into a il8n dictionary, in which case you may as well use a short column name. This is only an issue if you're internationalizing. – Evan Carroll Nov 30 '17 at 7:27
1

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.

Improve this answer
1
  • I always wondered why the system catalogs use a fixed-width type to store the names and not e.g. a text or varchar(x) – a_horse_with_no_name Jul 31 '19 at 5:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.