The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
11 views

Need postgres statement that returns non unique records even if I pass same ids in array

For Example, the following query SELECT id, name, price from tennis_ticket where id=ANY('{3,3}') returns: id | name | price ----+--------------+------- 3 | Eighty | 80 (1 row)...
0
votes
2answers
95 views

How to delete records without a primary key in a stardard way (i.e. not using ctid)

Case of use We have a log table without a PK with two columns (date, event); You inserted a row. Then you want to undo the inserted record. Is there a way to delete that row (without deleting ...
1
vote
1answer
46 views

How to change Postgres primary key column values?

My postgres database has a column called "id" that runs from 40,000,000 to about 50,000,000. The "id" column is the primary key. I need to change the "id" column values such that they span different ...
4
votes
1answer
461 views

Test Driven Design for postgres procedures

I am looking to introduce a test driven design style to writing my stored procedures for a postgresql database implementation. I have seen that pgTap is a popular unit testing tool for postgres and ...
8
votes
3answers
6k views

How to unnest and GROUP BY elements of a JSON array?

Given the band table, with a json column holding an array : id | people ---+------------- 1 | ['John', 'Thomas'] 2 | ['John', 'James'] 3 | ['James', 'George'] How to list the number of bands each ...
1
vote
0answers
53 views

Schema for social network using the supertype/subtype pattern

Scenario I'm trying to come up with a schema for a kind-of social network app, where users can publish Posts (and photos inside Posts), and also manage a private Diary that has also Posts (not sure ...
1
vote
1answer
91 views

Postgres 11 declarative partitioning - using large number of partitions

We are running a system using Postgres 11 and we implemented partitioning with ID which is most convenient to us but which produces many partitions (currently over 1000 with expected total of 10000 ...
1
vote
1answer
2k views

relation “(table name)” does not exist in non-public schema [closed]

Windows, pgAdmin, PostgreSQL server hosted on Amazon AWS. Logged in on PgAdmin 4 as admin/owner, accessing an admin created database called 'client'. I have two identical post-gis enabled tables, one ...
0
votes
1answer
30 views

PostgreSQL planner not using PK index

I have a query: select F.DVALUE as f1, VF.VAL as f2, f_strlen( VF.VAL ) as f3 from PR_STR_TO_LIST( '37593' ) L left join DUALS U on U.NUM >= 0 and U.NUM < 2 left join ...
0
votes
1answer
1k views

pg_upgrade issue from postgres 9.3 to 9.6

I am trying to use pg_upgrade to upgrade postgres server from 9.3 to 9.6. I have both binaries on the windows server R8. Here are the 2 pg_hba.conf : > 9.3 : # TYPE DATABASE USER ...
0
votes
1answer
35 views

Postgresql - sequences bigger than bigint

PostgreSQL sequences (CREATE SEQUENCE) are limited to bigint (which is usually big enough). But in my case I need a bigger sequence. The table will not have this much records, it will be cleaned over ...
0
votes
1answer
596 views

Having issues with postgresql, what should I log?

Here's the error message I am receiving. could not translate host name "localhost" to address: hostname nor servname provided, or not known From my searching, it seems like postgresql has crashed ...
1
vote
1answer
46 views

How To Access My PostgreSQL Server From Another PC Securely?

I want to be able to connect from any PC anywhere to my Database. Host OS: Windows 10 PostgreSQL: 12.0 Currently all Tutorials say the same: (Thats what i also did) Change listen_addresses to ...
4
votes
1answer
220 views

Pagination - Text comparison with greater than and less than with DESC

I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if ...
0
votes
2answers
316 views

pgbouncer on separate server than postgresql

Right now I am hosting postgresql and pgbouncer on the same Linode with 48GB of RAM and 12 Cores. The server gets many short lived connections per second. The server load will increase with time and ...
5
votes
2answers
10k views

Returning empty string when string_agg has no records

I am trying to return a text field in a PostgreSQL query that is of the form 'stringOne' || string_agg(field, ',') || 'stringTwo' where for certain elements in the group clause, field is always null....
2
votes
1answer
44 views

Trigger working on more than 50 million records [closed]

I have a table with more than 50,000,000 records. The table doesn't have any partition. The table is in PROD. I need to create a trigger there which will update previous entries flag to false for ...
0
votes
1answer
84 views

PostgreSQL's PGP_SYM_ENCRYPT log security

In one of our project, I tried to use PostgreSQL's security extension pgcrypto. I found that is function, PGP_SYM_ENCRYPT is quite handy. Using this function enable me to query encrypted data with ...
0
votes
2answers
104 views

Postgres Procedure Returns Table

We have a table structure returned by this function: RETURNS TABLE (ticker VARCHAR, current_recommendation INTEGER, previous_approved_recommendation INTEGER, ...
0
votes
2answers
266 views

How to get sum of last previous X days of everyday?

Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday. For example my graph will include Sum of Day - 60 to 30 Sum of Day - 59 to 29 Sum of ...
1
vote
2answers
2k views

Is there a recommended way to update a database schema when updating and app's RPMs?

I have a pretty standard server layout: 2 Application servers running Tomcat on Redhat. 2 Database servers running Postgres on Redhat. One database is a master and replicates to the slave. I'd like ...
1
vote
1answer
286 views

How to store user activity that supports undo?

I can track user activity on posts, so that users can see for example "2 updates!". The essence of the strategy I’m using leverages 2 tables: post_activity: post_id | user_id | time_created ...
0
votes
1answer
19 views

How do i disable or limit Postgres Replication creating causing too many WAL files generated?

On AWS RDS PostgreSQL 10.7, I turned on logical replication for a one specific table . on publisher: CREATE PUBLICATION mypublication FOR TABLE <mytable>; on subscriber: CREATE SUBSCRIPTION ...
0
votes
1answer
738 views

Visual Studio Code Postgres SQL Connection

I would like to use Visual Studio Code to preform my PostgreSQL queries, and general DB maintenance as an alternative to pgAdmin4. I have read the documentation included with the appropriate extention ...
0
votes
1answer
29 views

plpgsql - stored procedure Comma separated values in parameters

Im writing a stored procedure to get database and table names in variables and then I'll push these values into IN clause. CREATE OR REPLACE PROCEDURE bhuvi(db varchar(100), tbls varchar(100)) ...
1
vote
1answer
22 views

Querying for a specific language and if not found use default one instead

I currently store all language data (including default language) in my database. Generally a simple query making the assumption the language exists is as simple as doing: SELECT a.artifact_id, ...
0
votes
0answers
19 views

Calculating order rank after union

I'm trying to run a query such as this: select id, rank() over(order by last_name, age, id) as position from ( select id, first_name, last_name, age from public.user union select '925c26bf-5971-...
0
votes
1answer
78 views

How to create multiple tables based on existing tables in postgreSQL?

I have several tables, and I need to create a matching _hist table for each one. For example if I have: CREATE TABLE public.test ( first text, middle text, "last" text, ...
0
votes
1answer
12 views

PostgreSQL autoincrement up to certain number then reset for sharding

I've was recently reading an article around how Instgram used to shard their IDs and they use a unique function to generate ids on their servers https://instagram-engineering.com/sharding-ids-at-...
-1
votes
0answers
20 views

Postgresql Connection Pool & Pg Bouncer

I am trying to understand connection pooling and PgBouncer. I have a DigitalOcean Postgresql Database and .net core api and it seems I reached the connection limit (api was returning 500 with error - ...
4
votes
1answer
706 views

Is there a strategy to use timestamp as primary key?

The majority of times we use integer sequences for primary keys to maintain uniqueness of row in a table. Couldn't we use timestamps instead of integers for this purpose? Is there any flaw in that ...
0
votes
0answers
20 views

Postgres: I set log_hostname on but still client_hostname is null in pg_stat_activity

I have this problem with Postgres. I need to get the client_hostname from pg_stat_activity. I set log_hostname=on on the server but still client_hostname is null in pg_stat_activity. What am I ...
7
votes
1answer
144 views

Which data model/schema to apply to a timeseries datastore for data sources with varying fields

I'm asked to develop a data-storage for time-series data, yet despite significant research I'm unsure about the data model and storage technology to chose. About the data The source data which is ...
3
votes
1answer
539 views

Does PostgreSQL support ICU collation's options and settings?

ICU Specifies different LDML Collation Settings. Some of them seem pretty interesting, especially the ones on case and accent, “Ignore accents”: strength=primary “Ignore accents” but take case ...
2
votes
1answer
55 views

Database schema for inventory management for multiple people

I am setting up a Postgres database that keeps track of items that entities in my system own. Each incoming datum says that Entity Q has N of Item X. My first instinct is to have a separate table ...
0
votes
0answers
72 views

logical replication in postgresql - “server closed the connection unexpectedly”

I'm running two postgresql servers and I'm trying to setup logical replication. These are extra bits of configuration I added on master in order to make replication work: wal_level=logical ...
6
votes
3answers
4k views

How do I validate all my constraints in postgresql?

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html I now have e.g. duplicate primary ...
5
votes
1answer
71 views

Grouping by overlapping arrays, transitively, without duplicates

I've found: Group by array overlapping https://stackoverflow.com/a/23650080/5419599 However, I'm having trouble putting it to use in my case. I have a table like so (the real myid values are hashes,...
0
votes
1answer
17 views

Parse a unix/epoch value as a `TIMESTAMP` in PostgreSQL

PostgreSQL's COPY command is useful to quickly import lots of data, and the data must be in the textual representation for the datatype. I am importing lots of data, includes a timestamp column, but ...
0
votes
1answer
376 views

Postgres row_to_json precision for numeric type

I am using the row_to_json function to convert a table row to a json object. There are a few columns in the table that are of type numeric which contain float values. When row_to_json returns the ...
1
vote
1answer
39 views

Improve query performance of subquery with MAX()

I am looking to migrate our support database from MS SQL Server to Postgres and most of it has gone OK. However, some of the queries seem to be slower on PG than on the SQL Server. We have a ticket ...
0
votes
1answer
18 views

Revoke User Access to all System Information Functions on Postgresql

Is there a way to remove access to all system information functions for a given user (which is already limited to read only access) in Postgres? For example, a user could normally run ...
0
votes
0answers
12 views

How to reload repmgr configure?

How to reload the configuration after updated /etc/repmgr.conf?
1
vote
1answer
307 views

Execution time of triggers fired when a procedure is executed in postgresql

I have a stored procedure which may delete and/or insert tuples into a database table. This table has an AFTER INSERT OR DELETE OR UPDATE trigger. Basically I want to retrieve how much time was spent ...
2
votes
1answer
49 views

Unexpected behaviour for to_date() with week number and week day

I tried to convert a date string (year, week, day of week = 'YYYY-WW-D') into a date using the to_date() function. I got these results (demo:db<>fiddle): date string | to_date() -----------------...
0
votes
1answer
21 views

Postgres VACUUM VERBOSE information

I am using Postgres with psycopg2 and sometimes when I run VACUUM VERBOSE table_name; it gives me a line giving the oldest xmin and sometimes it does not. Is there a way to make sure that it always ...
1
vote
2answers
34 views

How can I create a user who could only create tables and can't delete/drop them

There's my problem. I'm using PostgreSQL and tried so many things to create a user who can create tables but can't drop them, it's like create a table but assign it to another user. I've tried one ...
13
votes
2answers
5k views

Fast hamming distance queries in postgres

I have a large database (16M rows) containing perceptual hashes of images. I'd like to be able to search for rows by hamming distance in a reasonable timeframe. Currently, as far as I properly ...
0
votes
2answers
58 views

Table with too many nullable columns - is there a better design?

I have a user table with around 30 columns. Most of these columns are almost always null unless user decides to share additional info about themselves. For example, weight, height, religion, ...
0
votes
1answer
30 views

UUID as primary key and download ID

I'm designing a Django Rest Framework API with PostgreSQL. In the database I'll be recording information about documents which can be requested for download. I want the request to include a unique id, ...