Postgresql Basic Usage Notes

Posted by zhulinpinyu on March 31, 2016

Postgresql Basic Usage Notes


Basic usage


psql -h -U username -p 5432

show DB


switch DB

\c db_name

show tables in DB


use \? see more detail

show view in DB


Show view detail with create shell

 \d+ view_name

SQL usage

split one field value to multi field

split_part 示例:
split_part(lgl_category, '-',1)
split_part(lgl_category, '-‘,3)

SELECT *,upper(split_part(lgl_category, '-',1)) as vtype, upper(split_part(lgl_category, '-',3)) as vsubtype FROM lg.venuesview_3857

lgl_category is field name

Uppercase method


concat multi field to one field

select lgl_category,concat(lgl_description,'(',lgl_description_eng,')') from lg.category_raw

lgl_description and lgl_description_eng are field name