We'll cover the basics of the PostgreSQL client, introduce you to the idea of schemas, and do some simple SQL.
**Postgresql Client**
The PostgreSQL client, which is a command line tool that allows complete access to databases, is called 'psql'. If you are the postgres user, the command: $ psql test
Should result in something like:
Welcome to psql 8.2.5, 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
test=#
Where the # (or > sometimes) is the prompt for commands.
The basic command is: psql [option] [dbname [username] ]
The options are not often used - check the man pages ($ man psql) for info.
If 'dbname' is omitted, the dbname that is chosen is the one that is called by the same name as the username.
If 'username' is ommitted, the assumed database user is the user who is logged in.
So, if you've just installed postgresql, the only database user is the user 'postgres'. So in order to use psql, you will have to be that user.
The first thing you should do is create a user for yourself.
test=#create user username with password 'good_password_here';
This will allow your normal unix user to have access to postgresql. This is your first SQL statement! (more on user permissions and creating users later.)
Once you've gone back into psql with your normal user ($ psql test), have a look at the commands available, by using /? (very helpful). The ones you'll probably do most often are /l (listing all databases), /d (listing all relations in one database), /c (connecting to other databases.) \h gives you some help on SQL commands. More on that later.
In a new install, this is what you should see:
test=> \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
test | postgres | UTF8
(4 rows)
None of these databases have visible tables (there are a whole host of hidden tables - more on that in a few weeks).
**Schema**
So what is a schema? A schema is, basically, the structure of a database. Here is an example schema:
Field Name | Type
--------------+---------------
userid | int
firstname | varchar(20)
lastname | varchar(20)
address | varchar(100)
city | varchar(20)
stateprov | varchar(20)
postalcode | varchar (15)
totalspent | real
comments | text
code | char(2)
I made this up out of whole cloth, but it's just to give you a very simple example of a schema for a table. We'll start with very simple tables (we'll do relations between tables in a week or so). The data types I've used here are easy ones. "int" are 4 byte integers, "real" are 4 byte floating point, char are fixed length, and varchar are variable length (in other words, the 'char' types will use up that space no matter how many actual characters are in the string, whereas varchar will only use up as much room as the string takes.) Text is unlimited length text. We will go much more deeply into data types later. So how would you create this table?
**Creating Tables**
The SQL command is "create table", and it would go like this:
test=>create table testtable1 (
test->
OK, a quick thing. Every command in postgresql has to be terminated with a semicolon. When psql thinks you are in the middle of a SQL command, it will give you a different prompt. You can always escape out of it by using Control-C. Whatever the command you were in the middle of will be aborted.
test->userid int,
test->firstname varchar(20),
...
test->code char(2) );
A create table command always has parenthesis around the fields. If you want the full skinny on CREATE TABLE, see http://www.postgresql.org/docs/8.0/static/sql-createtable.html
then:
test=> \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+-------
public | testtable1 | table | mpm
(1 row)
And \d testtable1 will give you the schema of the table.
A note: http://www.postgresql.org/docs/ is your best friend. It is extremely well written - you'll find all sorts of great info there.
Next week: putting data in, getting it out, and creating relationships between tables.
**Postgresql Client**
The PostgreSQL client, which is a command line tool that allows complete access to databases, is called 'psql'. If you are the postgres user, the command: $ psql test
Should result in something like:
Welcome to psql 8.2.5, 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
test=#
Where the # (or > sometimes) is the prompt for commands.
The basic command is: psql [option] [dbname [username] ]
The options are not often used - check the man pages ($ man psql) for info.
If 'dbname' is omitted, the dbname that is chosen is the one that is called by the same name as the username.
If 'username' is ommitted, the assumed database user is the user who is logged in.
So, if you've just installed postgresql, the only database user is the user 'postgres'. So in order to use psql, you will have to be that user.
The first thing you should do is create a user for yourself.
test=#create user username with password 'good_password_here';
This will allow your normal unix user to have access to postgresql. This is your first SQL statement! (more on user permissions and creating users later.)
Once you've gone back into psql with your normal user ($ psql test), have a look at the commands available, by using /? (very helpful). The ones you'll probably do most often are /l (listing all databases), /d (listing all relations in one database), /c (connecting to other databases.) \h gives you some help on SQL commands. More on that later.
In a new install, this is what you should see:
test=> \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
test | postgres | UTF8
(4 rows)
None of these databases have visible tables (there are a whole host of hidden tables - more on that in a few weeks).
**Schema**
So what is a schema? A schema is, basically, the structure of a database. Here is an example schema:
Field Name | Type
--------------+---------------
userid | int
firstname | varchar(20)
lastname | varchar(20)
address | varchar(100)
city | varchar(20)
stateprov | varchar(20)
postalcode | varchar (15)
totalspent | real
comments | text
code | char(2)
I made this up out of whole cloth, but it's just to give you a very simple example of a schema for a table. We'll start with very simple tables (we'll do relations between tables in a week or so). The data types I've used here are easy ones. "int" are 4 byte integers, "real" are 4 byte floating point, char are fixed length, and varchar are variable length (in other words, the 'char' types will use up that space no matter how many actual characters are in the string, whereas varchar will only use up as much room as the string takes.) Text is unlimited length text. We will go much more deeply into data types later. So how would you create this table?
**Creating Tables**
The SQL command is "create table", and it would go like this:
test=>create table testtable1 (
test->
OK, a quick thing. Every command in postgresql has to be terminated with a semicolon. When psql thinks you are in the middle of a SQL command, it will give you a different prompt. You can always escape out of it by using Control-C. Whatever the command you were in the middle of will be aborted.
test->userid int,
test->firstname varchar(20),
...
test->code char(2) );
A create table command always has parenthesis around the fields. If you want the full skinny on CREATE TABLE, see http://www.postgresql.org/docs/8.0/static/sql-createtable.html
then:
test=> \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+-------
public | testtable1 | table | mpm
(1 row)
And \d testtable1 will give you the schema of the table.
A note: http://www.postgresql.org/docs/ is your best friend. It is extremely well written - you'll find all sorts of great info there.
Next week: putting data in, getting it out, and creating relationships between tables.