ChatGPT-4 vs Bard: What Are the Differences for PostgreSQL Developers?

In the fast-paced world of artificial intelligence (AI) advancements, developers are looking for the most efficient and groundbreaking solutions to expedite and improve their work quality. For PostgreSQL developers, it is essential to select the ideal AI-powered tool that addresses their queries with utmost professionalism.

The popularity of AI tools has soared in recent years, with developers increasingly recognizing their potential in streamlining various aspects of their work. Some of the most renowned AI tools include OpenAI’s ChatGPT, Google’s Bard, IBM’s Watson, and Microsoft’s Azure Cognitive Services, among others. These tools have revolutionized the way developers approach problem-solving and have made previously time-consuming tasks more manageable. In this article, we will focus on comparing the responses of ChatGPT-4 and Google Bard to a range of common SQL development-related questions. By doing so, we aim to get a clear understanding of each tool’s capabilities and help you determine which one is better suited to your specific needs in the realm of SQL development.

Contents:

• What is ChatGPT?
• What is Google Bard?
• ChatGPT vs Google Bard
• How to use ChatGPT and Bard for PostgreSQL development
  • Example #1: Function
  • Example #2: Date function
  • Example #3: Window function
  • Example #4: JOIN clause
  • Example #5: Pivot table
• Which AI tool is better?
• Conclusion

What is ChatGPT?

ChatGPT, developed by OpenAI, is a state-of-the-art AI language model based on the GPT (Generative Pre-trained Transformer) architecture. As a large-scale language model, ChatGPT is designed to generate human-like text and engage in conversation with users, understanding the context and providing relevant responses. It is capable of performing various tasks such as answering questions, offering recommendations, creating content, and more.

Developers and businesses can harness the power of ChatGPT by integrating it into their applications, services, or products, enhancing user experience through natural language understanding and generation. ChatGPT is successfully applied in areas like customer support, content creation, virtual assistance, and many other domains where natural language processing is essential.

What is Google Bard?

Google Bard is a large language model, also known as a conversational AI or chatbot trained to be informative and comprehensive. Bard is trained on a massive amount of text data and is able to communicate and generate human-like text in response to a wide range of prompts and questions.

Although still in development, this tool can already assist SQL developers in various ways, including answering questions about SQL syntax and usage, aiding in SQL query debugging, generating SQL code tailored to particular tasks, and offering tutorials and documentation on SQL, among other features.

ChatGPT vs Google Bard

ChatGPT and Google Bard are both large language models, but they have some key differences.

• Data: ChatGPT is trained on a dataset of text and code that was collected up to 2021, while Google Bard is trained on a dataset that is constantly being updated. This means that Google Bard has access to more recent information and can provide more precise answers.
• Accuracy: Google Bard is generally more accurate than ChatGPT, especially when it comes to factual information. This is because Google Bard is trained on a larger and more up-to-date dataset.
• Creativity: ChatGPT is more creative than Google Bard when it comes to generating text formats, such as poems, code, scripts, musical pieces, emails, letters, etc. This is because ChatGPT is trained on a dataset that includes a wider variety of creative text formats.
• Availability: ChatGPT is available to anyone who wants to use it, while Google Bard is currently only available to a limited number of users.

How to use ChatGPT and Bard for PostgreSQL development

AI can play a significant role in PostgreSQL development by providing guidance on syntax and usage, generating SQL code, assisting with query debugging, and more. In this article, we will pose identical questions related to SQL development to both Bard and ChatGPT, and subsequently compare and validate their responses. Our objective is to assess the reliability and usefulness of either AI in the context of PostgreSQL development. To validate the answers provided by AI, we will use one of the best PostgreSQL GUI tools on the market – dbForge Studio for PostgreSQL.

Prerequisites

• Pagila sample database
• dbForge Studio for PostgreSQL
• Access to ChatGPT-4
• Access to Google Bard

Example #1: Function

Suppose, we need to create a function that returns the most rented films during the given period, along with their rental counts. Let us ask ChatGPT-4 to accomplish the task.

ChatGPT-4 generated the following SQL code:

