I am playing with inheritance tables and wanted to see if rows in separate children could share one parent row. I wrote the following script either expecting one row across all three tables or a uniqueness/primary key violation. The result is a single row in each child and two in the parent, as if there were no uniqueness contraint on the primary key. Is this a bug? https://sqlfiddle.com/postgresql/online-compiler?id=f44175e4-93a0-4e19-b659-e693008a3c28 ```sql CREATE TABLE p1 ( p1_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), i_p1 text ); CREATE TABLE c1 ( i_c1 text ) INHERITS (p1); CREATE T ..read more

PGDSAT (Postgresql Database Security Assessment Tool - https://github.com/HexaCluster/pgdsat) has been released. That sounds like fantastic news for PostgreSQL users! Having a dedicated security assessment tool like PGDSAT can greatly enhance the security posture of PostgreSQL clusters. With support for around 80 security controls, including CIS compliance benchmarks, it provides users with a comprehensive solution to identify and address potential security vulnerabilities. Elephant will be more secure from now on I've already tested and provided the summary of checks. You may run it without ..read more

submitted by /u/raia-live [visit reddit] [comments ..read more

Hello r/PostgreSQL Community! I'm excited to share our latest Medium article titled "Why is PostgreSQL Ideal for Enterprise Database Environments?" This article is perfect for anyone who's part of or interacts with large-scale database operations. In the article, we explore the key features and advantages of PostgreSQL that make it a standout choice for enterprise environments. From its robust transactional support to its scalability and reliability, we dive into why PostgreSQL is increasingly the go-to database for many organizations. You can read the full article here: Why is PostgreSQL Ide ..read more

We have a BDR cluster setup for Postgres DB where we have barman as well. We recently checked that our barman is taking base backup just twice a week. We want the base backup to taken on daily basis. How can we do it? Please guide. Where to make changes and what are the pre and post checks that should be considered. submitted by /u/RubNeat2575 [visit reddit] [comments ..read more

I am newbie to vector database. But as part of research I wanted to explore how I can use postgres with pgvector database to play with multidimensional data. Does anyone knows good resource to refer to start on as new learner? any website or on udemy or online course? I want to create a simple dummy table and play with it to find nearest neighbor. Thanks submitted by /u/green9cactus [visit reddit] [comments ..read more

The use case goes like this. There is a signup endpoint on which the user will submit their email and password 4 rows have to be created at once with a couple of ids generated The tables look like this authentication_types +--------------------------------------+----------+ | id | name | +--------------------------------------+----------+ | uuid | varchar | +--------------------------------------+----------+ | primary key | | +--------------------------------------+----------+ | 0aa4d9a9-e024-4792-bc41-36a4f3528d36 | password | +--------------------------------------+----------+ accounts ..read more

[version] OS- CentOS7.9 PostgreSQL -15.2 Citus - 11.3 [asking] I'm studying citus which offers sharding as a postgresql's extension. I want to know about scale-up of nodes and increase number of worker-node. If anyone has run through this testing scenario, I'd love some advice. Please tell me how to simply increase worker-nodes in citus and how to scale up nodes while leaving the number of worker-nodes the same. submitted by /u/Active_Baseball5483 [visit reddit] [comments ..read more

I'm new to coding in Postgresql and would LOVE some help. I'm trying to create a procedure to truncate all of the data in my existing tables, and then update them with data from my raw data table. When I do this I get an error at the beginning of my code after AS: could someone look over my code, and help me with what I'm doing wrong? CREATE PROCEDURE updatadata AS BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION; TRUNCATE TABLE intricate; TRUNCATE TABLE basic; INSERT INTO intricate SELECT * FROM rawdata; INSERT INTO basic SELECT SUM(total), COUNT(id) AS idtotal FROM int ..read more

Hey r/PostgreSQL community, We've just put together a detailed guide on the architecture of PostgreSQL and thought it would be cool to share it here. This article is great for anyone who's into the technical side of PostgreSQL, whether you're a database pro, a budding developer, or just curious about how things tick behind the scenes. In the guide, we dive into the key components of PostgreSQL, explaining how it manages transactions, connections, and its approach to MVCC, all in a way that's easy to digest. We're excited to bring some of the more complex topics down to earth and spark a conve ..read more