PostgreSQL: PostgreSQL is an advanced relational database system that stores data in tables with
defined schemas (columns, data types, constraints). It supports SQL for querying and manipulating data,
offering features like ACID compliance (Atomicity, Consistency, Isolation, Durability) for reliable
transactions. Its flexibility allows users to perform tasks like creating tables, copying data, and
exporting/importing data, making it ideal for academic exercises like Lab 1. In our discussion,
PostgreSQL was used to manage the imdb database, handling tables like actors or _actors, movies,
genres, and directors.
CREATE TABLE ... LIKE: This SQL command creates a new table with the same schema as an existing
table, including column names, data types, and constraints (e.g., primary keys). It is useful for
duplicating a table’s structure without copying its data, enabling scenarios like creating a backup table
(e.g., actors_copy or actors_2 from _actors). The INCLUDING ALL option ensures all constraints are
copied, maintaining data integrity. This was critical when you needed to create actors_2 with the same
structure as _actors to copy data without errors.
INSERT INTO ... SELECT: This SQL query copies data from one table to another by selecting rows from
the source table and inserting them into the target table. It requires the target table to have a
compatible schema (same number and types of columns) to avoid errors like “INSERT has more
expressions than target columns.” In your case, this was used to copy data from _actors to actors_2,
requiring careful column alignment to match the source and target schemas.
DELETE FROM: This SQL command removes all rows from a specified table while preserving its
structure (schema). Unlike DROP TABLE, which deletes the table entirely, DELETE FROM allows the table
to be reused for further operations. You used this to clear all data from actors_2, ensuring the table
remained intact for potential reuse, such as re-importing data or testing.
\copy Meta-Command: In psql, the \copy meta-command exports table data to a CSV file or imports
data from a CSV file into a table. Unlike the server-side COPY command, which requires server file
system permissions, \copy operates on the client’s file system, making it suitable for users without
server access. You used \copy to export data from actors or _actors to a CSV file (e.g., actors.csv) and
import it into another table, aligning with Lab 1, Sections C and D. The syntax includes options like WITH
(FORMAT CSV, HEADER) to ensure proper CSV formatting and column headers.
SELECT COUNT(*): This SQL query counts the total number of rows in a table, returning a single value.
It is commonly used to verify data presence or changes, such as after copying, importing, or deleting
data. You used this to confirm the number of rows in actors_copy or actors_2 after copying or deleting
data, ensuring operations were successful.
Schema Management: A table’s schema defines its structure, including column names, data types,
and constraints. Operations like copying data or importing CSV files require the source and target
schemas to align to prevent errors. You encountered a column mismatch error when copying from
_actors to actors_2, highlighting the importance of matching schemas using commands like CREATE
TABLE ... LIKE or explicitly selecting columns in INSERT INTO ... SELECT.
psql Command-Line Interface: psql is PostgreSQL’s interactive terminal for executing SQL queries and
meta-commands (e.g., \copy, \dt). Meta-commands are specific to psql and not valid in SQL query tools
like pgAdmin, which caused your syntax error when attempting \copy in the wrong environment.
Understanding psql’s role was crucial for executing tasks like exporting/importing CSV files and
inspecting table schemas with \dt.
File System Permissions: PostgreSQL operations like COPY require appropriate file system permissions
on the server, while \copy uses client-side permissions. Your permission errors with COPY (e.g., for C:\
Documents\College\Fourth-SEM) underscored the need to use \copy for client-side file operations,
ensuring access to writable directories like your Desktop.
Host-Based Authentication (pg_hba.conf): The pg_hba.conf file controls client authentication in
PostgreSQL, specifying which hosts, users, and databases can connect using methods like md5 or trust.
Your connection issues with a remote server (192.168.1.65) highlighted the need to configure
pg_hba.conf to allow specific IPs, ensuring secure and accessible database connections.
B. Write the the sql query in postgresql for the following
1. Find the number of actors, movies, genres, and directors.
OUTPUT:-
2. Find all movies released from 1990 to 2000.
Output :-
3. Find top 5 movies based on the rank.
Output:-
C. Copying data from PostgreSQL to a CSV file (i.e., exporting data)
output :-
D. Copying a CSV file into PostgreSQL (i.e., importing data)
output :-
CONCLUSION :-
Here, we have learned about the postgresSQL its installation, setup and some basic
functionality. Here we have learned about creating tables using SQL queries inserting ino those
table, deleting them, counting the data in the table, copying data from one table to another etc.
We learned that the sql can be used to convert the database into CSV data and viceversa
