Select * Except

How to select all the columns of a table except one column?

You can use this approach to get the data from all the columns except one:-

1. Insert all the data into a temporary table
2. Then drop the column which you dont want from the temporary table
3. Fetch the data from the temporary table(This will not contain the data of the removed column)
4. Drop the temporary table

Something like this:

SELECT * INTO #TemporaryTable FROM YourTableName

ALTER TABLE #TemporaryTable DROP COLUMN Columnwhichyouwanttoremove

SELECT * FROM #TemporaryTable 

DROP TABLE #TemporaryTable 

Select all except some rows

... WHERE NOT (model = 'Ford' AND color = 'yellow')

Select with except in postgresql

Use a VALUES list.

select * from (values (1),(2),(100000001)) as f (aid)
    except 
select aid from pgbench_accounts

How to Select all columns of a table except one

Create the table with all the columns and after that drop the geom column and rename the new one:

CREATE TABLE tableC AS
SELECT
    tableA.*,
    ST_Intersection (B.geom, A.geom) as geom2  -- generate geom
FROM
    tableA inner JOIN tableB ON ST_Intersects (A.geom, b.geom)
WHERE test.id = 2
;

alter table tableC drop column geom;
alter table tableC rename column geom2 to geom;

Dynamically exclude some fields from select using EXCEPT

I know, you most likely expect something more sexy, but I feel this is the only option so far

DECLARE hide_city  bool DEFAULT TRUE;

IF hide_city THEN
  SELECT * EXCEPT(city) FROM table;
ELSE 
  SELECT * FROM table;
END IF;

how to combine this with a CTE that goes before?

This can be something like below - so you "transform" your CTE into TEMP table

DECLARE hide_city  bool DEFAULT TRUE;

CREATE TEMP TABLE myTable AS 
WITH myCTE AS (
  SELECT ... UNION ALL
  SELECT ... UNION ALL
  ...
  SELECT ...
)
SELECT * FROM myCTE;

IF hide_city THEN
  SELECT * EXCEPT(city) FROM myTable;
ELSE 
  SELECT * FROM myTable;
END IF;    

Obviously you don't need CTE at all and rather can do

CREATE TEMP TABLE myTable AS 
SELECT ... UNION ALL
SELECT ... UNION ALL
...
SELECT ...;

---

Related Topics