Day 2 - Importing data and practicing queries
Today we will learn how to locate structured data and how to import it into our database safely and efficiently.
Generating test data is very useful for validating processes. There are online tools like Mockaroo that allow you to create data sets and download them in CSV format, one of the most popular text formats for data exchange. Make sure the CSV headers match the column names of the table or be prepared to map them manually. Pay attention to data types such as dates, numbers, and text. Mockaroo and other tools allow you to generate data in the correct format to avoid issues when importing.
Preparing the Docker container for import requires that the container can access the CSV file. Since containers are isolated, the usual solution is to create a shared volume between the host and the container. For example, you can stop the container with docker stop travel_mania_container and remove it with docker rm travel_mania_container, create a project folder and a container_data subdirectory on your machine, and run docker run --name travel_mania_container -e POSTGRES_USER=ugur -e POSTGRES_PASSWORD=ugur1234 -e POSTGRES_DB=travel_mania -p 5432:5432 -v ./container_data:/data -d postgres so that the CSV file placed in container_data on the host is accessible from /data inside the container. After this operation, remember to recreate the users table if you had deleted it.
Importing CSV can be done using SQL statements or through the graphical interface of your SQL client. If the CSV column names do not exactly match the table definition, there are several options to correctly map the fields.
Using the column order, you can explicitly indicate the expected order in the COPY statement. Example syntax: COPY users (id, email, username, first_name, last_name, created_at, updated_at) FROM /data/MOCK_DATA.csv DELIMITER , CSV HEADER; this way PostgreSQL skips the first row and places the columns according to the order specified in the list.
Another option is to first import into a temporary table that exactly reflects the CSV and then insert into the real table with the desired mapping. Example flow: CREATE TEMP TABLE temp_users (id INTEGER, user_email VARCHAR(255), username VARCHAR(50), fname VARCHAR(100), lname VARCHAR(100), creation_time TIMESTAMP, update_time TIMESTAMP); COPY temp_users FROM /data/MOCK_DATA.csv DELIMITER , CSV HEADER; INSERT INTO users (id, email, username, first_name, last_name, created_at, updated_at) SELECT id, user_email, username, fname, lname, creation_time, update_time FROM temp_users; DROP TABLE temp_users; This approach allows you to validate and transform data before populating the main table.
Query practice: question 1 Find all users whose first name starts with the letter J and whose last name starts with the letter F ordered by first name ascending. Example query: select username, first_name, last_name, email from users where first_name like J% and last_name like F% order by first_name;
Query practice: question 2 Find users whose username contains numbers and group them by email domain, for example gmail.com or yahoo.com. Example query: select split_part(email, @, 2) as email_domain, count(*) as users_with_numbers_in_username from users where username ~ [0-9] group by split_part(email, @, 2) having count(*) > 0 order by users_with_numbers_in_username desc;
Day 2 summary We learned how to import large data sets using the COPY statement, how to prepare Docker containers with shared volumes, and how to map columns when names do not match. Tomorrow we will look at string handling functions like SPLIT_PART, LENGTH, TRIM and date functions like AGE, DATE_PART, TO_CHAR to clean and transform data directly in SQL.
About Q2BSTUDIO At Q2BSTUDIO we are a custom software and application development company specialized in advanced technological solutions. We offer custom software, custom application development, artificial intelligence projects and AI for businesses, and design of personalized AI agents. We also cover cybersecurity, aws and azure cloud services, and business intelligence services including power bi implementations. Our team integrates expertise in artificial intelligence, analytics, and security to help companies transform data into real value. If you are looking for custom software, custom applications, cybersecurity solutions, or artificial intelligence projects and AI agents to improve processes, Q2BSTUDIO can help you design, develop, and implement the right solution.
Integrated keywords custom applications, custom software, artificial intelligence, cybersecurity, aws and azure cloud services, business intelligence services, AI for businesses, AI agents, power bi to improve web positioning.
