Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
0
votes
0answers
3 views

Show size of tables and other database objects in Postgres

I need to clear disk space, so I want to see which tables and other objects are using the most disk space in my Postgres databases. Ideally, I'd like to see: database name, schema name, object type, ...
4
votes
1answer
5k views

Postgres Streaming Replication: How to re-sync data in master (with standby) after failover

I am absolutely new to postgres 9.1 replication mechanisms. I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when ...
1
vote
2answers
26 views

How to get the second highest value per group?

Getting the second highest value from a table has been solved many times, but I'm looking for the second highest value in each group. Given this table: +----+-----+ | A | 10 | | A | 20 | | A | ...
0
votes
0answers
5 views

Grant read only access to postgresql schema

I want to grant user foo_user read only access to schema bar_schema. I executed the following commands postgres=> GRANT SELECT ON ALL TABLES IN SCHEMA bar_schema TO foo_user; GRANT postgres=> ...
3
votes
2answers
1k views

PostgreSQL read-only user for pg_dumpall

I'm running a PostgreSQL instance which is used by several related applications, each of which has its own database. For backups I want to create snapshots of the complete data in the instance and I ...
0
votes
0answers
8 views

SUM `amount` column based on `transactionType` in Postgresql "transactions" table

I have this table: +--------+-----------------+ | amount | transactionType | +--------+-----------------+ | 10 | Debit | | 5 | Credit | | 15 | Credit | | 20 ...
0
votes
0answers
6 views

PostgreSQL versioning question

In my terminal, a CLI query tells me my PostgreSQL is currently 13.2. I am using MacOS and have likely also used Homebrew to take care of installs and updates. I check things out with the brew command ...
-2
votes
1answer
19 views

How to backup a live database especifically Postgres

I have production Postgres database that is running remotely and I want to backup the live database without stopping it. I don't want to use pg_dump as it takes too long and not really reliable. Tried ...
0
votes
0answers
10 views

Why can't reset password for postgres?

I can login postgresql database without password : sudo -u postgres psql Now i want to reset password 'xxxxxxxx' for postgres: sudo -u postgres psql postgres=# ALTER USER postgres WITH PASSWORD '...
0
votes
1answer
89 views

Resolve parametrized schema in PostgreSQL

I'm looking for a way to use a parametrized schema in the DECLARE section of a PostgreSQL function. Why am I looking this way ? This functions will refer %ROWTYPE from multiple schema like Var1 s1....
3
votes
3answers
506 views

How can you replace the last occurrence of a character in a string (text column)?

Let's say I have a text field with this value in Postgres: 'bar$foo$john$doe$xxx' I'd like to replace just the last occurrence of the dollar ($) character with another character, for example '-'. ...
1
vote
1answer
294 views

SequelizeDatabaseError While Upserting, Wrong Table Being Referenced

I'm selecting from a table, and the query runs fine. It even shows up on the client side without a hitch. ` SELECT * FROM public."roleAllocation" ` However, when I run an upsert function from a ...
0
votes
2answers
32 views

Postgresql pg_toast tables, are they mandatory?

I have a small schema in PostgreSQL v13 with only schema structure and still no data. What I have seen, that I got some pg_toast tables for normal tables although there is no data inside. I have a ...
0
votes
2answers
35 views

Postgresql query optimisation with left join

I'm trying to optimize query run on Postgresql 12. There are 2 tables users (5 mln rows) grants (8 mln rows) I'm running a following query SELECT * FROM users u LEFT JOIN grants g ON u.id = g....
1
vote
2answers
368 views

Modelling users and groups - but with multiple types of groups

I'm modelling a user management system in PostgreSQL that is fairly typical in that it consists of users, groups and permissions (and the relationships between them). However, its seems I'm dealing ...
4
votes
1answer
408 views

How to check if int[] array contains a value less than X?

We can check if a specific value is contained in an array using 'X' = ANY(haystack). However, how do I check if an array of integers contains a value that is less than X?, e.g. I want to find all ...
1
vote
1answer
165 views

SELECT FOR UPDATE (NOWAIT) on auxilliary table for serializing updates?

