How to disambiguate PostgreSQL column references from table references in projections?-CodePudding

Assuming I have a schema like this:

create table x (t1 int, t2 int);
create table t1 (t1 int, t2 int);
insert into x values (1, 2);
insert into t1 values (1, 2);

Now in PostgreSQL, I can conveniently project table references to create nested rows in the result:

select x from x;

Producing:

|x     |
|------|
|[1, 2]|

But this doesn't work if there's a conflict between tables and columns:

select t1 from t1;

That just translates to an unqualified column reference:

|t1 |
|---|
|1  |

Now, as I'm maintaining a SQL generator, let's assume I cannot alias the table (because I might not know what column names it contains, and any alias could still conflict with actual columns).

Is there any syntax which allows for disambiguating between table reference and column reference, such as e.g. a hypothetical syntax below?

select t1::the_table_not_the_column from t1

CodePudding user response：

This seems to work, if I know the column names:

select row(t1, t2)::t1 as t1 from t1

Or, if I don't know the column names:

select row(t1.*)::t1 as t1 from t1

CodePudding user response：

I propose to simply use table aliases in these situations:

SELECT t FROM x AS t

Done.