CREATE OR REPLACE FUNCTION most_rented_films(start_date DATE, end_date DATE)
RETURNS TABLE (film_id INTEGER, title TEXT, rental_count INTEGER) AS $$
BEGIN
  RETURN QUERY
    SELECT f.film_id, f.title, COUNT(r.rental_id) AS rental_count
    FROM film AS f
    JOIN inventory AS i ON f.film_id = i.film_id
    JOIN rental AS r ON i.inventory_id = r.inventory_id
    WHERE r.rental_date BETWEEN start_date AND end_date
    GROUP BY f.film_id, f.title
    ORDER BY rental_count DESC;
END;
$$ LANGUAGE plpgsql;

Let us now open dbForge Studio for PostgreSQL and validate the code created by the AI.

As you can see the Studio throws an error because the schema for creating the function is not specified. Let us modify the code a little bit and try again.

The query was successful this time, and the function is now visible in the Database Explorer.

Now, it is time to run the SELECT statement to make sure that everything works as required.

Oops. There is an error again. In the function syntax, the rental_count column datatype has been specified as INTEGER, which is obviously wrong. We need to modify the syntax and try again. Thus, the working code is as follows:

CREATE OR REPLACE FUNCTION public.most_rented_films(start_date DATE, end_date DATE)
RETURNS TABLE (film_id INTEGER, title TEXT, rental_count BIGINT) AS $$
BEGIN
  RETURN QUERY
    SELECT f.film_id, f.title, COUNT(r.rental_id) AS rental_count
    FROM film AS f
    JOIN inventory AS i ON f.film_id = i.film_id
    JOIN rental AS r ON i.inventory_id = r.inventory_id
    WHERE r.rental_date BETWEEN start_date AND end_date
    GROUP BY f.film_id, f.title
    ORDER BY rental_count DESC;
END;
$$ LANGUAGE plpgsql;

So, we drop the function we created earlier, create a new one, and then run SELECT. This time it is successful.

Okay, now let us assess Google Bard.

So, we got the following code:

CREATE OR REPLACE FUNCTION most_rented_films (start_date DATE,
end_date DATE) RETURNS TABLE (
  film_id INT,
  film_title VARCHAR(255),
  rental_count INT
)
AS
$func$
BEGIN

    RETURN QUERY
    SELECT
        f.film_id,
        f.film_title,
        COUNT(*) AS rental_count
    FROM
        film_rentals fr
    JOIN
        films f ON f.film_id = fr.film_id
    WHERE
        fr.rental_date >= start_date
        AND fr.rental_date < end_date
    GROUP BY
        f.film_id,
        f.film_title
    ORDER BY
        rental_count DESC;

END
$func$;

Let us run it in the Studio, shall we? The pressure is on! And we get the same error – the schema has not been specified.

So, we modify the code and run it again. And get another error.

We need to modify the query further. After specifying the language and executing the syntax, we run SELECT. And… error again.

Google Bard refers to the non-existing table in its script; besides that, the datatype of the rental_count column is wrongly specified as INT. After we fix all these issues, we finally get the working code. Not fun, right? With Google Bard, we had to adjust the generated code for quite a while, and that required a certain level of expertise, which means that beginners in SQL might not be able to take advantage of its prompts. However, it is just the first example, let us move on.

Example #2: Date function

Suppose, we want to create a date function that returns the day of the week with the highest film rentals within the specified range. Let us first ask ChatGPT-4 to assist us with this task.

So, ChatGPT-4 has generated the following query for us:

CREATE OR REPLACE FUNCTION highest_rental_day(start_date DATE, end_date DATE)
RETURNS TEXT AS $$
DECLARE
    day_of_week TEXT;
BEGIN
    SELECT TO_CHAR(r.rental_date, 'Day') AS day_name
    INTO day_of_week
    FROM rental r
    WHERE r.rental_date BETWEEN start_date AND end_date
    GROUP BY EXTRACT(DOW FROM r.rental_date)::INTEGER, day_name
    ORDER BY COUNT(*) DESC
    LIMIT 1;

    RETURN day_of_week;
END;
$$ LANGUAGE plpgsql;

As you might remember from Example #1, we need to specify the schema before we run this code in dbForge Studio for PostgreSQL. Otherwise, we will get an error.

