Introduction to postgresql

dos2unix · Mar 9, 2025

What is postgresql? Is a database engine. What is a database? A way to store data in an indexed order. I will tell you before I begin here. I am not a DBA. (database administrator) this is not really my forte. I know enough SQL to be dangerous. I'm going to keep this pretty basic. Once you get familiar with databases and SQL (structured query language) you will want to do more advanced stuff like relational tables, foreign keys, left joins and other things. This tutorial will not cover those things. This is basic installation, basic use, and introduction to SQL language.

SQL databses aren't really a direct Linux skill per se. But many people who run Linux computer run SQL databases. There are
other types of databases called no-SQL as well. That's another subject.

Why postgresql? Mostly because it's what I am most familiar with with, I have ran OracleDB, MariaDB, and MySQL in the past. Oracle bought out MySQL several years back. It was branched into MariaDB. There's nothing wrong with those databases, if you like them, by all means use them. Postgresql is of an enterprise level database, it has more features and scales well.

So how do we install it? For my distro it looks like this.
Fedora/Redhat/Rocy

Code:

dnf install -y postgresql17 postgresql17-contrib postgresql17-plpython3 postgresql17-server

Debain/Ubuntu/Mint

Bash:

sudo apt update
sudo apt install -y postgresql-17 postgresql-contrib postgresql-plpython3 postgresql-server

Arch/Endeavor

Code:

sudo pacman -Syu
sudo pacman -S postgresql postgresql-contrib postgresql-plpython postgresql-server

By deffault my distro wants to install postgresql16, but 17 is in the repos, so I am using that. The version doesn't matter that much for this tutorial. The package called postgresql17 isn't really the database, it's the client you use to connect to the database. postgresql17-server is the actual server. The other two packages are optional, but they come in handy for many situations. Most SQL databases can use something called a "stored procedure". Often these are written in python or perl or tcl, and a couple of other languages. I tend to mostly use python these days, but feel free to install any add-ons you like.

Usually when you install postgresql, it will create a user called "postgres" on your system. You will need to know the password for this user. Make sure you write it down or remember it somehow. You can always change it again if you forget, but... (more about that later). Usually the first thing I do after installing postgresql is...

Bash:

sudo passwd postgres

If you get ahead of me here and try to start postgresql with something like...

Bash:

systemctl start postgresql

It probably won't start, you'll likely get some errors. There are a few things we need to do first. Depending on your distro, postgresql won't let you do some of these things as root. You'll have to become the postgres user. Befire we do that, lets fix some permissions.

Bash:

chown -R postgres /var/lib/pgsql

Bash:

sudo su - postgres

Once you are the postgres user, run this command. Usually postgsql puts it's data in /var/lib/pgsql by default. You can chnage this, but
for now, we'll just go with the default location.

Bash:

initdb -D /var/lib/pgsql/data

You will some stuff scroll by on the screen. At the end of this output it will tell you how to start postgresql. I recommend that you don't
do it that way. Instead, I prefer the systemd method.

You can exit being the postgres user for a bit here now. As root we can enable and start postgresql.

Bash:

systemctl enable postgresql
systemctl start postgresql

Now you shouldn't get any errors, it should start normally at this point. Congratulations your database engine is up and running.
Next... we will login and create a database.
... to be continued.

dos2unix · Mar 9, 2025

Before we create the database, we should talk about postgresql database security here for a moment. It has multiple layers, but at the base level, the configuration for everything is in your data directory. In this case we are using /var/lib/pgsql/data . You can do this next part as either root or the postgres user.

Bash:

sudo su - postgres
cd /var/lib/pgsql/data
ls

You will see a number of files here, but we really only care about two of them for now.
pg_hba.conf and postgresql.conf.
This is where the network configuration and network security are handled.
If we use nano or vi or whatever you like to edit the pg_hba.conf file here, you can scroll all the way down to the bottom and you see a section similar to this

Code:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             10.0.0.0/24             trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

The line with the 10.0.0.0/24 isn't there by default, you'll have to add your local subnet if you want other computers on your local subnet to see your database. If not, you don't really have to change anything. Save and close that file. Now we will edit the postgresql.conf file. Find the section that looks like this.

Code:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '10.0.0.67'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#reserved_connections = 0               # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp' # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)

Uncomment the listen address and put in the IP address of your computer here. Some tutorials I have seen will tell you to enter 0.0.0.0 here. That will work, but I have multiple interfaces and I don't want to broadcast to all of them. Using a specific IP address is more secure. You can also uncomment the port line. By default postgresql wants to run on port 5432. You can save and close this file. ou will need to restart postgresql for these changes to take effect.

Code:

sudo systemctl daemon-reload
sudo systemctl restart postgresql

If you're using a fedora/redhat based distro you can open the firewall port like this.

Code:

