SQL - PostgreSQL - Find gaps in booking calendar

I am currently setting up a web based system to allow users to find availabilities of boats in a calendar. I am using PostgreSQL 9.6 A user should be able to say I want to find a boat available for three hours in the next three months between 08h00...
more »

2017-02-12 14:02 (2) Answers

Composite Foreign Key in Django

I need a Composite Foreign Key in Django, which isn't supported. I could add it manually to the DB, or through migrations but then it won't be reflected in the model definition (sadpanda). The backend DB is postgres. Here's my models: class Trial(m...
more »

2017-02-10 19:02 (0) Answers

Postgres CREATE EXTENSION All Databases

I've got postgres installed in multiple environments. In each of those environments, I have 2+ databases. If I have superuser database rights, is there a way to install the CITEXT extension for all databases on a given postgres install? As of righ...
more »

2017-02-09 15:02 (1) Answers

Lazy order by/where evaluation

Edit It seems that a pure materialization can be stored as a column on the table and indexed; however, my specific use case (semver.satisfies) requires a more general solution: create table Submissions ( version text created_at timestamp ) ...
more »

2017-02-08 22:02 (4) Answers

Locking row of a referenced table postgres

I have a table A which is referenced by a table B that is to say A's schema looks like this: Table A ( id int, name varchar, ) While Table B's schema is: Table B ( id int, a_id int, val int ) I have a piece of code that creates a reco...
more »

2017-02-08 13:02 (3) Answers

Why does my pg_query update method skips row?

I'm just a beginner with php, even more so on PostgeSQL. I can say that my knowledge over php, json and sql is very limited. I have created an update method to refresh my returned data whenever I create or delete a record from database. But sometime...
more »

2017-02-06 22:02 (1) Answers

SQL - LIKE search in address fields

I have SQL query, which searchs in table with address fields. My problem is in City field. I assumed search column streets with house number and without him but I forgot the division of neighborhoods like: Kolin IV, Kolín II, etc. My question is, c...
more »

2017-02-06 14:02 (1) Answers

PostgreSQL aggregate nodes recursively

My table in postgres looks like below. Interpret those values in arrays as ID's of nodes that are connected in a directed graph. What I want to get is the list of possible paths (matching the last ID of each row with the first ID from other rows) Da...
more »

2017-02-02 10:02 (1) Answers

PostgreSQL performance testing - precautions?

I have some performance tests for an index structure on some data. I will be comparing 2 indexes side-by-side (still not decided if I will be using 2 VMs). I require results to be as neutral as possible of course, so I have these kinds of questions w...
more »

2017-01-29 23:01 (2) Answers

ORDER BY in SQL Server vs Postgresql

I am trying to run ORDER BY on a huge data set and produce dense rank values to extract the distinct number of rows based on the dense rank.Later, I am using the dense rank value as a surrogate key in my entire process to carry forward the intermedia...
more »

2017-01-27 19:01 (1) Answers

Django raw SQL queries with ForeignKey

models.py class Author(models.Model): name = models.CharField(max_length=30) surname = models.CharField(max_length=50) class Exhibit(models.Model): author_id = models.ForeignKey(Author) title = models.CharField(max_length=60) crea...
more »

2017-01-24 22:01 (2) Answers

Database function which updates json

In a Postgres DB I have a table (Users) which defines users. It is like |ID| meta | |01| {"email" : "test1@gmail.com","importance" : "High"} | |02| {"email" : "test2@gmail.com","importance" : "Medi...
more »

2017-01-24 15:01 (1) Answers

Type for storing `DiffTime` in database

I need to store a DiffTime from the time library in a database (PostgreSQL) and have three options for converting to a database type: Integer using fromIntegral . fromEnum Numeric via Rational toRational Real using realToFrac The integer is exact...
more »

2017-01-24 08:01 (1) Answers

Lack of performance in PostgreSql redis_fdw

Is it normal that inserting a single two-column line in a redis_fdw scalar/hash table lasts longer than in a regular Postgres table (in average 0.6 ms vs 0.06 ms)? I use Redis to speed-up my postgres, but why to use it if it dose the opposite thing? ...
more »

2017-01-19 23:01 (0) Answers

Querying JSONB using Postgres

I am attempting to get an element in my JSON with a query. I am using Groovy, Postgres 9.4 and JSONB. Here is my JSON { "id": "${ID}", "team": { "id": "123", "name": "Shire Soldiers" }, "playersContainer": { ...
more »

2017-01-17 12:01 (1) Answers