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
2answers
32 views

copy entire PostgreSQL cluster to another (identical) server

I'm looking to clone a PostgreSQL 10 cluster from server1 to server2, which is running the same Postgres version on identical hardware. The purpose is load-balancing and HA. Things to keep in mind: ...
1
vote
1answer
27 views

Why disable Postgres Autovacuum?

I've got a task to work with a 9.6 Postgres DB, and for some reason Autovacuum is disabled. The database was extremely slow, and after a manual VACUUM it could breathe again. The point is, why ...
1
vote
1answer
16 views

How I can find out the foreign wrapper that a foreign table belongs to?

I have found that the following query lists all my foreign tables in my PostgreSQL database: select * from information_schema."tables" where table_type='FOREIGN TABLE'; Now I want to find out the ...
1
vote
0answers
15 views

How to get db “state version seq” in postgres?

I need to run a script on every database change. The script works on diffs, so I need to know if I handled the previous state (the handler script could be down for some time for example). One way I ...
0
votes
1answer
27 views

How to Calculate Month over Month User Retention based on already active users (not based on user signup date)?

I have a Table which tracks User activity (i-e user started a session at what time ?). This table contains the data from Dec 2018 Till now. I need to calculate monthly retention (not based on signup ...
0
votes
0answers
22 views

Single metadata base table in Postgres

I am a fan of adding timestamps to tables in the form of created_at, updated_at and deleted_at. But it can be tedious to add these fields to every table. I was wondering, is it a bad design decision ...
0
votes
0answers
16 views

How to achieve this Nested Complex postgresql schema CRUD?

I'm trying to achieve this complex schema. { relation1_id : Number, ... ... item_caption: [ { caption: '', subcaption: '', products: [ { ...
0
votes
1answer
23 views

pg replication connection not returns wal events to java code client , it hangs some times

Describe the issue We are facing issue of pg replication slot not listen database wal events. Once pg database restarted and we attach Driver Version: 42.2.6 Java Version: JDK1.8 OS Version: Window ...
0
votes
1answer
20 views

postgres function definition not saving top whitespace

For reference I am running postgres 9.6 . I'd like to write functions inside of postgres where the top of the function saves. create or replace function apqp.apqp_list_gentex_tool_report_getlist(...
0
votes
1answer
14 views

How to reset object permissions to default?

I've just been added to a project using Postgresql 11.4 where database security has been an afterthought. As part of tightening things up, I've used ALTER DEFAULT PERMISSIONS to set sane ACLs on any ...
0
votes
1answer
20 views

pgadmin: can't insert jsonb[] values with strings

I have jsonb[] column and it allows me to insert some nested arrays, but only with number values like {{1,2,3}, {4,5,6}, {7,8,9}}. I'm not talking about inserting some nested objects like {{"one": "...
0
votes
1answer
19 views

How can I see what query is emitted by the query parser?

Consider this snippet: create table tbl_1(name text); create table tbl_2(name text); create function my_insert(_name text) returns text language sql as $$ insert into tbl_2(name) values (...
0
votes
1answer
43 views

Postgresql, escape character in string_agg function

Question, can I add escape character \ just for fields param_name and value for this? string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params", Result is. {name:"...
0
votes
2answers
39 views

PostgreSQL - getting Too many client repeatedly

I am running PSQL with PostGis extension. After the start of server, after about 3 weeks, I start getting fatal errors for connections with "too many clients already". When I restart the server, ...
0
votes
0answers
13 views

Postgresql, export to xml, <row> rename

When exporting to xml, I have this output. "<product><table xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns=""test""> <row> ... </row> <row> ... etc ...
0
votes
0answers
16 views

Reader rights by default in Postgres

My question is about database permissions and rights in Postgres. How can I create reader rights by default when objects are uploaded into PGadmin 4 schemata? I created the following schema in ...
0
votes
2answers
42 views

How to hide the partition child tables in PostgreSQL?

I am creating partition tables in postgreSQL. And the partition is on date column so its nearly high in number to show in schema. I want to show only my parent table for other users. I tried many ...
0
votes
0answers
18 views

How to prevent regular users to backup database, in Postgresql

How can I prevent/restrict regular users, that do have select rights granted on tables etc, to perform backup of Postgres database? Is there a way to enable users to connect, select, update etc. ...
0
votes
1answer
20 views

How to list all constraints of a table in PostgreSQL by schema?

Yes, I'm aware there is a question very similar to mine, the difference is that I want all constraints, including not null, which no answer is able to do.
1
vote
1answer
16 views

What does 'fully determines' mean in the context of postgres functional dependency statistics?

I am following the example of how to use 'create statistics' in postgresql from this page. CREATE TABLE tbl ( col1 int, col2 int ); INSERT INTO tbl SELECT i/...
0
votes
2answers
20 views

Postgresql - roles without linux accounts?

Basic question: what I'm reading describes how to create linux users in parallel with postgresql roles. However, I don't see the value in creating any of these users in linux - I just want to create ...
3
votes
0answers
32 views

How to optimize this recursive postgresql query?

I've been searching a lot on this topic but I'm stuck at optimizing a query. I've created a complete dbfiddle to see the problem, if you increase the amount of inserts for the comments table you'll ...
1
vote
1answer
18 views

using pg_prewarm after an ETL load has finished - worth it?

I have a database with many small reference data tables and a few large time series tables. The reference data tables can easily fit into memory. I have various ETL jobs that load data, mainly by left ...
0
votes
1answer
15 views

How to create a trigger that checks if a value in 2 fields is present and if it is change it to something else

I am new to db, and trying to create a trigger that checks some fields in a table for a value and if the value is present than it updates it to something else. These are my thoughts so far. CREATE ...
0
votes
1answer
22 views

How to increase index row maximum size in Postgresql?

I want to perform KNN on 512-dimensional vector using the solution suggested here. I couldn't create table with 512-d cube array. Solved by changing the CUBE_MAX_DIM in the source code. Couldn't ...
0
votes
2answers
57 views

Table design and query Optimization: Query to find the suitable work from a list of work items

I have table with jsonb column as below CREATE TABLE work ( id SERIAL NOT NULL, work_data JSONB ); sample data is as follows: 100 {"work_id": [7245, 3991, 3358, 1028]} ...
1
vote
1answer
24 views

Select in a PostgreSQL function

I'm trying to do my first Function in PostgreSQL, but I can't to do it. I was write this piece of code CREATE OR REPLACE FUNCTION getCars () RETURNS TABLE ( car_name VARCHAR, id_car ...
0
votes
2answers
26 views

Replacing Postgres User

I'm new to postgres. It might sound a silly question, due to some security requirement I need to remove default superuser 'postgres' and replace it with any other super user (for example:abc) created ...
0
votes
1answer
62 views

Error while restoring the data in postgres using pg_restore

I am getting the error of: pg_restore: [archiver (db)] Error while INITIALIZING: pg_restore: [archiver (db)] could not execute query: ERROR: unrecognized configuration parameter "...
0
votes
1answer
24 views

Postgresql, export to json

Version: PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1). Question: Is possible export to json file? Something like csv export. COPY ( SQL ) TO stdout DELIMITER '"' csv; Thanks. Update: Or can I add ...
0
votes
2answers
35 views

Migrate large postgresql database (windows -> linux) [closed]

I have a "large" PostgreSQL (9.6 with timescale plugin) database (about 1Tb). And would like to migrate it to a Linux machine. The naive approach would be pg_dump and pg_restore. (Not sure how long ...
0
votes
1answer
22 views

Exclusion constraint on recursive foreign key (on the example of a Tree)

I have the table with recursive foreign key. It's just hierarchical tree structure: CREATE TABLE tree ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, value ...
0
votes
0answers
23 views

PostgreSQL does not starting up after power failure

Could not start PostgreSQL after power failure: FATAL: could not find tuple for opclass 1 PostgreSQL version: root@ubuntu:/usr/lib/postgresql/9.6/bin# ./postgres -V postgres (PostgreSQL) 9.6.1 OS ...
2
votes
1answer
16 views

Identify the version of a collation from ICU in Postgres

Postgres 10 and later incorporates the International Components for Unicode (ICU) library for text-handling and other internationalization issues. Changes happen to human languages, such as sorting ...
1
vote
0answers
25 views

Postgresql 11 slow vacuum

I have a local install of postgresql 11 on my Windows 10 desktop (for my machine learning PhD). The data folder is 76gb (~15 tables, the largest has 100m rows). The database sits on a Samsung 970 EVO ...
0
votes
0answers
39 views

Is there any way to query postgres JSON/JSONB columns with ilike and indexing?

I have a table called user_data where I have JSON column called data which can have any key that can be given by application users. Here I wanted to do ilike query with using proper index. Since keys ...
1
vote
1answer
41 views

Postgres query plan shows sequential scan instead of a index scan with on the fly data

My use case is I am trying to select rows based on a list of known text values. For example, I have known text values as 'Frasier' and 'Cheers' and I want to select some rows that match the values. ...
0
votes
2answers
40 views

How to get the current row in postgres function?

I have a table where I have a column called event varchar(255) and user. An event column is a set of strings which tells me in what state was the user when the event was received. I want to count how ...
2
votes
0answers
50 views

Optimizing a paginated query with various filters and dynamic sorting

Introduction Imagine a (fairly advanced) ToDo app in which items are catogorized into lists (which can be temporarily deactivated), have custom additional attributes, can be assigned to a user and ...
1
vote
2answers
47 views

How to handle concurrency during an INSERT-SELECT cycle?

The application should limit each user's access to a certain resource to a maximum number of accesses every 24 hours. To keep track of those, we have the following table: CREATE TABLE accesses ( ...
0
votes
0answers
22 views

Regarding Postgresql column length [migrated]

I am investigating an issue and the error log shows an error like this one: Error occurred (psycopg2.DataError) value too long for type character varying(8192) I could not find what causes the ...
0
votes
1answer
33 views

Create gist index on cube array with 512 dimensions

I want to perform nearest neighbors search on db with 5000 entries of cube array with 512 dimensions. I referred to this answer and used docker image of postgresql with cube array limit set to 2048 ...
0
votes
1answer
15 views

List database CONNECT privileges on PostgreSQL

How can I view database-level privileges like CONNECT of users? I found 2 ways already, but neither of them is perfect: Use function has_database_privilege(). This works, but I have to run it ...
0
votes
0answers
26 views

How to reset email/password pgadmin?

I'm running pgadmin4.3 and I do not remember what login/password I used. Tried to reinstall but it not worked. How I can reset it?
0
votes
1answer
16 views

Postgresql and jsonb: perform a like over all values of an array

Jsonb seems great but I can't figure out how to perform a partial search (like) over an array. In my case it's an array of email: ["email1@foo.com", "email2@foo.com", "email3@foo.com"] I want to be ...
6
votes
2answers
438 views

NULL value causes blank row in SELECT results for text concatenation

I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows. I get a blank line in the results: postgres=# SELECT ' (' || obj_id || ', ''' || ...
3
votes
1answer
47 views

Try catch equivalent in Postgres

Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.
0
votes
0answers
40 views

Difference in DELETE performance between physical (snapshot based) and logical (basebackup) clones

We have a relatively large PostgreSQL database (500G - 1TB) and want to perform a big migration task. The migration consists in a bunch of ALTER, UPDATE, DELETE, SELECT. We have performed this ...
0
votes
0answers
10 views

How can I connect to Denodo using Python Pandas and SQLAlchemy? [migrated]

I'm trying to connect to Denodo using Python SQLAlchemy, and create a DataFrame from a table. My environment is Cloudera Data Science Workbench. Can I use psycopg2 and the PostgreSQL connection string?...
0
votes
2answers
47 views

Postgresql, commas in string_agg function [closed]

Need FIELD like this, with ' and without , at the end of whole field. {name:'color',value:'red'},{name:'width',value:'300 ml'} Tried string_agg(distinct '{name:\'' || param_name || '\',value:\'' ||...