Menu
Using PostgreSQL 9.3. Have a column with file name values like that:
qwerty.swf
some.image.jpg
some.other.image.jpg
This column also allow null values.
How to get file extensions from this column in sql query?
864
A useful extension to PostgreSQL typically includes multiple SQL objects; for example, a new data type will require new functions, new operators, and probably new index operator classes. It is helpful to collect all these objects into a single package to simplify database management. PostgreSQL calls such a package an extension.
When pg_dump is used, the CREATE EXTENSION command will be included in the dump, followed by the set of GRANT and REVOKE statements necessary to set the privileges on the objects to what they were at the time the dump was taken. PostgreSQL does not currently support extension scripts issuing CREATE POLICY or SECURITY LABEL statements.
The .split (".").pop () will return the last piece from the split file name. Hence it is a smart method to get file extension from a given filename. For extracting the filenames, we employ string operations as we are dealing with the file name string.
To define an extension, you need at least a script file that contains the SQL commands to create the extension's objects, and a control file that specifies a few basic properties of the extension itself. If the extension includes C code, there will typically also be a shared library file into which the C code has been built.
try :
with mytable as (
select unnest(string_to_array($$qwerty.swf
some.image.jpg
some.other.image.jpg
test
$$,E'\n')) filename)
select filename,substring(filename from '\.([^\.]*)$')
from mytable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
