Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql]

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

2
votes
1answer
19 views

pg_commit_ts growing in postgres instance

I have a customer who is trying to set up bidirectional replication with logical replication publication/subcription model across two instances with the help of last_update_wins and reported that with ...
0
votes
0answers
14 views

Adding PostgreSQL data source to excel didn't ask for credentials other than server IP and database

I saw Excel supported using PostgreSQL as a data source. I'm used to using ODBC connections for Access so having direct Postgres support is exciting. When connecting I entered the Server IP and ...
1
vote
1answer
43 views

How do we get around PostgreSQL autovacuum taking a table-level lock ACCESS EXCLUSIVE on replicas?

We are running: user@primary/client-n:~$ psql -d database -c "SELECT version();" version --------------------------------------------...
0
votes
1answer
11 views

resuming server replication on PostgreSQL servers

I got one main server and other two servers are replicating from the main. I got full disk space on the main server (I have an archive_command that put wal archives into the /wal_archive/ directory). ...
0
votes
1answer
23 views

How do I achieve this in subquery

I have table A : genre collection horror 20 action 50 rom-com 30 sci-fi 90 and table B: MovieId genre performance 1 sci-fi Hit 2 rom-com Hit 3 sci-fi ...
-1
votes
3answers
56 views

Selecting Only one value in duplicate record

I have a result like the bwlow image. now, i want to select only wireline instead of all. So, how can i select the 1st row if the all columns are same except produtc_line No, where conditions as we ...
0
votes
1answer
19 views

Postgres looped table (start sequence from 1 and rewrite old rows)

I want table to contain logs and be looped, so when it came to last key in sequence it will start over from one and will rewrite old logs. I can do it if i mark sequence as cycle and do requests ...
0
votes
1answer
22 views

Best practices for storing multiple SQL tables per user (built from uploaded csv file?)

I have a few users who will be uploading a few types of predefined CSV files. I need to store these as sql tables to be able to run queries on this data for a particular user, however that means there'...
0
votes
1answer
34 views

Alter the values in a column

I have at hand the following table (for data migration): recvtimets | recvtime | fiwareservicepath | entityid | entitytype | attrname | attrtype | ...
0
votes
0answers
15 views

Postgres INSERT slows down over time until server is restarted

I have a Rails app backed by an Azure-managed Postgres database and I use NewRelic for monitoring. What I've noticed is that the INSERT performance degrades almost linearly over time - what started ...
0
votes
1answer
23 views

How to return when inserting into multiple tables as part of a views on inert rule

I am trying to create rules for my views to insert into multiple tables at once but I need to return the new row and I cannot work how to return from multiple insert statements. This rule works fine ...
1
vote
1answer
26 views

Configure Postgresql column capitalization with being able to query using different capitalization

I have an existing database that we need to replace from MySQL to PostgreSQL. The problem being that we currently have two applications that use the database (one to create and one to view the data) ...
0
votes
0answers
8 views

which python version is required to install plpython3u of which postgresql version?

I want to know if there is a reference table or trick to find out which python version is required by plpython3u of which version of postgresql . example python 3.4 is required for plpython3u of ...
0
votes
0answers
18 views

How to make more dynamic Postgres configuration?

I am running PostgreSQL 11 on EC2 instances that can be scaled up and down. Everytime an instance is scaled his memory specs can change. If in my configuration I have for example something like this: ...
1
vote
1answer
37 views

Postgres Column Level Security

I have a schema snow within a database testdb. I'm trying to achieve column level security for my users. My users are boss, manager and intern. I set all of this table up as the superuser me. They are ...
1
vote
2answers
28 views

PostgreSQL partial index not used depending on boolean comparison

I need help understanding why a partial index would be used in one query and would not in second one which is effectively the same. I have the following table: Column | Type ...
0
votes
0answers
20 views

How to execute pl/pgsql do block in a bash script? [duplicate]

I want to migrate my databases to a new server, and some of them contain views, which I don't want to migrate. So I wrote a bash script that roughly looks like this # Some commands to migrate the ...
1
vote
0answers
23 views

How to troubleshoot occassionally slow insert?

Problem: I'm running a pretty simple test while learning about partitioned tables. Simply inserting rows quickly into a table. Usually this takes 3ms or less. Occasionally it takes 10-50ms. ...
1
vote
1answer
33 views

30 days rolling sum as base for fee calculation. Faster solution

I need to calculate PayPal fees or an online shop to show an overview table with the fees per day. I came up with a solution playing this over several views, but it's dead slow. I takes more than a ...
0
votes
1answer
43 views

PostgreSQL 10 Optimizing slow query performance with aggregate function

I have two tables person and tag. person has an bigint[] column containing ids of tags. I want to write a query which fetches all tags including the count of the tagged persons. So far I have the ...
0
votes
0answers
3 views

Postgres break table columns into multiple tables having unique rows

I have this table called transactions. It has two columns id and report_id. id is primary key. I want to create two new tables transaction_ids and report_ids both with just one column id. Both these ...
0
votes
1answer
15 views

How to summate weekly totals from a postgres date range type per user

I have the following table: Schedules +----+---------+------------+-------+--------------------------+ | id | user_id | project_id | hours | date_range | +----+---------+------------+---...
0
votes
1answer
16 views

ERROR: “…” is not a table or materialized view, when creating index in PostgreSQL

I got an error from PostgreSQL (11) when my code tries to CREATE INDEX on a view: ERROR: "_blk_l" is not a table or materialized view To avoid the error, I wanted to check if a regclass object can ...
0
votes
1answer
23 views

Postgresql update a partitioned table, where update affects multiple partitions

If I have a partitioned table partitioned on PKey column, can I update (or merge etc) the table like below (or more complex jonining to another table) which will cause multiple partitions to be ...
0
votes
1answer
16 views

ALTER TABLE SET LOGGED locked by autovacuum

metrics is an unlogged table that has around 3M rows, I have just executed the ALTER TABLE metrics SET LOGGED to keep this data logged. But it seems to be locked by some autovacuum running process. ...
1
vote
1answer
27 views

Making sure balance field is always consistently updated with triggers

This is my database schema, related to the problem. This is one of the triggers: CREATE OR REPLACE FUNCTION "public"."update_balance_bet"() RETURNS "pg_catalog"."trigger" AS $BODY$ DECLARE ...
1
vote
1answer
21 views

Compute new records based on existing ones (e.g., sum of 2 records)?

Background I have an inventory where I register the number of fruits I have in stock — both expected and actual quantities. For some fruits (e.g., here Banana and Cherry), I might not have both ...
0
votes
1answer
21 views

Query rows with common value over result set partition

I'm on Postgres 11 and I have these tables foo foobar | id | | foo_id | bar_id | ------ ------------------- | a | | a | 1 | | b | | a | 2 | | ...
1
vote
1answer
29 views

Different results from select and from materialized view

select distinct on (p) coalesce(native_fcm_token, push_token) p from session where user_id = 2779 and ((push_token is not null) or (native_fcm_token is not null)) returns 27 rows refresh ...
0
votes
0answers
8 views

Getting attribute of type postgres

lets say I create a type like this: CREATE TYPE books AS ( book_id NUMERIC , row_num NUMERIC ); I want to write a query that returns me the attributes (book_id, row_num) of books. I am not sure ...
0
votes
0answers
16 views

Checking a table has the same row type as a record type in PostgreSQL?

Is there a way to check in PostgreSQL that a table has the same row type as a given record type (composite type)? This is a follow-up question to Check if two tables have the same structure (are ...
3
votes
2answers
20 views

Optimizing PostgreSQL where clause with OR

I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate ...
0
votes
0answers
35 views

Schema: Table with many rows vs single table with many columns

I'm torn between two different schemas and would love some of your thoughts. Essentially, we have a list of items, and each item can have a bunch of extra boolean attributes, currently about 15 ...
0
votes
0answers
32 views

AWS DMS Fails with “Command failed to load data with exit error code 1”

I am trying to Migrate my Postgresql RDS to Aurora Postgresql using DMS. All my tasks are failing, and I am unable to understand what can be done to help the tasks progress. The error from cloud ...
1
vote
2answers
46 views

Why does a query take so much longer for 1 additional identical join?

I am trying to figure out why with the query below, adding the last join takes so much longer. If I run it with the last join commented out ( level 3) it runs in less than a second. Adding the 3rd ...
0
votes
1answer
25 views

Comparing nullable column values using “IS NULL” versus “= NULL”

I'm dealing with nullable values in a column of a table, i'm wondering why this: with cte as ( select 'c1'::text as c1,null::text as c2 union select 'c2'::text as c1,null::text as c2 ) ...
0
votes
1answer
25 views

Postgres special character rendering

I have an old Sql Server db with a ‘free entry’ field that is being accessed via a Postgres 10.6 foreign table. This field contains a lot of garbage characters (higher than Ascii 128) which Postgres ...
1
vote
1answer
14 views

Is it possible to export postgres database without views?

I check pg_dump doc, and there seems to be an option that allows to export only views. But is there a way to dump a database without all the views?
1
vote
0answers
49 views

DB model for EV charging station booking system

I am prototyping a system that will allow users to book and pay for a connection on a charging station for their EVs in a certain location. Business rules they can book a connection for as long as ...
1
vote
1answer
27 views

Dynamic pivoting in PostgreSQL

I have a scenario where I have to pivot a list of rows to columns dynamically .For a particular order_number there might be several products. I want all those products to be in a separate column, ...
0
votes
1answer
32 views

Run PostgreSQL without admin / superuser

I'm trying to setup a secure append-only database with PostgreSQL or MySQL. My idea for that is to generate a database where no admin/superuser accounts exists and only users with limited privileges ...
0
votes
0answers
16 views

PostgreSQL: Very high read IO on one of the servers

We have two servers dedicated to PostgreSQL 11.3: master (128Mb RAM, 16 core CPU, SSD) - all write queries and 65% of read queries slave (64GB RAM, 12 core CPU, SSD) - 35% of read queries For ...
0
votes
2answers
23 views

Sequence cache size [closed]

I have to change cache for all sequences in schema and database. I can not change one by one. Is there some script or query which can change cache size for all sequences?
0
votes
1answer
13 views

Is there a way to delete duplicate from a cell value (value of a given row and column) using Postgresql

In my Postgres db, I have a table with some columns where one column B has values as follows: A B a1 b0, b1, b1 a2 b2, b3 a3 b4, b4, b5, b5 My expected output should be like this: ...
0
votes
0answers
13 views

PostgreSQL & Pandas DF: is it possible to update a selection of data of a PostgreSQL db with a DF w/o impacting other entries?

I have a PostgreSQL database, which I interact with using SQLAlchemy in Python. The database contains daily price data (including non-working days), starting from Jan-2005; the date column is the ...
0
votes
1answer
16 views

how can I divide log files for specific DB queries?

Currently my db has a couple of triggers for some (not all) tables that get executed when a specific number of columns has been updated. I keep track if the triggers for those rows on a boolean column ...
0
votes
1answer
29 views

Unstructed data field: Query all values from array of objects by key

I have a table that has a unique ID, and a second 'collumn' named 'data' that contains simple key/value items like: "nickname": "value" "fullName": "value" "office": "value" "unity": "value" and a ...
0
votes
0answers
14 views

how to create select query using with

WITH data AS ( SELECT json_merge(fav.favorites::json, '{"111111": "2000-00-00 11:07:05.710000","123123": "1999-00-00 11:07:05.710000"}'::json)::json FROM mobappb2c_profile.client_material as fav WHERE ...
-3
votes
1answer
24 views

Return result sets from stored procedure in Postgres v12

How to return result sets from a procedure in Postgres v12(beta). The link below says that changes have been done in Postgres v12 beta. https://wiki.postgresql.org/wiki/TODO#Server-Side_Languages: ...
0
votes
1answer
33 views

Assign foreign keys only after populating database

I am porting a MongoDB database over to a PostgreSQL (relational) database. I would like to perform this task concurrently, and I have developed code with parallelism in mind, but dependencies between ...