Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

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
1
vote
1answer
9 views

postgres jsonb query performance is decreased by order by

I have a table with jsonb column. When I execute query below it evaluates quite fast. But then I need to add order by clause (created column is timestamp column). Execution time decreases by 1000 ...
1
vote
1answer
10 views

PostgreSQL 12: column proisagg does not exist

I created a PostGIS database using the following images here (i.e. 9.3-2.1, 11.0-2.5 and 12.0 tags), however I'm getting the following errors when I try to open the 'public' schema: An error has ...
0
votes
0answers
2 views

SQL - Find Results with MAXimum for given colum

I have a question on how to select best match in PostgreSQL for a given result/condition: I have a setup containing 3 tables. 1 is a result overview table with reliability and status| 2 is a ...
0
votes
0answers
12 views

Access .sql script files on other partition or hdd

I'm running a Linux Mint 19.1 - Tessa OS, which for you who might not know it is based on Ubuntu 18.04 - Bionic Beaver. On this I have installed postgresql 11 which work well. There is one problem ...
0
votes
0answers
4 views

PostgreSQL will stop logging when logrotate rotate postgresql log file

I have installed PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit And configured: log_filename = 'postgresql.log' But I would like to ...
0
votes
0answers
10 views

Calculate 12 months rolling / moving average, median, min, max, percentiles, etc. as single query in Postgres

