Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
13
votes
1answer
960 views

Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?

I have the following tables (taken from the Sakila database): film: film_id is pkey actor: actor_id is pkey film_actor: film_id and actor_id are fkeys to film/actor I am selecting a ...
13
votes
1answer
498 views

Why is PostgreSQL choosing the more expensive join order?

PostgreSQL using defaults, plus default_statistics_target=1000 random_page_cost=1.5 Version PostgreSQL 10.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit I've vacuumed and ...
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 ...
7
votes
2answers
1k views

PostgreSQL 10 Identity Column gets “null value” when inserting multiple rows with default keyword

I recently upgraded from PostgreSQL 9.5 to PostgreSQL 10. One of the nifty features in PostgreSQL 10 is the new identity column type, an alternative to PostgreSQL' serial pseudo-type. Official ...
7
votes
1answer
134 views

PostgreSQL ANALYZE execution time over 24h (still running)

I upgraded a Postgres DB 9.3.2-->10.5 using pg_upgrade (in place). I did everything according to the documentation and the instructions given by pg_upgrade. Everything went fine but then I realized ...
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 ...
6
votes
1answer
5k views

postgresql 10 for Ubuntu 17.10 artful?

Is PostgreSQL 10 available on Ubuntu 17.10 artful? I checked that the folder on APT actually exists: http://apt.postgresql.org/pub/repos/apt/dists/artful-pgdg/10/ But after I do: sudo add-apt-...
6
votes
1answer
2k views

Multiple on conflict targets

I have two unique indexes on columns a and b. I need something like this: insert into my_table (a, b) values (1, 2), (1, 2) on conflict (a) do update set c = 'a_violation' on conflict (b) do update ...
6
votes
1answer
508 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 ...
5
votes
2answers
129 views

Why are partial PostgreSQL HASH indices not smaller than full indices?

I want to create the most efficient index for a sparsely populated column. I only need equality operations, so a HASH index should be beneficial. Now I'm wondering why a partial HASH index isn't ...
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 ...
5
votes
2answers
5k views

Unquoting JSON strings; print JSON strings without quotes

SELECT json_array_elements('["one", "two"]'::json) gives result | json_array_elements | | :------------------ | | "one" | | "two" | I would like to ...
5
votes
1answer
172 views

pg_ctl hangs over ssh

I am designing a process to test my postgresql 10.8 backups by restoring them to a random point in time in a throwaway virtual machine. I have been unable to fully automate the process though. I am ...
5
votes
1answer
144 views

Do BRIN indexes support ENUM types?

BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type. I thought this code would work: CREATE TYPE test_enum AS ENUM ('a', 'b'); CREATE TEMPORARY TABLE my_table ( x test_enum ...
5
votes
2answers
483 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
0answers
417 views

Postgres 10 Logical Replication without truncating existing table

I have been fooling around with Postgres 10 logical replication, trying to learn about it and potentially apply it to a couple of projects. I have observed something that I am wondering about, but I ...
4
votes
1answer
1k views

Postgresql 10 create subscription hangs

On my local machine, I just upgraded to the latest postgres.app to get version 10. Before doing this, I ran pg_dumpall on the 9.6 database and then after installing version 10, I executed the script ...
4
votes
1answer
770 views

“ERROR: timestamp out of range” converting epoch stored as bigint to a timestamp

I'm getting ERROR: timestamp out of range: "1.52701e+15" when trying to convert an epoch stored as a bigint into a timestamp (value is taken from a real database table): select to_timestamp(...
4
votes
1answer
2k views

Replicate only a few tables in PostgreSQL 10

I have a need to keep updated just a few tables between databases with different purposes and schemas, except for these few tables. My current solution is a cron job that selects from one database ...
4
votes
1answer
939 views

Conditional INSERT with a nested CTE?

I am trying to figure out if there is a way I can make nested CTEs work for this particular case. Consider the following (highly contrived) scenario which is based on the actual application: there's ...
4
votes
1answer
167 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 ...
4
votes
2answers
1k views

Passing value of datatype interval in parametrized query

The context is connecting to a Postgres db from a rest server. To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is ...
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
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 ...
4
votes
1answer
467 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 ...
4
votes
1answer
909 views

Postgres 10. Hide users/roles from other users

I have a postgresql 10 installed and I want to hide the name of users/roles to other users. I searched through other posts and somebody suggested this: REVOKE SELECT ON pg_catalog.pg_authid FROM ...
4
votes
1answer
813 views

performances of distinct/group by

I'm trying to select the most recent authors of messages in a room, using this (simplified) table: Table "public.message" Column | Type | Nullable | Default ...
4
votes
0answers
48 views

PostgreSQL 10 backend processes

I have a particular issue with PostgreSQL 10: Postgres creates backend processes for each connection on different ports. The problem is that some of our apps also use many ports and sometimes they ...
4
votes
0answers
107 views

PostgreSQL autovacuum ignoring “inactive” tables resulting in “transaction id wraparound”

I have a PostgreSQL database with a lot of tables (currently 93k) and I'm seeing issues with transaction id age threatening wraparound, because autovacuum is not getting triggered for clean tables ...
3
votes
2answers
412 views

Select orphaned rows (in a generic way)

I have a table my_tables which gets referenced as foreign key in several tables. I would like to select all rows of my_table which are not referenced in an other table. AFAIK, it should be possible ...
3
votes
2answers
3k views

ORDER BY clause kills query performance

Context: PostgreSQL 10, with 3667438 records in users table, the users table has a JSONB called social, we usually use a strategy of indexing computed function outputs, so we can aggregate ...
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
2answers
78 views

Crossjoin-like operation except A x {} = A?

Is there a way to implement operation (let's annotate it ∘) that behaves just like regular cross join except for empty sets it returns non empty side. ∘ - new operation x - cross join {} - empty ...
3
votes
1answer
4k views

column must appear in the GROUP BY clause or be used in an aggregate function

I have a simple table with columns col1, col2, col3. All not nullable. I want to delete all rows where the tuple (col1, col2) has several entries. Background: a unique constraint for (col1, col2) ...
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 ...
3
votes
1answer
473 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 ...
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 ...
3
votes
3answers
156 views

Index scan on UNIQUE index for count(*)

I've got a table t with ~23 million rows (4248 MB in size). There's a column row_id in it, with a not null constraint. And a unique index p1 on t(row_id). When I do select count(*) from t to count ...
3
votes
1answer
37 views

Are PostgreSQL 10.x identity columns inherently indexed? unique?

Are PostgreSQL 10.x identity columns inherently indexed? unique? Or do I need to add a (unique) index to the column as desired?
3
votes
1answer
3k views

Postgres table partitioning “no partition of relation ”parsel_part“ found for row” error?

I'm trying to use new partitioning method of PostgreSQL 10. I have a parent table that contains 1.5 million rows. I want to create partitioning on this already populated table. I've created a new ...
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
302 views

Query to obtain weighted percentile

Trying generate SQL to compute a weighted continuous value at a given set of percentile values (the 25%,50%,and 75% levels used below, but solution should allow for an arbitrary parameter level). In ...
3
votes
4answers
119 views

Getting difference of a column between first and last row with count number

I have table mytable as shown below CREATE TABLE mytable( id, created_at, total_number ) AS VALUES ( 17330 , '2018-05-24 19:25:29'::timestamp, 26909 ), ( 46331 , '2018-05-25 00:57:34', ...
3
votes
1answer
63 views

Return values at more/less specific time points

In PostgreSQL 10.0, I have a big table with two columns: CREATE TABLE xs ( ts timestamp with time zone NOT NULL, x integer NOT NULL ) Now, if I have 100 given time points, how do I return ...
3
votes
1answer
41 views

Slow performance on partitioned PostgreSQL table when using now() to filter

I have a PostgreSQL v10 table partitioned on a timestamp field: CREATE TABLE front.probes ( bucket_start timestamp NOT NULL DEFAULT now(), -- Extra fields here ... ) PARTITION BY RANGE (...
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 (...
3
votes
0answers
150 views

Restoring from PostgreSQL 9.5 dump to 10 is super slow

I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering ...
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
1answer
292 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"...
2
votes
1answer
3k views

psql gives Invalid data directory error

I have VM with ubuntu 18.04 server installed. I have installed postgres 10 from the repo. Default installation. I made some connection count related changes in postgres.conf and connection security ...