CODEWITHSUNDEEP
sqlpostgresqlnaming-conventionsalias
Nemanja Boric
Nemanja Boric

Reputation: 22177

SELECT statement returning unqualified column names

I'm having difficulties managing the queries like this:

SELECT c.id, p.id FROM claim c, procedure p LIMIT 1;

This query will return following set:

 id | id
----+----
 49 |  1

Is there any way to make it return c.id and p.id for column names? This one doesn't work:

SELECT c.id as c.id, p.id as c.id FROM claim c, procedure p LIMIT 1;

Or is this my final solution?

SELECT c.id as c_id, p.id as p_id FROM claim c, procedure p LIMIT 1;

Upvotes: 0

Views: 1790

Answers (3)

Pambos Nikolaou
Pambos Nikolaou

Reputation: 11

And the last answer that is missing here which i believe is more correct approach is to use brackets [ ] 

SELECT c.id AS [c.id], p.id AS [p.id]
FROM claim c, procedure p LIMIT 1;

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657892

With a proper naming convention you would rarely have to deal with this problem to begin with.

I suggest to use something like this instead:

CREATE TABLE claim (claim_id serial PRIMARY KEY, ...);

CREATE TABLE procedure (procedure_id serial PRIMARY KEY, ...);

"id" is a very bad choice for a column name. Unfortunately, some half-wit ORMs use this anti-pattern. Avoid it where you can.

Related:
Join one to many and retrieve single result
Using UNNEST with a JOIN

Upvotes: 2

zero323
zero323

Reputation: 330303

SELECT c.id AS "c.id", p.id AS "p.id" FROM claim c, procedure p LIMIT 1;

or simply:

SELECT c.id "c.id", p.id "p.id" FROM claim c, procedure p LIMIT 1;

Upvotes: 3

Related Questions