The function has been successfully created. Let us check how it is working.

Unbelievable! It is working without any adjustments. Well, almost without.

Let us look at how Google Bard will cope with the task.

Here is the code that we got.

CREATE FUNCTION get_highest_rental_day(
    start_date DATE,
    end_date DATE
) RETURNS VARCHAR(10) AS
$func$
BEGIN
    DECLARE
        -- Get the total number of film rentals for each day of the week.
        rental_counts TABLE (
            day_of_week VARCHAR(10),
            total_rentals INT
        );
    BEGIN
        -- Populate the rental_counts table.
        INSERT INTO rental_counts (day_of_week, total_rentals)
        SELECT
            EXTRACT(DOW FROM rental_date),
            COUNT(*)
        FROM
            film_rentals
        WHERE
            rental_date >= start_date
            AND rental_date <= end_date
        GROUP BY
            EXTRACT(DOW FROM rental_date);
    
        -- Get the day of the week with the highest number of rentals.
        SELECT
            day_of_week
        FROM
            rental_counts
        ORDER BY
            total_rentals DESC
        LIMIT
            1;
    END;
END
$func$ LANGUAGE plpgsql;

We specify the schema and run it in the Studio for PostgreSQL. When trying to execute SELECT from this function, we get an error. There is no TABLE datatype in PostgreSQL.

We asked Google Bard to rewrite the syntax, yet we didn’t get the functional code. As a result, it receives a demerit for this particular task.

Example #3: Window function

A window function in PostgreSQL is a type of function that performs a calculation across a set of rows related to the current row. It allows you to perform advanced calculations that involve comparing the current row with other rows within a specified window or partition. Window functions are useful for tasks such as ranking, cumulative sums, moving averages, and more.

Suppose, we want to calculate the cumulative sum of payments for each customer and get those ordered by payment date. Let us first ask ChatGPT-4 for help.

Here is the syntax we got, in case you would like to check it yourself:

SELECT 
  customer_id,
  payment_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY payment_date) as cumulative_amount
FROM
  payment
ORDER BY
  payment_date;

Now we open dbForge Studio for PostgreSQL and run the query that ChatGPT-4 has generated for us.

Excellent. right? Let us ask the same question to Google Bard.

This is the code we got:

SELECT
  customer_id,
  SUM(payment_amount) AS cumulative_sum_of_payments,
  payment_date
FROM payments
GROUP BY customer_id
ORDER BY payment_date;

However, there is a bunch of problems with it:

1. There is no payments table in the pagila database, it is called payment.
2. The payment_amount column does not exist either. It is called amount.
3. There is no grouping by date.
4. Sorting is done by the wrong column.

Novices may struggle to use that prompt, as numerous modifications are needed for the query to work properly.

Example #4: JOIN clause

JOINs are used to combine data from two or more tables in a relational database based on a related column between them. They allow you to retrieve information from multiple tables in a single query, making it an essential tool for working with relational databases.

Suppose, we want to get a list of all films with the categories they belong to. Let us first ask ChatGPT-4.

Here is the syntax we got:

SELECT 
  f.title,
  c.name as category
FROM 
  film f
JOIN 
  film_category fc ON f.film_id = fc.film_id
JOIN 
  category c ON fc.category_id = c.category_id
ORDER BY
  f.title;

The query looks fine at first glance. Let us validate it.

And it is working as expected. Well done, ChatGPT-4. Let us approach its rival now.

Below is the syntax we got:

SELECT
  film.film_id,
  film.title,
  category.name
FROM film
JOIN film_category ON film.film_id = film_category.film_id
JOIN category ON film_category.category_id = category.category_id;

Let us run the query generated by Google Bard in the Studio.

As you can see, Google Bard has coped with the task too. The queries are pretty much similar, the only difference is that ChatGPT-4 has added the ORDER BY clause for better results analysis and visibility.

Example #5: Pivot table

In PostgreSQL, a pivot table is a data summarization tool that allows you to aggregate and transform data from a database into a more readable format.

Suppose, we want to get a pivot table that shows the total rental amount for each customer by film category. Let us go and ask ChatGPT to write a corresponding query for us.