Background: I have an inventory tracking application which needs to avoid the following sort of situation where the same inventory might be used twice due to lack of serialization. The constraint is ...
2
votes
1answer
168 views

Slow PostgreSQL 9.6.5 query with LIMIT, but only for tsquery with no results

Table a has > 3 million rows and this index: CREATE INDEX idx_a_txt ON a USING gin (txt); This query takes 5 seconds if I search for something that doesn't exist, and 31 milliseconds if I ...
3
votes
3answers
215 views

Postgres Repeatable Read vs Serializable

I'm trying to understand whether the repeatable read isolation level is good enough for my scenario in an application that uses Postgres, but the docs are making it difficult to understand which is ...
28
votes
3answers
9k views

Constraint to enforce "at least one" or "exactly one" in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | email_address ...
0
votes
1answer
26 views

How i can use the function encrypt in one consult update

I'm trying to duplicate a column, but I would like to encrypt it with not very high data value, and I was able to do it, well, just the first part. CREATE TABLE probeta ( id int primary key, ...
26
votes
6answers
37k views

Display user-defined types and their details

I've created a few new UDTs in PostgreSQL. However, now I have two problems: how to see which UDTs have been defined? how to see the columns defined within these UDTs? Unfortunately, I couldn't ...
1
vote
1answer
177 views

Postgresql streaming replication slow on macOS

I am using PostgreSQL 10.1 on MAC on which I am trying to set up streaming replication. I configured both master and slave to be on the same machine. I find the streaming replication lag to be slower ...
0
votes
2answers
5k 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
1answer
1k views

What is the key to pg_restore?

We have been working on a large-ish PostgreSQL database migration to a Google Cloud VM instance (Linux 14.04) for several weeks now, and are having issues with the final step: pg_restore. We are ...
0
votes
1answer
727 views

Query tables with information_schema in pgsql

I am trying to query the tables contained in a schema from an insert trigger on another table. My process is : Manually create the schema with DB Manager inside QGIS. Load tables foo, bar, baz. ...
1
vote
1answer
98 views

PostgreSQL query for parent - child relations

I need to store objects in a PostgreSQL database version 13. Each object has an ID, a name, occupies a level and belongs to a parent object, relation denoted by belongs_to. The values for the levels ...
0
votes
0answers
19 views

postgresql performance issue with multiple EXISTS

