This is a quick-command list of Postres. If you want detailed instructions, please visit the Postgres Manual.

1. How do I Show all databases?

postgres=# \l
List of databases
Name | Owner | Encoding
------------------+----------+----------
postgis | postgres | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
template_postgis | postgres | UTF8
(5 rows)

Note: Do not drop template databases if not necessary.

2. How do I run a script from the prompt?
\i
OR
psql -d cybersoftbj -u user < myfile.sql

Its very usuful in reloading user-defined functions.

3. How do I create a user?

CREATE ROLE lbs WITH LOGIN PASSWORD 'tracking' SUPERUSER INHERIT CREATEDB CREATEROLE;

4. How to dump database in a text file?

pg_dump -U lbs -d cybersoftbjv1 -h 127.0.0.1 -W > cybersoftbjv1.sql

4. How to dump database cleanly?

pg_dump -c -d -E UTF8 -h 127.0.0.1 -U lbs -W platform_v1 > platform_v1.sql

5. How to update using two tables?

UPDATE road_for_update u
SET the_geom = r.the_geom
FROM roads r
WHERE r.rd_id = u.rd_id;

6. How to change a column type with Cast?

ALTER TABLE roads ALTER COLUMN class_new TYPE integer USING class_new::integer;

7. How to provide/restrict access privileges to tables?

GRANT SELECT ON TABLE table TO user;
REVOKE SELECT ON TABLE table FROM user;

8. How to add a geometry column to a table?

SYNTAX: AddGeometryColumn(

,
, , , )
EXAMPLE: SELECT AddGeometryColumn(‘public’, ‘poi’, ‘the_geom’, 4326, ‘POINT’, 2)
9. Changing column names with spaces?
ALTER TABLE class_aroundme RENAME "level 1" TO level_1; 10. Setting kernel shmmax for postgres
sysctl -w kernel.shmmax=134217728
Note: For permanent changes see /etc/sysctl.cfg11. How to backup table(s) from pg_dump?
pg_dump poi_beijing -t class -t poi_class -f $BACKUPDIR/test_$MYDATE.sql