Logo ← PostgreSQL Blog

PostgreSQL Meets Python

PostgreSQL is a highly extensible and robust relational database system that supports multiple procedural languages to enable advanced…

PostgreSQL Meets Python

PostgreSQL is a highly extensible and robust relational database system that supports multiple procedural languages to enable advanced functionality. Among these, PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python stand out, allowing developers to implement procedural logic directly within the database. While PL/pgSQL is PostgreSQL’s native procedural language, many developers prefer PL/Python due to Python’s extensive libraries, ease of use, and versatility. With PL/Python, you can perform complex computations, manipulate data efficiently, and even integrate machine learning models directly into your database workflows. In this post, we will focus entirely on PL/Python and demonstrate various examples using it, showcasing how Python can enhance database capabilities.

Installing PL/Python3U

To use Python inside PostgreSQL, install the necessary package:

dnf install postgresql14-plpython3 -y

Then enable it in your database:

CREATE LANGUAGE plpython3u;

Example 1: Converting a String to Lowercase

CREATE FUNCTION py_lowercase(x text)
RETURNS text
AS $$
return x.lower()
$$ LANGUAGE plpython3u;

Call it with:

postgres=# SELECT py_lowercase('Kemal OZ');
 py_lowercase
--------------
 kemal oz
(1 row)

postgres=#

Example 2: Reversing a String

CREATE FUNCTION py_reverse_string(x text)
RETURNS text
AS $$
return x[::-1]
$$ LANGUAGE plpython3u;

Call it with:

postgres=# SELECT py_reverse_string('kemal');
 py_reverse_string
-------------------
 lamek
(1 row)

postgres=#

Example 3: Checking If a Number is Even

CREATE FUNCTION py_is_even(x int)
RETURNS boolean
AS $$
return x % 2 == 0
$$ LANGUAGE plpython3u;

Call it with:

postgres=# SELECT py_is_even(10);
 py_is_even
------------
 t
(1 row)

postgres=# SELECT py_is_even(9);
 py_is_even
------------
 f
(1 row)

postgres=#

Example 4: Generating a Sequence of Numbers

CREATE FUNCTION py_generate_sequence(n int)
RETURNS int[]
AS $$
return list(range(1, n+1))
$$ LANGUAGE plpython3u;

Call it with:

postgres=# SELECT py_generate_sequence(5);
 py_generate_sequence
----------------------
 {1,2,3,4,5}
(1 row)

postgres=# SELECT py_generate_sequence(3);
 py_generate_sequence
----------------------
 {1,2,3}
(1 row)

postgres=#

Example 5: Getting the Length of a Text

CREATE FUNCTION py_text_length(x text)
RETURNS int
AS $$
return len(x)
$$ LANGUAGE plpython3u;

Call it with:

postgres=# SELECT py_text_length('Fernerbahce');
 py_text_length
----------------
             11
(1 row)

postgres=# SELECT py_text_length('Kemal');
 py_text_length
----------------
              5
(1 row)

postgres=#

Example 6: Inserting a User into the Database

First, create a users table:

CREATE TABLE users(
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    fullname VARCHAR(100) NOT NULL
);

Now, create a function to insert users:

CREATE FUNCTION add_user(username text, fullname text)
RETURNS text
AS $$
try:
    plpy.execute(f"INSERT INTO users(username, fullname) VALUES ('{username}', '{fullname}')")
    return "User added successfully"
except plpy.SPIError:
    return "Error adding user"
$$ LANGUAGE plpython3u;

Call it with:

postgres=# SELECT add_user('kemal', 'Kemal Oz');
        add_user
-------------------------
 User added successfully
(1 row)

Example 7: Fetching Users from the Database

CREATE FUNCTION get_users()
RETURNS TABLE(id int, username text, fullname text)
AS $$
return plpy.execute("SELECT * FROM users")
$$ LANGUAGE plpython3u;

Call it with:

postgres=# SELECT * FROM get_users();
 id | username | fullname
----+----------+----------
  1 | kemal    | Kemal Oz
(1 row)

postgres=#

Example 8: Calculating Factorial

CREATE FUNCTION py_factorial(n int)
RETURNS int
AS $$
import math
return math.factorial(n)
$$ LANGUAGE plpython3u;

Call it with:

postgres=# SELECT py_factorial(5);
 py_factorial
--------------
          120
(1 row)

postgres=# SELECT py_factorial(4);
 py_factorial
--------------
           24
(1 row)

postgres=#

Example 9: Getting the Current Timestamp

CREATE FUNCTION py_current_timestamp()
RETURNS timestamp
AS $$
import datetime
return datetime.datetime.now()
$$ LANGUAGE plpython3u;

Call it with:

postgres=# SELECT py_current_timestamp();
    py_current_timestamp
----------------------------
 2025-03-17 11:17:49.169871
(1 row)

postgres=#

Example 10: Checking If a String Contains Another String

CREATE FUNCTION py_contains(text text, substr text)
RETURNS boolean
AS $$
return substr in text
$$ LANGUAGE plpython3u;

Call it with:

postgres=# SELECT py_contains('Kemal oz', 'oz');
 py_contains
-------------
 t
(1 row)

postgres=#

Conclusion

PL/Python allows developers to leverage Python’s power inside PostgreSQL. These simple examples demonstrate how to manipulate text, perform calculations, interact with the database, and work with timestamps. Additionally, with PL/Python, you can create triggers, execute dynamic queries, and even integrate external APIs directly from your database functions. Whether you need to automate data transformations, implement business logic, or perform advanced analytics, Python inside PostgreSQL opens up endless possibilities. If you have more use cases, feel free to share them in the comments