Postgresql Commands

Today, I have downloaded map of a country and imported it into the postgresql database through osm2pqsql.

All the relations in the database were having millions of rows. So, while selecting all of them at once I was getting error.

First I here tell how to open the psql shell. If you are not sudo user. Don’t Worry. We have a solution for that also.

To open shell as a superuser.

$sudo -u postgres psql

It will prompt to psql command line. To connect to particular database

\c gis

That’s all.

If you are a normal user. Request to sudo user to create new role for you and create new database for you.

postgres=# create role tamil login password ‘tamil’;

postgres=# CREATE DATABASE mydatabase WITH OWNER = tamil;

Then you access that particular database with sudo power.

$ psql -h localhost -d mydatabase -U tamil

Then , Dr. H S Rai suggested me to limit the number of rows. From there I got a keyword Limit and searched relevant query. I was not having an idea about this query initially. Without wasting more time in telling my story I here directly code the query.

gis=# select * from planet_osm_nodes LIMIT 3;


-[ RECORD 1 ]—
id | 15382126
lat | 115301682
lon | 849624182
tags |
-[ RECORD 2 ]—
id | 15382127
lat | 115310352
lon | 849612464
tags |
-[ RECORD 3 ]—
id | 15382129
lat | 115327713
lon | 849590461
tags |

Another postgresql query to clear the screen of the psql command line.

gis=# \! clear

You can also use shortcut  Ctrl +l

Command to check the extensions created in the postgres.

postgres=# \dx


List of installed extensions
Name | Version | Schema | Description
hstore | 1.3 | public | data type for storing sets of (key, value) pairs
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 2.2.1 | public | PostGIS geometry, geography, and raster spatial types and functions
(3 rows)

I want to know what basically happen when we create an extension. By creating an extension “postgis” it creates the relation “spatial_ref_sys”. You can check it also.

new=# create extension postgis;

It created a table

new=# \dt

List of relations
Schema | Name | Type | Owner
public | spatial_ref_sys | table | postgres
(1 row)

To retrieve the value of a first ten rows of column tag with particular key

gis=# SELECT tags->’name:hi’ from planet_osm_polygon limit 10;

COALESCE function will output the first not null value among the arguments.

gis=# SELECT coalesce(tags->’name:hi’,name) from planet_osm_polygon limit 10;

To play more with the column having datatype hstore you can follow the tutorial.

Command to check database size.





Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s