Accessing Column Alias in Postgresql

Accessing column alias in postgresql

In general, you can't refer to an aggregate column's alias later in the query, and you have to repeat the aggregate

If you really want to use its name, you could wrap your query as a subquery

SELECT * 
FROM
(
    SELECT DISTINCT robber.robberid, nickname, count(accomplices.robberid)  
    AS count1 FROM robber                   
    INNER JOIN accomplices  
    ON accomplices.robberid = robber.robberid  
    GROUP BY robber.robberid, robber.nickname  
) v
ORDER BY count1 desc

access a column aliases in the where clause in postgresql

Here is how you did it - alias in the column name

select 
  -- etc etc 
  (select innerDLI.datetime_created from distribution_line_items innerDLI where innerDLI.item_number = distribution_line_items.item_number order by innerDLI.datetime_created asc limit 1) as Origination,
  -- etc etc
from distribution_stop_information
  -- etc etc

Here is how you can put it in the where, alias in the join

select 
  -- etc etc 
  Origination.datetime_created
  -- etc etc
from distribution_stop_information 
left join distribution_line_items AS Origination ON Origination.item_number = distribution_line_items.item_number
where Origination.datetime_created > to_date(?, 'YYYY-MM-DD') - interval '180 days'  
-- etc etc

There is nothing about this that is better than the solution you posted (the sql optimizer should result in the same plan) but it is "using an alias in the where clause"

Using an Alias column in the where clause in Postgresql

MySQL's support is, as you experienced, non-standard. The correct way is to reprint the same expression used in the SELECT clause:

SELECT
    jobs.*, 
    CASE 
         WHEN lead_informations.state IS NOT NULL THEN lead_informations.state 
         ELSE 'NEW' 
    END AS lead_state
FROM
    jobs
    LEFT JOIN lead_informations ON
        lead_informations.job_id = jobs.id
        AND
        lead_informations.mechanic_id = 3
WHERE
    lead_informations.state IS NULL

Accessing columns inside a record using table alias in Postgresql

Fortunately the answer here is relatively simple. You have to use parentheses to indicate tuples:

IF (inv_row.s).processor <> (inv_row.d).processor THEN

This is because SQL specifies meaning to the depth of the namespaces and therefore without this PostgreSQL cannot safely determine what this means. So inv_row.s.processor means the processor column of the s table in the inv_row schema. However (inv_row.s).processor means take the s column of the inf_row table, treat it as a tuple, and take the processor column of that.

Column aliases from a function in PostgreSQL

Please use the following example:
RETURNS TABLE (dstring character varying, ...)
select * from your function_name;

You will see column names dstring,... as it specified in function declaration. You don't need to use alias: just name it as you wish.

Get column aliases from sql query

I used a regular expression to match all columns/aliases of a SQL string. I think I covered the majority of scenarios, but I may have missed some.

(?:              # Start non-capturing group
  select         # Match "select" literally
 |               # OR
  \G             # Match the end of the previous match (our last column)
)                # End non-capturing group
\s+              # Catch extra whitespace
\K               # Remove everything to the left from the match
(?:              # Start non-capturing group
  (?:            # Start non-capturing group
    .*?          # Lazily match column (this can be modified to your needs)
    \s+as\s+     # Match " as " literally
    ([^,]+)      # Capture the alias (anything but a comma)
    ,?           # Optional comma
  )              # End non-capturing group
 |               # OR
  ([^,]+)        # Capture the column (anything but a comma)
  ,?             # Optional comma
)                # End non-capturing group
(?=              # Start lookahead
  .*?            # Lazily match characters (whitespace)
  \bfrom\b       # Up to "from" literally (with word delimiters)
)                # End lookahead

This will capture aliases in the first group and columns in the second group, the whole string will be matched. It will likely have problems if you start having crazy column aliases, but seems to match anything basic to intermediate. Note I used the modifiers gsi for global, dot-matches-newline, and case-insenstive.

Regex101

---

Related Topics