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
1 answer

Postgres performance issue with query based on filter values

I am not an expert in Postgres, but I am trying to understand this strange behaviour and perhaps some of you might give me some insight. Those are the tables and indexes involved Tables CREATE TABLE swp_am_hcbe_pro.submissions ( id…
Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
1
vote
1 answer

Invalid input syntax for type interval Error

When i add a day to a date in postgres as below SELECT '2018-08-20 00:00:00.0' + INTERVAL '1 DAY'; Getting below exception in pgAdmin4. Can any one help me here. ERROR: invalid input syntax for type interval: "2018-08-20 00:00:00.0" LINE 1: SELECT…
ga7202
  • 93
  • 8
1
vote
1 answer

Drop temporary table when exiting function that return a JSON in PostgreSQL

I'm new on PostgreSQL and I want to know why my temporary table is not dropped after a return different to return query. I can execute this function many times without a "table already exists" error. CREATE OR REPLACE FUNCTION test_table() …
CarlosS
  • 161
  • 1
  • 13
1
vote
1 answer

How to convert postgres "double precision" to "numeric" without data loss/truncation

We have an existing column(type- double precision) in our postgres table and we want to convert the data type of that column to numeric, we've tried the below approaches but all of them had truncation/data loss on the last decimal…
Jagadeesh
  • 119
  • 2
  • 7
1
vote
0 answers

Pgbouncer super slow

We've been using pgbouncer for couple days now and found out that the bouncer is super slow. I'd like to have your feedback on what is wrong with my bouncer configuration: Pressures table is 600k rows The query I use to test: SELECT * FROM Pressures…
Louis Creteur
  • 70
  • 8
1
vote
1 answer

Postgresql FATAL: required WAL directory "pg_wal" does not exist

I'm trying to setup postgresql db - version 10 with high availability setup and also want to use custom directories for data,archive and wal directories After i have initiated db using below command,i have modified the DATADIR path in systemd file…
JPNagarajan
  • 802
  • 1
  • 12
  • 32
1
vote
1 answer

Slow Postgres Query when using GROUP BY

I have table in postgres database. After the row reach 2 mio rows, the query become slower. This is my query SELECT c.source, c.destination, c.product_id, sum(c.weight), count(c.weight), c.owner_id FROM stock c GROUP BY c.source, c.destination,…
Brosky Samson Putra halim
  • 93
  • 8
1
vote
1 answer

Reason to perform Bitmap index scan to Index-only scan?

As I understood by reading this blog post, Bitmap index scan may be superior to index scan, since it batches access to pages of the table itself to fetch data that is not present in the index. Yet, it still needs to traverse index, so I see no…
KAction
  • 587
  • 2
  • 10
1
vote
2 answers

recursive query to get all children

I have an edibles table with the following definition: Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- id | integer | | not null | name …
J86
  • 14,345
  • 47
  • 130
  • 228
1
vote
1 answer

ERROR: invalid logical replication message type "T"

I am getting below error from Postgres 10.3 logical replication. Setup In master, postgresql used 12.3 In logical, postgres 10.3 Logs 2021-03-22 13:06:57.332 IST @ 25929 LOG: checkpoints are occurring too frequently (22 seconds apart) 2021-03-22…
user3710949
  • 47
  • 7
1
vote
1 answer

Psql finds path when connected to the database but not when running command with -c

I'm trying to loop through a directory, copying all the json files into my PostgreSQL database. As I copy them, I must replace any \ with \\ so that Postgres can properly parse it. When I run: $ psql -p 30001 my_database my_database=#\copy my_jsons…
fraoudas
  • 23
  • 5
1
vote
0 answers

How can I import data into PostgreSQL from json file in this format?

I have hundreds of json files in the format: {"name": "example", "location": "example"} {"name": "example2", "location": "example2"} Each line is a different json object and I have all the json files stored locally on a Linux machine. The…
fraoudas
  • 23
  • 5
1
vote
1 answer

How to get all the categories and sub categories in PostgreSQL?

I am creating a small ecommerce demo app using .net core 5 with EF core and postgres 10. I am trying to get all the categories and sub categories on my home page. Here is my category hierarchy: Men (Parent category) Topwear (Sub Category) Formal…
Glenn singh
  • 233
  • 1
  • 6
  • 18
1
vote
1 answer

How to check whether constraint_exclusion is enabled or not in my database?

As per official Postgres documents default value of constraint_exclusion is "partition". How can I ensure this? Is there any way to check current value from PgAdmin?
Yash Mochi
  • 769
  • 6
  • 15
1
vote
1 answer

Concatenate JSON rows

I have the following table with sample records: create table jtest ( id int, jcol json ); insert into jtest values(1,'{"name":"Jack","address1":"HNO 123"}'); insert into jtest values(1,'{"address2":"STREET1"}'); insert into jtest…
MAK
  • 6,824
  • 25
  • 74
  • 131
1 2 3
…
42 43