Tagged Questions
0
votes
1answer
21 views
Insert `tsv` files into postgresql db
I have several files which are saved as tsv. I want to insert them into a postgresql db, to analyze them with sql.
However, my problem is how to INSERT this tsv files into postgresql 9.2 under ...
0
votes
1answer
31 views
Postgres join categories
I need to join subcategories to general categories, I have page with list of all categories and subcategories, subcategories have one category array ex. cat = {10,17}, that array contains ids of ...
1
vote
2answers
35 views
How to store a handful of relational data (e.g. user roles) in postgres 9.3?
What's the best way to store a handful of relational data in postgres 9.3?
e.g. what roles each user has (editor, publisher, admin, viewer,
contributor)
e.g. file extensions readable by app (pdf, ...
2
votes
3answers
65 views
Is it bad design to use arrays within a database?
So I'm making a database for a personal project just to get more than my feet wet with PostgreSQL and certain languages and applications that can use a PostgreSQL database.
I've come to the ...
1
vote
2answers
39 views
How to do nickname searches in Postgres
Hi guys so I have a question about nick name searches.
I have a very large database and in my Accounts Entity I have a firstname column. When a user searches for an account by first name, it is ...
1
vote
2answers
44 views
Check Constraint Usage
I am trying to make a constraint that will keep ids unique for specific users.
Each user is a separate entity within the world so 2 people having 1 as id is not a problem. I just don't want one ...
1
vote
5answers
77 views
Allow null in unique column
I've created the following table:
CREATE TABLE MMCompany
(
CompanyUniqueID BIGSERIAL PRIMARY KEY NOT NULL,
Name VARCHAR (150) NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL UNIQUE,
...
0
votes
0answers
85 views
Ways to horizontally scale a multi tenant application with per schema per tenant approach [closed]
We are working on database architecture of a multi tenant application, and our application context/domain is best fit for per schema per tenant approach, but we concern about scaling on this approach, ...
0
votes
0answers
68 views
Multiple schemas on multiple machines in postgres
Scaling multiple schemas on multiple machine, I am using PostgreSQL as backend system, now I want to perform the lookup of schema that on which machine the schema resides, so like I have two machines ...
-1
votes
0answers
30 views
Reverse engineering radio buttons with multiple options and its sub-values
I need to reverse engineer below field in Rails. I've tried various methods like saving into another string column then reference this from types table with belongs_to relationship. This doesn't seem ...
0
votes
0answers
31 views
Rails how to implement hierarchical associations
Is there a Rails way to implement hierarchical associations?
I'm currently trying to map below layout fields in database:
Product -> Product URL -> No Link
-> ...
1
vote
4answers
82 views
Store the day of the week and time?
I have a two-part question about storing days of the week and time in a database. I'm using Rails 4.0, Ruby 2.0.0, and Postgres.
I have certain events, and those events have a schedule. For the event ...
1
vote
1answer
36 views
What is the simplest way to save a file-tree in a postgres database?
I'm retrieving a file-tree from the dropbox api. In the api every folder is read with a separate, api call, so I will iterate through the entire file-tree to get all the folders. This is done with a ...
2
votes
1answer
55 views
Structuring a database
In my database I need the following relations:
Tournament
Tournament Participant (Tpart)
relates a User to a tournament
Round
This is a single match
Hole
relates a Tpart to a round
also ...
1
vote
1answer
92 views
query planner inaccurate choosing nested join
I have this from EXPLAIN ANALYZE
-> Nested Loop (cost=2173.66..30075.48 rows=77 width=4)
(actual time=30.949..399.463 rows=95959 loops=1)
So there's a difference in almost 3 ...
1
vote
2answers
45 views
Comment on (write once) database design storing network information
I have raw logs of up to a week of associations of wifi enabled devices to the wifi routers in my Institute.
As of now I plan to put it in a database as follows:
One big table having columns:
...
1
vote
1answer
67 views
Database design decision: normalization or repetition?
I'm building a delivery system, by now, my design looks like that:
The problem is, very frequently, I'll need a structure (array, json, objects...) that looks like that (very hierarchical):
The ...
0
votes
1answer
63 views
Conditional Statement in PostgreSQL Query
I have here 3 tables namely:
person (
id int PRIMARY KEY
,fullname text)
phonenumber (
id int PRIMARY KEY
,personid int REFERENCES person(id)
,phonetypeid REFERENCES phonetype(id)
,number ...
2
votes
2answers
87 views
Tabular view of data from normalized SQL database
I've tried creating a normalized database, but I'm having trouble displaying the data in a tabular format.
In my example below, the database is used to track numbers (in various, arbitrarily-named ...
3
votes
1answer
147 views
Many-to-many relationship with non-zero cardinality
Let's say I have a PostgreSQL database with tables A, B and C, where A and B have a many-to-many relationship via junction table C. These tables have the following SQL definitions:
CREATE TABLE A
(
...
1
vote
1answer
82 views
Display table where foreign keys are values from second column of referenced tables
Is it possible to write such a universal query? By universal I mean that I won't need to type column names that are foreigns keys, so that I can use this query for other tables. Simple example:
...
4
votes
1answer
91 views
How to programmatically check if row is deletable?
Say we have a PostgreSQL table like so:
CREATE TABLE master (
id INT PRIMARY KEY,
...
);
and many other tables referencing it with foreign keys:
CREATE TABLE other (
id INT PRIMARY ...
1
vote
4answers
70 views
Propagate UPDATE to dynamically determined table
In PostgreSQL I have created trigger procedure to do some validate and to execute some queries before inserting or updating rows in table.For insert operation my trigger works well so I'm executing ...
1
vote
1answer
77 views
Postgres query optimization which performs a seq scan
I have a query which takes 7 seconds to execute. I am trying to optimize it.
This is the query:
explain analyze select count(*) from ab_view inner join ab_trial on (ab_view.trial_id = ab_trial.id) ...
0
votes
0answers
67 views
ERM tool for PostgreSQL with inheritance and SQL-export [on hold]
I'm searching for a database design tool for a while. Maybe someone can help me?
It provide visual erm-design, export to postgresql-code and the big problem, it should understand/provide postgresql ...
0
votes
2answers
52 views
Change the character varying length with ALTER statement?
I am trying to change length of a column from character varying(40) to character varying(100).
Following the method described in this question Increasing the size of character varying type in ...
1
vote
2answers
137 views
What's a good database design for hierarchical data versioning with Hibernate?
I need to store a history of changes for hierarchical data. The data is a one-to-many relation: a setting entity has many properties. Here are my tables:
Setting
------------------------------
Id ...
1
vote
3answers
146 views
Inventory Management: How do I handle sold inventory units in the database?
i sell liquor. so i have an inventory of bottles. so far i have an "InventoryUnit" model which references product and line_item.
should every single bottle be stored as an individual InventoryUnit ...
0
votes
2answers
51 views
How do I relate data without one single master or starting point
I have a set of "people" data that contains identity numbers. This data comes from various sources in a format like below
Source1: IDNumber:I1, Passport:P1,SocialSecurity:S1,DateOfBirth,13/03/1967
...
0
votes
1answer
43 views
Heroku Postgres Django: Best database configuration for efficency?
I am building an eCommerce application that will be hosted on Heroku, use their Postgres database, and the app will be built with Django. There will be a massive amount of products & product ...
heroku
1
vote
1answer
59 views
Query XY array pair for y value at arbitrary x in SQL
I'd like to make a database of products. Each product have characteristics described as an array of x values and corresponding y values.
And I'd like to query products for certain characteristics.
...
2
votes
1answer
56 views
Use PostgreSQL database from RAM
I'm learning how to create a fast postgresql cluster to a web app in my job. I already know it's possible to create a tablespace on a virtual disk 1 2 3, mounted with ramfs or tmpfs, so my idea is:
...
1
vote
1answer
54 views
Many to Many Constraint to same parent table
I'm trying to constrain that a many-to-many relationship between two entities ensures that those entities both share the same additional parent entity.
ie, Job, Claim, Category, ClaimCategory
Claim ...
1
vote
2answers
28 views
One field with many “precisions” for each data type?
I have about 50 product types in my PostgreSQL 9.2.3 database.
Some of them are divisible, some are not.
What makes things more complicated is that "volume" of different products should have ...
0
votes
3answers
44 views
See which posts user has viewed
I have a table user it has:
id INT PRIMARY AUTO_INCREMENT
name VARCHAR(30) NOT NULL
Also I have table post
id INT PRIMARY AUTO_INCREMENT
post TEXT NOT NULL
If user visits a certain post I would ...
0
votes
1answer
56 views
how do I split database models based on a single spreadsheet table (beginner)?
My question is conceptual, I suppose, rather than practical (though I don't mind if the practical makes an appearance).
I'm a graphic designer that is now building a web application at my workplace ...
1
vote
1answer
118 views
Trigger vs. check constraint
I want to add a field-level validation on a table. There is a field named "account_number" and this field should always pass a "luhn" check. I've found a function called "luhn_verify" that seems to ...
1
vote
1answer
248 views
What are the minimum hardware requirement & setup for a PostgreSQL cluster with Rails frontend with a lot of small writes and long reads?
Background:
Trying to figure out my minimum requirements for an app I'm building. I'm fairly fluent in MySQL and PostgreSQL for a developer, but I'm not a DBA, hence my question.
I'm building a ...
0
votes
2answers
131 views
Sane way to store different data types within same column in postgres?
I'm currently attempting to modify an existing API that interacts with a postgres database. Long story short, it's essentially stores descriptors/metadata to determine where an actual 'asset' ...
1
vote
1answer
64 views
Strategy to maintain large table of backup catalogs per project in MySQL or PostgreSQL
I am developing an LTO backup/restore solution based on gnu tar.
We either keep the tapes inhouse, or the customer might buy these backups from us.
Hence the choice of a widely available, and free ...
2
votes
2answers
194 views
Disadvantages of table with too many columns
I have some data that I need to put in a PostgreSQL database. These data are related to schools. So there are a LOT of attributes relating to the school, mostly small integers, floats or small texts. ...
0
votes
2answers
101 views
How to model database where different users/roles are in a project table, while all of them have to belong to user table?
I am trying to model a database for my current project and I came across a new problem. I have a Project which is supervised by Supervisor, Coordinator and Company. So Project table has Supervisor.id ...
2
votes
2answers
200 views
PostgreSQL partitioning with joined table - partition constraint not used in query plan
I have a large table in PostgreSQL 9.2 that I've partitioned as described in the manual. Well... almost! My real partition key is not in the partitioned table itself, but in a joined table, like this ...
0
votes
2answers
36 views
trigger or calculated columns or update or view
I have two csv files file1.csv and file2.csv I load those files in
two tables in a database 'table1' and 'table2'. table1 contains
two columns, those columns use table2 columns to calculate ...
0
votes
2answers
297 views
currency exchange database schema [closed]
I'm trying to create a database schema for currency exchange. I want to have information about various currencies every 30 seconds. This is the schema I had in mind.
Column SQL ...
1
vote
2answers
118 views
PostgreSQL internals composite column cardinality
EDIT
This is not a question about space, but about uniqueness of index, which affects the query plan.
In terms of cardinality, which index scenario is higher:
A
Table:
(
Col1 smallint,
Col2 ...
0
votes
1answer
148 views
Should I place EAV values in a datatype table?
I am designing a database for entities like stores, products, customers, suppliers and warehouses. The database will be used as an online transactional system in different physical stores.
In my case ...
1
vote
1answer
105 views
Login system using physical postgres users not “logical” users
I'm currently building an application that has to conform with SOX auditing requirements. One of these, is that all inserts, updates and deletes (but delete you can ignore), need to leave a trail that ...
0
votes
1answer
65 views
Filter 2-dimensional array
I have this array:
1:0, 2:0, 3:0, 4:0, 5:0, 6:0, 7:0, 8:0, 9:0, 10:0,11:0,12:0,13:0,14:0,15:0,16:0
17:0,18:0,19:0,20:0,21:0,22:0,23:0,24:0,25:0,26:0,27:0,28:0,29:0,30:0,31:0,32:0,
...
1
vote
1answer
123 views
Is it a good idea to use mongodb , inmemory db and postgres together? [closed]
Our application deals with both report and transactions (OLAP and OLTP).
The application needs to display the generated report in form of charts and tables. And other part of application deals with ...
mongodb
