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

Reclaim disk space without locking table - PostgreSQL 10

I have a couple of tables in a PostgreSQL database which are used very frequently (for Insert/Delete purposes). Sometimes, their size of the tables grow up to GB's. How do I reclaim the disk space from these tables without locking them. These tables…
P_Ar
  • 377
  • 2
  • 9
  • 25
4
votes
1 answer

how to stop and exit from the postgresql restoration if an error occurs

we are trying to restore PostgreSQL schema dumps into a database from the command line using the below command psql -U postgres -d dbname < filename and we are getting response in command line during the restoration like…
jithin giri
  • 733
  • 2
  • 6
  • 17
4
votes
5 answers

Permission denied with a Copy From (pgAdmin4, postgresql10)

I use pgAdmin4 and postgresql 10 on a window 10 laptop. When I use a COPY FROM to import a CSV file COPY BD.CP FROM 'C:\Users\NOMUSER\Desktop\MONDOSSIER\Fichier.csv' with delimiter ';'; PGS returns an error message of Permission denied : ERROR: …
Logique
  • 61
  • 1
  • 3
4
votes
1 answer

Postgres 10: do rows automatically move between partitions?

Assuming I have a parent table with child partitions that are created based on the value of a field. If the value of that field changes, is there a way to have Postgres automatically move the row into the appropriate partition? For example: create…
Winker
  • 805
  • 2
  • 7
  • 9
4
votes
1 answer

PostgreSQL password authentication failed for user "user"

I have tried to Google this question for a long time now, and I do not seem to get to a solution. I have downloaded PostgreSQL on Windows, and therefore have a user with username: postgres and password: postgres. I have no problem with connection…
marasva
  • 53
  • 6
3
votes
1 answer

node.js | Replacing DB client with Prisma keeping preserving DB structure - issue with uuid_generate_v4()

I am trying to add the Prisma DB client to the existing node.js project while preserving the DB structure. Postgresql Prisma 4.7.1 I've set up the initial Prisma configuration (env vars, etc.). I've used the command npx prisma db pull to generate…
Boštjan Biber
  • 444
  • 3
  • 12
3
votes
3 answers

Postgres query planner won't use less expensive composite index

We've run into a strange issue with Postgres, initially unearthed as we prepare to upgrade from 10.21 to 11+. What we'd initially thought was a performance regression in newer versions of Postgres appears, in fact, to be a performance regression…
Sydin
  • 607
  • 5
  • 14
3
votes
1 answer

How to optimize fuzzy string matching pairs of phrases (intersection names) in PostgreSQL

We have a table of intersection names like 'Main St / Broadway Ave' and we are trying to match potentially messy user input (of the form (street1, street2)) to these names. There's no guarantee the input would be in the same order as the street…
raphael
  • 2,762
  • 5
  • 26
  • 55
3
votes
1 answer

Postgres upgrade from 10 to 12: pg_upgrade fail for presence of required library

$ /usr/pgsql-12/bin/pg_upgrade \ > -b /usr/pgsql-1 pgsql-10/ pgsql-12/ > -b /usr/pgsql-10/bin/ \ > -B /usr/pgsql-12/bin/ \ > -d /var/lib/pgsql/1 10/ 12/ > -d /var/lib/pgsql/10/data/ \ > -D /var/lib/pgsql/12/data/ \ > --check Performing Consistency…
Shubham Singh
  • 141
  • 2
  • 7
3
votes
0 answers

Postgres sqlx upsert causing pq: duplicate key value violates unique constraint

I have a table with a unique index called user_conn_unique and I perform an 'upsert' on that table however intermittently I am getting an error pq: duplicate key value violates unique constraint "user_unique_connection" I have tried recreating the…
clD
  • 2,523
  • 2
  • 22
  • 38
3
votes
1 answer

Unable to update row of table with Row Level Security

I'm trying to update a row in a table with row level security policies, but keep getting the error new row violates row-level security policy for table "my_table". Here's how I set up RLS policies: alter table my_table enable row level…
Denis Yakovenko
  • 3,241
  • 6
  • 48
  • 82
3
votes
1 answer

How to control order of fields in jsonb_object_agg

I am trying to build a jsonb object with keys ordered alphabetically. However it seems jsonb_object_agg(k, v) disregards the order of inputs and sorts the input by key length and then alphabetically. E.g. select jsonb_object_agg(k, v order by k)…
AdamAL
  • 1,571
  • 2
  • 14
  • 25
3
votes
1 answer

PostgreSQL - Getting consistent year week pairs across years

I've been banging my head towards a solution to retrieving a consistent (year, week) pair in PostrgeSQL. After looking at the question here, and, in particular, at this comment: Grouping by date_part('year', x), date_part('week', x) is most…
umbe1987
  • 2,894
  • 6
  • 35
  • 63
3
votes
1 answer

PostgresSQL nested jsonb query for multiple key/value pairs

Starting out with JSONB data type and I'm hoping someone can help me out. I have a table (properties) with two columns (id as primary key and data as jsonb). The data structure is: { "ProductType": "ABC", "ProductName": "XYZ", …
cleatedheels
  • 67
  • 1
  • 7
3
votes
1 answer

Jooq INSERT...ON CONFLICT DO UPDATE...RETURNING doesn't return value or catch the database exception

Using Jooq 3.13.5, Postgresql 10. The expectation is that the insert query will insert the row into the database table if it doesn't already exist and return the id column regardless. Table: CREATE descriptor_table ( id serial primary key, name text…
Evan
  • 412
  • 3
  • 7
1 2 3
…
42 43