A few practical required PostgreSQL commands.

1. Check PostgreSQL server Status, stop and start.

Depending on what version of PostgreSQL

Check Service status ( # /etc/init.d/ppas-9.2 status

Stop Service (#  service ppas-9.2 stop)

Start Service (#  service ppas-9.2 stop)

[root@ip—~]# /etc/init.d/ppas-9.2 status

pg_ctl: no server running

[root@ip-~]# service ppas-9.2 start

Starting Postgres Plus Advanced Server 9.2: 

waiting for server to start.... done

server started

Postgres Plus Advanced Server 9.2 started successfully

[root@ip-~]# /etc/init.d/ppas-9.2 status

pg_ctl: server is running (PID: 8595)

/opt/PostgresPlus/9.2AS/bin/edb-postgres "-D" "/opt/PostgresPlus/9.2AS/data"

2. Confirm PostgreSQL version

# select version();

[root@ip- ~]# psql

psql (9.2.4.8)

Type "help" for help.

edb=# select version();

                                                      version                   

                                   

--------------------------------------------------------------------------------

-----------------------------------

 EnterpriseDB 9.2.4.8 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 2

0080704 (Red Hat 4.1.2-52), 64-bit

(1 row)

edb=# 

3. List out the databases in your connected PostgreSQL server

# \l 

Note: above is slash with lowercase L

# select datname from pg_database;

edb=# select datname,datcollate,datctype,datconnlimit from pg_database;

   datname    | datcollate |  datctype  | datconnlimit 

--------------+------------+------------+--------------

 template1    | en_US.UTF8 | en_US.UTF8 |           -1

 template0    | en_US.UTF8 | en_US.UTF8 |           -1

 postgres     | en_US.UTF8 | en_US.UTF8 |           -1

 edb          | en_US.UTF8 | en_US.UTF8 |           -1

 tejidatabase | en_US.UTF8 | en_US.UTF8 |           -1

 testdb       | en_US.UTF8 | en_US.UTF8 |           -1

(6 rows)

edb=# \l

                                  List of databases

     Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   

--------------+----------+----------+------------+------------+-----------------------

 edb          | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | 

 postgres     | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | 

 tejidatabase | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | =Tc/postgres         +

              |          |          |            |            | postgres=CTc/postgres+

              |          |          |            |            | teji=CTc/postgres    +

              |          |          |            |            | testuser=CTc/postgres

 template0    | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/postgres          +

              |          |          |            |            | postgres=CTc/postgres

 template1    | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/postgres          +

              |          |          |            |            | postgres=CTc/postgres

 testdb       | teji     | UTF8     | en_US.UTF8 | en_US.UTF8 | 

(6 rows)

4. Last but not the least is to be able to get psql commands help and information.

# \?

Note: will show command prompt help

# \h SELECT

Note: will show details about the select command

This can be used for checking syntax of any psql commands.

edb=# \h select

Command:     SELECT

Description: retrieve rows from a table or view

Syntax:

[ WITH [ RECURSIVE ] with_query [, ...] ]

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]

    * | expression [ [ AS ] output_name ] [, ...]

    [ FROM from_item [, ...] ]

    [ WHERE condition ]

    [ GROUP BY expression [, ...] ]

    [ HAVING condition [, ...] ]

    [ WINDOW window_name AS ( window_definition ) [, ...] ]

    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]

    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]

    [ LIMIT { count | ALL } ]

    [ OFFSET start [ ROW | ROWS ] ]

    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]

    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]

    function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )

    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

edb=#