0

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;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Nemanja Boric
  • 21,627
  • 6
  • 67
  • 91

3 Answers3

3
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;
zero323
  • 322,348
  • 103
  • 959
  • 935
  • See [7.3.2. Column Labels](http://www.postgresql.org/docs/9.3/static/queries-select-lists.html) in the PostgreSQL documentation. –  Sep 14 '13 at 15:54
2

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

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I know it is. However, I didn't designed nor the databases, nor the system (and it is already in the use for a long time). – Nemanja Boric Sep 14 '13 at 16:31
1

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;
Pambos Nikolaou
  • 11
  • 3