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

could not open extension control file for Citext in postgresql 10

I just installed postgresql 10 in Fedora 28. then I did a mix ecto.migrate but it says ** (Postgrex.Error) ERROR 58P01 (undefined_file): could not open extension control file "/usr/pgsql-10/share/extension/citext.control": No such file or…
Tae
  • 313
  • 2
  • 13
2
votes
1 answer

PostgreSQL. Improve indexes

I have the following structure: create table bitmex ( timestamp timestamp with time zone not null, symbol varchar(255) not null, side varchar(255) not null, tid varchar(255) not null, size …
Yakovenko Alexey
  • 147
  • 1
  • 1
  • 8
2
votes
1 answer

Can I 'recompile' table returning functions after that table is ALTER-ed during database migration?

I have a stored procedure that returns a record type. If I ALTER the table this stored procedure returns (let's say I add a column), I have to either disconnect my database session or re-run the CREATE OR REPLACE. When I don't I will get the error…
Gregor Petrin
  • 2,891
  • 1
  • 20
  • 24
2
votes
1 answer

How does now() get evaluated when passed into a function as a parameter

I have a table that is range partitioned on timestamp with timezone field. I was pretty surprised to find that the following where condition caused the planner to query every single 'child' table in the partition: WHERE reading_time > (now() -…
Debaser
  • 427
  • 1
  • 5
  • 17
2
votes
2 answers

Change PostgreSQL service Name - Linux

I have two different PostgreSQL 10 servers running on the same system (using different ports) on a CentOS 6/7 system. Can someone tell me how to name these PostgreSQL services differently? Like when I run a ps on the system, it should show both the…
P_Ar
  • 377
  • 2
  • 9
  • 25
2
votes
3 answers

STRING_AGG ignores GROUP BY in PostgreSQL

I have prepared an SQL Fiddle for my question - In a 2-player word game I store players and their games in the 2 tables: CREATE TABLE players ( uid SERIAL PRIMARY KEY, name text NOT NULL ); CREATE TABLE games ( gid SERIAL PRIMARY KEY, …
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
2
votes
2 answers

Postgres pg_dump version mismatch... even though versions are the same

I'm trying to dump a Postgres db from Amazon RDS, which I recently updated to 10.1. To do so, I download pg_dump 10.1 from enterprisedb.com (http://get.enterprisedb.com/postgresql/postgresql-10.1-1-linux-x64-binaries.tar.gz) but when I try to use…
Manuel Meurer
  • 3,238
  • 6
  • 35
  • 50
2
votes
1 answer

Constructing a string out of several records with 2 columns

I have prepared a simple SQL Fiddle for my question - In a word game written in Pl/pgSQL for PostgreSQL 10.2 player moves are stored in the table: CREATE TABLE words_scores ( mid bigint NOT NULL REFERENCES words_moves ON DELETE…
2
votes
1 answer

Rails 5.1 store cryptocurrency values in postgresql database

i want to make a rails app to keep track of my cryptocurrency coins. How would you save the values in the database? Which datatype field would you choose? I thought about using the rails-money gem, but only BTC (Bitcoin) is set as a currency. Thanks…
user993460
  • 821
  • 2
  • 8
  • 8
2
votes
1 answer

How to efficiently query items with tags

My schema looks something like this: items ( id, title) tags (id, name ) items_tags ( item_id, tag_id ) I want to efficiently get a list of items, each with its own set of tags. Presumably with one query to the database. By efficient I mean that…
bxq
  • 273
  • 1
  • 8
2
votes
1 answer

Lock multiple rows using CTE and FOR UPDATE

In one session, I trying to lock multiple rows in "users" table, and get "status" column for user WHERE id = 2. do $$ declare user_status int; begin WITH t(id, status) AS( SELECT id, status FROM users WHERE id in( 2,4,7,6) order by id…
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
2
votes
1 answer

PostgreSQL OK to use HASH exclude constraint for uniqueness?

Since hashes are smaller than lengthy text, it seems to me that they could be preferred to b-trees for ensuring column uniqueness. For the sole purpose of ensuring uniqueness, is there any reason the following isn't OK in PG 10? CREATE TABLE test ( …
IamIC
  • 17,747
  • 20
  • 91
  • 154
2
votes
1 answer

Counting unread news in a large table

I got a pretty common (at least as I think) database structure: there are news (News(id, source_id)), each news has a source (Source(id, url)). Sources are aggregated to topics (Topic(id, title)) via TopicSource(source_id, topic_id). In addition…
2
votes
0 answers

Postgres 10 Partitioning - Parent Table Not Visible

I've upgraded to Postgres 10 and I'm planning to deploy some partitioned tabled using the new declarative partitioning syntax: -- Create a parent table create table test( name text, created timestamp with time zone default now() ) partition by…
Matt
  • 3,793
  • 3
  • 24
  • 24
2
votes
0 answers

How can I do less than, greater than in JSON array object Postgres fields? But performance is much better required

I want to retrieve data by specific field operation it store array of object. i want to add new object in it. CREATE TABLE justjson ( id INTEGER, doc JSONB); INSERT INTO justjson VALUES ( 1, '[ { "name": "abc", "age": "22" }, { …
Sandip Mavani
  • 120
  • 2
  • 5