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
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
38 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
46 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
39 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 ...
1
vote
1answer
33 views

PostgreSQL - do we need to DROP the database and recreate it if we want to RESTORE it?

I worked with many different databases (SQL Server, Oracle, IBM db2) and for all of them if we have a backup it is just enough to execute RESTORE command in order to restore the data. I am beginner ...
2
votes
1answer
29 views

Change column datatype without rewrite when data remains unchanged

For example: timestamp(tz) to int8 date to int4 json to text The raw data is unchanged, and the only difference is how that data is handled. So it should be possible to change back and forth cleanly ...
0
votes
1answer
28 views

PostgreSQL - PUBLICATION tries to copy the data which already exists in another database?

I have database and table collection_city on one server. It has 21 rows. I have database and table collection_city on second server. It also has 21 rows. They both have this row: tinker=# table ...
0
votes
1answer
20 views

Understanding joined set-returning functions in the FROM list

I am using a construct like this very often: SELECT * FROM my_table, my_srf(my_column) for example here: CREATE TABLE my_table ( my_col text[] ); INSERT INTO my_table VALUES ('{a,b}'), ...
0
votes
1answer
30 views

Detect bottleneck of server via psycopg2

I have PostgreSQL superuser called "admin" which can connect to a PostgreSQL database. I am not managing the database server. I have no root-access to this machine. Sometimes the performance is not ...
0
votes
1answer
35 views

Moving a hard disk with PostgreSQL data folder to another server

I'm running a NAS server with an SD card mounted as / and RAID-1 storage mounted under /media/hdd. /etc folder is on SD card. PostgreSQL data folder is on the RAID-1 disk. /-+ +- etc | +- ...
-1
votes
1answer
30 views

Postgres: deadlock for CREATE PARTITION with INSERT

I have a partitioned table named logs with a lot of INSERT transactions. Every day I run a cron job with a query for CREATE PARTITION on this logs table. But it seems INSERT statements block the ...
1
vote
1answer
94 views

flatten JSON array into comma delimited list

I am trying to construct a SELECT statement which will take a JSONB column in the following format: { "Cities": [ { "Name": "Atlanta" }, { "Name": "London" }, { ...
0
votes
1answer
25 views

PostgreSQL - how to list all UNIQUE indexes for specific database?

I would like to get a list of all UNIQUE indexes (for unique column combinations) for specific database in PostgreSQL. I searched but could not find the query for this. I found these two queries. ...
1
vote
1answer
28 views

Is it necessary to ANALYZE a table after an index has been created?

In my transaction, I am creating a temporary table: create temporary table x on commit drop as select ... I also add an index on that table: create index on x(some_column); Is it now necessary to ...
1
vote
1answer
31 views

Postgresql Vacuum and replication

I have an architecture of cascading replication with 3 postgresql db Because the master is becoming too big (~ 170Gig), I wanted to run a "cleaning" job over the week-end that would do multiple ...
0
votes
1answer
23 views

How can I setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group?

I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees". The goal: To setup ...
0
votes
1answer
41 views

SELECT optimization in Postgres 10

I want to select last unique rows based on time, so: SELECT DISTINCT ON (track) * FROM eco.tracks WHERE id > (SELECT id FROM eco.tracks WHERE time_track < ((SELECT time_track FROM eco....
0
votes
1answer
98 views

Postgres Logical Replication - “replication slot ”mysub“ was not created in this database”

I have two database servers 192.168.150.210 and 192.168.150.220 I created publication and subscription successfully for logical replication. postgresql.conf file is modified on both servers. ...
0
votes
1answer
36 views

How is a table locked regarding UPDATE-SELECT queries in PostgreSQL?

Is it possible to combine these UPDATE queries? UPDATE myTable1 SET fld1 = myTable2.value FROM myTable2 WHERE myTable1.id = myTable2.id AND myTable2.style = 45; UPDATE myTable1 SET fld2 = myTable2....
0
votes
1answer
40 views

PostgreSQL Trigger to keep track of table changes

I am trying to create a trigger (Postgres 9.6) to track changes made to a table. This is my approach: CREATE OR REPLACE FUNCTION taxon_history() RETURNS trigger AS $BODY$ BEGIN IF TG_OP = '...
0
votes
1answer
26 views

Cannot drop materialized view privileges from user

Legacy database to me, pg version 9.6.12 running on a linux ami. I've a user ('example_user') that I would like to drop. I've confirmed that 'example_user' owns no objects. However, when I run DROP ...
0
votes
1answer
57 views

Postgres - Vacuum doesn't complete on large/busy table

We have a decently active PG database hosted on AWS. We recently started getting notifications like the following: Transaction ID age reached 750 million. Autovacuum parameter values for [...
2
votes
2answers
37 views

Postgres query very slow when using index

So I've run into this issue with a slow Postgres query. I've added an index but it doesn't seem to help. It takes 8 minutes to get 100 records from a table with 2822968 records. Here's the query ...
1
vote
1answer
53 views

Constraint on multiple columns?

I have a table edges, which describes relations in a graph: CREATE TABLE IF NOT EXISTS edges ( src INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE ,tgt INT NOT NULL ...
0
votes
1answer
33 views

How to filter a value in json field type on Postgres 9.2?

My json field data is like this: {"active":true,"id":"xxxxxxxxxxxxxxxxx","settings":{"secret":"xxxxxxxxxxxxxxxxxxxxxxxxxx","token":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","expires":"2019-12-16 01:11:23"},...
0
votes
1answer
36 views

Can queries that users ORDER BY using date to sort data be non deterministic?

I have a table in my database where I store dates using the following standard: YYYY-MM-DD. As you can see, I'm not saving the hour. The first time I exported the same table to a csv file I got the ...
0
votes
1answer
64 views

wal_level both hot_standby and logical

I have a PostgreSQL setup with one master and three slaves. It's configured as streaming replication. Now, I want to replicate only a few tables from the master server to an analytic server using ...
1
vote
1answer
35 views

Is a 'block' and a 'page' the same thing in PostgreSQL?

I've seen many blogs and videos talking about Postgres 'blocks'. Are these the same as 'pages' in other database management system implementations?
0
votes
1answer
21 views

how to get column count in postgreSQL

i have a query below select emp_id, sum(case when leave_type = 'Planned' then 1 else 0 end) Planned, sum(case when leave_type = 'Not Informed' then 1 else 0 end) NotInformed, sum(case when ...
0
votes
1answer
34 views

Autovacuum is not running even after configuring it on

I am using aurora-postgresql version 9.6.8. and I have one main instance , one writer and one reader replica. I am looking for below information: How I can check on a particular instance when ...
0
votes
1answer
24 views

Date pattern regex doesn't match the records in PostgreSQL query?

I am working with a dataset in PostgreSQL which is quick messy. I have written a few regex operations to find date patterns in the records of a table. Since I don't have a generalized REGEX defined ...
0
votes
0answers
14 views

Postgresql to log errors caught within BEGIN/EXCEPTION/END blocks

I'd like to have errors caught within a BEGIN/EXCEPTION/END block logged. Here's what I have, compare 1. and 2. -- 1. Nothing will be logged CREATE OR REPLACE FUNCTION func() RETURNS VOID AS $$ ...
0
votes
0answers
10 views

how to get data from multi table with column json in postgresql?

I have a lot of tables like the following tables. main_table id |customer_id | tabl2_id | tabl1_subJson_id |... ---|------------|-----------|------------------|------ 1 | 1 | 1 | ...
0
votes
1answer
39 views

Design critique on a table to keep track of which person flipped a switch at what time

I created a table to keep track of which person flipped a switch at what time. It consists of: create table records ( name varchar not null , changed_at timestamp with time zone not null , ...
0
votes
1answer
12 views

Restructure create and delete events on rows into single table

Similar to this problem: Using start and end event logs to create a table/view containing spans between the times of each log. I have table that logs 'create' and 'delete' events for a resource that ...
0
votes
0answers
19 views

Select / remove nested JSON attributes postgresql

I have the following JSON structure in postgres. drop table if exists data cascade; create table json_test (data json); insert into json_test values ('{ "a": "1", "b": "2", "c": "3", "level2"...
0
votes
0answers
26 views

Use Thesaurus as Synonym Dictionary for FTS?

As I understood, the function of this dictionaries are the same, except that Synonyms Dict do not support operation with phrases. Is It OK to use one dictionary for thesaurus and phrases in ...
1
vote
1answer
128 views

Postgres 10 Query waits on IO: BufFileWrite causing failure to get new database connections

I have a query (attached with query plan) that is run every 5-7 seconds from every device that is running our dashboard (typically, 500+ devices at peak time). This query, at the outset looks like it ...
1
vote
3answers
143 views

PostgreSQL: difference between collations 'C' and 'C.UTF-8'

In PostgreSQL, what is the difference between collations C and C.UTF-8? Both show up in rows of pg_collation. Is it perhaps the case that C.UTF-8 is the same as C with encoding UTF-8 regardless or ...
0
votes
1answer
18 views

PostgreSQL Correlation Between “max_connections” and “numbackends”

I'm trying to figure out why the field numbackends in pg_stat_database never goes above 16 (and often stays the same even after a restart). I have max_connections set to 100 and ...
3
votes
1answer
53 views

Get base table from materialized view in postgresql

I use materialized view in postgresql. when retrieving the list of materialized view (select * from pg_matviews), in the "definition" column, there is a query building the materialized view. how do i ...
2
votes
2answers
27 views

Postgres database not using indexes

I have a SQL optimization question. Table Name: charts Total Records: +250 million Columns: timestamp(string), imported(boolean), .... Indexes: CREATE INDEX index_charts_on_timestamp ON public....
0
votes
0answers
13 views

on insert rows in a table how to update that inserted data in another table?

I'm trying to update large amount of rows (20,000) rows, so i created a temp table similar to this one. and i want to create a trigger on insertion or update and a function to take the inserted or ...
0
votes
0answers
22 views

Restoring Postgresql database on Windows if only “data” folder available?

Is is possible to restore a PostgreSQL database if all I have available is the "data" (C:\PostgreSQL\data in my case) folder of the server whose databases I want to restore.I know this folder contains ...
-4
votes
2answers
67 views

Why is this query not returning the right result? [closed]

I want to select/filter by 2 columns status and state from a table of 6 columns . I have written the query below to select cars that are unsold and either new or used but the filter returns both new ...
1
vote
1answer
15 views

Using plpgsql to remove all functions

I intended to drop all functions in all schemas within a certain database, however, after I executed the bash file containing the following code block, the functions are still there # use pg_dump and ...
0
votes
0answers
15 views

Postgres count the same join by different values

I've been trying to create a query that counts the number of different types of events associated with each entry in another table city job job_events id | name | state ...