Prepend Table Name to Each Column in a Result Set in SQL? (Postgres Specifically)

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

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] 

SQL select join: is it possible to prefix all columns as 'prefix.*'?

I see two possible situations here. First, you want to know if there is a SQL standard for this, that you can use in general regardless of the database. No, there is not. Second, you want to know with regard to a specific dbms product. Then you need to identify it. But I imagine the most likely answer is that you'll get back something like "a.id, b.id" since that's how you'd need to identify the columns in your SQL expression. And the easiest way to find out what the default is, is just to submit such a query and see what you get back. If you want to specify what prefix comes before the dot, you can use "SELECT * FROM a AS my_alias", for instance.

In a join, how to prefix all column names with the table it came from

You could

select ah.*, l.*, u.*, pi.* from ...

then the columns will be returned ordered by table at least.

For better distinction between every two sets of columns, you could also add "delimiter" columns like this:

select ah.*, ':', l.*, ':', u.*, ':', pi.* from ...

^{(Edited to remove explicit aliases as unnecessary, see comments.)}

Select column name and value from table

Of course, you could write a PL/pgSQL function and query the catalog table pg_attribute yourself. But it's so much easier with one of the following:

JSON

The function row_to_json() provides functionality that goes half the way. Introduced with Postgres 9.2:

SELECT row_to_json(t, TRUE) FROM tbl t;

You don't have to mention the column names at all, the function derives them from the table type.

->SQLfiddle demo.

But you'll need json_each_text() from Postgres 9.3 to go all the way:

SELECT json_each_text(row_to_json(t)) FROM tbl t;

To get a sort order like you display:

SELECT (json_each_text(row_to_json(t))).*
FROM   tbl t
ORDER  BY 1, 2;

(It's unclear how you want to sort exactly.)

Untested. SQLfiddle does not provide Postgres 9.3, yet.

hstore

However, you can do the same with the additional module hstore. Available since 8.4. Install it once with:

CREATE EXTENSION hstore;

Details:

Key value pair in PostgreSQL

Query:

SELECT (each(hstore(t))).*
FROM   tbl t
ORDER  BY 1,2;

That's all.

Again, no SQLfiddle, since one can't install additional modules there.

New table with all columns as differences between two base tables columns

You can build the whole statement from information in the system catalog (or information schema) and execute it dynamically with a DO command. That's what I would do.

DO
$do$
BEGIN

EXECUTE (
SELECT 'CREATE TABLE tab3 AS
SELECT '
|| string_agg(format('a.%1$I - b.%1$I AS %1$I', attname)
            , E'\n     , ' ORDER BY attnum)
|| '
FROM   tab1      a
FULL   JOIN tab2 b USING (permno, datadate)'
FROM   pg_attribute
WHERE  attrelid = 'tab1'::regclass
AND    attnum > 0        -- exclude system columns (neg. attnum)
AND    NOT attisdropped  -- no dropped (dead) columns
);

END
$do$;

Assuming that tab1 and tab2 are visible in your search_path.

Produces and executes what you requested exactly. Just replace dummy table and column names with your real names.

Read about format() and string_agg() in the manual.

More information in these related answers:

Table name as a PostgreSQL function parameter

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

---

Related Topics