Postgresql – Select all columns from table but apply function to 1 column

postgresql

What I want to do: Select all columns but apply a function to one of the selected columns in a shorthand manner such that I don't have to type out all of the column names

Current approach: I am currently using select table.* to select all columns in a table.

Example table

Name data1 data2 data3 ....
a    1     1     1
b    1     1     1
c    1     1     1

Code:

SELECT * from table

Desired outcome: I want to be able to select all columns then apply a function to a specific column, is it possible to do this with select * or does it require selecting all column names and applying a function to my specified column?

Best Answer

Select all columns ... such that I don't have to type out all of the column names

But that is exactly what you should be doing!

Never use select * in production code.

Why? Two reasons.

The database has to work harder, first to work out which columns exist, construct the query and then actually retrieve the data for you. OK, the amount of extra work is small but involves core, data dictionary tables which can result in performance bottlenecks.
Databases are inherently shared environments. Today, your query might be perfectly happy, pulling back all three of the columns in a given table. Life is Good. Tomorrow, someone else might add a hundred BLOB fields into that table and suddenly your query's performance goes through the floor, pulling back all that data in which your application has absolutely no interest.

Always specify the columns that your query requires explicitly.

Also, are those really your column names? If so, then your table design worries me greatly.

Database != Spreadsheet.

Basic Data Normalisation rules should extract those repeated attributes out into a another [sub-]table, rather than running them "along" a row.

Related Solutions

Postgresql – SQL query to sum a column prior to date and show all entries after that date

WITH parametros AS (
   SELECT '2015-03-06'::timestamp AS fecha_desde  -- provide parameters here
        , '2015-03-12'::timestamp AS fecha_hasta
   )
SELECT tropa, max(fecha) AS fecha, sum(cantidad) AS sum_cantidad
     , sum(sum(cantidad)) OVER (PARTITION BY tropa
                                ORDER BY max(fecha)) AS saldo
FROM   movimiento_stock, parametros p
WHERE  fecha <= p.fecha_hasta
GROUP  BY tropa, CASE WHEN fecha >= p.fecha_desde THEN id END
ORDER  BY 1, 2;

The core feature is bold. Read the manual about CASE.
Older rows get NULL for id (the default in a CASE expression); id is NOT NULL in the underlying table, so collisions are not possible.

This groups all rows older than fecha_desde into one group per tropa and leaves newer rows ungrouped (form individual "groups"). It's unclear how to aggregate other columns, so I only included fecha.

The same, wrapped into an SQL function:

CREATE OR REPLACE FUNCTION informe_tldr(fecha_desde timestamp
                                      , fecha_hasta timestamp)
  RETURNS TABLE(tropa int, fecha timestamp, sum_cantidad int, saldo int) AS
$func$
SELECT m.tropa, max(m.fecha), sum(m.cantidad)::int
     , sum(sum(m.cantidad)) OVER (PARTITION BY m.tropa
                                  ORDER BY max(m.fecha))::int AS saldo
FROM   movimiento_stock m
WHERE  m.fecha <= fecha_hasta
GROUP  BY m.tropa, CASE WHEN fecha >= fecha_desde THEN m.id END
ORDER  BY 1, 2;
$func$ LANGUAGE sql;

SQL Fiddle.

Postgresql – Why does VALUES provide a different DEFAULT column name than SELECT

The general approach of the SQL standard is that many things not imposed by chosen semantics are left to the vendor to optimize or specify. Under the chosen policy users choose names when they care what they are.

SELECT without FROM isn't standard SQL either. MySQL chooses to use the expression that evaluated to a column's value as its name. Those PostgreSQL "?column?"s aren't the column names, they are strings output instead of the column names. If the system defaults to internal unique names then clashes are avoided compared to defaulting to patterned names. In your example you could have selected "column1" but with your expected behaviour you'd need "t2.column1".

Ultimately, they just chose a certain design for the language. And even where people leave a rationale, engineering and ergonomics are about pragmatic timely tradeoffs where one decision cannot necessarily be justified as "best".

Best Answer

Related Solutions

Postgresql – SQL query to sum a column prior to date and show all entries after that date

Postgresql – Why does VALUES provide a different DEFAULT column name than SELECT

Related Question