Getting to Know PostgreSQL and Its Primitives

PostgreSQL is a robust, open-source relational database system. It uses and extends SQL language combined with many features that safely store and scale complicated data workloads, making it popular in various industries like finance and AI.

One of PostgreSQL's primary constructs is 'Tables.' Tables in PostgreSQL, similar to other Relational Database Management Systems (RDBMS), are a collection of related data held in a structured format within a database. They consist of columns and rows, similar to Python's lists and dictionaries. Data can be accessed, managed, and manipulated using SQL commands.

PostgreSQL also has a concept of 'Views.' They are pseudo-tables, meaning that they are not actual tables. Instead, they are essentially the result-set of a SELECT statement. A view can contain all rows of a table or specific rows based on specific conditions.

Let's take an example of Python code to understand how we can interact with PostgreSQL. We are going to connect to a PostgreSQL database, execute a SQL SELECT query, and fetch the results. (Please replace the host, port, database, user, and password parameters if you want to execute the code on your local system).

In the next lesson, we are going to construct a primitive version of PostgreSQL as part of the course 'Build Datastores From Scratch.' We will wrap basic data structures with utilities and continue to expand until we've achieved 'feature parity'. Thus, this knowledge of PostgreSQL and its primitives forms a crucial foundation for the coming lessons.

xxxxxxxxxx

if __name__ == "__main__":

    # PostgreSQL example using Python

    import psycopg2

    try:

        connection = psycopg2.connect(user="sysadmin",

                                      password="pynative@#29",

                                      host="127.0.0.1",

                                      port="5432",

                                      database="postgres_db")

        cursor = connection.cursor()

        postgreSQL_select_Query = "select * from mobile"

        cursor.execute(postgreSQL_select_Query)

        print("Selecting rows from mobile table using cursor.fetchall")

        mobile_records = cursor.fetchall() 

        print("Print each row and it's columns values")

        for row in mobile_records:

            print("Id = ", row[0], )

    except (Exception, psycopg2.Error) as error :

        print ("Error while fetching data from PostgreSQL", error)

    finally:

        #closing database connection.

        if(connection):

            cursor.close()

            connection.close()

            print("PostgreSQL connection is closed")