Questions tagged [postgresql]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
16 views

Ubuntu, postgresql, show whole running sql command

System Linux vm16745 3.13.0-77-generic #121-Ubuntu SMP Wed Jan 20 10:50:42 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux psql (PostgreSQL) 9.3.24 Issue I don't know if it is right section/forum, but I ...
0
votes
1answer
30 views

Whats the best way to implement multi tenant architecture with multiple unique identifier [on hold]

What's the best way to implements multi tenancy in a single database with one schema where current user can be identified with multiple unique identifier. Each tenant can have many business ...
0
votes
0answers
4 views

Connect Data directory with Postgres after a forced clean installation of MacOS

I‘m running Postgres on my Dropbox and access via pgAdmin. This worked very fine. However, after a few crashes of my macOS High Sierra, I had to reinstall the macOS and created a new user in the OS. ...
1
vote
1answer
329 views

Row level security in PostgreSQL for different groups

Need a little help with row-level security in Postgres. I have several tables and three group roles (group1, group2, and group3). I have several login roles that are members of each group, while "...
3
votes
2answers
285 views

Select into specific array positions with array_agg()?

Is there a way to set values in specific positions inside an array, based on information from other columns? (Postgres 9.3 or later.) For example, I would like to select an item and its stock ...
0
votes
1answer
10 views

Upload file to RDS Postgres Instance

I want to create custom Full Text Search dictionary with special stop words from some_words.stop. How to upload this file to my RDS instance by FTP?
4
votes
1answer
4k views

How to copy a Postgres database within the same DB cluster?

I want to replicate a Postgres database without dumping data using pg_dump. Both source and target database are on the same machine (Test and Production server). I would like to create a ...
1
vote
1answer
176 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 ...
1
vote
1answer
49 views

Creating a view that calculates percentages

I have this table getting populated with top10 destination ports every ten minutes along with a "total" entry that records the total number of bytes and packets transmitted for each port for the time ...
-1
votes
1answer
38 views

ERROR: check constraint “dates_check” is violated by some row

ALTER table myTable ADD CONSTRAINT dates_check CHECK(start_date::date < end_date::date); Gives.. ERROR: check constraint "dates_check" is violated by some row But.. select start_date, ...
1
vote
2answers
22 views

Generate table of all databases and schema with a single psql command

In PostgreSQL is it possible to generate a list of all databases and all schema with a single psql command? To be concrete, let me provide an example. I have two databases in a PotgreSQL database ...
2
votes
1answer
4k views

unable to start psql : no pg_hba.conf entry for host “::1” , user “postgres”, database “postgres”, SSL off

I am unable to connect using psql to PostgreSQL 9.6 on Windows 10. I saw almost all the StackExchange questions regarding this error. While installing Postgres it asked for superuser password and I ...
0
votes
1answer
87 views

Why could pgloader fail with 'undocumented reason' while migrating large MySQL database to PostgreSQL?

The database I would like to import is approximately 55GB in binary format, about 36 GB exported by mysqldump. But as we know even the postgres compatible mysqldump is not 100% compatible, so I'm ...
1
vote
1answer
21 views

Increase Postgres Transactions per second [on hold]

Is the TPS restricted by some sort of config parameter or underlying hardware? I'm coming up against a performance issue where my production DB is running 45 TPS, by comparison my local DB which can ...
0
votes
1answer
16 views

Postgres - how to investigate a dependency before DROPing a field

I want to drop a field from a partitioned table, POSTGRES claims there are dependencies and proposes using CASCADE. How can I check that nothing important will be deleted before risking DROP with ...
1
vote
1answer
1k views

When exactly does pg_stat_all_tables.last_autovacuum get updated?

I'm looking into issues with our autovacuum settings and trying to understand last_autoanalyze and last_autovacuum from the pg_stat_all_tables. I understand autovacuum to work in an incremental way, ...
0
votes
1answer
168 views

MS Access - Creating a report based on a pass-through query that requires a parameter

I am attempting to make a report in Access that uses parameters as well as a pass-through query. I'm open to using a temporary table but the guides I can find using temporary tables all use an ...
9
votes
6answers
23k views

