Posts

Book Review - Mastering PostgreSQL 17

Image
      Authors find it challenging to write a comprehensive book about PostgreSQL for several reasons. The first is that a new version of PostgreSQL is released every year with changes, new features, and tweaks.  I am lucky to have been able to review several new database titles each year, and I would like to let you know that Hans-Jurgen Schonig's Mastering PostgreSQL is a well-written reference.  The first chapter of this book covers changes in 17. The explanations and examples are clear, concise, and easy to comprehend. For instance, the CPY command is convenient, and in 17, it can handle errors. The book's example of this new ability quickly shows how it works—short and sweet!  From there, it covers transactions and locking, indexing, advanced SQL log files, system statistics, optimizing queries (very well done here), how to write stored procedures, security, backups, replication (also very well done), extensions, and troubleshooting.  I will ...
Post a Comment
Read more

Data Security and AI - Sharing Your PostgreSQL Database With Ollama AI

Image
You probably saw the story about your public ChatGPT queries being indexed by Google and other search engines . Yikes!  You may want AI with your data but do not wish your prompts to end up in a search engine, especially if you are working with sensitive data.    Previously , we covered using some of DBeaver's Artificial Intelligence Assistant features with Ollama . Ollama is popular because it runs on your system, keeping your data from travelling over networks to an AI and back. What happens only on your system stays on your system, to paraphrase the Las Vegas motto. Paranoia in defense of your data is a good thing. Secure By Default We will start with an example of preference settings for DBeaver Enterprise Edition for Ollama. DBeaver has other security options I want to bring to your attention when working with an AI. Ollama differs from most other AIs because you do not have to obtain a key to work with it. AI Configuration - DBeaver Enterprise Edition The abili...
Post a Comment
Read more

PostgreSQL, Ollama, and the DBeaver AI Assistant

Image
Ollama is an open-source project that simplifies running large language models (LLMs). It runs locally on your machine, and you can choose from multiple LLMs. Keeping all the data on your machine should provide a security bonus.    DBeaver is a universal database tool with an AI Assistant. This assistant provides an extra layer of security by allowing you to lock down what is shared, with the default being metadata only, no data. Webinar I recently presented a webinar  on using the DBeaver AI Assistant. Several questions were raised about using Ollama, one of the many AI Engines available. But I had not tried it. The first step is downloading Ollama for your platform (Linux, Windows, or Mac) and installing.  Once you have Ollama installed, you will need to pick a LLM.  I chose the Gemma3 and installed it using the command line client. Adding the Gemma3 LLM to Ollama DBeaver Configuration Ollama listens on port 11434. I was able to load the Gemma3 model us...
Post a Comment
Read more

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

Image
 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.  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 v...
Post a Comment
Read more

Write PostgreSQL Queries in English?

Image
 If you've ever wanted to skip SQL and its quirks and write queries in English, I am happy to report that you can do it with an AI of your choice and DBeaver. I had a webinar on July 24th that shows you how to do just that.   I will show you how to set up DBeaver  using a wide variety of commercial AIs to let you work with an AI. We will also go through the security features to ensure your data is safe. By default, only the schema metadata is shared with the AI. You can lock down access to just one or more tables being available to the AI. Imagine not having to translate a data request from management speak to SQL! Here we simply ask an AI to provide the five most popular films by revenue and get an answer.  I will also show you how an AI can help by explaining and fixing query problems. You can even identify how that 'thrown over a wall' query works. Have you ever been handed a strange database and have no idea of its content? Then, use an AI to identify its u...
Post a Comment
Read more