How to run multiple postgresql statements without losing alias

Question

I have around 10 distinct queries. Using UNION and UNION ALL cause loss of information, i.e. the aliases disappear

Q1. Is there any way to retain aliases while trying to run multiple individual queries as a single query using UNION?

Q2. I intend to export to a CSV file and import into excel. Is it possible to generate the query results in a specific format?

SELECT 'dummy', count(*)  as Total_Active_P
FROM prop  
WHERE lifecycle_step = 2
UNION ALL
SELECT 'dummy', SUM(activities.compensation) as Daily_Exp
FROM activities 
where DATE(due_at) = current_date - INTERVAL '1 day'
UNION ALL
select type, sum(compensation) as All_Activities_Comp
from activities 
where DATE(due_at) = current_date - INTERVAL '1 day' 
group by type
UNION ALL
select activities.type, sum(activity_fees.amount) as All_Activity_Fees
from activity_fees inner join activities 
on activity_fees.activity_id = activities.id 
where DATE(activities.due_at) = current_date - INTERVAL '1 day' 
group by activities.type
UNION ALL
SELECT 'dummy', avg(activities.rating) as Avg_Vendor_Rating
FROM fellows 
inner join authentications on authentications.authenticatable_id = fellows.id 
inner join activities on activities.fellow_id = fellows.id  
WHERE fellows.type in ('X', 'Y', 'Z') 
and authentications.deactivated = false 
and DATE(activities.due_at) = current_date - INTERVAL '1 day' 
and activities.rating is not null
UNION ALL
SELECT 'dummy', COUNT(*) as Total_Active_Vendors
FROM fellows inner join authentications 
on authentications.authenticatable_id = fellows.id  
WHERE fellows.type in ('X', 'Y', 'Z') 
and authentications.deactivated = false
UNION ALL
select 'dummy', (sum(total_cost / DATE_PART('day', checkout - checkin)) / count(*)) as Rev_Booked_Per_Prop
from reservations 
where managed_by_owner = false 
and canceled = false 
and checkin <= current_date - INTERVAL '1 day' 
and checkout >= current_timestamp - INTERVAL '1 day'
UNION ALL
select 'dummy', sum(total_cost / DATE_PART('day', checkout - checkin)) as Daily_Reservation_Rev
from reservations 
where managed_by_owner = false 
and canceled = false 
and checkin <= current_date - INTERVAL '1 day' 
and checkout >= current_timestamp - INTERVAL '1 day'
UNION ALL
SELECT 'dummy', count(activities) as Daily_Activities
FROM activities 
where status = 3 
and DATE(due_at) = current_date - INTERVAL '1 day'
UNION ALL
SELECT 'dummy', SUM(amount) AS Daily_Fee_Income
FROM reservations 
INNER JOIN activities ON activities.reservation_id = reservations.id 
INNER JOIN activity_fees ON activity_fees.activity_id = activities.id 
WHERE (checkin <= current_timestamp - INTERVAL '1 day' AND checkout >= current_timestamp - INTERVAL '1 day')
UNION ALL
SELECT 'dummy', COUNT(*) as Total_Users FROM users
UNION ALL
SELECT 'dummy', avg(activities.compensation) as Avg_Cost_Per_Activity
FROM activities 
WHERE (activities.status = 3) 
AND (DATE(due_at) = (current_date - interval '1 day'))
UNION ALL
SELECT 'dummy', COUNT(*) as Daily_Booking
FROM reservations 
where (checkin <= current_date + INTERVAL '-1 day' and checkout >= current_date + INTERVAL '-1 DAY')
UNION ALL
SELECT 'dummy', COUNT(DISTINCT users.id) as Total_Active_Users
FROM users INNER JOIN properties ON properties.user_id = users.id 
INNER JOIN postal_addresses ON postal_addresses.postally_addressable_id = properties.id 
AND postal_addresses.postally_addressable_type = 'Property' 
and properties.calendar_availability > 0
WHERE (postal_addresses.service_area_id is not null)

Thank you.

Answer 1

If you want a single table as result of all your unions, you could put the information given by the alias as string in the first field, changing each query in way similar to this:

SELECT 'Total_Active_P' as Info , count(*)  as Value
FROM prop  
WHERE lifecycle_step = 2
   UNION ALL
SELECT 'Daily_Exp' as Info, SUM(activities.compensation) as Value
FROM activities 
where DATE(due_at) = current_date - INTERVAL '1 day'
   UNION ALL
...

In this way you will have a single big table, with two columns, the first column, labelled Info, giving the information necessary to understand the value in the second column, labelled Value.

Answer 2

On your first question, after union, the column names of the unioned tables will be lost. If you did not set alias for column names for the result, the column names of the first table will be used. If you require to distinguish which row came from which table, I suggest you to add another column, i.e. (1 for the first table, 2 for the second table,...) However imho you may also consider to get this results separately.

On your second question, on pgadmin query window there is a button (the one with green play triangle and save icon) which saves the result to file. And it gives you options to select which character to use separating columns, etc.