Welcome to the world of Postgres and OpenAI! In this guide, we’ll walk you through using the ChatGPTExperimental PostgreSQL extension, which allows you to run SQL queries naturally with just your language. Imagine ordering a meal at your favorite restaurant rather than choosing from a complex menu; that’s how easy querying can be with this extension!

Demo

To see the magic in action, check out this demo. This demonstration uses data from the Hacker News and Azure CloudQuery plugins.

How Does It Work?

The extension operates by sending a portion of your database schema to ChatGPT, then receiving a generated SQL query based on your input. Think of it as having a knowledgeable friend who knows your entire database schema but can help you craft the perfect question.

Before You Start

• Note: This plugin sends schema (without the data) to the OpenAI GPT API, so it is not recommended to use it on production databases.
• Note: This is an experimental plugin and not officially supported by CloudQuery.

Installation

To get started, you will need to install pgx first. Follow these steps:

• Install pgx:

  bash
  cargo install --locked cargo-pgx
  cargo pgx init
  

• Clone the extension repository:

  bash
  git clone https://github.com/cloudquery/pg_gpt
  cd pg_gpt
  

• Set your OpenAI key and run the extension:

  bash
  export OPENAI_KEY=YOUR_KEY
  cargo pgx run
  

• Drop into the psql shell and create the extension:

  sql
  create extension pg_gpt;
  set openai.key = YOUR_OPENAI_API_KEY_HERE;
  -- try out a query
  select gpt('show me all open aws s3 buckets');
  -- this will output the following query to execute
  -- select * from aws_s3_bucket;
  

Available Functions

The extension includes the following powerful functions:

• gpt(text): Generates a SQL query based on your input and the full database schema. Great for smaller schemas!
• gpt_tables(table_pattern, text): Works similarly to gpt but focuses only on specific tables that match your defined pattern, allowing for more precise queries using wildcards.

Installing the Extension on an Existing Postgres Instance

If you already have a Postgres instance running, follow these steps to install the extension:

• Run the following command:

  bash
  cargo pgx install
  

• In your Postgres instance, create the extension and set your OpenAI key:

  sql
  create extension pg_gpt;
  set openai.key = YOUR_OPENAI_API_KEY_HERE;
  -- you can now begin using the extension
  

Limitations

While the extension is impressive, it does have limitations:

• Schema Size: Currently using gpt-3.5-turbo, it is limited to 4096 tokens, so be mindful of the size of your schema. If needed, use the gpt_tables function to narrow down your table selection.

Troubleshooting

If you run into issues while using the extension, consider the following troubleshooting tips:

• Ensure your OpenAI API key is set correctly. Double-check that there are no extraneous spaces or characters.
• If a query generation fails, simplify your input or narrow down the schema using the gpt_tables function.
• For more insights, updates, or to collaborate on AI development projects, stay connected with fxis.ai.

At fxis.ai, we believe that such advancements are crucial for the future of AI, as they enable more comprehensive and effective solutions. Our team is continually exploring new methodologies to push the envelope in artificial intelligence, ensuring that our clients benefit from the latest technological innovations.