Skip to main content
Software Engineering

Questions tagged [postgres]

Ask Question

PostgreSQL, A.K.A Postgres, is an open source object-relational database management system (ORDBMS).

152 questions
Newest Active Bountied Unanswered
Filter by
Sorted by
Tagged with
173 votes
21 answers
164k views

I'm working at a project in one of the world's top 3 IT consulting firms, and was told by a DBA that company best practice's state stored procedures are not a "best practice". This is so contrary to ...
Community wiki
4 revs, 3 users 86%
user673740
134 votes
10 answers
72k views

I'm working on a wishlist system, where users can add items to their various wishlists, and I plan to allow users to re-order the items later on. I am not really sure about the best way to go about ...
Tom Brunoli's user avatar
  • 1,443
41 votes
3 answers
48k views

Is varchar just a remnant from before text came around, or are there use cases where you would want to use a varchar? (Or char for that matter..) (I use Postgres and MySQL (MyISAM) daily, so those ...
Izkata's user avatar
  • 6,118
19 votes
2 answers
83k views

I'm creating a dashboard which should monitor a database table. I have only database access (no application layer). The table is rather large (10 million rows), however not changing rapidly (100 ...
Kiril's user avatar
  • 529
14 votes
5 answers
8k views

I have an inventory of products stored in Postgres. I need to be able to take a CSV file and get a list of changes—the things in the CSV file that are different to what is in the database. The CSV ...
Isvara's user avatar
  • 630
14 votes
6 answers
5k views

I'm designing an application which will in an early stage collect data A, B, and C from clients, but later on will instead collect data A, B, and D. A, B, C, and D are very related and right now ...
Jad S's user avatar
  • 571
12 votes
3 answers
6k views

In postgres, I need to store the hours a merchant is open each week. Essentially, the information you'd see posted on a sign in the merchant's door: Mondays: Closed Tuesday-Friday: 9am-1pm, 2pm-6pm ...
Jonah's user avatar
  • 1,496
11 votes
9 answers
28k views

I always thought the largest scale of banks use Oracle. However, there is no proof they really use Oracle instead of Mysql or PostgreSQL, nobody knows the secret. Any idea what they really use? Can ...
YumYumYum's user avatar
  • 367
10 votes
5 answers
3k views

I know that triggers can be used to validate stored data to keep database consistent. However, why not perform validation of data on the application side before storing them into the database? For ...
Yan Khonski's user avatar
  • 211
8 votes
2 answers
2k views

I've spent the last year becoming really comfortable with MySQL, but due to its increasing trendiness and my desire to homogenize my web-apps with Heroku, I'd like to start using PostgreSQL for my web ...
Andrew Latham's user avatar
  • 586
7 votes
3 answers
582 views

I'm quite new to software development and whenever I come across small projects which involve storing (relational) data I always ask myself if something like a micro-blog (or any other project with ...
One One's user avatar
  • 89
7 votes
4 answers
4k views

We started a project recently, and during the design process, we have some arguments among team members. We have to deal with enumerations so we ended up with three choices. Have the enumerations as ...
pik4's user avatar
  • 385
7 votes
1 answer
10k views

I'm building an app where I need access to quite large reports about different publicly accessible URLs, JSON objects about 200kb to 500kB in size. Each user would generate hundreds of these reports ...
any_h's user avatar
  • 171
7 votes
4 answers
12k views

Note: Yes, I know that storing functions in databases should be punishable by law. We are developing a financial web application using PostgreSQL, Sinatra and AngularJS. As you may have guessed, a ...
Ianis Vasilev's user avatar
  • 197
7 votes
2 answers
2k views

Problem description I have a situation where I want to enforce that a relationship can only be defined once and I want to solve it on database (Postgres) level. The following situation I consider ...
Dynom's user avatar
  • 179
6 votes
5 answers
939 views

In Postgres, it used to be quite common to use a 4-byte integer auto field for primary keys, until it started becomming somewhat common to run into the 2147483647 limit of 4-byte integers. Now, it's ...
orokusaki's user avatar
  • 1,093
6 votes
2 answers
797 views

We have to migrate an old (million LOC) system from SAP ADS (formerly Sybase) ISAM based (so called free tables) based system to a PostgreSQL database. The system implements kind of multitenancy ...
πάντα ῥεῖ's user avatar
  • 1,583
6 votes
1 answer
3k views

I have a particular problem addressing the PostgreSQL advisory locking functions using the bigint variants. Basically I want to create a 64 bit bigint value from a text type obtained with the ...
πάντα ῥεῖ's user avatar
  • 1,583
6 votes
1 answer
192 views

I want to create a class with static constants which are used whenever a part of an application want to access the database in any way. This way I want to remove all magic numbers and string inside my ...
maxik's user avatar
  • 163
6 votes
1 answer
2k views

Imagine I have a marketplace application - where users can search for products (we concentrate on clothes). Every product has an ID, name (text), description (text), price (numeric), size (numeric), ...
Anton's user avatar
  • 161
5 votes
2 answers
174 views

I have written a warehouse management web app. The application handles batch picking, warehouse routing, packing, and the final piece is handled by UPS worldship to "ship" the packages. Worldship will ...
Ominus's user avatar
  • 1,879
5 votes
4 answers
5k views

Where I work we have over a dozen internal SW systems that we have developed in-house using a variety of technologies, frameworks, and languages. Those include various from Microsoft (ASP.NET Web ...
RSW's user avatar
  • 151
5 votes
3 answers
449 views

I am taking over management and development of a website developed in Java using Hibernate and Postgres, running on Apache/Tomcat. I am an experienced Java developer, and have experience in Ruby on ...
kevin cline's user avatar
  • 33.8k
5 votes
1 answer
2k views

Recently I've discovered such a thing as a Apache Mesos. It all looks amazingly in all that demos and examples. I could easily imagine how one would run for stateless jobs - that fits to the whole ...
zerkms's user avatar
  • 776
4 votes
3 answers
2k views

We're developing an application whose data domain (or at least 90% of it) can be modeled effectively using a relational database. We've been using PostgreSQL since the beginning and have had no ...
Tony E. Stark's user avatar
  • 437
4 votes
2 answers
498 views

In an application, I have a recursive folder structure (like folders in OS X or Windows file system). Each folder can contain three kind of things: Other folders (hence the recursive structure) ...
Jivan's user avatar
  • 315
4 votes
2 answers
442 views

I would like to do SELECT * TABLE t (using data from 1st of march 2012) I already have a nice audit trail of all tables in the database. It basically makes a copy of all rows that change, storing ...
David's user avatar
  • 4,449
4 votes
1 answer
2k views

I am setting up a PostgreSQL database with a very small number of tables for a simple website. I am looking at ways to make it as solid and auditable as possible. Is there any particular reason why ...
Max Murphy's user avatar
  • 181
4 votes
2 answers
545 views

I have a great, working web application which uses SvelteKit, DrizzleORM, a PostgreSQL database running on Amazon RDS. It is hosted on Netlify, which means that the “backend” consists of one big ...
Anselan's user avatar
  • 159
4 votes
1 answer
615 views

The pain that I've often felt when creating database migration files, is best described in this Speakerdeck: Sane Database Change Management with Sqitch. Paste entire function to new "up" script ...
Christiaan Westerbeek's user avatar
  • 165
4 votes
0 answers
371 views

Our system is fundamentally a multi-tenant setup, where each client maintains a separate database with its own data and users. The authentication system on login issues an authentication token which ...
moberemk's user avatar
  • 473
3 votes
4 answers
3k views

I want to adhere to the best practices and obviously encrypt the data sent to me by user. And I also want to provide the full-text search feature to the user (user can only search their own data, if ...
miqem's user avatar
  • 105
3 votes
2 answers
2k views

I'm creating a crawler app that will always, in the beginning, when the Class constructor, ensure that the basic database structure exists. Is this a bad practice? What is the advantage OS creating ...
Renato Prado's user avatar
  • 157
3 votes
1 answer
149 views

I'm kind of stumped by a seemingly simple problem in Db design, but the more I think about about it, the more it twists my mind. Let's say I have columns C1 and C2 in a table. How do I enforce that ...
prmph's user avatar
  • 141
3 votes
1 answer
2k views

I need to standardize coding practices for project that compromises, among others, of pl/pgsql database, that has some amount of nontrivial code. I look for: Code formatting guidelines, especially ...
jb.'s user avatar
  • 406
3 votes
2 answers
735 views

We have a project that loads metric data from various etls and services into an AWS SQS queue, where that data is processed and inserted into a metrics database, also running on AWS RDS. When we ...
Igneous01's user avatar
  • 2,333
3 votes
1 answer
258 views

I'm struggling on a design question for my Ruby on Rails website. I'm working on an estimation tool for construction companies for renovation projects. It orders items this way by default: Room (ex: ...
Shrolox's user avatar
  • 149
3 votes
2 answers
3k views

I created a new application and I am thinking where is the best place to generate a UUID. Generate a UUID in application level and persist it Generate a UUID in Database level I have a feeling that ...
pik4's user avatar
  • 385
3 votes
3 answers
4k views

I am designing a database for a document management app for my office. I want to get the backend right so I don’t have issues in the future. Basically all users will be categorized into roles e.g, ...
ss_millionaire's user avatar
  • 149
3 votes
3 answers
949 views

I have a feature I'm building that aggregates data across about a dozen or so tables. This data is aggregated from a heavily joined query. This page then has a lot of filtering, sorting, counting, and ...
Andy Baird's user avatar
  • 174
3 votes
0 answers
912 views

Background Information I have a web app that provides the user with the usual CRUD functionality for "widgets" let's say. But I also have give them the option of creating widgets in bulk via ...
dot's user avatar
  • 581
2 votes
3 answers
3k views

I'm working/playing on an app where individual users' data will be completely independent from each other. Of course, there will be common tables for other parts of the app (e.g. "users"), but for the ...
john2x's user avatar
  • 237
2 votes
3 answers
6k views

I basically have one big gigantic table (about 1.000.000.000.000 records) in a database with these fields: id, block_id, record id is unique, block_id is not unique, it contains about 10k (max) ...
David Hofmann's user avatar
  • 141
2 votes
1 answer
152 views

When you have sharding, horizontal partitioning, you have multiple DBMS instances, and your server app chooses which one to use by some rule that uses record IDs. Well, for example, we have ...
AivanF.'s user avatar
  • 191
2 votes
2 answers
743 views

I referred Compiling vs using pre-built binaries performance?. The post talks about performance and opportunities for optimization. My question is about reliability. I built both python and ...
Rk..'s user avatar
  • 125
2 votes
2 answers
626 views

I have a Django application of 2 GB running and I need to receive a CSV file of more than 1 GB, read it and load the data to a PostgreSQL DB in IBM Cloud. The problem is that if I receive the file, it ...
Elvin Quero's user avatar
  • 29
2 votes
2 answers
1k views

I must store prices for various items on multiple dates. The table schema would look like this: CREATE TABLE date_wise_price ( item_code varchar, date date, price numeric(19,4) ) An ...
Rohit Jain's user avatar
  • 177
2 votes
2 answers
3k views

We have a REST API which calls a third-party REST API to Send Emails. The Third Party API is not super reliable and randomly fails every now and then with a 500. Our Clients do not want to retry at ...
Ahmad's user avatar
  • 139
2 votes
2 answers
143 views

I've seen the url like this domain.com/some-article-title-which-can-be-very-long many times. There's no ID embedded in it. Therefore, in a db it must be defined as "unique article_url varchar(NNN)" ...
Rakori's user avatar
  • 797
2 votes
1 answer
770 views

I built an app that display time series data. The display is very similar to a yahoo finance chart (ie https://finance.yahoo.com/quote/AMZN/). I am using Postgres to store the data because that was ...
achyrd's user avatar
  • 121

1
2 3 4