Often in PostgreSQL you may want to select columns based on their position.
You can use the following syntax to do so:
SELECT col2, col4 FROM athletes AS t(col1, col2, col3, col4);
This particular example will select columns in position 2 and 4 from the athletes table.
By creating an alias for the actual column names, we are able to simply select the columns by the alias names based on their position.
The following example shows how to use this syntax in practice.
Example: How to Select Columns by Position in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER PRIMARY KEY,
team TEXT,
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Hawks', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Hawks', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
id | team | points | assists
----+---------+--------+---------
1 | Mavs | 22 | 5
2 | Hawks | 30 | 12
3 | Kings | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Hawks | 10 | 5
8 | Celtics | 14 | 13
9 | Hawks | 20 | 8
10 | Hawks | 26 | 3
Suppose that we would like to only select the columns in positions 2 and 4 of the table.
We can use the following syntax to do so:
SELECT col2, col4 FROM athletes AS t(col1, col2, col3, col4);
col2 | col4
---------+------
Mavs | 5
Hawks | 12
Kings | 3
Kings | 9
Mavs | 14
Mavs | 3
Hawks | 5
Celtics | 13
Hawks | 8
Hawks | 3
This returns the values from the columns in positions 2 and 4 from the original table, which correspond to the team and assists columns.
You can use similar syntax to select columns based on position in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Columns Based on Condition
PostgreSQL: How to Count Occurrences of Each Value in Column
PostgreSQL: How to Count Number of Occurrences of Character in String
