IT Mission Linux Tips, Hacks, Tutorials, Howtos - Itmission.org

Howto about PostgresSQL database server.

Use the \dt command to see tables in psql. For a complete list of commands inside psql you can use \?.

There are also system tables beginning with pg_ that describe these too.

Use psql -l will list all databases.

Create database (issues faced while creating db)

    shibu@shibu-laptop:~$ createdb mydb
    createdb: could not connect to database template1: could not connect to server:
    No such file or   directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

    shibu@shibu-laptop:~$ sudo /etc/init.d/postgresql-7.4 start
    Password:
    * Starting PostgreSQL 7.4 database server                               [ ok ]

    shibu@shibu-laptop:~$ createdb mydb
    createdb: could not connect to database template1: FATAL:  user "shibu" does not exist

You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name; in that case you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name.

Create database:

   root@shibu-laptop:~# su postgres
   postgres@shibu-laptop:/root$ cd
   postgres@shibu-laptop:~$ createdb mydb
   CREATE DATABASE

drop database

  postgres@shibu-laptop:~$ dropdb mydb
  DROP DATABASE
  postgres@shibu-laptop:~$


  postgres@shibu-laptop:~$ createdb mydb
  CREATE DATABASE

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 manipul

    mydb=# select version();
                                                   version                      
    -----------------------------------------------------------------------------------
  PostgreSQL 7.4.12 on i486-pc-linux-gnu, compiled by GCC gcc-4.0.gcc-opt (GCC) 
  4.0.3 (Ubuntu  4.0.3-1ubuntu4)
  (1 row)

  mydb=# selcet current_date;
  ERROR:  syntax error at or near "selcet" at character 1
  mydb=# select current_date;
    date
  ------------
  2007-01-19
  (1 row)

  mydb=#                  ate a database. These possibilities are not covered in this tutorial.

  postgres@shibu-laptop:~$ psql mydb
  Welcome to psql 7.4.12, the PostgreSQL interactive terminal.

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

  mydb=#    


  For example, you can get help on the syntax of various PostgreSQL SQL commands by typing:

mydb=> \h

  To get out of psql, type

  mydb=> \qpostgres@shibu-laptop:~$ psql -l
        List of databases
   Name    |  Owner   | Encoding
  -----------+----------+----------
  mydb      | postgres | UNICODE
  template0 | postgres | UNICODE
  template1 | postgres | UNICODE
  (3 rows)

  postgres@shibu-laptop:~$

Create user...

  postgres@shibu-laptop:~$ createuser shibu
  Shall the new user be allowed to create databases? (y/n) y
  Shall the new user be allowed to create more new users? (y/n) n
  CREATE USER

  shibu@shibu-laptop:~$ createdb mydb
  CREATE DATABASE
  shibu@shibu-laptop:~$

  shibu@shibu-laptop:~$ psql -l
        List of databases
   Name    |  Owner   | Encoding
  -----------+----------+----------

mydb | shibu | UNICODE

 template0 | postgres | UNICODE
 template1 | postgres | UNICODE
 (3 rows)

shibu@shibu-laptop:~$

  shibu@shibu-laptop:~$ psql -V
  psql (PostgreSQL) 7.4.12
  contains support for command-line editing
  shibu@shibu-laptop:~$

LearningPostgresSQL