Often in PostgreSQL you may want to get the file extension from a string column that contains a filename.
You can use the following syntax to do so:
SELECT *,
substring(filename FROM '\.([^\.]*)$') AS extension
FROM my_files;
This particular example will extract the file extension from each filename in the filename column and save the result to a new column named extension.
For example, if the filename column contains the string my_file.csv then the extension column would contain the string .csv since this represents the file extension.
The following example shows how to use this syntax in practice.
Example: How to Get File Extension from Filename in PostgreSQL
Suppose that we create the following table named my_files that contains information about various files:
-- create table named 'athletes'
CREATE TABLE my_files (
id INTEGER,
filename TEXT
);
-- add rows to the table
INSERT INTO my_files VALUES (1, 'new_roster.csv');
INSERT INTO my_files VALUES (2, 'new_roster.xlsx');
INSERT INTO my_files VALUES (3, 'updated_standings.csv');
INSERT INTO my_files VALUES (4, 'coaches.txt');
INSERT INTO my_files VALUES (5, 'leagues.csv');
INSERT INTO my_files VALUES (6, 'managers.txt');
-- view all rows in table
SELECT * FROM my_files;
id | filename
----+-----------------------
1 | new_roster.csv
2 | new_roster.xlsx
3 | updated_standings.csv
4 | coaches.txt
5 | leagues.csv
6 | managers.txt
Suppose that we would like to extract the file extension from each string in the filename column.
We can use the following syntax to do so:
SELECT *,
substring(filename FROM '\.([^\.]*)$') AS extension
FROM my_files;
id | filename | extension
----+-----------------------+-----------
1 | new_roster.csv | csv
2 | new_roster.xlsx | xlsx
3 | updated_standings.csv | csv
4 | coaches.txt | txt
5 | leagues.csv | csv
6 | managers.txt | txt
Notice that the resulting table contains a new column named extension that contains the file extension for each corresponding string in the filename column.
Feel free to use similar syntax to extract the file extension from filenames in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Remove Substring from a String
PostgreSQL: How to Check if String Contains Substring
PostgreSQL: How to Extract Substring Between Two Characters
