Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
6
votes
3answers
3k views

What are Identity Columns?

I was reviewing the commit-fest scheduled for 7/01 for PostgreSQL and I saw that Pg is likely going to get "identity columns" sometime soon. I found some mention in information_schema.columns but ...
4
votes
1answer
475 views

Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?

Setting time zone as numeric offset (without abbrev, thus IMO not in POSIX format) is still interpreted by PostgreSQL as POSIX format: SET TIME ZONE '+02:00'; At least I assume, because checking the ...
3
votes
1answer
4k views

Search for nested values in jsonb array with greater operator

Here is the table definition (simplified): CREATE TABLE documents ( document_id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, data_block jsonb NULL ); Sample values: INSERT INTO documents ...
12
votes
2answers
2k views

What is a “transition table" in Postgres?

The page describing what's new in Postgres 10 mentions “Transition Tables for Triggers”. Transition Tables for Triggers This feature makes AFTER STATEMENT triggers both useful and performant ...
3
votes
1answer
475 views

Does PostgreSQL support ICU collation's options and settings?

ICU Specifies different LDML Collation Settings. Some of them seem pretty interesting, especially the ones on case and accent, “Ignore accents”: strength=primary “Ignore accents” but take case ...
4
votes
2answers
1k views

How to quote qualified table name with format() function?

I have some problems in properly quoting a table name using the format function in the example below. CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text) RETURNS bool AS $...
4
votes
1answer
179 views

Slow left join lateral in subquery

I have three tables: units (apartment units), leases, and line_items (a better name would have been recurring rents). A unit has many leases and lease has many line items (associated with each rent ...
1
vote
1answer
3k views

Declaring variable for a whole script

I have a PostgreSQL 10 script for which I would like to declare variables once at the beginning and then use them throughout the script (with possible transactions). I know I can do: DO $$ DECLARE ...
5
votes
2answers
491 views

How to speed up querying last values in a time series?

I have a time series table prices in a PostgreSQL 10 DB. Here is a simplified test case to illustrate the problem: CREATE TABLE prices ( currency text NOT NULL, side boolean NOT NULL, ...
5
votes
1answer
2k views

How to add a PostgreSQL 10 identity column to an existing table?

I'm reading about the new Postgres 10 identity column, and saw how it is used to replace SERIAL columns when CREATE TABLE. Is it also possible to add an identity column to an existing table? For ...
4
votes
1answer
1k views

Specify ICU collations as `ENCODING`, `LC_COLLATE`, and `LC_CTYPE` in Postgres 10

Postgres 10 gains the ability to use International Components for Unicode (ICU) collations rather than depending on host OS implementations. See More robust collations with ICU support in PostgreSQL ...
3
votes
2answers
8k views

Uninstall Postgres 10 on macOS – EnterpriseDB installer

Title says it all… How to completely remove a Postgres cluster from a macOS computer? I used the installer app provided by EnterpriseDB as a courtesy to the community. This uninstallation question ...
3
votes
1answer
37 views

Why does postgresql differentiate between named and numeric time zone notations? [duplicate]

Why is there a difference between named timezone and numeric timezone offset, both of which yield different UTC timestamps for TIMESTAMPTZ? Assume Europe/Prague timezone, which is currently +02:00 (...
2
votes
2answers
4k views

How can I change an existing type from “bigint” to “bigserial”?

I have a PostgreSQL table with the following structure: I simply need to change the TYPE of prove_identity_id from bigint to bigserial. I read the docs but wasn't able to understand how to legally ...
2
votes
2answers
386 views

How to prevent deadlocks in many-to-many insert/update trigger function?

I'm having a problem with deadlocks on a many-to-many insert and am pretty far out of my league at this point. I have a tweet table that receives thousands of records per second. One of the columns ...
2
votes
1answer
94 views

Aggregate additional values to min and max

This is a follow-up question to: Make custom aggregate function easier to use (accept more input types without creating variants) This SQL statement is given: SELECT c.name AS commodity_name, ...
0
votes
1answer
232 views

Queries using GIN index on f_unaccent() seem slow?

I have almost 20M rows in one table that contains product, including their names. I wish to search fulltext and fast by the name so I created this index: CREATE INDEX ...
6
votes
1answer
510 views

How to add a PostgreSQL 10 identity column to an existing table with rows?

I have a table with some rows and I try adding an identity column using the command: ALTER TABLE sourceTable ADD COLUMN ogc_fid int GENERATED BY DEFAULT AS IDENTITY; The ...
3
votes
1answer
4k views

Update nth element of array using a WHERE clause

I have a product table with a jsonb column named 'metadata' in a PostgreSQL 10 database. It's my first time working with documents and Postgres. jsonb values look something like this: { "name" : "...
3
votes
1answer
2k views

Efficient pagination for big tables

Using PostgreSQL 10.5. I'm trying to create a pagination system where the user can go back and forth between various of results. In an attempt to not use OFFSET, I pass the id from the last row in ...
2
votes
0answers
318 views

ts_headline() not highlighting properly for <-> operator

ts_headline() does not seem to highlight the input text in a way that respects the <-> operator. The following query: SELECT ts_headline('English','This Commercial Bank does not have any ...
2
votes
1answer
176 views

PostgreSQL Geo Spatial Query is slow

After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only. I need to fetch records from a Table ...
2
votes
1answer
364 views

Efficiently return two aggregated arrays from a m:n table

I have a table for a many-to-many relationship over a users table to represent a follow relationship between users: CREATE TABLE users ( id text PRIMARY KEY, username text NOT NULL ); CREATE ...
2
votes
1answer
418 views

Was my Postgres cluster built with the ICU libraries available for Postgres 10 and later?

I wonder if my installation of Postgres 10 Beta 2 has been built to include the new International Components for Unicode (ICU) collations. For background info, see More robust collations with ICU ...
2
votes
1answer
304 views

Postgres partial index on IS NULL not working

Postgres version Using PostgreSQL 10.3. Table definition CREATE TABLE tickets ( id bigserial primary key, state character varying, closed timestamp ); CREATE INDEX "state_index" ON "tickets"...
1
vote
1answer
87 views

Combining array_agg and unnest

Given a dataset (with a GIN index on values): key | values ------------- 1 | {4,2,1} 1 | {2,5} 2 | {4,1,3} I want to aggregate the arrays: key | values ------------- 1 | {4,2,1,5} 2 | {4,...
1
vote
1answer
100 views

JSONB vs BRIN Index

The requirements go like this: Each user has a wallet that can contain tens to a hundred or so different currencies, and for each currency an amount Number of currency can grow > 1600 (make me ...
1
vote
2answers
162 views

Why is this seemingly simple insert trigger function causing deadlocks?

This is a simplified version of my prior question. I removed the many-to-many complexity and still deadlocks. It happens WAY less frequently, but still happens. 😕 The situation... I have a tweet ...