Writing Queries In English to Create An Address Book On A PostgreSQL Instance

 Structured Query Language, or SQL, is my favorite programming language, but its quirky syntax can sometimes be frustrating. Many have professed a need to bypass SQL and query databases in English (another quirky language. Is it possible to have an Artificial Intelligence take a request in English and translate it into SQL on my behalf?

I am using the AI Chat Feature of DBeaver Enterprise to test. 

The Prompt 

My prompt was Create a table in this postgresql database to hold address book information and then add ten example records from random data to OpenAI. 

AI Dialog

The Response

Sure, let's create a table named `address_book` with the following fields: `id`, `first_name`, `last_name`, `email`, `phone_number`, and `address`.


CREATE TABLE address_book (

   id serial PRIMARY KEY,

   first_name varchar(50),

   last_name varchar(50),

   email varchar(100),

   phone_number varchar(15),

   address text

);

Now, let's insert ten example records into the `address_book` table. Please note that the data is randomly generated and does not represent real individuals.

INSERT INTO address_book (first_name, last_name, email, phone_number, address) VALUES

('John', 'Doe', 'john.doe@example.com', '123-456-7890', '123 Main St, Anytown, USA'),

('Jane', 'Doe', 'jane.doe@example.com', '234-567-8901', '456 Maple St, Anytown, USA'),

('Jim', 'Smith', 'jim.smith@example.com', '345-678-9012', '789 Oak St, Anytown, USA'),

('Jill', 'Smith', 'jill.smith@example.com', '456-789-0123', '012 Pine St, Anytown, USA'),

('Joe', 'Johnson', 'joe.johnson@example.com', '567-890-1234', '345 Birch St, Anytown, USA'),

('Jenny', 'Johnson', 'jenny.johnson@example.com', '678-901-2345', '678 Cedar St, Anytown, USA'),

('Jack', 'Williams', 'jack.williams@example.com', '789-012-3456', '901 Elm St, Anytown, USA'),

('Jill', 'Williams', 'jill.williams@example.com', '890-123-4567', '234 Spruce St, Anytown, USA'),

('Jerry', 'Brown', 'jerry.brown@example.com', '901-234-5678', '567 Redwood St, Anytown, USA'),

('Jasmine', 'Brown', 'jasmine.brown@example.com', '012-345-6789', '890 Willow St, Anytown, USA');

Please replace the example data with your actual data when you use this in a production environment.


The Result

So, I ran a quick SELECT * FROM address_book

1 John Doe john.doe@example.com 123-456-7890 123 Main St, Anytown, USA

2 Jane Doe jane.doe@example.com 234-567-8901 456 Maple St, Anytown, USA

3 Jim Smith jim.smith@example.com 345-678-9012 789 Oak St, Anytown, USA

4 Jill Smith jill.smith@example.com 456-789-0123 012 Pine St, Anytown, USA

5 Joe Johnson joe.johnson@example.com 567-890-1234 345 Birch St, Anytown, USA

6 Jenny Johnson jenny.johnson@example.com 678-901-2345 678 Cedar St, Anytown, USA

7 Jack Williams jack.williams@example.com 789-012-3456 901 Elm St, Anytown, USA

8 Jill Williams jill.williams@example.com 890-123-4567 234 Spruce St, Anytown, USA

9 Jerry Brown jerry.brown@example.com 901-234-5678 567 Redwood St, Anytown, USA

10 Jasmine Brown jasmine.brown@example.com 012-345-6789 890 Willow St, Anytown, USA

Yup, I got exactly what I asked for. The ONLY SQL I used was to query the data after the AI did the 'heavy lifting'.

Analysis

I could quibble that the table structure was not efficient, with the address column defined as TEXT. This would restrict searches by postal code, city, or state. That could easily be fixed by asking the AI in a second round to break out those fields. 


Overall, this was a very quick way to create a table and fill it with test data. So, it is possible to have an Artificial Intelligence take a request in English and translate it into SQL on my behalf.

Comments

Post a Comment

Popular posts from this blog

Incremental Backups in PostgreSQL 17

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use. This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies. Step 1 - Enable WALL Summerization The incremental backups require Write Ahead Log Summarization. It is likely not on your instance by default. But it is easy to enable. demo=# alter system set summerize_wal = 'on'; ALTER SYSTEM demo=# select pg_reload_conf(); pg_reload_conf  ----------------  t (1 row) Step 2 - Full Backup The examples below are much simpler than you will run into in real life.  The scenario is that we make a full backup on Sunday and do incrementals the rest of the week. Wednesday through Saturday are omitted below.  stoker@ThinkPa...
Read more

Can Artificial Intelligence Created Better Tables Than You?

 Artificial Intelligence is one of those conundrums where the ability to have some tasks handled for you contrasts with a mix of ego and pride that it may be better than you at those tasks.  I recently wrote a blog using another database about an AI-generated SQL that was quite sophisticated . But what about asking an AI to create a table with a specification like a DBA/SRE/Analyst might receive? I used Grok and entered the following prompt: Write the SQL to create a table on a PostgreSQL version 17 server to record data on customers. The data will include a first name, a last name, an address, a birthdate, and a unique UUID primary key. Partition the data by the year of birth. And produce some test data of at least ten records. I am sure many of us have started large projects given less instruction.  Notice: I did not denote the format for the address (US Post Office's format, or UK, or other). Nor did I set a name length. I wanted to see what assumptions were made by t...
Read more

Saving The Output From psql

  Occasionally, you will need to capture data when working with a database. You can cut-n-paste or use a shell command like script . In typical PostgreSQL fashion, psql  has two ways to save your output. The First Way You must invoke psql with the --log-file-<filename> or -L <filename> option. The entire session will be recorded. stoker@ThinkPad:~$ psql --log-file=/tmp/psqllog demo Password for user stoker: psql (17.4 (Ubuntu 17.4-1.pgdg24.04+2)) Type "help" for help. demo=# \d ledger                    Table "public.ledger"      Column     |  Type   | Collation | Nullable | Default ----------------+---------+-----------+----------+---------  id             | integer |           |          |  tx_id          | integer |        ...
Read more