To display the results of a postgresql query as a table (in the example postgresql runs as a docker container locally) use something like the following
psql -h localhost -p 5555 -U postgres database_name -xc 'select * from table_name where id=29'Since the documentation does not contain any information about how you can connect an existing application to the newly created percona pgo cluster, you can use something like the following in your pod postgresql connection string.
postgresql://username:password@cluster1.pgo-perc-production.svc.cluster.local/production
where cluster1.pgo-perc-production.svc.cluster.local points to your newly created cluster and the /production is the database to connect to.
There are different ways of using the json(b) operators in a PostgreSQL column.
The differences between two of them (->) and (->>) are described below:
‘[1,2,3]’::json->2 returns “2”
‘[1,2,3]’::json->>2 returns 2
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:
TRUNCATE TABLE table_name RESTART IDENTITY;
from here
Problem
You need to set up postgres for the first time in a new computer and you need the instructions in one place.
Solution
Follow the example given in the answer below:
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:
select * from pg_stat_activity
make a note of the pid of the query that you want to stop/kill and issue the following:
select pg_cancel_backend(pid)
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.
CREATE OR REPLACE FUNCTION bitmask(code text) RETURNS integer AS $$
DECLARE bm INTEGER := 0;
DECLARE total INTEGER := 0;
DECLARE counter INTEGER := 0;
DECLARE code_to_translate TEXT := '';
BEGIN
LOOP
EXIT WHEN LENGTH(code) = 0;
/* only take first 2 or 3 characters */
code_to_translate := SPLIT_PART(code, ',', 1);
CASE code_to_translate
WHEN 'XX' THEN bm = 0;
WHEN '01a' THEN bm = 2;
WHEN '01b' THEN bm = 4;
WHEN '01c' THEN bm = 8;
WHEN '02a' THEN bm = 16;
WHEN '02b' THEN bm = 32;
WHEN '03' THEN bm = 64;
WHEN '04a' THEN bm = 128;
WHEN '04b' THEN bm = 256;
WHEN '04c' THEN bm = 512;
WHEN '04d' THEN bm = 1024;
WHEN '05' THEN bm = 2048;
WHEN '06a' THEN bm = 4096;
WHEN '06b' THEN bm = 8192;
WHEN '10a' THEN bm = 16384;
WHEN '10b' THEN bm = 32768;
WHEN '10c' THEN bm = 65536;
WHEN '10d' THEN bm = 131072;
WHEN '11a' THEN bm = 262144;
WHEN '11b' THEN bm = 524288;
ELSE bm = 0;
END CASE;
total := total + bm;
counter := counter + 1;
/* Remove checked code so we do not have duplicates */
code := regexp_replace(code, code_to_translate||'[,]*', '', 'g');
END LOOP ;
RETURN total;
END;
$$ LANGUAGE plpgsql;
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:
ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR: insert or update on table "tbl_name" violates foreign key constraint "tbl_name_cln_name_fk_tbl_name_fk" DETAIL: Key (col_name)=(VALUE) is not present in table "fk_tbl_name".
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:
psql -U super_user alter role use_for_tests superuser;
You may need to drop and create your test database.
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.
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:
create extension plr;
you are getting the following error:
ERROR: could not open extension control file "/usr/share/postgresql/9.3/extension/plr.control": No such file or directory
Solution
Install the necessary package for your PostgreSQL version with the following, and install the extension in your psql:
sudo apt-get install postgresql-9.x-plr