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

pg_dumpall without prompting password

We are trying to automate the backup of all PostgreSQL databases of a cluster, and we decided to use 'pg_dumpall' utility. But we couldn't find a way to execute 'pg_dumpall' without prompting password. We are using PostgreSQL-10. We are trying to…
jithin giri
  • 733
  • 2
  • 6
  • 17
8
votes
2 answers

Set-returning functions are not allowed in UPDATE when using Postgres 10

We have an old Flyway database update UPDATE plays SET album = (regexp_matches(album, '^6,(?:(.+),)?tv\d+'))[1] ...that runs fine with any Postgres version from 9.2 to 9.6 but fails with latest Postgres 10. Happens even when ran directly without…
Anze Rehar
  • 323
  • 3
  • 10
7
votes
2 answers

postgres_fdw: possible to push data to foreign server for join?

suppose I have a query like select * from remote_table join local_table using(common_key) where remote_table is a FOREIGN TABLE with postgres_fdw and local_table is a regular table. local_table is small (100 rows) and remote_table is large…
wrschneider
  • 17,913
  • 16
  • 96
  • 176
7
votes
3 answers

How get values from postgres JSONB column?

I have PostgreSQL 10.5 database with Rails 5 application. My model: # == Schema Information # # Table name: property_keys # # id :integer not null, primary key # name :string # created_at :datetime not null # …
bmalets
  • 3,207
  • 7
  • 35
  • 64
7
votes
1 answer

root.crt not found postgresql

I have a postgres docker image that i am using and I am enabling SSL on it. I want it to verify-full because I have a root.crt and want to make sure all the certs that can use SSL are verified. So, in my docker-compose file, i have mounted my…
Pravan Kalaga
  • 71
  • 1
  • 1
  • 3
6
votes
1 answer

What is the fastest way to rebuild PostgreSQL statistics from zero/scratch with ANALYZE?

I have a PostgreSQL v10 database with a size of about 100GB. What is the most efficient (fastest) way to rebuild statistics, for example after a major version upgrade? ANALYZE with no parameters updates statistics for then entire database by default…
maxTrialfire
  • 532
  • 3
  • 16
6
votes
1 answer

Alter TYPE RENAME VALUE works in Postgres 10 but not in Postgres 9.6?

I am using Postgres 10.3. Based on this question, I use: ALTER TYPE name RENAME VALUE attribute_name TO new_attribute_name to rename an enum value. But I need a solution that works with Postgres 9.6 that does not require updating pg_enum manually…
ankit
  • 2,591
  • 2
  • 29
  • 54
6
votes
2 answers

measuring replication lag in postgresql

im trying to measure the replication time lag in my system. (postgresql 10.1) I use a combination of pg_last_xact_timestamp(), pg_last_receive_lsn() and pg_last_replay_lsn() functions in a query to check the lag. (Took an example of how to measure…
krithikaGopalakrishnan
  • 1,217
  • 10
  • 28
6
votes
1 answer

INNER JOIN issue with JPQL (even if the SQL request is working)

"bonjour à tous" (hi to everyone) I'm coming from this thread : How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL? This guy is doing almost what I need in my project. In fact, I'm trying to do something like Git :…
Antoine
  • 181
  • 1
  • 8
6
votes
1 answer

How to remove configuration parameter

In Postgres it is possible to create your own configuration parameters, something like a "cookie" that persists for duration of either session or transaction. This is done like that: SELECT set_config(setting_name, new_value, is_local) or SET […
Łukasz Kamiński
  • 5,630
  • 1
  • 19
  • 32
6
votes
1 answer

Get rid of double quotation marks with SQLalchemy for PostgreSQL

I'm trying to import 200 SAS XPT files to my PostgreSQL database: engine = create_engine('postgresql://user:pwd@server:5432/dbName') for file in listdir(dataPath): name, ext = file.split('.', 1) with open(join(dataPath, file), 'rb') as f: …
Ryan
  • 1,040
  • 3
  • 16
  • 25
5
votes
1 answer

PostgreSQL: VACUUM FULL duration estimation

I inherited a PostgreSQL database in production with one table that is around 250 GB in size. It only has around ten thousand live rows which I estimate to be not more than 20 MB. The table grew to such a size because AUTOVACUUM has been turned off…
dajood
  • 3,758
  • 9
  • 46
  • 68
5
votes
1 answer

How to use UUID with unprivileged user on Postgres?

How make a default value for uuid rows?, the uuid_generate_v4() function works only if the uuid-ossp extension is enabled but can not enable. postgres=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION postgres=# SELECT…
e-info128
  • 3,727
  • 10
  • 40
  • 57
5
votes
1 answer

Set lc_monetary for PostgreSQL

How do I set lc_monetary to show money (docs) data type as EUR? I tried: change postgresql.conf and set lc_monetary="de_DE.UTF-8@euro. PG will not start with this change (currently set to en_US.UTF-8) do the same through pgAdmin and psql (using…
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
5
votes
0 answers

psql runs out of memory when restoring dump

I have a PostgreSQL text dump file approximatley 4.5GB in size (uncompressed) that I am trying to restore, but always fails due to running out of memory. Interestingly enough, no matter what I try it always fails at the exact same line number of the…
Bob Pusateri
  • 722
  • 5
  • 15
1
2
3
…
42 43