Crash course in PostgreSQL, part 1

Get started with the world's most advanced open source database.

By , ITworld |  Data Center, crash course, database


Viewing tables

Let's look at some tables while we're in the postgres DB. First, list its system tables:

postgres=# \dtS
              List of relations
   Schema   |      Name       | Type  |  Owner   
------------+-----------------+-------+----------
 pg_catalog | pg_aggregate    | table | postgres
 pg_catalog | pg_am           | table | postgres
 pg_catalog | pg_amop         | table | postgres

What's inside the pg_am table?

postgres=# \d pg_am

Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers 
-----------------+----------+-----------
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amcanorder      | boolean  | not null
 [...]
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

This is just the table structure without data. It shows the column names, the data type for each column, and any optional modifiers. Try creating your own table in your test database. For instance, this simple table tracks my underground comics collection:

CREATE TABLE comics (
    name              varchar(80),
    publisher         varchar(80),
    date_published    date
);

You can copy and paste this right into your own psql prompt:

testdb=# CREATE TABLE comics (
testdb(#     name              varchar(80),
testdb(#     publisher         varchar(80),
testdb(#     date_published    date
testdb(# );
CREATE TABLE
testdb=# 

The commas tell psql where your columns end, and the semi-colon tells where your command ends. Try running the commands you've already learned on your test database and new table to see how they look. Use DROP TABLE tablename;

If you're up for more, read Part 2 to find out how to populate tables with data, and to learn about schema, normalization, views, and transactions.

This article, "A crash course in PostgreSQL, Part 1," was originally published at ITworld. For the latest IT news, analysis and how-tos, follow ITworld on Twitter and Facebook

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Data CenterWhite Papers & Webcasts

See more White Papers | Webcasts

Answers - Powered by ITworld

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Ask a Question