Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
13
votes
1answer
2k 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
758 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
11k 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 ...
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 ...
10
votes
1answer
6k 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 ...
9
votes
3answers
5k 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 ...
9
votes
1answer
6k views

What is the data type of the ‘ctid’ system column in Postgres?

The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns. That page mentions that oid values “are 32-bit quantities”. And that page says the same about ...
7
votes
2answers
2k 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
310 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
1answer
4k 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 ...
6
votes
1answer
2k 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 ...
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
809 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
141 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
269 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
8k 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 ...
5
votes
1answer
2k 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 ...
5
votes
1answer
6k 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 ...
5
votes
1answer
236 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
757 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
2answers
3k 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 ...
5
votes
3answers
44 views

Allow for dynamic result sets from plpgsql function by filtering based on an array passed in?

I think I've avoided the XY problem here, as I'm laying out my solutions to the real underlying problem (summarizing multiple tables in a dynamic fashion) and I'm only asking about the one final part ...
5
votes
0answers
478 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
2answers
5k 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 ...
4
votes
2answers
9k 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 ...
4
votes
1answer
3k views

How to get information about the sequence behind the new Identity Column in Postgres 10? (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY)

Postgres 10 brings an implementation of the SQL standard GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY feature, loosely known as identity column. This feature supplants Postgres’ own SERIAL pseudo-...
4
votes
1answer
1k 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,...
4
votes
1answer
3k 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
4k 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
630 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
2k 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
2k 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
761 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
2k 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
1k 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
69 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
175 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
7k 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 ...
3
votes
2answers
719 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
1answer
5k 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 ...
3
votes
1answer
1k 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"...
3
votes
1answer
817 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
2answers
79 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
5k 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
8k 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
2answers
1k views

Postgres jsonb vs composite type performance differences

What considerations are involved in choosing between a jsonb column and a composite type column of the same structure? For example, consider a column like that used in the Postgres documentation: ...
3
votes
1answer
303 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 ...
3
votes
3answers
227 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
7k 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
562 views

Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties?

I went overboard on CITEXT columns on a particularly large table in my application. I would like to back some of these out as it's confusing how to trigger lookups on the desired indexes. My question ...

1
2 3 4 5
7