Skip to main content

Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
31 votes
4 answers
47k 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 ...
Maxim Yefremov's user avatar
26 votes
1 answer
30k 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 ...
user606521's user avatar
  • 1,375
25 votes
2 answers
24k 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 ...
Basil Bourque's user avatar
19 votes
2 answers
7k 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 by ...
Basil Bourque's user avatar
19 votes
1 answer
58k views

How do I cleanup PostgreSQL's WAL?

What is the correct way to cleanup PostgreSQL's WAL? I have a database with more than 100 GB and it has around 600 GB in pg_wal. Also I have 2 logical replications set up. Primary and replicas are ...
Gabriel Weich's user avatar
16 votes
3 answers
14k 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 ...
Evan Carroll's user avatar
  • 63.6k
16 votes
2 answers
22k views

What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer) [duplicate]

I'm at a crossroads where I need to decide if I'm going to stick with bigserial as my primary key, or change to uuid (non-auto-generating—my API server will generate the ID using uuid v4 and insert it)...
ffxsam's user avatar
  • 271
14 votes
1 answer
11k 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 ...
Jelly Orns's user avatar
13 votes
3 answers
5k 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: ...
Metropolis's user avatar
13 votes
1 answer
3k 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 ...
Paul Draper's user avatar
12 votes
2 answers
14k 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,...
OrangeDog's user avatar
  • 338
12 votes
1 answer
22k 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 ...
David's user avatar
  • 123
11 votes
1 answer
15k 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 ...
tinlyx's user avatar
  • 3,590
9 votes
1 answer
37k 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 ...
Capan's user avatar
  • 193
9 votes
1 answer
5k 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 ...
wjozsi's user avatar
  • 171
8 votes
2 answers
12k views

Update all values for given key nested in JSON array of objects

I have a jsonb column data in a Postgres table my_table. It contains the following data: [ {"id":"1","status":"test status1","updatedAt":"1571145003"}, {"id":"2","status":"test status2","...
kishram's user avatar
  • 83
8 votes
1 answer
13k 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 ...
ivarec's user avatar
  • 229
8 votes
2 answers
11k 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 ...
Basil Bourque's user avatar
8 votes
1 answer
6k 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 ...
BillRobertson42's user avatar
8 votes
2 answers
10k 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 ...
ahron's user avatar
  • 813
8 votes
1 answer
3k 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 ...
Ionut Popescu's user avatar
8 votes
3 answers
11k views

Why is there no max(uuid)/min(uuid) function?

Why can I use a UUID to sort rows: SELECT uuid_nil() ORDER BY 1; But I cannot compute the maximum value: SELECT max(uuid_nil()); [42883] ERROR: function max(uuid) does not exist Hint: No function ...
xehpuk's user avatar
  • 307
8 votes
1 answer
4k 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 ...
Basil Bourque's user avatar
7 votes
2 answers
26k 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 ...
Dave Chambers's user avatar
7 votes
2 answers
22k views

Postgres: What could cause the error "cannot call populate_composite on a scalar"?

I have a column of type JSONB, where each row contains a JSON with an array of objects, eg: [ { "grade": "4.44/5", "endYear": 2011, "startYear": 2006, "userId": "...
Brandon's user avatar
  • 205
7 votes
4 answers
12k views

json_object_agg errors on null in field name

PostgreSQL versions: my local install 11.3 and the below fiddle is on 10.0. Both behave the same. I have a schema of pages, each page has sections and each section can have different kind of ...
Tim's user avatar
  • 247
7 votes
1 answer
29k views

Error when creating a stored procedure in postgreSQL-10 [closed]

I couldn't get my first stored procedure to work, so I copied this example from the manual: CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL AS $$ INSERT INTO tbl VALUES (a); INSERT ...
Granny Aching's user avatar
7 votes
2 answers
3k 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 ...
danwoz's user avatar
  • 73
7 votes
1 answer
9k 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-...
Basil Bourque's user avatar
6 votes
2 answers
3k views

AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?

I'm using AWS Aurora PostgreSQL Serverless with autoscaling. It appears as though scaling clears the shared buffer, so right when we want to crank out the performance, we are forced to face-plant with ...
Brandon's user avatar
  • 163
6 votes
3 answers
9k views

How to prevent asking for password when creating new database in PostgreSQL 10?

I use the following batch file to create a new database in PostgreSQL 10. echo off cd "C:\Program Files (x86)\PostgreSQL\10\bin" createdb -h localhost -p 5432 -U postgres myDB But it asks to enter ...
Codename K's user avatar
6 votes
1 answer
9k 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"...
GeekJock's user avatar
  • 115
6 votes
1 answer
12k views

pg_resetxlog command not found

I can't find pg_resetxlog under /usr/bin. postgres (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1) cd /usr/bin postgres@node-1:/usr/bin$ ./pg_resetxlog -su: ./pg_resetxlog: No such file or directory
CJ Chang's user avatar
  • 285
6 votes
1 answer
5k 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 ...
ahron's user avatar
  • 813
6 votes
1 answer
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-...
tinlyx's user avatar
  • 3,590
6 votes
1 answer
916 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 ...
Peter Vandivier's user avatar
6 votes
2 answers
2k 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, ...
ivarec's user avatar
  • 229
6 votes
3 answers
946 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 ...
Wildcard's user avatar
  • 588
6 votes
1 answer
4k 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 ...
OscarAkaElvis's user avatar
6 votes
0 answers
694 views

Postgres 10 Logical Replication without truncating existing table [duplicate]

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 ...
RedHack's user avatar
  • 161
5 votes
3 answers
14k 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 ...
gagan singh's user avatar
5 votes
1 answer
21k 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 ...
Ryu's user avatar
  • 53
5 votes
2 answers
484 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 ...
Ortwin Gentz's user avatar
5 votes
1 answer
5k views

Is there a way to speed up a DISTINCT query?

I have a table t in a database (PostgreSQL 10.4): \d t; Table "public.t" Column | Type | Collation | Nullable | Default ----------+------------------------+------...
lugger1's user avatar
  • 153
5 votes
2 answers
16k 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 ...
Imanol Y.'s user avatar
  • 785
5 votes
1 answer
2k views

Safe to use the same temporary table name in parallel transactons?

Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
Tim's user avatar
  • 247
5 votes
1 answer
571 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 ...
karldw's user avatar
  • 153
5 votes
1 answer
9k views

How to sum (aggregate) all the values in a key/value JSONB?

in Postgres 10 I have the following table: CREATE TABLE testtable ( id int PRIMARY KEY, qty jsonb ); INSERT INTO testtable (id,qty) VALUES ( 1, '{"2018-08-01": 10, "2018-08-11": 20, "...
TheStranger's user avatar
5 votes
1 answer
5k 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 ...
RubyRedGrapefruit's user avatar
5 votes
2 answers
3k 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 ...
Whee's user avatar
  • 189

1
2 3 4 5
8