Prepend table name to each column in a result set in SQL? (Postgres specifically)

Question

How can I get the label of each column in a result set to prepend the name if its table?

I want this to happen for queries on single tables as well as joins.

Example:

  SELECT first_name, last_name FROM person;

I want the results to be:

 | person.first_name | person.last_name |
 |-------------------|------------------|
 | Wendy             | Melvoin          |
 | Lisa              | Coleman          |

I could use "AS" to define an alias for each column, but that would be tedious. I want this to happen automatically.

  SELECT first_name AS person.first_name, last_name AS person.last_name FROM person;

The reason for my question is that I am using a database driver that does not provide the meta-data informing me the database column from where the result set got its data. I am trying to write generic code to handle the result set.

I would like to know how to do this in SQL generally, or at least in Postgres specifically.

SQLite had such a feature, though I see it is now inexplicably deprecated. SQLite has two pragma settings: full_column_names & short_column_names.

Seeing how SQLAlchemy does all this "manually", I too doubt that this can be done. — Maxim Sloyko, Mar 03 '11 at 12:59
One reason this is impossible is that there's nothing to magically tie a result column to a table. The output in your column might be an expression made from 0 or more columns (and 0 or more tables). The default column name for the output is often borrowed from a column name, but it can just as well come from a function name (i.e. 'SELECT min(x)' returns a column named 'min' by default), and may be '?column?' when there is no default. I think your (unfortunately tedious) suggestion of using 'AS' is the only answer, short of hacking and recompiling Postgres with different behavior. — Flimzy, Jun 15 '11 at 09:13
I agree with @a_horse_with_no_name. When you do a SELECT, you are in effect executing a relational expression who's result is a new relation. By analogy if you UNION 2 sets {a, b} U {c, d} the resulting set would not know the origin of its members. I think you are faced with a similar problem here. As an aside, your application logic should have this knowledge about where a particular column came from. — Damon Snyder, Apr 21 '12 at 21:27
Thanks for the information. I now understand that, within the SQL conceptual world, my question does not make sense. My prior experience with a simpler relational but proprietary (not SQL-based) database ([4D](http://www.4d.com/)) led me to think of each field of each row being a fixed item. But that is not the case in SQL where the row/column intersection's value is, in a sense, always being *generated* rather than *retrieved*. That makes the two answers provided here all the more impressively clever. — Basil Bourque, Mar 12 '14 at 23:06

Daryl · Accepted Answer · 2014-01-08T15:27:33.517

I know this question is a bit old, but perhaps someone will stumble over the answer and it will help them out.

The proper way to do what you're looking for is to create and use a View. Yes, it will be a bit tedious one-time to type out all those new column names as aliases, but if there are a lot of columns here's a trick you can use to leverage the PostgreSQL metadata to write out the text of the view:

select 'CREATE OR REPLACE VIEW people AS SELECT ' || 
(select string_agg(column_name || ' AS person_' || column_name, ', ')
from information_schema.columns
where table_name = 'person'
group by table_name) || 
' FROM person;';

running this yields:

?column?                                                 
------------------------------------------------------------------------------------------------------------- 
CREATE OR REPLACE VIEW people AS SELECT last_name AS person_last_name, first_name AS person_first_name FROM person; 

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 4/21/12 2:05:21 PM EDT ] [Execution: 9/ms]

you can then copy and execute the results and voila:

select * from people;

 person_last_name     person_first_name    
 -------------------  -------------------- 
 Melvoin              Wendy                
 Coleman              Lisa                 

 2 record(s) selected [Fetch MetaData: 1/ms] [Fetch Data: 0/ms]

Is it possible to run this in one statement? Because now it needs two roundtrips. Maybe use something like `EXECUTE`, but it works only in functions. — davekr, Mar 15 '13 at 15:49

score 6 · Answer 2 · edited Jun 20 '20 at 09:12

To get the VIEW (Daryl's idea) in a single statement use a function or a DO command with EXECUTE:

DO
$do$
BEGIN

EXECUTE (
   SELECT format(
      'CREATE TEMP VIEW people AS SELECT %s FROM %I'
     , string_agg(format('%I AS %I', attname, attrelid::regclass || '.' || attname), ', ')
     , attrelid::regclass)
   FROM   pg_attribute
   WHERE  attrelid = 'person'::regclass  -- supply source table name once
   AND    attnum > 0
   AND    NOT attisdropped
   GROUP  BY attrelid
   );

END
$do$;

This immediately executes a command of the form:

CREATE OR REPLACE VIEW people AS
SELECT person_id AS "person.person_id"
     , first_name AS "person.first_name"
     , last_name AS "person.last_name"
FROM   person;

Would be less hassle to concatenate legal column names with '_' instead of '.'. But you need to be prepared for non-standard names that require double-quoting (and defend against possible SQL injection) anyway.

You can optionally provide a schema-qualified table name (myschema.person). The schema-name is prefixed in column names automatically if it is outside the current search_path.

For repeated use, you wrap this into a plpgsql function and make the table name a text parameter. All text-to-code conversion is sanitized here to prevent SQL injection. Example with more information here:

Table name as a PostgreSQL function parameter

And you might use the new to_regclass() in Postgres 9.4+:

How to check if a table exists in a given schema

Prepend table name to each column in a result set in SQL? (Postgres specifically)

2 Answers2

Linked