sudo firewall-cmd --add-service=postgresql --perm
sudo firewall-cmd --reload

Debian/Ubuntu/Mint - Arch

Code:

sudo ufw allow postgresql
sudo ufw reload

... to be continued.

dos2unix · Mar 9, 2025

We need to do all of this next part as user postgres

Code:

sudo su - postgres
psql

This will put you into the root level of the database engine. We want to create a database here.

Code:

CREATE DATABASE contacts;
\q

The \q is for quit/exit. This will kick you back out to the system prompt. Now we enter the database again, slightly differently.

Code:

psql contacts

Notice we put the database name after the psql command. This will put you nto the contacts database context. You could create multiple database and login into each one separately with different credentials and permissions for each database. Now that we are in the contacts database, lets create a table.

Code:

-- Create the database
CREATE DATABASE contacts;

-- Connect to the database
\c contacts;

-- Create the table
CREATE TABLE phonebook (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    street_addr VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(50),
    zip VARCHAR(10),
    phone_numb VARCHAR(15),
    phone_type VARCHAR(20)
);

You could simply have a name field here with both the first name and last name together, and you could put the street address, city, state and zip code all in one field also, but I deliberately wanted multiple fields for these to help with learning about SQL. Now we need to add some data to our database.

Code:

-- Insert sample data into the phonebook table
INSERT INTO phonebook (first_name, last_name, street_addr, city, state, zip, phone_numb, phone_type) VALUES
('John', 'Doe', '123 Maple Street', 'Springfield', 'IL', '62701', '+1-217-555-1234', 'Mobile'),
('Jane', 'Smith', '456 Oak Avenue', 'Metropolis', 'NY', '10001', '+1-212-555-5678', 'Home'),
('Carlos', 'Garcia', '789 Pine Road', 'Los Angeles', 'CA', '90001', '+1-310-555-9012', 'Work'),
('Emily', 'Johnson', '101 Birch Lane', 'Seattle', 'WA', '98101', '+1-206-555-3456', 'Mobile'),
('Aiko', 'Tanaka', '202 Cherry Blossom Way', 'Tokyo', 'Tokyo', '100-0001', '+81-3-5555-6789', 'Home');

Hmm.. lets add another user here with the same city and state as another user.

Code:

-- Insert a 6th contact with the same city and state as John Doe
INSERT INTO phonebook (first_name, last_name, street_addr, city, state, zip, phone_numb, phone_type) VALUES
('Michael', 'Brown', '321 Elm Street', 'Springfield', 'IL', '62702', '+1-217-555-6789', 'Work');

Feel free to change the data to some actual real people you may know if you like. Otherwise you can just use this sample data, as far as I know none of these people actually exist.

If all went according to plan, you should see several "INSERT" statements. Now that have some data in our database, we can actually start learning to use SQL to query our database. So lets look at our data so far.

Code:

SELECT * FROM phonebook;

Congratulations, you are now a SQL expert. Well, OK maybe not yet. But you've ran your first SQL query. It's a good start. For now we will exit the database again with...

Code:

\q

... to be continued.

dos2unix · Mar 9, 2025

Where were we. Oh yeah. Lets login or sudo to user postgres. Lets log back in to the database.

Code:

psql contacts

We can view all our data again.

Code:

 select * from phonebook;
 first_name | last_name |      street_addr       |    city     | state |   zip    |   phone_numb    | phone_type
------------+-----------+------------------------+-------------+-------+----------+-----------------+------------
 John       | Doe       | 123 Maple Street       | Springfield | IL    | 62701    | +1-217-555-1234 | Mobile
 Jane       | Smith     | 456 Oak Avenue         | Metropolis  | NY    | 10001    | +1-212-555-5678 | Home
 Carlos     | Garcia    | 789 Pine Road          | Los Angeles | CA    | 90001    | +1-310-555-9012 | Work
 Emily      | Johnson   | 101 Birch Lane         | Seattle     | WA    | 98101    | +1-206-555-3456 | Mobile
 Aiko       | Tanaka    | 202 Cherry Blossom Way | Tokyo       | Tokyo | 100-0001 | +81-3-5555-6789 | Home
 Michael    | Brown     | 321 Elm Street         | Springfield | IL    | 62702    | +1-217-555-6789 | Work
(6 rows)

Whenever you create a database, or create a table or query a table, you almost always end that line with a semicolon.
Even when we entered data in the message above we used a semicolon. Now we will go ahead and give privileges to our postgres user.

Code:

GRANT ALL PRIVILEGES ON DATABASE contacts TO postgres;

Now the postgres user can do just about anything to our database.

Warning: These next two commands are here for example only. Don't actually run them, they will delete your data.
How would I delete my phonebook table in my database?

Code:

DROP TABLE phonebook;

How would I delete the whole database?

Code:

DROP DATABASE contacts;

Let's add a few more contacts here.

Code:

INSERT INTO phonebook (first_name, last_name, street_addr, city, state, zip, phone_numb, phone_type) VALUES<br>('Alice', 'Williams', '123 Market Street', 'San Francisco', 'CA', '94103', '+1-415-555-1234', 'Mobile'),<br>('Bob', 'Johnson', '456 Main Street', 'San Bernardino', 'CA', '92401', '+1-909-555-5678', 'Home'),<br>('Charlie', 'Davis', '789 Broadway', 'San Diego', 'CA', '92101', '+1-619-555-9012', 'Work'),<br>('Diana', 'Martinez', '101 Ocean Blvd', 'San Diego', 'CA', '92102', '+1-619-555-3456', 'Mobile');

Now let's check our database again.

Code:

contacts=# select * from phonebook;
 first_name | last_name |      street_addr       |      city      | state |   zip    |   phone_numb    | phone_type
------------+-----------+------------------------+----------------+-------+----------+-----------------+------------
 John       | Doe       | 123 Maple Street       | Springfield    | IL    | 62701    | +1-217-555-1234 | Mobile
 Jane       | Smith     | 456 Oak Avenue         | Metropolis     | NY    | 10001    | +1-212-555-5678 | Home
 Carlos     | Garcia    | 789 Pine Road          | Los Angeles    | CA    | 90001    | +1-310-555-9012 | Work
 Emily      | Johnson   | 101 Birch Lane         | Seattle        | WA    | 98101    | +1-206-555-3456 | Mobile
 Aiko       | Tanaka    | 202 Cherry Blossom Way | Tokyo          | Tokyo | 100-0001 | +81-3-5555-6789 | Home
 Michael    | Brown     | 321 Elm Street         | Springfield    | IL    | 62702    | +1-217-555-6789 | Work
 Alice      | Williams  | 123 Market Street      | San Francisco  | CA    | 94103    | +1-415-555-1234 | Mobile
 Bob        | Johnson   | 456 Main Street        | San Bernardino | CA    | 92401    | +1-909-555-5678 | Home
 Charlie    | Davis     | 789 Broadway           | San Diego      | CA    | 92101    | +1-619-555-9012 | Work
 Diana      | Martinez  | 101 Ocean Blvd         | San Diego      | CA    | 92102    | +1-619-555-3456 | Mobile
(10 rows)

Now we will try a few different SQL statements to try to get the hang of it.
What if I only want to see the contacts located in California?

Code:

SELECT * FROM phonebook WHERE state = 'CA';

What if I only want to see the contacts in cities that start with "San"?

Code:

SELECT * FROM phonebook WHERE city LIKE 'San%';

This should return something like...

Code:

contacts=# SELECT * FROM phonebook WHERE city LIKE 'San%';
 first_name | last_name |    street_addr    |      city      | state |  zip  |   phone_numb    | phone_type
------------+-----------+-------------------+----------------+-------+-------+-----------------+------------
 Alice      | Williams  | 123 Market Street | San Francisco  | CA    | 94103 | +1-415-555-1234 | Mobile
 Bob        | Johnson   | 456 Main Street   | San Bernardino | CA    | 92401 | +1-909-555-5678 | Home
 Charlie    | Davis     | 789 Broadway      | San Diego      | CA    | 92101 | +1-619-555-9012 | Work
 Diana      | Martinez  | 101 Ocean Blvd    | San Diego      | CA    | 92102 | +1-619-555-3456 | Mobile
(4 rows)

Notice all the cities start with "San".

Now you'll have to think on your own a bit. How would I look up a contact who's first name is "Charlie"?
How would I look up a contact who lives in Tokyo"? How would I look up a contact who's last name is "Johnson"?

Is it starting to make sense yet? It's not so hard. By now you should be pretty comfortable adding data, and querying the data.

... to be continued.

dos2unix · Mar 9, 2025

Notice we have a contact named Michael Brown. For some reason we want to delete him. How would we do that?

Code:

DELETE FROM phonebook WHERE first_name = 'Michael' AND last_name = 'Brown';

I did this DELETE based on two fields, first_name and last_name. In this case, I could have just used the first name or the last name because they were the only contact named Michael, and they were the only contact with the last name "Brown". But what I had two Michaels? If I would have deleted "WHERE first_name = 'Michael', I would have deleted both users. By adding the last_name, I can be more specific. If I had two Michael Brown's, I could also have checked the city.

How would I have done that? Any ideas, how to adjust my delete statement to check for that?

I also have a user named Jane Smith, she used to live in Metropolis, but she recently moved to Gotham City. How would I update her record without deleting her? I could delete her and create a new record, or I could do this...

Code:

UPDATE phonebook SET city = 'Gotham City', zip = '10002' WHERE first_name = 'Jane' AND last_name = 'Smith';

Now, how would I update her street address? Hopefully this is all starting to make sense now. This is just basic SQL.

