PostgreSQL - working with columns and rows

The previous article explained how to create and drop tables using the psql client.

Now we'll explore adding columns and rows (records) to our database's tables.

---

Preparation

To work through the examples given in this article, go ahead and work through the previous one (linked above), but don't drop the 'customers' table as demonstrated at the end.

Connect with psql

Logged into my demo slice as a Linux user named "mike" I'll make a local TCP connection with psql:

mike@demo:~$ psql -U demorole1 -d demodb1 -h localhost
Password for user demorole1: 
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

demodb1=>

Once we're connected with psql we can get started with the SQL commands. If you need help connecting to your postgres server, please see the three making connections articles in this series.

Adding a column

When we created the 'customers' table we setup two columns: "id" and "email".

It would be helpful if we had a column to store each customer's first name, and one to store his last name — it's only polite to call a customer by his proper name in emails sent from our slice!

We can accomplish this easily with the ALTER TABLE command. See the official documentation for a full explanation and other command options:

ALTER TABLE customers
    ADD COLUMN firstname varchar(50);

We'll paste that code into the psql session and hit enter to execute the command.

We can then add another column with the same code, changing 'firstname' to 'lastname'.

Let's verify our work with the '\d' command:

demodb1=> \d customers
                                  Table "public.customers"
  Column   |         Type          |                       Modifiers                        
-----------+-----------------------+--------------------------------------------------------
 id        | integer               | not null default nextval('customers_id_seq'::regclass)
 email     | character varying(50) | 
 firstname | character varying(50) | 
 lastname  | character varying(50) | 
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)

Looks good!

Renaming a column

On second thought, it may make more sense to have a single table column for the customers' first and last names. We can rename the 'firstname' column for that purpose:

ALTER TABLE customers
    RENAME COLUMN firstname TO fullname;

Dropping a column

We'll not be needing the 'lastname' column, and we can drop (delete) it like so:

ALTER TABLE customers
    DROP COLUMN lastname;

NOTE: This deletion is irreversible; a dropped column and all the data it contains are permanently erased from the database.

Let's check our work again:

demodb1=> \d customers
                                 Table "public.customers"
  Column  |         Type          |                       Modifiers                        
----------+-----------------------+--------------------------------------------------------
 id       | integer               | not null default nextval('customers_id_seq'::regclass)
 email    | character varying(50) | 
 fullname | character varying(50) | 
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)

Perfect! Now we're ready to populate our table with some data.

Inserting a row

We can populate the a table manually using the INSERT command. Let's insert the details for two fictitious customers, "Albert" and "Enrico":

INSERT INTO customers (id, email, fullname)
    VALUES (DEFAULT, 'albert@physics.edu', 'Albert Einstein');

INSERT INTO customers (id, email, fullname)
    VALUES (DEFAULT, 'enrico@physics.edu', 'Enrico Fermi');

The column names are specified first, and then in the same order the column values for that row (record). The 'DEFAULT' parameter indicates a column should be filled with its default value.

Selecting a row

We can pull data out of a table with the SELECT command:

demodb1=> SELECT * FROM customers;
 id |       email        |    fullname     
----+--------------------+-----------------
  1 | albert@physics.edu | Albert Einstein
  2 | enrico@physics.edu | Enrico Fermi
(2 rows)

Note that the "id" column was auto-populated with incremented integers, just as we desired when we set it up with the 'serial' data type in the previous article.

Updating a row

If there is a mistake in a row's data, or it needs to be altered for some other reason, we can use the UPDATE command to make changes.

Suppose that "Enrico" has changed his email address and we need to update our database accordingly. We'll use his row's unique "id" value to identify it:

demodb1=> UPDATE customers
    SET email = 'enrico.fermi@domain.com'
    WHERE id = 2;

The SELECT command can be used to verify our change.

Deleting a row

Rows can be removed from a table using the DELETE command.

Suppose we need to remove "Albert's" data from the table. Again, we'll identify a row using its unique "id":

DELETE FROM customers
    WHERE id = 1;

NOTE: This deletion is irreversible; a deleted row and its data are permanently erased.

Let's check the table's contents one more time:

demodb1=> SELECT * FROM customers;
 id |          email          |   fullname   
----+-------------------------+--------------
  2 | enrico.fermi@domain.com | Enrico Fermi
(1 row)

Perfect!

Summary

Manual changes to a database's tables, columns and rows can save a lot of time, and the SQL syntax is straightforward.

We've barely scratched the surface, but the official PostgreSQL documentation can help you construct advanced queries to quickly find the data you're looking for, and to alter one row or thousands as necessary.

—

Mike