explain select count(*) from (SELECT tcy.clientid, tcy.projectid, max(tcy.lastmodifieddate) AS projectupdatedat FROM test_cycles tcy JOIN projects ...
2
votes
1answer
56 views

Why is FROM VALUES massively faster than visiting a table once?

Given this setup, CREATE TABLE t(foo) AS SELECT 'foobar'::text; Why is this query, EXPLAIN (ANALYZE, TIMING OFF) SELECT generate_series(1, 1000000), upper(foo) FROM ( VALUES ('foobar') ) AS t(foo)...
4
votes
1answer
2k views

Slow postgres query that uses quick sort instead of hash-aggregate

I'm on Postgres 9.5 and working with a web analytics database that records visitor traffic. I'm trying to optimize a very slow query that gives me a count of the unique people that visited a given ...
1
vote
4answers
34k views

Opening a .backup file for PostgreSQL

I'm going to warn you that whoever explains this needs to talk to me like I'm five years old, because this is something way, way beyond my level of expertise. In short, I have what I think is a ...
1
vote
1answer
42 views

Postgres ERROR: could not compare Unicode strings

I have a database on my development machine where everything runs just fine. I copied the database to the server using file system level backup. Most queries run fine and fast. But I got problems with ...
6
votes
3answers
230 views

How can I calculate all grouping permutations of an input string in SQL?

Given an input like "ABC" generate a query that calculates all potential splits of 0 or more of the given string, Desired output, A B C A BC AB C ABC Given an input like "ABCD&...
1
vote
1answer
462 views

dockerized postgresql listing no clusters

I'm upgrading an existing dockerized postgres:9.6 to 10. There are plenty of good Q/As on DBA and SO that go the path of pg_upgradecluster, but it is not working for me. When I start a vanilla pg:10 ...
2
votes
3answers
5k views

Postgresql query is very slow for my big table

My database version is postgresql 9.5. create table if not exists request_log ( id bigserial not null constraint app_requests_pkey primary key, request_date timestamp not ...
0
votes
2answers
132 views

Is it possible to COPY tables into PostgreSQL without setting structure first?

I'm just getting to grips with PostgreSQL 9.6. I'm wondering if there is a simple way to add a .csv table to a database without first creating the table structure in psql or pgAdmin? Or...I've not ...
0
votes
1answer
22 views

Postgres list only user's database and keep other hidden?

I've created a postgres user 'tester' and allowed to readonly to a database A. This user has no permisssion on database B & C. But when I do \l to list database. The database B and C are also ...
0
votes
1answer
90 views

postgresql deadlocks: SELECT blocked and blocking query

Based on the 2nd answer on https://stackoverflow.com/questions/22775150/how-to-simulate-deadlock-in-postgresql I performed the following snippet: Transaction 1 Transaction 2 BEGIN; ...
1
vote
2answers
150 views

Increasing query efficiency of queries spanning multiple tables

I am looking for ways to improve the performance of some relatively complicated queries against both PostgreSQL and MySQL databases. (One of the requirements of the application which the database is ...
1
vote
1answer
34 views

Postgres find gaps between one date range and others

My question is similar to https://stackoverflow.com/a/41267928/2585154 , but in my case I need to divide/split date range by multiple date ranges, not only one. In my case is also guaranteed that &...
1
vote
1answer
815 views

Postgres HAVING ilike any wildcard in array_agg

I need a filter where the input value is similar to any value in an aggregate, using ilike and wildcards. My attempt at a query so far is: SELECT p.id, p.name, array_agg(vo.name) FROM product p LEFT ...
2
votes
1answer
307 views

How to deal with seldom used big database and postgresql?

I loaded in PostgreSQL (9.3) OpenStreetMap data for whole europe (400gb). Then I installed a geocoding api (nominatim) that queries the database with gis queries. My problem is : This database is ...
0
votes
0answers
14 views

Connection to AWS database gives back "You may not specify an IPv4 CIDR for an existing referenced group id rule."

I try to connect to a AWS database instance from a client by following this tutorial: https://medium.com/simplebackups/allow-a-remote-ip-to-connect-to-your-amazon-rds-mysql-instance-2d5074d601eb I ...
6
votes
5answers
514 views

Fetch a row that contains the set of last non-NULL values for each column

Suppose there is a table version col_A col_B col_C 1 A1 B1 (null) 2 A2 B3 (null) 3 A3 B2 (null) 4 A5 (null) C1 5 A1 (null) (null)...
1
vote
1answer
48 views

Order by one column if not null, otherwise by another column, columns are of not matching types

I have an application that uses PostgreSQL as RDBMS. There is a table containing orders placed by customers. The table has an id as primary key, an order_number (integer) and a created_at field (...
5
votes
4answers
2k views

How to run pg_dump so that it doesn't slow down other connections?

I am running a daily pg_dump on PostgreSQL 9.3.14 database of approx. 50 GB of size: pg_dump -Fc dbname > file The problem I am having is that while pg_dump runs, which takes several hours, all ...
1
vote
3answers
2k views

Hash index vs. btree index performance on inserts and updates

I have table which is several time bigger than available RAM. Table size is 181 GB, 92 million rows. PostgreSQL 10. There is field with uuid in the table. And there is need to insert into the table ...
0
votes
0answers
18 views

Access ForeignKey columns via dot in PostgreSQL?

I have a table user with columns id, name, email I have a table book with columns id, name, author (ForeignKey to user). Is there a way to write this SQL query in PostgreSQL? select name, author....
0
votes
1answer
18 views

Pivot table in PostgreSQL by pivoting 10~20 values in a column

Is it possible to crosstab a table in PostgreSQL without having to define an "as" clause given by a tuple (column type)? Example: The created table "t" would have only 3 columns '1'...
0
votes
2answers
1k views

Empty table after pg_restore

I have a backup of an unlogged table called potential_users, the relation has 58677237 records in it. Steps: pg_dump --format custom --verbose --file "potential_users.backup" --table "public....

1
2 3 4 5
282