... to be continued.

dos2unix · Mar 10, 2025

Back when we created our phonebook table we used a script like this...

Code:

CREATE TABLE phonebook (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    street_addr VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(50),
    zip VARCHAR(10),
    phone_numb VARCHAR(15),
    phone_type VARCHAR(20)
);

Notice every field was something called a "varchar" with a number after it. What is this? What is the number?
varchar stands for variable character. It's the most generic data type, but there are other data types you might want to use.

The VARCHAR type is used to store variable-length strings. Specifying a field size (e.g., VARCHAR(50)) sets a maximum limit on the number of characters that can be stored in that field. This helps:
Optimize Storage: Limits the amount of storage space used.
Enforce Data Integrity: Ensures that data does not exceed the expected length.

INT: Stores integer values. Example: 123.
BIGINT: Stores larger integer values. Example: 9223372036854775807.
FLOAT: Stores floating-point numbers (real numbers). Example: 123.45.
MACADDR: Stores MAC addresses. Example: 08:00:2b:01:02:03.
INET: Stores IPv4 and IPv6 addresses. Example: 192.168.1.1 or 2001:db8::1.
BOOLEAN: Stores TRUE or FALSE values.
DATE: Stores calendar dates (year, month, day). Example: 2025-03-09.
TIMESTAMP: Stores date and time (without time zone). Example: 2025-03-09 16:52:04.
TIMESTAMPTZ: Stores date and time with time zone. Example: 2025-03-09 16:52:04-07.
TEXT: Stores variable-length strings without a specified limit.
NUMERIC: Stores exact numeric values with user-defined precision. Example: 12345.6789.
UUID: Stores universally unique identifiers. Example: 550e8400-e29b-41d4-a716-446655440000.

There may be times when you what to create a table that looks something like this...

Code:

CREATE TABLE contacts2 (
    contact_id UUID PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email TEXT,
    phone_number VARCHAR(15),
    birth_date DATE,
    is_active BOOLEAN,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

This way it forces me to put a specific type of data in a specific field. I can only put a MAC address in a filed type of MACADDR
and I can't put anything but an IP address in a field type of INET. I can't accidentally put a user name in the date field.

... to be continued.

dos2unix · Mar 10, 2025

Constraints in SQL are rules applied to columns in a table to enforce data integrity and ensure the accuracy and reliability of the data. Here are some common types of constraints with examples:

PRIMARY KEY: Ensures that each row in a table has a unique identifier.

Code:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

UNIQUE: Ensures that all values in a column are unique.

Code:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

NOT NULL: Ensures that a column cannot have a NULL value.

Code:

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

CHECK: Ensures that all values in a column satisfy a specific condition.

Code:

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    balance NUMERIC(10, 2) CHECK (balance >= 0)
);

FOREIGN KEY: Ensures referential integrity by linking a column to the primary key of another table.

Code:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE NOT NULL
);

That last one is a bit advanced; we won't really cover foreign keys here.

To be continued.

KGIII · Mar 10, 2025

dos2unix · Mar 10, 2025

Back to our contacts phonebook.

We've already done several SQL queries. Lets do just a few more. Now we don't really have a lot of data in our database, only 10 rows so far. But what if we had hundreds of names. Well you know how look up users named "Michael" or "Sally", but what if I wanted to sort the query, say in alphabetical order?

Code:

SELECT * FROM phonebook ORDER BY last_name;

What if I wanted to order the results according to which state they lived in?

Code:

SELECT * FROM phonebook ORDER BY state DESC;

The DESC means descending order, you can also try ASC for ascending order.

I also showed you how you can do a partial name search using LIKE. Remember this query?

Code:

SELECT * FROM phonebook WHERE city LIKE 'San%';

Well that works for San Franciso and San Diego, but what if I had a typo and spelled one city like "san Bernandino"?
Notice the "san" starts with a lower case "s" now. You will find out that your "LIKE" statement won't find it.
I can use ILIKE

Code:

SELECT * FROM phonebook WHERE city ILIKE 'San%';

Now my query will find all of the cities that start with San or san.

That pretty much concludes the basics of postgresql unless anyone has some questions.
Be warned - if it's a really advanced question, I probably don't know the answer.

But here is a better source of information than I am.

... those should be more than enough to get you started.

Introduction to postgresql

dos2unix

Well-Known Member

dos2unix

Well-Known Member

dos2unix

Well-Known Member

dos2unix

Well-Known Member

dos2unix

Well-Known Member

dos2unix

Well-Known Member

dos2unix

Well-Known Member

KGIII

Administrator

dos2unix

Well-Known Member

PostgreSQL 17.4 Documentation

W3Schools.com

W3Schools.com

Free SQL Downloads: Sample Databases, Scripts, and Tables for Practice

Similar threads

Members online

Latest posts