Select Only the Columns You Need

SELECT * is the first thing everyone writes and the first thing worth unlearning. It returns every column, including the ones you do not need, and its result changes whenever someone changes the table. This page shows what goes wrong and what to write instead.

Step 1: see what SELECT * returns

You need a small table to work with. Paste this.

CREATE TABLE customers (
  customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  email       text NOT NULL,
  city        text
);
INSERT INTO customers (name, email, city) VALUES
  ('Ana Reyes', 'ana@example.com', 'Manila'),
  ('Ben Cruz',  'ben@example.com', 'Cebu');

CREATE TABLE
INSERT 0 2

SELECT * FROM customers;

 customer_id |   name    |      email      |  city
-------------+-----------+-----------------+--------
           1 | Ana Reyes | ana@example.com | Manila
           2 | Ben Cruz  | ben@example.com | Cebu
(2 rows)

You wanted the names for a dropdown. You got an id, an email and a city as well, and sent all of it to whatever reads the result. On a table with a large text column, that is a lot of data nobody asked for.

Step 2: name the columns

SELECT name FROM customers ORDER BY name;

   name
-----------
 Ana Reyes
 Ben Cruz
(2 rows)

Now the query says what it needs. A reader can see that the email and the city are not used here, and the database does not have to read or send them.

The second reason is about change. Add a column and watch what happens to a query that uses *.

ALTER TABLE customers ADD COLUMN notes text;
SELECT * FROM customers;
SELECT name, city FROM customers;

ALTER TABLE
 customer_id |   name    |      email      |  city  | notes
-------------+-----------+-----------------+--------+-------
           1 | Ana Reyes | ana@example.com | Manila |
           2 | Ben Cruz  | ben@example.com | Cebu   |
(2 rows)

   name    |  city
-----------+--------
 Ana Reyes | Manila
 Ben Cruz  | Cebu
(2 rows)

The first result gained a notes column that nothing planned for. A program that reads columns by position, or a report with a fixed layout, breaks or shifts. The named query did not change at all.

Step 3: the exceptions

SELECT * is fine in a few places.

• Looking at a table while you explore. Type it, read it, move on. Do not save it.
• Counting rows. In count(*) the star means "rows", not columns, and nothing is read from them.
• Inside EXISTS. In EXISTS (SELECT * ...) the list is ignored, so the star costs nothing.

SELECT count(*) AS customers FROM customers;

 customers
-----------
         2
(1 row)

Rule of thumb

Write SELECT * at the prompt, never in code you keep: not in an application, a view or a saved report. A query you save will outlive the table design it was written against.

Related course: SQL for Beginners.