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. — Jonathan Hall, 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 7 · 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

Kriil · Answer 3 · 2023-07-20T13:53:31.000

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

As other answers indicated, you can't do this outright. However, you can get table names prepended for columns that share the same name (like foreign keys and last_updated columns). This will give you enough information to tell which table the column came from.

select t1.*, t2.*, t3.*
from table1 t1
join table2 t2 using (fk1_id)
join table3 t3 using (fk2_id)

Column names would show up like this:

pk1_id | t1.last_updated | unique_col_name_from_t1 | t1.fk1_id | t1.fk2_id | t2.fk1_id | t2.last_updated | unique_col_name_from_t2 | t3.fk2_id | t3.last_updated | unique_col_name_from_t3

This won't help in your particular scenario, but answers the general question posted.

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

3 Answers3

Linked