Stack Exchange Network

Stack Exchange network consists of 174 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
7 views

Retreiving database dump after copying from remote server

I used command pg_dump -C -h remotehost -U remoteuser db_name | psql localhost -U localuser to copy database on remote server to my local machine. Can I somehow retreive the dump obtained after ...
-1
votes
0answers
11 views

SQL Log File to Table Insert/Update

I have a emp table which contains three columns. Lets say EmpNo, EmpName and Age. Empno is a primary key column. I am getting data from logs or text file. If there is no particular Empno then row ...
0
votes
0answers
10 views

Poor query planning in Postgres 10

The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join. Here's my ...
0
votes
0answers
14 views

Make use of PostgreSQL 10 multi-column statistics when planning index scans

I'm using PostgreSQL 10 and am trying to optimize query performance on a large table (~70M rows). The query looks like: SELECT * FROM large_table -- many joins -- WHERE col_3 = 'some val' -- time-...
0
votes
0answers
24 views

Select constrains with on update no action

We use Postgres 9.5 Is it possible to modify this script to show ON UPDATE value for each foreign key reference at this table? Or get this values somehow? Thanks a lot! SELECT tc.table_schema,...
1
vote
2answers
45 views

How to optimize a table for a very high-frequency updates?

I have a table that contains a list of tasks that need to be run periodically: applaudience=> \d+ maintenance_task Table "...
0
votes
0answers
15 views

Using Postgresql logical replication, how do you know that the subscriber is caught up?

Postgresql has some interesting monitoring tools for monitoring the new logical replication system's progress, but I don't really understand them. The two tools I'm aware of are: pg_stat_replication ...
0
votes
0answers
18 views

Obtain Postgres binaries for FreeBSD

I can find the latest Postgres versions such as 11.1 as source code configured to be downloaded and built for FreeBSD amongst the Ports Collection. I cannot find compiled binaries for the latest ...
0
votes
1answer
24 views

PostgreSQL upsert implications on read performance after bulk load

In PostgreSQL documentation on populating databases, they mention how we can improve bulk loading operations by disabling constraints and indexes, but seeing how INSERT ... ON CONFLICT (upsert) ...
1
vote
0answers
19 views

Bucardo - without Primary Key and Unique Key

I read this from Postgresql Wiki page. Cannot incrementally replicate tables without a unique key (it can "fullcopy" them) Now we have 5.5.0, so still do we need PK or unique key for this? What ...
1
vote
1answer
14 views

Postgresql - archive_cleanup_command with multiple standby servers

Regarding the archive_cleanup_command command, the Postgresql 9.6 documentation states the following: Note however that if multiple standby servers are restoring from the same archive directory, ...
0
votes
1answer
18 views

Postgresql streaming replication - choose databases to sync

I have been reading on the Postgresql replication streaming (as described here) and was wondering if it is possible to choose which databases are synced, or if it has to be the entire DB server. E.g. ...
0
votes
0answers
22 views

Should I split my database by schema or do a table partition?

