Working with PostgreSQL : Creating / Deleting a database

This tutorial is a continuation of the Introductory tutorial on the installation of PostgreSQL / PostGIS and it will show how to create databases in using the graphical user interface pgAdmin III.

Right click on the Database as shown and select New Database.

Under the Properties tab, put the Name of database and its owner. During installation I choose postgres as the user name and this is listed in the dropdown arrow as the Owner.

Explore other tabs to understand their functions. The SQL tab, for instance, shows the SQL code for creating the table without using the Graphical User Interface. Use of SQL code has been covered in Querying the database section.

Click OK. The database is now created. The next step is to create a table that will be within the database. The table will contain rows and columns of data.

Expand the database as shown. Under schema > public, right click on Tables and select New Table.

Enter the name of table, the owner and the schema.

Under the columns tab, add as many columns as you require. Each time ensure that you specify the data type accordingly. There are many data types such as text, character integer, date/time etc.

To access the columns that have been added, expand the Table as shown below.

To delete a table or any other object such as a database or a column in a table, right-click on it and select Delete/Drop.

To view / add data to the table, right-click on the table and select View Data > View All Rows. This will show all the data in your table and also give an opportunity to add the data manually in the table.

This will show all the data in your table and also give an opportunity to add the data manually in the table. 

In some instances, the data may be in an external file such as a csv file. To add such an external file ensure that the column names and data types of the external file match with what is in the database.

The figure below shows a CSV file with some few rows of data to be imported in the database.

Right-click on the table you created in the database and select Import

In the File Options tab, browse to where the data is and choose the relevant Format and Encoding of your data.

The CSV file has only two columns that need to be imported.

Since the data is comma separated, choose ‘ , ‘ in the Delimiter drop-down. Choose the relevant option according to the format of your data. Ensure that the Header check box is ticked.

Click Import.

Right-click on the table and select Refresh. Right-click again and select View Data > View All Rows.

All the data in the CSV file has been imported to the table.

Source of featured image : http://gisvm.com/gisvm-postgis/

Add a Comment

Your email address will not be published. Required fields are marked *