Crash course in PostgreSQL, part 2

In part 1, we learned important PostgreSQL fundamentals. Today, we'll learn how to populate a table with data, and about important concepts like schema, normalization, views, and transactions.

[A crash course in PostgreSQL, Part 1]

Populating a table with data

In part 1, we created a small example comics table. We learned how to list all of the tables in our database, including the system tables that are always present. This command lists only the tables we created:

testdb=# \dt
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | comics  | table | carla

We only created the table structure, so let's populate it with data. Tables have columns and rows. Each column contains a specific data type, and each row contains your data. Don't get bogged down in row order because it doesn't matter, and you can't control it anyway. It may help to think of a database as a collection of reasonably-organized pots of data, into which you dip precise queries to retrieve whatever data you want. It's the queries that need to be specific and well-ordered, not your table rows. The INSERT statement put data into our table rows, like this:

testdb=# INSERT INTO comics VALUES ('Fabulous Furry Freak Brothers 1',<br>   'Rip Off Press', '1971-03-15');
INSERT 0 1

You must insert your data in the correct column order. If you don't remember your column order, there are two options: one, read your table structure:

testdb=# \d comics
               Table "public.comics"
     Column     |         Type          | Modifiers 
----------------+-----------------------+-----------
 name           | character varying(80) | 
 publisher      | character varying(80) | 
 date_published | date                  | 

Or list your columns, in any order, in your INSERT command, and then list your values in the same order:

testdb=# INSERT INTO comics (date_published, publisher, name)<br>    
 VALUES('1971-03-15', 'Rip Off Press', 'Fabulous Furry Freak Brothers 1');

Did it work? Let's look:

testdb=# SELECT * from comics;
              name               |   publisher   | date_published 
---------------------------------+---------------+----------------
 Fabulous Furry Freak Brothers 1 | Rip Off Press | 1971-03-15
(1 row)

By golly, it did!