Passing arguments to psql

I'm running a plpgsql script in Postgres 8.3 - I would like to pass arguments to this script via psql. I'm currently executing the script like: psql -d database -u user -f update_file.sql I came ...
7
votes
2answers
6k views

How to create an index on an integer json property in postgres

I can't figure out for the life of me how to create an index on a property of my json column which is an integer. I tried it this way (and also dozens of others) CREATE INDEX user_reputation_idx ON ...
0
votes
0answers
13 views

Updating Feed according to privacy settings (PostgreSQL, Redis) using fan-out write approach [on hold]

I am planning a system design for a social network, with following relationships between users (like Instagram) and posts that they can post. if user A follows user B, user A can see all the content ...
-1
votes
0answers
20 views

Storing past appointments, orders and invoices as jsonb [on hold]

Will that be wise idea to store past appointments orders, reports and invoices in a postgres's json b format. Idea is, once these entities are excuted user can't change the content other than voiding ...
1
vote
0answers
19 views

Merge join does not use index for sorting on PostgreSQL

I am running PostgreSQL 10.9 and stuck with a strange problem. My schema is a typical EAV with two tables: contacts and contacts_values. There are two relevant indexes: create unique index ...
0
votes
0answers
19 views

Postgres: strange long request: A is fast, B is fast, A WHERE IN B is slow

There is a lot of rows in table "logs" (the Request A FROM). Request A is quick and Request B is quick. Put together, Request A WHERE IN Request B is very long. Is that normal? Request A is very ...
0
votes
0answers
33 views

Postgres - Why Planning time for a query is greater than the execution time? [on hold]

I am working with a customer who migrated from Oracle to Postgres and is experiencing slow query performance. While troubleshooting the query performance, the EXPLAIN ANALYZE returned Planning Time: ...
0
votes
1answer
18 views

DB for searching posts by keywords in them (full text search) [on hold]

I'm trying to build some kind of social network, where users could post Posts, similar to Facebook. Each post could be in different Places, and it will also include hashtags for better search if ...
1
vote
1answer
15 views

Postgres: Getting the “partition number” instead of row_number

Figure a table with the following data, how to exclude 'alone' values? id; data (1, 'foo'), (2, 'foo'), (3, 'foo'), (4, 'bar'), (5, 'bar'); (6, 'jak'); I try with this: select id, data, row_number()...
0
votes
1answer
25 views

Postgres value that never goes below zero

For my application i need to make a row with a value that can never go below zero. I know about check but this one can't fail the query if i do for example: update table set special_row = special_row -...
1
vote
1answer
22 views

Postgres backup PITR strategies [on hold]

Note:-Might be the question sounds too broad. But, I believe ,it's very good platform(stackexchange) and I can get advice from worldwide expert DBA here. Since, I'm not able to make decision or choose ...
3
votes
1answer
3k views

postgres backup for specific table with blob oid

I have a table which has an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table only has 10 entries (which is approximately 30 ...
0
votes
1answer
21 views

How to find out unused port on Linux server

We are trying to use port 5444 for our new PostgreSQL instance and we are not sure whether 5444 is used or unused. Could you please help us to find the answers to the below questions. We want to ...
1
vote
1answer
20 views

Unable to update a record with a trigger using a dynamic column on a PostgreSQL DB

I'm trying to get a column named XXX_LASTCHANGE updated whenever a record is updated. As the XXX would suggest, this is a dynamic value I want to pass with the trigger definition rather than creating ...
2
votes
2answers
1k views

How to accurately measure query time in PostgreSQL (because EXPLAIN ANALYZE does not seem to do it)?

I know that EXPLAIN ANALYZE returns the query time, but it does not appear to do so for my use case. (Unless I am missing something...what could it be...?) SELECT cars.*, users.*, makers.*, places.* ...
0
votes
1answer
19 views

Dump pglogical Config to files

We use the tool etckeeper to keep track of the configuration of servers. I would like to dump the configuration of pglogical to files for this purpose. I tried this: db@host:~$ pg_dump -t '...
6
votes
0answers
208 views
+100

Postgresql 11 logical replication - stuck in `catchup` state

I'm running two postgresql 11 servers - master and slave (setup with logical replication). The problem I'm facing is that today after weeks of uninterrupted work slave got out of sync with this error ...
3
votes
1answer
4k views

Postgres: Difference between Tuples inserted and Live tuples

Many apologies in advance, but I was not able to find this out myself: What is the difference between live tuples and tuples inserted in postgres statistics? There's this post, but it doesn't answer ...
1
vote
1answer
6 views

Inherit default privileges: every table created by *any member of* role A is readable by role B

If I do this ... ALTER DEFAULT PRIVILEGES FOR ROLE engineering IN SCHEMA reference GRANT SELECT ON TABLES to analytics; I get ... This syntax runs and has a valid meaning. The way it is ...
1
vote
1answer
21 views

Expressing the largest floating point value in PostgreSQL?

How to express the largest floating point value (for FLOAT8) in PostgreSQL? I need it to replace 'Infinity'::FLOAT8 during conversion to jsonb field values, because json does not allow NaN and ...
1
vote
1answer
363 views

Is there a way to enforce uniqueness of arbitrary key-value pairs in a JSONB object column

I use Postgres JSONB columns to store translations for dynamic content of a multi-language site — locales are keys, translated strings are values ({"en": "Hello", "ru": "Привет"}). There're ...
3
votes
2answers
4k views

Assign to NEW by key in a Postgres trigger

In the trigger body, how can I assign a value to NEW by it's field name? That's what I want to do: some_key = "some_column"; NEW[some_key] = 5;
4
votes
2answers
1k views

Reg: Default Privileges on PostgreSQL Schemas

I have a situation where many users can create database objects across all the available schema. There is a generic user pg_dump_user used for by all the developers to dump the whole database to ...
1
vote
1answer
52 views

Which PgBackRest policy for differential backups in PostgreSQL

I set up pgBackRest and PostgreSQL configuration in two different servers. I set up with crontab one full backup a week and one differential backup a day. 0 1 * * 0 pgbackrest --type=...
0
votes
1answer
29 views

Design a table of items with many features; how to group those features

I have this table Storeswhich represents stores. Those stores are managed for many types of persons, like Administrators, Managers, Supervisors and so on, i.e. Each store has an administrator, a ...
1
vote
1answer
74 views

Postgres - Foreign Key Constraint not working [on hold]

I have a UAT server where the foreign key constraint is not working at all as I can insert any value not in the parent table. The same table with same data works as expected in production. Please ...
0
votes
1answer
28 views

After bulk load, how to ensure an updated visibility map?

We have a number of tables that have an active version, an inactive version, and a view pointing at the active one. We periodically truncate the inactive table, drop the indexes, load a bunch of data ...
1
vote
2answers
48 views

join vs virtual table in query performance

Suppose I have two tables linked by a foreign-key example DDL's CREATE TABLE raw_collection ( info_datetime DATETIME NOT NULL, asset_id INTEGER NOT NULL, price FLOAT, PRIMARY KEY (...
0
votes
2answers
34 views

pg_basebackup failing from another server

I want to take backup of my PostgreSQL database cluster from another server. I'm using below command ,from another server: [root@shiwangini2 pgsql]# sudo -u postgres pg_basebackup -h 192.168.XX.XX -...
4
votes
3answers
62 views

Expression index on a citext column ignored, why?

Running on RDS with about 32M rows. PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit Also testing locally on macOS with about 8M rows. ...
1
vote
1answer
370 views

Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script?

Let's say I have 20 different schemas on a Postgres database. I create a new user theUser and now I want to grant only the SELECT and INSERT privileges for this user on all schemas. Is there an easy ...
0
votes
0answers
22 views

Size and performance different on UPDATE with/out intermediate CTE

I've got two tables with an indexes id column in common. I'm trying to add a new column in the target table using the value of a column in the source table. The two tables are rather large with couple ...
0
votes
1answer
505 views

PostgreSQL warm standby: sync WAL files

I'm new to PostgreSQL (using ver 8.4.20) and need to setup a master and standby SQL server setup. I am planning to use warm standby setup. I have read the docs on PG and understand the basics, but a ...