I have a PostgreSQL database whose data comes from 800 different locations and right now consists of 4 billion rows in total (all locations combined). All this data is arranged in 3 tables (approx 1....
0
votes
0answers
19 views

Advise on indexing for bulk data harvesting, Postgres

I'm experiencing performance issues when transfering/duplicating data from remote machines to a central server. Essentially I need to upload all records from client_table to a similarly structured ...
1
vote
1answer
15 views

Export each row of a table into a separate text file

I have a table in my database that contains data with three columns: id, name, and notes. The notes column contains unstructured text data that I want exported for each row as its own text file. This ...
0
votes
1answer
32 views

Window function IGNORE NULLS workaround for PostgreSQL

With the following query I can use the LAG() function to repeat the last non null value of c column: SELECT coalesce(open_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000) ...
2
votes
1answer
58 views

How can it be possible that sum operation is so much faster than count?

When I make a simple benchmark test in a table that has 11.065.763 records. I get the results below: select sum(t.amount * t.exchange) from table t; Finalize Aggregate (cost=658027.39..658027.40 ...
0
votes
0answers
16 views

I am not able to see the postgres databse created by me in the list

Logged into postgres database by following command sudo -u postgres psql postgres created a postgres database by following command: create database tukatechgarmentstore_production But when I ...
0
votes
0answers
18 views

View with Foreign (Tables / Data Wrappers) on PostgreSQL

I have two tables in two different databases which contains records on almost same schema. In order to make the access to viewing that data easier, I created a foreign table in one of the databases ...
0
votes
0answers
18 views

“[unknown]@[unknown] LOG: could not accept SSL connection: Success” in PostgreSQL logs

My PostgreSQL 10.6 server (Ubuntu 10.6-0ubuntu0.18.04.1) has the following lines in the log approx 4x per second: 2018-12-05 19:40:35.881 CET [25348] [unknown]@[unknown] LOG: could not accept SSL ...
1
vote
1answer
16 views

How to prepend an element to a JSON array?

I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty). How ...
0
votes
0answers
39 views

Are there separate Object Oriented and Relational Databases in PostgreSQL?

I am new to ORDBMS and I want to know, can I have two types of databases in Postgres? - Object Oriented databases and Relational databases separately. Or there is only one kind of database which is ...
0
votes
1answer
17 views

Postgresql - Large object exists in pg_largeobject table but cannot open with lo_open?

I have a problem with opening Large Object in postgresql version 9.2, select * from pg_largeobject where loid = '19423'; will return data like this: loid oid;page no integer;data bytea 19423;0;"<...
0
votes
0answers
22 views

missing default privileges when creating a table in Postgres

I set up default privileges for 5 users in my db: postgres (default), admin, backup, migration and user. When I create a new table from posgres everything is ok: but when I try to create a new table ...
1
vote
0answers
22 views

Is it possible to drop inheritance from the parent table (top down)?

Our system uses inheritance on two levels: master child_level_1 child_level_2 child_level_2 child_level_2 child_level_1 child_level_2 child_level_2 child_level_1 child_level_2 The idea was to ...
2
votes
1answer
14 views

postgresql configuring wal_archive directory

I had a problem with PostgreSQL server. I took over PostgreSQL server from my former colleague, and today my server crashed due to filled up disk space with wal_archives. Usually wal segments were ...
3
votes
0answers
26 views

Calculating balance sheet with a single query

I have this schema for double entry bookkeeping system: CREATE TABLE account ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, parent_id INTEGER REFERENCES account(id), UNIQUE (name, ...
0
votes
0answers
19 views

Oracle to PostgreSQL: Create type body

In oracle: create or replace type typ_calendar as object( 年 varchar2(8), 月 varchar2(8), 星期日 varchar2(8), 星期一 varchar2(8), 星期二 varchar2(8), 星期三 varchar2(8), 星期四 varchar2(8), ...
0
votes
1answer
26 views

SQL update multiple rows in destination table with same id but different values from other table

creditNote table id invoice_id generated_at(timestamp) status 10101 111 2018-02-28 14:42:39.247 generated 23982 111 2018-03-30 11:11:11.247 ...
-1
votes
1answer
32 views

Receiving error about ambiguous field, but there is only 1 table being referenced

My understanding of ambiguous fields, or where I see the error elsewhere, is when I've defined a JOIN on multiple records and failed to specifically say which table has the field when the field names ...
0
votes
0answers
18 views

Performance impact from redundant casting of timestamp values in Postgres queries

I am inheriting some Postgres queries where there are some redundant casting for timestamp columns such as timestamp without time zone, timestamp, etc. for columns which will be implicitly be of ...
0
votes
1answer
12 views

Counting and grouping over multiple OUTER JOINs

I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a ...
0
votes
0answers
21 views

Combine results with same key

I have a PostgreSQL 10.6 database schema where a scenario references scenario_steps, and the scenario_steps contain a step (steps table) (referenced by step_id) and this scenario_step also contains a ...
1
vote
0answers
9 views

PostgreSQL Materialized View not refreshing

I'm using PostgreSQL in DBeaver and querying data from AWS. ana.bookings is a materialized view and it shows only the data up to 26th November 2018 even though all the tables and views prior to it ...
0
votes
0answers
21 views

Why is searching by one varchar column not working correctly on replica?

PostgreSQL: 9.6, master, replica. Async replication, wal archiving. We detected that if we execute a select query on a varchar column against a replica, then the results return no rows, but if we ...
1
vote
1answer
17 views

query on 2 fields but 1 is a unique value

postgresql 9.6, windows 7 Here is an example of a database : fld1 fld2 abc D123 xyz D123 abc D789 how can I select rows with fld1='abc' if/only for fld2 value exists just one time in the ...
0
votes
0answers
15 views

Find out if two PostgreSQL database instances contain the same data

I have a running PostgreSQL database instance that was created (by my predecessor) by applying a few manual steps to base version from a backup. I would now like to fully automate the creation of that ...
1
vote
1answer
17 views

install postgresql in unattended error

I try to install PostgreSQL within myapp.exe (which used advanced installer to build) in unattended mode, I set parameters as below: --mode unattended --superpassword xxxxxxx --prefix c:\programs ...
0
votes
2answers
55 views

PgAdmin4 unusable when database has lots of schemas

I have a production database with 60K database schemas. PgAdmin4 is unusable, I cannot even open "Query Tool" window. I suppose that PgAdmin tries to load database metadata, so that's the reason why ...
0
votes
0answers
15 views

ERROR: tablespace “data_db” is not empty

I'm trying to move all objects from my data_db tablespace to another. I'm following this guide: http://www.postgresqltutorial.com/postgresql-drop-tablespace/ I've moved database objects using: ...
1
vote
1answer
108 views

postgresql rename index

I want to understand how safe is the rename command: ALTER INDEX old_name RENAME TO old_name; The index was created by command(for the foreign key column): CREATE INDEX old_name ON table_t1 (...
1
vote
1answer
38 views

Efficient group by on sorted CSV using PostgreSQL FDW

I have a large CSV file sorted on the first column. Here is an extremely simplified version): a 7 a 4 b 6 c 3 c 9 c 2 I want to group by the first column and sum the second. How do I tell the ...
0
votes
1answer
53 views

Postgresql schema only works when wrapped in quotation marks

I'm seeing an odd behaviour - if I don't wrap my schema name in quotes, the server returns [42601] ERROR: syntax error at or near "." The query is simply: select count(*) from "authorization"....
0
votes
0answers
12 views

Postgres sequence table not updated

I am new to postgres so just trying to get some insights, our postgres database is hosted on Azure and we had to restore our postgres database on the local server.We did take the backup through azure ...
0
votes
2answers
36 views

Postgres list constraints that use a stored procedure

I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions. something like: CREATE TABLE payment ( amount bigint, CONSTRAINT amount_gt_0 CHECK (...
1
vote
0answers
26 views

2 sets of multivalued attributes

I have multivalue attribute which I need to store in 2 different tables. It represents qualificiations for certain jobs. One table is for workers profiles and another for company. Company table states ...
0
votes
0answers
29 views

Faster and most elegant for commom-use little datasets: JSONb or usual table?

General context: I am using modern PostgreSQL servers (with all JSONb functionality), and have a "library of snippets" in the schema lib... The snippets are adaped for each project, I can reuse ...
1
vote
1answer
15 views

How do you you suppress a specific warning in the postgresql log output?

Is there any way to suppress a specific warning in the postgresql log output? Basically, I have an application that often does a spurious commit before beginning database operations, and it's leading ...
1
vote
2answers
49 views

Why is this seemingly simple insert trigger function causing deadlocks?

This is a simplified version of my prior question. I removed the many-to-many complexity and still deadlocks. It happens WAY less frequently, but still happens. 😕 The situation... I have a tweet ...
0
votes
0answers
14 views

Where are calls to PG's systemd template unit located? And how to remove them?

On Ubuntu 16.04, if I run systemctl list-units postgres*, I get: UNIT LOAD ACTIVE SUB DESCRIPTION postgresql.service loaded active running PostgreSQL RDBMS ● ...