Once you have created a database, you can access it by:
Running the PostgreSQL interactive terminal program, called psql, which allows you to interactively enter, edit, and execute SQL commands.
Using an existing graphical frontend tool like PgAccess or an office suite with ODBC support to create and manipulate a database. These possibilities are not covered in this tutorial.
Writing a custom application, using one of the several available language bindings. These possibilities are discussed further in Part IV.
You probably want to start up psql, to try out the examples in this tutorial. It can be activated for the mydb database by typing the command:
$ psql mydb
If you leave off the database name then it will default to your user account name. You already discovered this scheme in the previous section.
In psql, you will be greeted with the following message:
Welcome to psql 8.0.26, 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 mydb=>
That would mean you are a database superuser, which is most likely the case if you installed PostgreSQL yourself. Being a superuser means that you are not subject to access controls. For the purpose of this tutorial this is not of importance.
If you encounter problems starting psql then go back to the previous section. The diagnostics of createdb and psql are similar, and if the former worked the latter should work as well.
mydb=> SELECT version(); version ---------------------------------------------------------------- PostgreSQL 8.0.26 on i586-pc-linux-gnu, compiled by GCC 2.96 (1 row) mydb=> SELECT current_date; date ------------ 2002-08-31 (1 row) mydb=> SELECT 2 + 2; ?column? ---------- 4 (1 row)
The psql program has a number of internal commands that are not SQL commands. They begin with the backslash character, "\". Some of these commands were listed in the welcome message. For example, you can get help on the syntax of various PostgreSQL SQL commands by typing:
To get out of psql, type
and psql will quit and return you to your command shell. (For more internal commands, type \? at the psql prompt.) The full capabilities of psql are documented in psql. If PostgreSQL is installed correctly you can also type man psql at the operating system shell prompt to see the documentation. In this tutorial we will not use these features explicitly, but you can use them yourself when you see fit.
For those coming from MySQL:
SHOW TABLES = \d
SHOW DATABASES = \l
SHOW COLUMNS = \d table
However the \* commands only work in psql and not via other interfaces, such as queries via PHP. Similar data can be retrieved with the following SQL commands:
SHOW TABLES (\d) = SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'
SHOW DATABASES (\l) = SELECT datname FROM pg_database;
SHOW COLUMNS FROM table (\d table) = SELECT column_name FROM information_schema.columns WHERE table_name ='table';
To get the column names in their "natural" order (as in, the order they were created), use:
SELECT column_name FROM information_schema.columns WHERE table_name ='<table>' ORDER BY ordinal_position;
(of course, change <table> to the desired table name)
Alternatively you could send psql metacommands to psql directly:
SHOW TABLES -> echo "\d" | psql <database>
Use "psql -tA" for script friendly format.