Avoid repeated columns from a select statement

My database contains a table person with these fields username, name, online, visible, ...

person

username, name, online, visible, ...

I have the following query to fetch the person

SELECT *,
CASE
WHEN online = 0 THEN 0
WHEN visible = 0 THEN 0
ELSE online
END AS online
FROM person
where id = SOMEID


I am trying to move the online/offline with respect to user-set visibility logic right into the query. Basically show online status depending on visibility status (If the user has chosen to be invisible, show offline even if the user is online).

This kind of works but with an issue. I get two repeated columns in the result of this query.

username | name | online | visible | ... | online
x | x | x | x | ... | x


For reasons not in my control I am stuck with using SELECT * instead of manually typing in the columns as SELECT username, name, visible, .... Is there a way to exclude online field from SELECT *? Since it will be created by the CASE statement later.

SELECT *

SELECT username, name, visible, ...

online

SELECT *

2 Answers
2

Unfortunately, * means all the columns, there is no way to exclude a particular column from select *. May be you can name your column something else like "online_status" and use "online_status" in your code instead of the column "online".

Such a feature exists in neither Postgres nor the SQL. I think this is a quite interesting question so I googled a little bit and came across an interesting article on postgresonline.com.

They show an approach that selects the columns directly from the schema:

SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
FROM information_schema.columns As c
WHERE table_name = 'officepark'
AND c.column_name NOT IN('officeparkid', 'contractor')
), ',') || ' FROM officepark As o' As sqlstmt


You could create a function that does something like that. Such topics were also discussed on the mailing lists, but the overall consensus was pretty much the same: query the schema.

I'm sure that there are other solutions but I think they will all involve some kind of magic schema-queriying-foo.

BTW: Be carefull with SELECT * ... as this can have performance penalties

SELECT * ...

Such a feature exists in neither Postgres nor the SQL.

SELECT * EXCEPT ...

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.