Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

postgresql

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
like image 882
Lukas Eder Avatar asked Dec 10 '25 08:12

Lukas Eder


1 Answers

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
like image 61
Lukas Eder Avatar answered Dec 11 '25 22:12

Lukas Eder