I'm trying to see if I can optimize the following query which was written by my predecessor:

   SELECT tms.*, 
          0 as kyori ,
              (139.745069 - to_number(tms.lng,'000D00000000')) / 0.0111 * 1000000, 
              2) + 
              (35.662978 - to_number(tms.lat,'000D00000000')) / 0.0091   *1000000, 
          ) AS kyori2 
  FROM ttb_members tms, 
       mtb_tenshu mt 
  WHERE (tms.tenshu_cd = mt.small_cd) 
   AND (tms.view <> 0) 
   AND (tms.type in (2,3)) 
   AND (tms.delete_datetime is null)  
  ORDER BY kyori2 ASC 

and I can't find any reference to the tms.* listed in the very beginning. The query seems to run if I remove it so I can't figure out exactly why it's there. Further, later in the query in the FROM line, there is mtb_tensyu mt which I can't get what the mt is. (For the record, 'kyori' in Japanese means 'distance').

I've Googled this and looked thoroughly at the PostgreSQL documents, but I can't find what these are referencing. Any help would be greatly appreciated.

  • I'm sorry - I didn't know what it meant and I thought I could turn to this site for educating myself. After several Google searches I never found references to aliases. I am really grateful to Craig Ringer for helping me, though. Didn't mean to offend anyone. – Tensigh Jul 22 '15 at 5:57
  • I edited the question because (ironically) I was warned deleting it would deprive someone else of the knowledge from an answer. Hopefully this will put me back in good standing. Sorry for the mix-up. – Tensigh Jul 22 '15 at 6:03
  • For future reference: the relevant chapter in the manual is in "Chapter 7 - Queries". In there it's in the section "Select lists/Column labels": postgresql.org/docs/current/static/… – a_horse_with_no_name Jul 22 '15 at 6:11

They are table aliases.

See, later in the query:

FROM ttb_members tms

this is an alias, declaring that you intend to reference the table ttb_members as tms in the query. Aliases are useful for abbreviation and for when you want to reference one table multiple times in a query.

| improve this answer | |
  • Thank you. I figured it was something like that but I didn't find it in the docs. My bad. – Tensigh Jul 21 '15 at 5:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.