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
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [postgresql]

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

-1
votes
2answers
64 views

Is there a non-syntactical guide for naming database columns? [closed]

Is there a guide or best case practise to help people on choosing the best database column names. I am not looking at syntactical naming conventions such as snake case, camel case or even singular or ...
1
vote
0answers
29 views

Updating table and column names in an existing postgresql db

What is the best way to go about updating table names and column names in a postgresql database that already has loads of data but, as part of an overall web app upgrade, the database is also being ...
3
votes
1answer
41 views

Find invalid objects in database?

I'm playing around with PostgreSQL and would like to validate that everything is valid after migrating a data model. In: how-to-show-all-invalid-objects-in-postgresql it is stated that only indexes ...
0
votes
1answer
37 views

Tagging PostgreSQL database

Is it possible to "tag" a PostgreSQL database with meta-data? Here is my intended use case: I have a database that goes through several workflow steps, and it would be nice to record somewhere ...
1
vote
1answer
58 views

Downgrading PostgreSQL 10 to 9.4

Are there any particular precautions I should take when downgrading PG 10 to 9.4? I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to ...
2
votes
1answer
33 views

Multi-row insert vs transactional single row inserts

On Postgres (>10) is there any performance gain on using multi-row insert: insert into tbl (c1, c2) values (v1, v2) (v3, v4); over: begin; insert into tbl (c1, c2) values (v1, v2); insert into tbl (...
0
votes
1answer
18 views

Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner?

I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner? When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without ...
3
votes
3answers
450 views

Do all Relational DBMSes store table tuples in a clustered index based on primary key by default?

So i was reading MySQL innoDB, which apparently stores table data by default on a clustered Index (b+tree) based on primary key, and the tuples are in the leafs of that b+tree https://blog.jcole.us/...
0
votes
1answer
23 views

Should we favor multiple INSERTs or multiple UPDATEs

I always have an impression that INSERT would be beneficial for performance (let's forget storage for now) rather than UPDATES, in all the DB that I know. Especially in Postgres, UPDATES involves ...
0
votes
1answer
18 views

Update column value divisible by 100

Hello experts I need to update next column value with divisible by 100
2
votes
1answer
26 views

Postgres triggers for group_id

I have a table that looks like this: CREATE TABLE transactions( id SERIAL NOT NULL PRIMARY KEY, value NUMERIC(10,3), note TEXT, group_id INTEGER); I'd like to update the group ID automatically with ...
0
votes
1answer
22 views

Postgres 10.5: Granting full schema access to a user that isn't the owner

So the underlying problem I'm having is trying to migrate my on premise Postgres 10.5 instance to RDS. RDS does NOT give end users the SuperUser role so I'm hitting tons of permissions issues when ...
0
votes
2answers
32 views

PostgreSQL - how to generate an array containing column name and column data…?

I'd like to create a dictionary - an array - from a simple query, to include column names in output. IE, I'd like to turn this: SELECT id, last, first FROM names; id | last | first ---...
-1
votes
0answers
41 views

pg_basebackup taking way too long

I'm trying to set up a replica of my Postgresql DB using repmgr, and every single time it takes way too long for it to finish. This is the command that I actually run: repmgr -h MASTER_DB_ADDRESS -U ...
5
votes
2answers
156 views

PostgreSQL - retrieve all IDs in a tree for a given subnode

I have a non-binary tree of customer, and I need to obtain all the IDs in a tree for the given node. The table is very simple, just an join table with a parent id and child id. This is a ...
1
vote
1answer
51 views

Is there any benefit to having both an ordered and unordered index on the same column in postgres?

I have inherited a postgres database where there are two identical indexes except for their order: CREATE INDEX avail_time_ix ON table (availability_time); CREATE INDEX avail_time_dc_ix ON table (...
0
votes
2answers
39 views

Getting Records with Min Max Value on Grouped in Single Table in postgresql

The table looks like this: Existing Table: table_1 Desired Result: table_1 I have been able to get result like the following using this code: table_1 But I would like to get minimum and maximum ...
2
votes
3answers
48 views

Pass array of mixed type into stored FUNCTION

I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction. the problem here is, that one argument is list of n ...
0
votes
2answers
40 views

Postgresql: A bit confused on listing all roles in my system

I am just getting into PostgreSQL. I am ultimately interested in using PostGIS but thought it would pay off in the long run to get thoroughly familiar with the client/server authentication systems and ...
1
vote
1answer
23 views

Questions to Continuous Archiving and Point-in-Time Recovery with PostgreSQL

I'm planning my database backup strategy and came across the continuous archiving concept of PostgreSQL which is exactly what I'm looking for. I have two questions on this. I'm planning to mount ...
0
votes
2answers
62 views

Handling INSERT race condition for generated unique column value

I am generating a unique slug during creation of a record like so (based in this answer) INSERT INTO ks.shares ( , slugbase , slugindex , slug , userid ) SELECT , Pslugbase , ...
0
votes
1answer
15 views

Increase in number of temp files being generated after increasing checkpoint_timeout

I have pgbadger set up to analyze the log files from my postgres db. I recently changed a couple of parameters in my postgres config. I changed the checkpoint_timeout from the default value of 300 ...
0
votes
1answer
18 views

Retrieving 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 ...
2
votes
0answers
32 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 ...
1
vote
0answers
44 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 = 'particular date'::...
1
vote
1answer
39 views

Select FK constraints with ON UPDATE NO ACTION

We use Postgres 9.5. Is it possible to modify this script to show the ON UPDATE action for each foreign key reference at this table? Or get this values somehow? SELECT tc.table_schema, ...
1
vote
2answers
114 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 "...
1
vote
3answers
83 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
25 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
37 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
1answer
28 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
33 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
21 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. ...
1
vote
1answer
16 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
60 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
64 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 ...
-1
votes
0answers
85 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
22 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
49 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
55 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;"<...
2
votes
0answers
33 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
33 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 ...
4
votes
1answer
52 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
1answer
45 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
47 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
1answer
14 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 ...
-1
votes
0answers
22 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
1answer
21 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
1answer
29 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 ...