Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
1answer
35 views

PostgreSQL CREATE TYPE using multiple relations?

I'm becoming familiar with PostgreSQL's ability to define a data type https://www.postgresql.org/docs/current/sql-createtype.html using CREATE TYPE syntax. It appears to me that this type does not ...
0
votes
0answers
42 views

PostgreSQL Race condition and Serializable

Hello I try to debug a tricky race condition in my app (golang, pq, sqlx, postgresql Debian 10.6-1.pgdg90+1). I have a table CREATE TABLE j ( id BIGSERIAL PRIMARY KEY NOT NULL, ...
0
votes
1answer
24 views

Parsing, Planning and Optimizing Prepared Statements

PostgreSQL (2nd Edition) by Douglas and Douglas notes: Use server-side procedures (triggers and functions) to perform common operations. A server-side procedure is parsed, planned and optimized the ...
0
votes
0answers
22 views

Change locale of Postgres server

I have a big Postgres server with 2 Tb of data that has been created with the wrong locale. How can I correct it, without recreating the whole server with pg_dump and pg_restore. It would take a ...
0
votes
1answer
11 views

How to know the locale that has been used to initialize a Postgres server?

I need it in order to create a new database with initdb with the same locale, otherwise pg_upgrade will fail.
0
votes
1answer
37 views

How to set order for constraints to be checked in Postgres

I have a table with 2 constrains like below. ALTER TABLE entity_instance ADD CONSTRAINT instance_name_uniq UNIQUE (tenant_id, name) ALTER TABLE entity_instance ADD CONSTRAINT instance_source_uniq ...
2
votes
0answers
29 views

Two constraints of the same name?

When I try to migrate a database from Postgres 9.0 to a new server using Postgres 10.8, I got the error from pg_restore which says pg_restore: [archiver (db)] could not execute query: ERROR: ...
1
vote
2answers
28 views

Multiple aggregates with cross (lateral) join

Following on from Combining array_agg and unnest, given this data: key | a | b | c ------------------ 1 | 0 | 1 | {1,2} 1 | 1 | 2 | {3} 1 | -1 | 3 | {2} 1 | 2 | 4 | {} Executing this ...
3
votes
1answer
32 views

What's the smartest way to find the scale of an unrestricted numeric type?

By laziness and convenience I integrated quite a lot of data from a partner in an unrestricted numeric type in my PostgreSQL database. However it now seems that depending of different shipments from ...
1
vote
2answers
31 views

Postgres \copy command into single column without delimiter

I am trying to import big fixed length files into Postgres and I am using the copy command and a staging table following this guide. The problem is that this files has a lot of different characters ...
0
votes
0answers
31 views

postgresql query performance issue with timestamp and order by filter

I have table definition as following. CREATE TABLE monmgr_pg.order_staged_events ( event_id varchar(64) NOT NULL, order_number varchar(40) NOT NULL, oms_order_key varchar(64) NOT NULL, ...
2
votes
1answer
51 views

PostgreSQL. Unstable execution time hugely exceeding planning time

PG version: 10.9 Schema: create table core_bundle ( id serial not null constraint core_bundle_pkey primary key, dt_add timestamp with time zone not null, dt_upd timestamp with time zone ...
1
vote
1answer
20 views

Isolation level serializable not working as expected?

I have this table create table testing( c1 text, c2 text); I open two transactions simultaneously. t0 t1.. denote series of time snapshots in increasing order Transaction 1 BEGIN; -- t0 SET ...
0
votes
1answer
15 views

Trigger on PostGIS line layer occurs twice

Using this tutorial, I've established a SQL trigger in order to track changes on a PostGIS layer. It works great for a point layer. However, using the script below, it causes problems with line layer, ...
1
vote
1answer
37 views

Duplicate rows with primary key constraint in PostgreSQL 10.6

I have a table that's had CONSTRAINT table_pkey PRIMARY KEY (id) since setup. However, I recently found that I have around 100 duplicate rows (I ran select count(*) from (select count(*) from table ...
1
vote
1answer
32 views

How to run EXPLAIN ANALYZE and get results of the query?

I have written a custom PostgreSQL client and I want to add a function that would automatically capture query execution time. The current executionTime value in Slonik results records the total query ...
1
vote
1answer
91 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,...
0
votes
1answer
47 views

`cube` query performance issues with ordering

I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the But ...
1
vote
1answer
18 views

INSERT violating unique key constraint on primary key

on a table with primary key 'id' with default value nextval(), inserting a row throws the error Query 1 ERROR: ERROR: duplicate key value violates unique constraint "table_pkey" DETAIL: Key (id)=(6)...
1
vote
1answer
25 views

Use jsonb array inside where IN or ANY in PostgreSQL

I want to select all products that have active shipments (those have one or more shipment_ids in shipments table). My product table has a jsonb column called data like this: { "name": "foo", ...
4
votes
1answer
304 views

Why is this delete operation slower after a foreign key relationship has been added?

Imagine the following table about cat owners. drop table if exists owners cascade; create table owners( id bigint primary key generated always as identity , name text not null ) ; insert ...
0
votes
1answer
29 views

How to increase timing performace of a SQL query

I'm using postgresql 9.6 on the 16GB Ubuntu-PC with 4 cores & SSD with following params: max_connections = 200 shared_buffers = 6GB work_mem = 256MB ...
0
votes
1answer
34 views

why is this SQL query failing to run?

SELECT gs.h hours, severity."alarmSeverity", COUNT(t1.eventtime) FROM GENERATE_SERIES(0, 23, 1) gs(h) CROSS JOIN (SELECT DISTINCT t1."alarmSeverity" FROM (SELECT eventdate,...
-2
votes
1answer
119 views

syntax error at or near “AS” [closed]

I have a program that takes in one or more than one table names, from a list stored in a reference table and runs a SELECT query on it. so I retrieve the table names and insert it into the SELECT ...
0
votes
1answer
20 views

CTE vs Function/trigger performance in Postgresql

I have a very long CTE query that I have shortened here for readability. But it tries to insert state, county, city, zipcode and street into their own tables and then insert their ID for inserting the ...
0
votes
4answers
54 views

SQL - How to return first encountered row that matches specific criteria

I have a table that has the following format: id | price | category --------------------- 1 22 hardware 2 11 software 3 null hardware 4 null hardware 5 55 software 6 null ...
0
votes
1answer
22 views

Select total count and percent

Say have table like: user_uuid | value | state ------------------+------+------- uuid_1 | 111 | t uuid_1 | 111 | f uuid_1 | 111 | f uuid_1 | 111 | f uuid_2 | 333 | t uuid_2 | ...
0
votes
1answer
50 views

Postgres column type unknown

I am trying to port a database where there is a table with a column of type unknown. pg_restore raised the error pg_restore: [archiver (db)] could not execute query: ERROR: column "color" has ...
0
votes
1answer
32 views

POSTGRESQL: Refresh the materialized view which effect when there are change based on columns

I have a database in postgresql. There are several materialized view (matview) in this database. In each table, i have a trigger and have a function for trigger to refresh the matview, and it works ...
1
vote
1answer
15 views

Getting 2 columns from sets of 2 rows in Postgresql

So I have my data organized as: id(bigint) | report_item (varchar) | report_value (numeric) | stock_id (uuid) | date 1 | netIncome | 100 | stockid1 | 2019-05-01 2 | totalRevenue |1000 | stockid1 | ...
0
votes
1answer
25 views

PostgreSQL: relation would be inherited from more than once

Assume I have a schema which initially have several yearly sliced data like: schema: land_price - land_price_1990 - land_price_1991 ... - land_price_2010 Now I would like to create a parent ...
0
votes
0answers
15 views

does aliasing unions save processing time?

suppose I have to union some tables and then join it with some other tables. does creating an alias cache the result of the union somewhere, or is it a convenience thing?
0
votes
1answer
11 views

How to move a PostgresSQL/ PostGIS to another drive

Firstly I'd like to say I know little about databases (GIS background) and I might have bluffed it all so far. But we wanted to set up a database for use with QGIS. My understanding was that the ...
0
votes
2answers
36 views

Postgres select using lateral

I'm after running a query with a subquery (perhaps inefficient) that using a regular expression pattern it can return how many instances of a characters are included with a given record. In doing so, ...
0
votes
1answer
15 views

Posgresql - deadlock on non related queries

I am using Postgresql 11. I have found an error similar to following in my logs: org.postgresql.util.PSQLException: ERROR: deadlock detected Detail: Process 332091 waits for ShareLock on ...
1
vote
1answer
43 views

Select results in one table from multiple rows in subquery

I am looking for how to select multiple rows from one table based on the results from a subquery in PostgreSQL (10). I am not very familiar with advanced database concepts, so I might be missing ...
0
votes
1answer
21 views

Query returns results on pgAdmin running on remote client but not directly through psql on db server

When I query my database using a role in pgAdmin on a remote client, I get the results of the query. However if I run the same query directly on the db server with the same role , I don't see the ...
2
votes
1answer
28 views

Understanding Query Plan’s “width”

The Douglas and Douglas PostgreSQL book includes the following EXPLAIN result: The first step of the query, the Seq Scan, shows a width of 1917. However the last step of the query, the Sort, has a ...
0
votes
1answer
42 views

Postgres index not being used

Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
-1
votes
2answers
61 views

In Postgres txid_current() should return biginteger but instead an integer is returned

In Postgres 11, according to documentation, txid_current() should return biginteger but I'm getting small integer that doesn't look like is extended by epoch counter. My txid looks like this: 1123 "...
0
votes
2answers
26 views

How to delete data from both tables or least one of them, when using “delete from … using … where” in Postgresql?

I have this sql script for my Postgresql db: DELETE FROM table1 USING table2 WHERE table2.frg_table1_id = table1.id AND table1.id = $1 I want to it to always delete data from table1 if the ...
1
vote
1answer
72 views

In Postgres - when transaction IDs wraparound what are their numbers? [duplicate]

In Postgres 11, when transaction IDs pass the 4 billion wraparound mark, assuming that the database is properly autovacuumed, what will be the next txid returned by txid_current()? Will the returned ...
1
vote
1answer
37 views

Postgres choosing a filter instead of index cond when OR is involved

I have a table that daily gets about 20 million records added to it, and I'm trying to paginate through it to let people get access to all the data in it, but the query time has to be "decent" (in my ...
0
votes
1answer
15 views

How to group time intervals by order of magnitude in Postgres?

I have a bunch of time intervals that I would like to analyze. (Thousands of them.) They range from 0 seconds up to several thousand days, but most of them are less than ten minutes. I would like ...
0
votes
1answer
41 views

What caused queries to execute repeatedly with different pids?

Recently I have noticed some of the queries are repeatedly being executed with new pids, this results in 100% CPU utilization. So I queried my postgres to see what's going on, with: SELECT pid, ...
0
votes
1answer
29 views

PostgreSQL >= 10 does not generate WAL if there are no transactions

I noticed that PostgreSQL >= 10 does not generate WAL if there are no transactions. In previous versions, PostgreSQL generated WAL at the expiration of archive_timout, because it wrote some empty ...
0
votes
1answer
56 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 ...
1
vote
1answer
35 views

will this insert query take too long to run? [closed]

I am bulk inserting from a node server to a psql database by constructing the query like let query = `INSERT INTO ${table} (timestamp, lattitude, longitude, type) VALUES `; locationData.forEach(...
0
votes
1answer
27 views

Three WHERE slow query down

I have a table adjacency, which stores information about connected lines and their angle to each other is build by CREATE TABLE public.adjacency ( currentedge integer, currentgeom geometry(...
0
votes
1answer
41 views

PostgreSQL logical replication slave - shouldn't it be read-only and available only for subscription updates?

I have database and table collection_city on one server. It has 21 rows. I have database and table collection_city on a second server. It also has 21 rows. They both have this row: tinker=# table ...