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
1
vote
2 answers

Error when init database postgresql 10.10: PANIC: could not generate secret authorization token

I have a problem when run command: sudo -su user_test ./pgsql/bin/initdb -D /example/folder I had researched many sources from the internet but don’t found a solution. I hope everyone could help me. Thanks. Enviroment: initdb (PostgreSQL)…
andrewvo148
  • 13
  • 1
  • 3
1
vote
0 answers

PostgreSQL to Kafka replication using Pglogical

Can we setup PostgreSQL to Kafka replication using Pglogical? Please share steps if possible. Note- We are using AWS RDS and Aurora for PostgreSQL. Thanks,
Chetan Dev Khanna
  • 127
  • 7
1
vote
1 answer

How to SELECT rows in an order dictated by a JSONB array of primary key IDs?

This is my dummy setup: CREATE TABLE containers ( id SERIAL PRIMARY KEY, positions jsonb ); CREATE TABLE bits ( id SERIAL PRIMARY KEY, container_id integer REFERENCES containers(id) ON DELETE CASCADE ON UPDATE CASCADE, data…
ffxsam
  • 26,428
  • 32
  • 94
  • 144
1
vote
1 answer

Why postgres database server slows down after dump/restore with --no-owner option?

When I do dump/restore everything works fine. But when I add option --no-owner and dump/restore database the queries slows down. For example one complex query run about 3 minues instead of 1sec before dump/restore The only thing that is changed is…
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
1
vote
1 answer

pg-promise: SQL Names ~ or :name does not seem to work with ParameterizedQuery

I am trying to use a ParameterizedQuery with SQL Names inside its text parameter. I know that the docs read that this parameter must be a string or a QueryFile. Basically, what I'd like to do is something like: import pgPromise from…
umbe1987
  • 2,894
  • 6
  • 35
  • 63
1
vote
1 answer

PostgreSQL left join query with one to many relationship

I have below tables CREATE TABLE employee(id serial PRIMARY KEY,employee jsonb); CREATE TABLE perks_details(id serial PRIMARY KEY,details jsonb); insert into employee(employee) values ('{"name": "name1", "perks": [ {"id": 123,…
MPI
  • 47
  • 6
1
vote
1 answer

Why log duration without query?

I enabled in postgresql.conf this parameters: log_statement = 'all' logging_collector = on log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_directory = 'pg_log' and now I see this: 2020-08-26 11:05:33.151 UTC [29051] postgres@mydb LOG: …
Анар Лятифов
  • 69
  • 1
  • 5
1
vote
2 answers

Postgresql update JSONB object to array

I don't know why, but probably PHP persisted some of my data as object and some of them as array. My table looks something like: seller_info_address Table: ID (INT) | address (JSONB) …
crazyyou
  • 581
  • 1
  • 4
  • 15
1
vote
0 answers

PostgreSQL query in grafana

I am not able to see the output in grafana v 7.1.0 as per group by coil_id: I use this query: SELECT $__timeGroupAlias("time",$__interval),coil_id as coil, avg(drawing_force)*0.1 AS "force" FROM drawing WHERE $__timeFilter("time") GROUP BY…
Nitesh
  • 19
  • 6
1
vote
0 answers

Postgres' insert fail results in doubled database size - VACUUM FULL does not reclaim space

So this has been baffling me for days. I have a Postgres database with the Timescale extension active. I have a table table_name which is partitioned by week on field created_at (date time with timezone) using Postgres data partitioning feature. It…
mkaran
  • 2,528
  • 20
  • 23
1
vote
2 answers

Move partition from one table to another table PostgreSQL 10.11

I am new to postgreSQL, I am working on a project where I am requested to move all the partitions older than 6 months to a legacy table so that the query on the table would be faster. I have the partition table with 10 years of data. Lets assume if…
Dpk
  • 15
  • 1
  • 7
1
vote
1 answer

How to do a lookup for array elements in jsonb field?

Let's say I have a table: SELECT * FROM settings; | id | name | strategies | | -- | --- | --- | | 1 | default | [{name: xyz, enabled: true}, {name: bot, enabled: true}] | | 2 | new1 | [{name: bot, enabled: true}, {name: xyz,…
van_folmert
  • 4,257
  • 10
  • 44
  • 89
1
vote
1 answer

Add a row for each unique column value only if it doesn't already exist

I have two tables: IsCompanyValid and CompanyData. IsCompanyValid: Company IsValid A TRUE B TRUE C TRUE D FALSE CompanyData: Company Data A Data1 A Data1 A Data1 B Data1 B …
Tim
  • 478
  • 4
  • 15
1
vote
1 answer

Postgres timestamp with microseconds

I have a column in a table with datatype set as timestamp without time zone. I need the time part with microseconds(6 digits), but sometimes if the last digit is zero, the microseconds part ignores it. I am able to query it with the below query to…
Mano
  • 601
  • 10
  • 32
1
vote
0 answers

Postgresql : UNNESTING & NESTING

I started exploring json in postgresql. I have below jsonb in table. '{ "Owner":[ { "Edgeid":4, "Weight":40, "EdgeColor":"Black" }, { "Edgeid":1, "Weight":10, …
VBAGuy
  • 172
  • 1
  • 9
1 2 3
…
42 43