My question has two parts: . Firstly, how to adjust the code below to calculate 12 month moving avg, median etc.? As is, the query will return a list of daily, weekly, monthly or annual values (...
0
votes
0answers
8 views

How to Rename all primary key constraints from all tables at once in PostgreSQL [on hold]

I have a database with multiple schemas where table constrains named differently on a similar table.
1
vote
1answer
12 views

Schema changes on sharded database

I have performance issues with one particulary large table, 500+ Million rows, 300Gb data, Postgres 10.5. It is already partitioned. I am working on optimising it here and there, but that is not ...
3
votes
3answers
213 views

Query slow when using function in the WHERE clause

This is fast (49ms): v_cpf_numerico := ext.uf_converte_numerico(new.nr_cpf); select cd_cliente into v_cd_cliente from central.cliente where nr_cpf_cnpj = v_cpf_numerico; This is slow (15 seconds): ...
0
votes
0answers
33 views

How protect Database stolen remotely by hacker [on hold]

Can a database be stolen remotely by some hacker if a backup was saved? Should I change my password to postgresql? Can't the police post trace who it was? How can I protect myself? Only by changing ...
0
votes
1answer
21 views

PosgreSQL query slow when sorting on indexed timestamp colum

I've the following issue: the table below: CREATE TABLE public.current_read ( id bigint NOT NULL DEFAULT nextval('current_read_id_seq'::regclass), cube_id bigint NOT NULL, end_time ...
0
votes
0answers
23 views

posgresql 11 can not start autovacuum

I want to use autovacuum for vacuum and analyze in PostgreSQL 11.5. I am trying to enable it on a dev server but I can't get it started. According to documentation : autovacuum = on ...
0
votes
1answer
32 views

Aurora PostgreSQL CPU usage stuck at 100% for 8 hours

One of our Aurora PostgreSQL database's(48 cores) CPU usage stuck at 100% for nearly 8 hours. These situation happened 3 times in the past 4 months. We are using AWS's business support plan, in the ...
0
votes
0answers
16 views

How to get a field value from SELECT in postgres loop function [on hold]

I have tried to execute a SELECT statement in the loop to get a field value from another schema, but i got none. DECLARE sql TEXT := ''; sch record; shared_client record; client_r record; ...
0
votes
1answer
16 views

postgres npgsql DDL statement success or failure

I am using npgsql with a postgres DB, writing a DB updater that will need to execute a series of (usually) DDL statements like "ALTER TABLE ADD COLUMN blah..." and the like. The simple way to do this ...
1
vote
2answers
54 views

Optimizing a query by adding an index

Here are my tables: accounts_company: - id ... accounts_companyview: - account_id: FK to company - viewer_id: FK to company shipments_address: - companyview_id: FK to companyview - is_shipper:...
1
vote
0answers
13 views

Does the Postgres 9.4.9 update fix MultiXactId “does no longer exist” in addition to “has not been created yet” errors?

Postgres 9.4.9 has a fix in it's release notes which reads (emphasis mine): Prevent possible failure when vacuuming multixact IDs in an installation that has been pg_upgrade'd from pre-9.3 (Andrew ...
0
votes
1answer
27 views

How to model multiple types of contacts?

I'm not sure if this can be called a polymorphic association - what is needed is to for each Message to have a Sender Contact and a Recipient Contact. Both Sender Contact and Recipient Contact could ...
-2
votes
0answers
25 views

Nested select statement work procedure [on hold]

I was wondering how nested select statement works. Let me be clear with an example(with an academic example). According to Database system concept of Silberschatz I am using an university database. ...
1
vote
2answers
51 views

Is it possible to write a function in postgresql which directly writes its parameters to a text file?

I have a text file which contains some rows of data,say 10 rows. Each row has 5 columns, each of the type varchar. Is it possible to write a function in postgresql, which takes its parameters as 5 ...
1
vote
1answer
27 views
+50

Does postgres single-user mode use postgresql.conf settings?

Does running postgres in single-user mode (--single argument) cause postgres to start and use the settings present in the postgresql.conf file in the data directory defined by the -D option? I am ...
0
votes
0answers
25 views

Postgresql v10.1 - How to fix ERROR: duplicate key value violates unique constraint

I'm not sure how this happened to this table but have an issue with ERROR: duplicate key value violates unique constraint trying to use AWS's DMS service to move a postgres v10.1 DB to RDS v10 The ...
0
votes
2answers
20 views

Select data from table join or UDPATE?

I have a question regarding PostgreSQL DB performance. I am given a CSV file with a lot of rows (let's say around 1 million rows) that represents updates to a database table (let's call it MY_TABLE). ...
0
votes
1answer
35 views

Can we copy data from 1 text file to 2 postgresql tables?

I have a text file with some data and I have two similar tables in my postgresql database. Is it possible to copy a part of the data present in the file, say first 10 lines, to the first table and the ...
1
vote
1answer
19 views

Postgresql Master/Slave Using Patroni

Can i promote a slave node to be a master for insert/update/delete using postgresql-patroni once the original master become down?
0
votes
1answer
14 views

Use plpgsql to count records selected for output AND output those records

My query SELECTs 1 row from table1, which has a number of fields plus one very long text field. It JOINs with a second table2, that may have many matching corresponding rows. I would like to be able ...
1
vote
2answers
25 views

How can I have an aggregate function in the WHERE clause?

Using PostgreSQL 9.3.24 Issue Just simple operation, but I don't know how to do that best simplest way. How can I display results only when sum of available_counts for same product_id is 0 and ...
0
votes
0answers
20 views

Can copy command in postgresql append data to a file? [duplicate]

I want to add some data from a postgresql table to a text file. But already some data is present in the file. I do not want to delete that data. Is it possible? The questions asked earlier were the ...
0
votes
1answer
13 views

how to take postgres server backup skipping some of databases?

I have PostgreSQL server version 9.5. Contains 20 Databases. I want take server backup skipping of some 10 databases. Am using pg_dumpall, but it was taking full server backup. thanks,
0
votes
2answers
42 views

SQL cannot add columns to query

I have the following query (PostgreSQL 9.5.19): with userlist as ( select a.ips, array_agg(t.super_user_id) as super_users from tmp_table_1 t left join unnest(t.ips) as a(ips) on ...
0
votes
0answers
9 views

Converting this Postgres query to sequelize [migrated]

Hi I'm trying to convert my query into a sequelize query but I haven't been able to do it, The query is: SELECT SUM(od."totalValue") AS total FROM "OrderDetails" AS od LEFT JOIN "Orders" AS o ON od....
0
votes
2answers
32 views

Can't see process progress, machine getting slow trying to import 500 million records

Working environment: PostgreSQL 12.0, Ubuntu 18.10, 4GB memory, i5-3230M CPU @ 2.60GHz × 4, Os 64 bits I'm trying to import a .csv file with more than 500 million records into a PostgreSQL db table, ...
0
votes
0answers
29 views

FTS is slower on tsvector than text

I tested the performance of searches on two columns : text, tsvector. Somehow it appears that tsvector is slower than text. Also, there's some incosistency with planning time and execution time. It ...
3
votes
1answer
117 views

Add banner motd to postgres database

I would like to present a banner motd (message of the day) to users that connect to a Postgresql database. Similar to cisco router and switch cli where you use: banner motd "Seien Sie bitte ...
0
votes
0answers
19 views

Postgres Java heap space error while selecting on a very small table [closed]

I have a very small table - below 4,000 rows, and 45 columns. When running a regular select, the table returns after a while - about 30 seconds. When adding a condition to the select, for example, ...
0
votes
1answer
20 views

how to create Anonymous function and how to call Anonymous in postgres? [closed]

Please Any one help the steps of create Anonymous function and how to call Anonymous functions in postgres. Thanks
1
vote
1answer
35 views

postgresql pg_dump out of memory

is it possible to limit somehow usage of resources for pg_dump? The problem is, whole DBase is 775GB and i have two very large tables: pg_largeobject table is – 390GB pg_largeobject statistics: ...
0
votes
1answer
14 views

When might a tsvector field pay for itself?

I've been experimenting with tsvector indexes for Full Text Search, and see that it's a common practice to generate an store a vector in a column of type tsvector. We're on Postgres 11.4, but I've ...
0
votes
0answers
17 views

Creating a custom thesaurus dictionary on RDS

We're deployed on RDS using Postgres 11.4, and I'm hoping that there is some way to define a custom thesaurus dictionary. I think the answer is a hard no, based on this StackOverflow thread and this ...
1
vote
2answers
32 views

How to get the oldest values over each id in a PostgreSQL table efficiently?

How can PostgreSQL return a list of the oldest timestamp values over a table of sensor id measurements? Let me explain the situation with a sample table: CREATE TABLE sensor_data( sensor_id INTEGER, ...
2
votes
1answer
39 views

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

The manual for Postgres 12 says: The following operations are always parallel restricted. Scans of common table expressions (CTEs). Scans of temporary tables. ... Further down on ...
1
vote
1answer
17 views

Is it safe to restart PostgreSQL if process terminated for some reason?

I am writing a docker-compose config with a container with PostgreSQL in it. docker-compose has a configuration options called restart. If I enable it then whenever the Postgres process stops it will ...
-1
votes
0answers
17 views

PostgreSQL : double entry board [closed]

I have a table of the type: col1 col2 col3 N1 A C1 N2 B C1 N3 A C3` How can we make a double entry array of the type under PostgreSQL ? col3 ( C1 C3`... (col2) ...
1
vote
1answer
16 views

Build a single JSON value from two jsonb columns in two different tables

I have two tables. Both have a jsonb column with an array of objects, each containing the key student_id. I am trying to build one aggregated JSON value from these jsonb columns. Lets say table A ...
0
votes
0answers
19 views

How to run chain_job in Postgres using PGagent [closed]

Please help to find out on how to run chain_jobs in PGAdmin using PGagent. I am finding it most difficult and this is most required for my job. (sys.dbms_scheduler.create_chain) in oracle sql
0
votes
1answer
33 views

Is DEFAULT NULL and 'nullable value without default' completely the same?

ALTER TABLE test_table ADD COLUMN a DEFAULT NULL; vs. ALTER TABLE test_table ADD COLUMN a; Both columns will set NULL if column a is not specified. As far as I know, if I add a column into a table ...
0
votes
0answers
14 views

Partioning a big table, or splitting it into seperate tables for speeding up queries (PostgresSQL 9) [closed]

At work we are using Postgres 9, and I currently have to optimise a page, which loads very slowly (like 70 seconds). This happens mainly due to the queries executed against the database and that ...
0
votes
2answers
19 views

Explain plan changes for insert vs select vs CTAS in Postgres11

We have a postgres DB on version 11.2. We are running the below query and the objective is to insert into a staging table. The number of records inserted are only 82. But the performance varies ...
0
votes
1answer
20 views

Function does not exist, but im pretty sure it does

So, i have a procedure.. create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp) as $$ begin update sensor set last_read = read_time where ...
1
vote
1answer
26 views

Postgres full text search on words, not lexemes

I've got a table with columns of text that I'd like to search by word, not lexeme. More to the point, that I'd like to have indexed by word rather than lexeme. We've got error dumps with a lot of code ...