Resetting sequence number in PostgreSQL

Email this to someoneShare on Google+Share on FacebookTweet about this on TwitterShare on LinkedIn

Problem

While issuing the query TRUNCATE table TABLE_NAME works for MySQL for restarting the id of the table, this does not work with PostgreSQL.

Solution

In order to restart the sequence of the table in PostgreSQL you will have to issue the following:

from here

Stopping a blocking query in postgresql

Email this to someoneShare on Google+Share on FacebookTweet about this on TwitterShare on LinkedIn

Problem

You run a query in PostgreSQL that takes a lot of time to finish (maybe because of an error) and you want to stop/kill it as it is blocking other queries in db.

Solution

You will need to find the pid for the query, either by using a GUI program like Pgadmin or by issuing the following to an SQL prompt:

make a note of the pid of the query that you want to stop/kill and issue the following:

function for getting the bitmask integer value in PostgreSQL

Email this to someoneShare on Google+Share on FacebookTweet about this on TwitterShare on LinkedIn

Problem

You have set up some bitmask values and you would like to return the calculated value given a string with one or more of the codes used, to be able to used it in an SQL query.

Solution

You can use the following to create a function called bitmask. This one deals with any number of individual codes (2 and 3 digit codes in this case).
It also does not calculate duplicate values since they are removed.
So for this example the string ’02a’ is the same as ’02a,02a’ and should give 16 in both cases.

ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR: …

Email this to someoneShare on Google+Share on FacebookTweet about this on TwitterShare on LinkedIn

Problem

You have a Ruby on Rails application/engine with some tests in Minitest and using postgresql as the db. In the original development environment all the tests that use fixtures and foreign key validations pass, but when you try to move to a different environment with new database some or all of you tests fail with a message that refers to the ForeignKeyViolation as in:

Solution

Since active record disables the foreign keys before loading the fixtures in the database, the user that you run the tests with needs to have superuser permissions in the test database. Otherwise the fixtures are not loaded. So add the superuser permissions to your user:

You may need to drop and create your test database.

Setting R_HOME for PL/R in PostgreSQL in Ubuntu

Email this to someoneShare on Google+Share on FacebookTweet about this on TwitterShare on LinkedIn

Problem

You want to use the PL/R extension in PostgreSQL, and after the package installation you need to set up the R_HOME in PostgresQL.

Solution

Add the following line to the bottom of your PostgreSQL configuration file as sudo:

PostGIS in Action by Regina O. Obe & Leo S.Hsu (Manning)

Email this to someoneShare on Google+Share on FacebookTweet about this on TwitterShare on LinkedIn

This is another book from Manning publications in the excellent ‘.. in Action’ series, which guides the reader through some practical uses of the book’s subject. In this case the subject is PostGIS, which for people that come across the term for the first time, is a spatial database extender for the PostgreSQL database management system. As described in the introduction the audience of this book includes GIS Practitioners and Programmers, DB Practitioners as well as Scientists, Researchers, Educators and Engineers. That makes it obvious that the audience covers a wide spectrum of professionals that would have various degrees of experience with the subject matter.

The material is divided in three main parts, which are: Learning PostGIS, Putting PostGIS to work, and Using PostGIS with other tools as well as four additional appendices.

The first part about Learning PostGIS is an introduction to GIS database concepts and practices, that introduces the geometry, geography, raster and topology types and what problems can be solved by each one of them. There is a thorough explanation of what PostGIS is and what you can do with a spatially enabled database that is not possible with a relational database. There are also chapters describing the spatial types that PostGIS offers and their related functions, an introduction to spatial reference systems and their concepts, tools for loading spatial data as well as desktop tools for viewing and querying them, and the use of geometry, geography and raster functions, geocoding and finally an introduction to spatial relationships.

The second part Putting PostGIS to work, is where all the pieces are put together, using the theory foundation from the previous part, in order to solve real world problems to questions like: which places are within X distance and what are the N closest places?
These cover the traditional methods of finding closest neighbours as well as KNN indexes. Following that there is a section dedicated to geotagging. Geometry and geography processing has its own chapter to demonstrate techniques to manipulate geometries, and some of the most common problems and solutions related to them. Other chapters include raster processing, topology which includes creating a topology, and building and working with topogeometries as well as the simplification and validation of them. The final two chapters of this part offer the reader practical solutions in how to organise the spatial storage depending on the requirements, and some very useful tips about query performance tuning and optimisations. It should be also noted that throughout the book there are plenty of examples for the reader to follow, and especially in this part, that are of great practical use.

In the Using PostGIS with other tools part we are told how PostGIS can be extended by means of add-ons like the PostgreSQL procedural languages PL/R and PL/Python that allows us to use the wealth of statistical functions and plotting capabilities of R as well as the numerous Python packages. A variety of travelling-salesperson problems are displayed in this section, and the pgRouting used for building routing applications is also covered. The remaining chapters cover server-side mapping servers and client-side mapping frameworks to display PostGIS data on the web.

Finally the appendices have a very useful section with additional resources, instruction for installing PostGIS, an SQL primer and a separate section with the PostgreSQL features that includes table inheritance, roles, functions and performance tips.

To summarise, this is an extremely useful book for a variety of professional people interested in discovering PostGIS and at the same time PostgreSQL. It does not require any previous knowledge of geospatial databases as there is a great explanation and coverage of the theory, systems and tools needed. It would be helpful if the reader has some knowledge of SQL in order to follow the examples provided, even though there is very good appendix that covers SQL.
A highly recommended book for starting your exploration in the world of spatial databases.

Disclosure of Material Connection: I received this book free from the publisher. I was not required to write a positive review. The opinions I have expressed are my own. Regardless, I only recommend products or services I use personally and believe will add value to readers.

ERROR: could not open extension control file “/usr/share/postgresql/9.3/extension/plr.control”: No such file or directory

Email this to someoneShare on Google+Share on FacebookTweet about this on TwitterShare on LinkedIn

Problem

You are trying to install the PL/R extension to PostgreSQL, after install R in your linux ubuntu development environment, but when trying to install the extension inside your PostgresSQL with:

you are getting the following error:

Solution

Install the necessary package for your PostgreSQL version with the following, and install the extension in your psql:

No PostgreSQL clusters exist; see “man pg_createcluster”

Email this to someoneShare on Google+Share on FacebookTweet about this on TwitterShare on LinkedIn

Problem

After installing postgresql in a new Ubuntu 14.04 installation you get the error:

and when you try to start the server with:

you get the error that the server does not exist:

Solution

The solution can be found in the article here, and is to create manually the first cluster by: