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.

0
votes
1answer
16 views

Postgres - 2 database mirror

I have 1 DB called - DB_A. I would like to create read only database DB_B from DB_A which updates itself every 10 mins. Is their any easy way to do it in postgres? I am using Postgres 11. Also any ...
0
votes
0answers
27 views

Postgres TRIGGERS to create csv file after insert on Foreign Table

I have a foreign table called vw_asset_register with over 5000 rows in there which I'm calling from MSSQL database. SELECT * FROM vw_asset_register. I always use this to create CSV file into my ...
0
votes
1answer
21 views

Generate metadata file from PostgreSQL server [closed]

Is there a way that I can make a metadata-sheet that lists all the available tables from a postgresql-database, so users can see documentation of the tables? I know that there are descriptions, ...
0
votes
0answers
7 views

Pass 'interval' value to date_trunc function in PostgreSQL procedure

I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns. The syntax in the ...
0
votes
0answers
20 views

Postgres COPY locking on a busy table

Does COPY lock a table for writes? I have a busy table, both reads and writes, and I'd like to COPY it to a logical replica. The table is several GBs, so the COPY will take a while.
0
votes
1answer
9 views

Problem in PostgreSQL replication - ALTER DATABASE

Recently I put to work a Master/Slave replication in my PostgreSQL (9.3). Everything is ok... insert, update, delete, in data and changes in tables... but when I changed the data base name the ...
1
vote
1answer
35 views

PostgreSQL ensure no concurrent reads

I'm sorry if it's a very basic question, but would really appreciate educated advice on this: Say I have an operation, which first has to read from a table and then insert a row if the read value ...
0
votes
1answer
13 views

Safe to use the same temporary table name in parallel transactons?

Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
1
vote
0answers
16 views

How to read jsonb or json in postgresql spi?

How can I read a json/jsonb field in a PostgreSQL server side function? I am trying to write a server side function using C and SPI for PostgreSQL 11. The json field in mind is simple and contain ...
0
votes
1answer
23 views

What's the most appropriate type of index to use for this function-based query?

I'm using Postgres 9.5. I'm going to be running a lot of this kind of query SELECT * FROM mydb_article where regexp_replace(url, '\?.*$', '') = :url_wo_query_string so I figured I would need to ...
0
votes
0answers
18 views

Is there a possibility to run 2 minor Postgres version in parallel (Postgres 10.x)?

I have Postgres db instance 10.6 running in production say node-b: rhel 7.6 while I am building another cluster (not instance) of 2 nodes. Out of these 2 nodes one posgres node will reside on this ...
-1
votes
0answers
41 views

One query with multiple JOINs vs multiple queries [closed]

I am working on Posrgres 9.6 with PostGIS 2.3, hosted on AWS RDS. I'm trying to optimize some geo-radius queries for data that comes from different tables. I'm considering two approaches: single ...
0
votes
0answers
23 views

Increasing working memory in Postgres does not improve query performance

I am very new to database administration and could use any advice from more expert users of PostgreSQL My database is an AWS RDS db.m4.large whose specification are here My queries against one of ...
1
vote
1answer
53 views

Optimizing read query in PostgreSQL

I'm having a tough time optimizing a read query from PostgreSQL 10. The query is as follows SELECT mp.id_person, ( COUNT(CASE WHEN mp.id_g_type = mc.id_g_type (and mc.g1 ...
0
votes
1answer
35 views

Postgres 10 and malformatted array literal when using EXECUTE

I am trying to create a function on Postgres 10 that would check whether all the given ids exist in the target table. CREATE OR REPLACE FUNCTION "public"."has_mismatching_ids"( _ids TEXT[], ...
0
votes
1answer
22 views

how to show a specific value for a non existing value in second table in join?

I have 2 tables, City(city_id, city_name) and count_staff_in_city(city_id, count_staff). the first table keeps the name of the city and its id, and the second table keeps the number of staffs working ...
1
vote
3answers
42 views

Is there a more concise way in Postgres to extract a portion of a string?

I'm using Postgres 9.5. I have a table with a column that records URLs. Sometimes the URL has a query string and sometimes it does not. I would like to extract the URL, minus any query string, so I ...
0
votes
0answers
26 views

Strange behavior changing type off column

I'm changing the column type of a table from timestamp without timezone to timestamp with timezone and something strange is happening. For some unknown reason, when i run the alter table statement: ...
1
vote
1answer
31 views

Is there way to get transaction commit timestamp in Postgres?

I have data-pulling functionality that once in 5 seconds grabs all the data from Postgres table basing on modified_timestamp column. It works the following way: SELECT * FROM my_table WHERE ...
0
votes
1answer
20 views

How to use COLUMN and TABLE options in a RAISE statement

According to the docs, there are options to RAISE called TABLE and COLUMN. However , if I explicitly set them, they do not end up in the resulting error message. It seems only DETAIL and HINT are ever ...
0
votes
1answer
48 views

Loop Block with execution doesn't persist

I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist. Like this... do $$ declare dados Record; begin for ...
0
votes
0answers
14 views

Query to get the “Item — (1,n) --> Attributes” relation table?

given is the following database scheme and I am wondering how to build the appropriate query on a Postgres system. It is defined that Items can have multiple (1 -> n) Attributes which relation is ...
0
votes
0answers
12 views

Getting last modification date of a schema [duplicate]

Is there a way to discover the date a schema was last modified? I'm trying to decide if a schema's still being updated and is therefore "active" in my system. I use PostgreSQL 9.2 under Linux CentOS ...
0
votes
0answers
26 views

Sync PostgreSQL with Cloud and multi Local Locations

I need help with a complex (for me) multi server setup with PostgreSQL. I have to setup a Cloud Server and 3 Local Servers for our company. Each Server will have: Linux Ubuntu 16.04 Docker ...
0
votes
2answers
27 views

Select rows where one row meets a condition

I have a table with 3 columns: UserId ProductId Status 1 100 1 1 1 0 2 100 0 4 100 0 5 1 1 5 100 1 6 ...
0
votes
0answers
16 views

Postgres ALTER TABLE xyz REPLICA IDENTITY FULL

I am trying to alter a table in a database with (postgres 10.6) ALTER TABLE xyz REPLICA IDENTITY FULL; However, the command does not return. Is there any way to diagnose what the issue is, or any ...
0
votes
0answers
15 views

Are multiple simultaneous direct loads a better strategy than individual inserts in PostgreSQL?

In our application we run a background processor called Sidekiq which allows us to run hundreds of idempotent jobs against the database in parallel. We have one job which can add up to 3,000 inserts ...
0
votes
0answers
17 views

PostgreSQL - Which SQL commands I need to create a column and insert it images base64 strings? [closed]

I'm trying to save 3 base64 strings from 3 images, so I think the best way to do this is storing the content in an array of strings. So, I want to know, In Postgres, How to create the specific column ...
0
votes
0answers
24 views

PostgreSQL 11 and pgagent connection string issue

On PostgreSQL 11 running on Windows 2012 server, I created a job with pgagent and pgAdmin4. In the step, in connection string I used the password parameter and it works. Instead, I would like to use ...
0
votes
1answer
14 views

select query to fetch json column internal objects

I have a json like {"disks":{"value":[{"Type":"NA"},{"Type":"NA"}]}}. How can I write a query to fetch value in Type
2
votes
0answers
22 views

AWS RDS PostgreSQL: Significant query runtime difference between two (identical?) instances

TL;DR: Solution The problem was that one instance hat magnetic storage attached and was therefore significantly slower. Original Question I have two identical AWS RDS PostgreSQL 10.6 databases ...
0
votes
1answer
15 views

How to capture client host name in postgresql?

pg_stat_activity system view has client_address and client_hostname, but it always empty or null. Do I need to tweak anything to update the view?
1
vote
1answer
19 views

Searching from array (complex criteria) without unnesting?

I have table with arrays of dates. How to search in them? create table if not exists somedates ( startdate date, otherdates date[]); insert into somedates (startdate, otherdates) values ('2000-11-14'...
0
votes
1answer
30 views

Postgres - autovacuum on several big tables

I have an issue with autovacuum - I have several big tables (over 10 million records) which are updated very frequently (500 updates per second). Everything works OK until bloat happen :) As I can ...
1
vote
1answer
30 views

How much time should a backup of a 100Gb database take using streaming server setup?

I've installed barman on a dedicated server to backup all my postgresql servers. One of the server is now using 100Gb space and the instruction barman backup myserver takes 23 hours. I find it too ...
1
vote
1answer
15 views

PostgreSQL unique continue on violation

I am creating ETL where I rely on the database to refuse duplicate values for a business key. In SQL Server this works like a charm. The database gives an error, the ETL ignores the error, continues ...
0
votes
1answer
29 views

connecting to Postgres running inside docker container 'posgres' does not exist

I want to connect to Postgres (running in a container) using psql What I've done: docker pull postgres docker run -p5432:5432 --name mydb -e POSTGRES_PASSWORD=pass123 -d postgres Trying to connect ...
1
vote
1answer
40 views

Why am I getting ERROR: duplicate key value violates unique constraint "movies_pkey'

So, I'm creating a database for one of my classes using pg admin. I got all the data in no problem but my one issue is this one error that I don't really understand. ERROR: duplicate key value ...
0
votes
0answers
18 views

ETL from non public database to a public one

I have a database that is in a private network. The database is not publicly accessible (you have to be on the local network to access it). We would like to use an ETL tool like FiveTran to export the ...
0
votes
0answers
14 views

PostgreSQL pg_trgm sorting

I am using a gin_trgm_ops index on a varchar(255) column. I am having trouble sorting the results. For example if I look up similarity(name, 'samsung ssd') as similarity I get varying results. ...
0
votes
1answer
11 views

“INSERT INTO res_partner () VALUES ()” failure (autoincrement in postgres)

This is DDL for the table: CREATE TABLE public.res_partner ( id serial NOT NULL, CONSTRAINT res_partner_pkey PRIMARY KEY (id) ); This is SQL: insert into public.res_partner () values (); ...
0
votes
1answer
16 views

Postgresql SELECT item from JSONB array - array as top-level json object

I have JSONB column which stores the data in this format [ { "item1": "value1", "item2": "value2" }, { "item1": "value3", "item2": "value4" } ] I am ...
0
votes
1answer
15 views

Code review database design for storing college assessments & enrolments

I'm desigining a database for a university testing tool – running tests in the cloud, very similar to a CI system. It needs to store users, courses, enrolments, assessments, submissions & results....
0
votes
1answer
38 views

How to join two columns and find unique combinations

I have the following topological structure (topo) with their types: id(string) | id_from(string) | id_to(string) | type(string) '111' | 'aaa' | 'bbb' | 'type1' '222' ...
1
vote
2answers
35 views

Inner join only for not empty values

i can get right result using the where join , with next sql: WITH fktable1 AS ( SELECT 2 AS id ) ,fktable2 AS ( SELECT 3 AS id ) SELECT main.* FROM -- main data ( VALUES (1, NULL, NULL), ...
0
votes
0answers
52 views

Can't figure out how to start postgres on port 5432 in windows

Setting up Postgres on Windows for frontend web dev This is not my area of expertise (frontend guy), but I need postgres simply running on my side to connect with the test DB for the project I'm ...
0
votes
1answer
29 views

Why am I getting table does not exist error?

so I'm trying to creating a database table using pg admin. I created a table in pg admin and then I was supposed to run the table using putty. But when I run it it says error table does not exist. The ...
1
vote
1answer
29 views

Index Optimization for Datetime comparing day of week and hour

I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int, and to fill in gaps with data we have models by day of week and hour of day: model_id int, hour_of_day int, ...
1
vote
1answer
37 views

How can I make “phraseto_tsquery” as fast as “plainto_tsquery” in PostgreSQL

Some background: I have a table, documents with over 200,000 rows that has a column body, which can be very long. I put an index on this table: CREATE INDEX documents_body_tsvector_index ON ...
1
vote
1answer
27 views

Manually setting most_common_vals in Postgres

I am currently building a system for exploration purposes on top of Postgres. Essentially, this results in queries on a table of form (key, value) pairs, representing a graph model: edge_id | term_id ...