I am attempting to retrieve either a null or boolean value from a query. Here is my query: SELECT EXISTS (SELECT 1 FROM employee where add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6') AS a, CASE WHEN a = false then null ELSE (SELECT exists (SELECT 1 FROM employee where add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6' and is_active = true ) ) END Results in: > SQL Error : ERROR: column "a" does not exist Despite experimenting with multiple options, I am still unable to achieve the desired outcome.

Given a table such as this, I want to create a table via SQL without doing window functions |id | time| attribute1| attribute1atFirstRecordedTimeperID| |--|--|--|--| | 1 | 0 | a | a| | 1 | 2 | b | a| | 2| 0 | c | c| | 2| 4 | d | c| I tried SELECT t1.id, t1.time, t1.attribute1, attribute1fixedattime0 = t2.attribute1 FROM s t1 INNER JOIN ( SELECT attribute1 AS attribute1fixedattime0 FROM s WHERE time = 0 ) t2 ON t1.id = t2.id Is the column alias correct? That throws an error If I fix this code, I want to make the SQL code work for the case when id=2 does not have their first attribute1 recorded at time t=0, but for t>0. Then do I do a min on the join condition or inside the JOIN? I want to create a table via SQL without doing window functions to turn into To go from | id | time| attribute1| |--|--|--| | 1 | 0 | a | | 1 | 2 | b | | 2| 42 | c | | 2| 69 | d | to the table | id | time| attribute1| attribute1atFirstRecordedTimeperID| |--|--|--|--| | 1 | 0 | a | a| | 1 | 2 | b | a| | 2| 42 | c | c| | 2| 69 | d | c| if you use a row_number() window function to mark the spot at which first time is within every id, then how do you join or would you not use a join but something else? SELECT id, time, attribute1, ROW_NUMBER() OVER (PARTITION BY id ORDER BY time) AS rn FROM your_table SELECT id, time, attribute1, FIRST_VALUE(attribute1) OVER (PARTITION BY id ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS attribute1atFirstRecordedTimeperID FROM ( -- Previous query here ) AS subquery

In PostgreSQL anyway you can pass a table (row) alias into a function and it will pass the entire record in. But when you have a table alias x and also a column name x, when you specify f(x) it will not know which one, throw error "ambiguous name x" or pass the wrong one. Obviously it is easy to disambiguate for the column name: just precede with it's table alias: t.x. But what if I want to disambiguate for the table alias? Simple example: select f(x) from (select 1 x) x Obviously I can avoid having those clashes by renaming either the table alias or the column, but there should be a way to say "!x" or "table x" or whatever to force a reference to the table alias (or row variable).

select CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v.trip_order_start_date end + (interval '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago' + (interval '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') to_char((now() at time zone 'utc' at time zone 'America/Chicago' - interval '1' month), 'YYYYMM'))) THEN 'YES' ELSE 'NO' END AS accrual WHERE accrual = 'YES' The problem is there is no table for me to reference the "accrual" column so I get the below error [Code: 0, SQL State: 42703] ERROR: column "accrual" does not exist Position: 10708 [Script position: 10708 - 10715]

I am trying to calculate distance between two coordinates and fetching some information based on a few conditions. A similar query with the 'having' clause was working in MySQL. But why is it not working in PostgreSQL? This is my query:

SELECT *, ( 6371 * acos( cos( radians(latitude) ) * cos( radians( latitude1 ) ) *
    cos( radians( longitude1 ) - radians(longitude) ) + sin( radians(latitude) ) *
    sin( radians( latitude1 ) ) ) ) AS distance from table WHERE
    verified=true AND best_for LIKE '%xyz%' AND uuid NOT IN (SELECT uuid::uuid FROM table2 WHERE
    from_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date AND
    to_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date  AND )
    HAVING distance < 50 ORDER BY distance

Why does distance not exist? And how can I modify this query to make it work in PostgreSQL?