Posts

Migrating From MySQL To PostgreSQL in Five Simple Steps

Image
There are many reasons you may want to migrate from MySQL to PostgreSQL, which we will skip over for brevity.  I see many questions about moving tables and data, and sadly, the answers range from sloppy to incredibly complicated.  Each database migration is unique, as issues range from heavily relying on a vendor's feature to extremely complex schemas that require attention to minute detail. Step 1 This may seem obvious, but the first step is access to the original 'source' database. You will need FULL admin access to the database.  If you do not have full access for security, administrative, or other reasons, you will most likely not receive all the desired data.  We will use DBeaver Enterprise to connect to the source database. We need to choose a MySQL database driver Configure the connection We will be porting the World database. In this example, we will port the MySQL World database. This dataset has been used for decades in MySQL documentation, training, and ex...
Post a Comment
Read more

Do You Need An AI Assistant?

Image
 Artificial Intelligence has been over hyped the last several years. But one of the bright, shining spots for AI is acting as an assistant. a super-powerful IDE, when working with a database. Example You want to find your top three customers in the Sakila database. This database has been used for decades in the MySQL world. As your question in English (or another language) The '@ai' directs the rest of the prompt to the DBeaver Artificial Intelligence Assistant See the generated SQL Here is the generated Structured Query Language And get the results. Now we have the top three customers without having to write any SQL directly Fixing Mistakes Occasionally, you may 'fat finger' a query.  I will take the query from above and change first_name to just first .   SELECT c . customer_id , c . first , c . last_name , SUM ( p . amount ) AS total_revenue FROM customer AS c JOIN payment AS p ON c . customer_id = p . customer_id GROUP BY ...
Post a Comment
Read more

PG NYC 2025

  PG NYC  2025 starts November 29th, and you can find me in the DBeaver booth.  This event has become one of my favorites, packed with three days of intense content. And it will all happen less than a week after the release of PG 18. Tickets are still available if you can make it to Manhattan. And I will be demoing new DBeaver features, including talking to the AI Assistant. Please stop by the booth to see it.
Post a Comment
Read more

Using AI To Track Formula 1 Drivers With MySQL

Image
 Formula 1 Car Racing is very popular, and tracking the performance of each team or driver can be quite complex. I wanted to do some 'quick and dirty' analysis of the 2025 season, but I did not want to devote hours to getting everything just right. So, I used DBeaver Enterprise 's AI Assistant Feature. DBeaver's AI Assistant I connected to my local MySQL server and entered this prompt: Create a table to hold information on this seasons performance of Formula 1 drivers to hold their name, car manufacturer, race location, and the drivers points earned for each race, Then collect the data for inclusion in the table This was sent to OpenAI, which returned with: Sure, I can help you create the table. However, I can't collect the data for you as I'm an AI and don't have access to external databases or the internet. You'll need to collect the data yourself and insert it into the table. Here's how you can create the table: CREATE TABLE formula1_season_perfor...
Post a Comment
Read more

The MySQL SQL Error 1064 Blues

Image
          Writing error messages is an art. Sadly, most of us lack talent in that area. One of the prime examples that you see with SQL User Interfaces is the MySQL SQL Error 1064. SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select @var' at line 2 Error position: line: 1 Explicit and clear, it ain't.  I am using DBeaver for this example, but the issue can be reproduced on MySQL Workbench, Toad, and many other tools.  In the following, an environment variable is set on the first line, and then a SELECT is used to return that variable. However, we receive a response of 1064.  So what happened?  While both lines were input to the console, only the second line was executed. When we ask the server about @var, it has no knowledge of it and replies with the 1064 Error. "But we typed it in!" Yeah, we typed it in, but the set was not e...
Post a Comment
Read more

MySQL Entity Relationship Maps With DBeaver

Image
    Even the most experienced database professionals are known to feel a little anxious when peering into an unfamiliar database. Hopefully, they will inspect how the data is normalized and how the various tables are combined to answer complex queries.  Entity Relationship Maps (ERM) provide a visual overview of how tables are related and can document the structure of the data.     The Community Edition of  DBeaver  can easily provide an ERM. First, connect to the database. Right-click on 'Tables' and then select 'View Diagram'.      The ERM is displayed.   And it gets better! Are you not sure how two tables are joined? Click on the link between the tables, and the names of the columns you want to use in your JOIN statement are highlighted. Conclusion     Exploring an unfamiliar database can be daunting. Entity Relationship Maps provide a way to navigate the territory, and  DBeaver  is a fantastic tool for w...
Post a Comment
Read more

Database Administration Query Repository

 I have started a repository of handy SQL Queries for Database Administration, which I have collected over the years. Finding unused or duplicate indexes or queries without indexes and other information can speed up response times. Knowing statistics like cache hit rates can aid in planning upgrades.  I am currently populating this repo as I run across the queries as part of my 'spring cleaning. ' I welcome your participation if you have a handy bit of SQL for an open-source database you'd like to share. The repo is at https://github.com/davestokes/HandySQL , and I will be adding to it over the next few months. If you have a question that you would like to see addressed, please don't hesitate to let me know. I will try to accommodate you. 
Post a Comment
Read more