ChatGPT has provided us with the following query:

WITH rental_amounts AS (
  SELECT
    c.customer_id,
    cat.name AS category,
    SUM(p.amount) AS total_amount
  FROM
    rental r
    JOIN payment p ON r.rental_id = p.rental_id
    JOIN inventory i ON r.inventory_id = i.inventory_id
    JOIN film f ON i.film_id = f.film_id
    JOIN film_category fc ON f.film_id = fc.film_id
    JOIN category cat ON fc.category_id = cat.category_id
    JOIN customer c ON r.customer_id = c.customer_id
  GROUP BY
    c.customer_id,
    cat.name
)
SELECT
  customer_id,
  SUM(CASE WHEN category = 'Action' THEN total_amount ELSE 0 END) AS "Action",
  SUM(CASE WHEN category = 'Animation' THEN total_amount ELSE 0 END) AS "Animation",
  SUM(CASE WHEN category = 'Children' THEN total_amount ELSE 0 END) AS "Children",
  -- Add more categories as needed
  SUM(total_amount) AS "Total"
FROM
  rental_amounts
GROUP BY
  customer_id
ORDER BY
  customer_id;

Now it’s time to validate it in dbForge Studio for PostgreSQL. Ready?

And it is working! Quite impressive, isn’t it? Now we go to Google Bard with the same request.

And we get the following code:

WITH rental_amount AS (
  SELECT
    customer_id,
    film_id,
    SUM(rental_amount) AS total_rental_amount
  FROM rental
  GROUP BY customer_id, film_id
)
SELECT
  customer_id,
  category.name AS category,
  SUM(rental_amount) AS total_rental_amount
FROM rental_amount
JOIN film_category ON rental_amount.film_id = film_category.film_id
JOIN category ON film_category.category_id = category.category_id
GROUP BY customer_id, category
ORDER BY customer_id;

However, when we run the query that Google Bard has generated for us, we face a couple of mistakes.

1. There is no film_id column in the rental table, therefore grouping by it is also impossible.
2. The rental_amount column does not exist.
3. The rental_amount.film_id = film_category.film_id JOIN is not valid, since, as mentioned above, there is no film_id in rental.

Which AI tool is better?

Both Google Bard and ChatGPT-4 are continuously evolving, and these tools possess immense potential in the field of AI language models. However, based on the analysis conducted in this article, ChatGPT-4 demonstrates superior performance in handling PostgreSQL prompts. The code generated by ChatGPT-4 typically requires fewer modifications, making it more efficient. Additionally, ChatGPT-4 boasts greater accessibility, as obtaining access to this AI model is a more straightforward process compared to its counterpart.

Furthermore, the code generated by ChatGPT is a little bit more user-friendly; for example, the AI thoughtfully includes clauses such as ORDER BY and GROUP BY, making the results more comprehensible and easier to analyze. This facilitates the identification of trends and patterns, ultimately improving the overall user experience when working with the output.

Want to learn more?

Refer to our blog posts on utilizing AI for SQL and database development:

• How to Use ChatGPT to Write SQL JOIN Queries
• Power Up Your MySQL Queries: How ChatGPT Can Help You Retrieve MySQL Data
• How ChatGPT Can Help Database Developers Write Unit Tests for SQL Server
• Exploring ChatGPT’s Capabilities in Creating and Optimizing SQL Queries for Oracle

Conclusion

In this article, we have explored and compared the capabilities of two prominent AI language models, Google Bard and ChatGPT-4, in the context of assisting PostgreSQL developers with query writing. Our analysis reveals that ChatGPT-4 outperforms Google Bard in generating more user-oriented code, requiring fewer adjustments, and boasting greater accessibility. As AI continues to advance and shape the future, it becomes increasingly crucial for developers to learn how to effectively interact with such powerful assistants.

To validate the AI-generated responses, we utilized dbForge Studio for PostgreSQL, an all-in-one solution for database management, development, and administration. The Studio offers an extensive range of features and tools designed to simplify and streamline the work of database professionals. To experience its benefits first-hand, we encourage you to download the free trial of dbForge Studio for PostgreSQL and explore its capabilities for your own projects.