Stack Overflow
Stack Overflow

Questions tagged [postgresql-10]

for PostgreSQL questions specific to version 10

On 5 October 2017, PostgreSQL 10.0 was released. Major enhancements in PostgreSQL 10 include:

  • Logical replication using publish/subscribe
  • Declarative table partitioning
  • Improved query parallelism
  • Significant general performance improvements
  • Stronger password authentication based on SCRAM-SHA-256
  • Improved monitoring and control

Note that as of version 10, PostgreSQL has moved from 3 component version ids to 2 components, so 10.1 is a minor release.

The official documentation for this version is available at: http://www.postgresql.org/docs/10/static/index.html

632 questions
2
votes
1 answer

window function over computed column

I'm writing a query which looks something like this: select parent.id, parent.date, sum(child.amount) filter (where child.is_ok) as child_sum, sum(sum(child.amount) filter (where child.is_ok)) over (order by parent.date) from…
F0RR
  • 1,590
  • 4
  • 16
  • 30
2
votes
1 answer

Hibernate does not close idle session

I cannot kill session in idle status on postgres 10 This caused a very big problem during some test and fill postgres connection pull. After close connection, the session is in idle state and wont close I have the same problem in local and in…
Luca Nitti
  • 67
  • 8
2
votes
2 answers

How to save query in psql by terminal

I created a query in PSQL and run and would like to save it. Query example: CREATE VIEW total_revenue_per_customer AS SELECT customers.id, customers.first_name, customers.last_name, SUM(items.price) FROM customers …
Bruno
  • 133
  • 1
  • 11
2
votes
1 answer

PostgreSQL: latest row in DISTINCT ON less performant than max row in GROUP BY

I have a situation that I would like to better understand: I've a table t with two rows and one index: CREATE TABLE t ( refid BIGINT NOT NULL, created TIMESTAMPTZ NOT NULL ); CREATE INDEX t_refid_created ON t…
Alechko
  • 1,406
  • 1
  • 13
  • 27
2
votes
1 answer

Is it possible to upgrade postgres 9.6 to 10 with zero downtime. What are the approaches that can be followed?

I'm trying to upgrade my postgres database from 9.6 to 10 without any downtime. Can this be done?
user3837299
  • 329
  • 2
  • 12
2
votes
0 answers

django.db.utils.ProgrammingError: multiple default values specified for column "_id" of table "Asset_movie"

After make migrations , i tried to do migrate, but i am getting the django.db.utils.ProgrammingError: multiple default values specified for column "_id" of table "Asset_movie" from django.db import models import date time from django.contrib import…
Karthik Sai
  • 87
  • 1
  • 4
2
votes
0 answers

error while trying to name column in select query

I tried to run the following query earlier at work and I'm got an error that I didn't understand. I've never encountered it before as I don't think I ever tried writing a SELECT query with aggregates that returned a column named cost. Here's the…
Haleemur Ali
  • 26,718
  • 5
  • 61
  • 85
2
votes
1 answer

Recursive CTE concatenate fields with parents from arbitrary point

How can I concatenate a list of parent items in a RECURSIVE CTE with PostgreSQL (version 10.2)? For example, I have: CREATE TABLE test ( id SERIAL UNIQUE, parent integer references test(id), text text NOT NULL ); with: INSERT INTO…
Rob
  • 341
  • 1
  • 3
  • 11
2
votes
2 answers

search for names which have an accent

I am trying to find a way to list only the names that have an accent. I am using the following example taken from this question https://dba.stackexchange.com/questions/94887/what-is-the-impact-of-lc-ctype-on-a-postgresql-database select firstname…
Tito
  • 601
  • 8
  • 23
2
votes
2 answers

PostgreSQL : comparing two sets of results does not work

I have a table that contains 3 columns of ids, clothes, shoes, customers and relates them. I have a query that works fine : select clothes, shoes from table where customers = 101 (all clothes and shoes of customer 101). This returns clothes -…
codebot
  • 517
  • 8
  • 29
  • 55
2
votes
1 answer

JOOQ's insert...returning with PostgreSQL 10 and JOOQ 3.10?

It seems that JOOQ's insert... returning does not work for generated code. There is a similar issue which deals with plain SQL, however i am not using plain SQL. This is the questionable line: public Integer add(MemberRecord member) { // always…
John
  • 5,189
  • 2
  • 38
  • 62
2
votes
1 answer

My remote Postgres query seems to hang forever

I am running the following query on a remote Postgres instance, from a local client: select * from matches_tb1 order by match_id desc limit 10; matches_tb1 is a foreign table and has match_id as unique index. The query seems to hang forever. When…
Friends_little_black
  • 51
  • 6
2
votes
1 answer

How to search table recursively by given path for a tree-like structure in postgresql-10

I have a table like this: +-----------------------------------+ | id | client_id | main_id | name | |-----------------------------------| | 1 | 1 | NULL | hello | | 2 | 1 | 1 | hello2 | | 3 | 1 | 2 |…
Dennis
  • 1,805
  • 3
  • 22
  • 41
2
votes
2 answers

STLineMerge do not make my MULTILINESTRING in to LINESTRING

This is my part of multilinestring : MULTILINESTRING((59.6338836103678 36.3408616511151,59.6336405351758 36.3410074124113),(59.648377513513 36.3329201331362,59.6481338515878 36.3326094998047,59.6478482801467 36.3322287937626)) When i execute this…
Cyrus the Great
  • 5,145
  • 5
  • 68
  • 149
2
votes
1 answer

CREATE TABLE AS is not allowed in a non-volatile function in Postgresql

I have wrote this method on postgresql 10 : create or replace function get_users() returns TABLE(user_idd uuid, device_idd text, shapee text , datee timestamp) AS $$ begin create temp table lines as SELECT DISTINCT user_id, device_id from…
Cyrus the Great
  • 5,145
  • 5
  • 68
  • 149
1 2 3
…
42 43