Tagged Questions
0
votes
0answers
17 views
rails/postgresql: daily voting, results for time periods
Given there's a model named 'Topic' and a model named 'User'.
There's a static model 'Statement' that has a predefined set of records.
User is able to create Topics and set variable number of ...
1
vote
1answer
26 views
Converting an logical model to a physical model. Trouble understanding ERD
I'm working with an ERD. It is supposedly a logical model and I am supposed to make a physical model from it. I should be formatting in UML and our DBMS is PostgreSQL.
Some of my research ...
0
votes
1answer
17 views
PostgreSQL - set a default cell value according to another cell value
If i have a column say column a of any given values, and i want another column column b to have a default value according to the value of column a
In another words:
if column a = 'peter' then column ...
0
votes
1answer
19 views
How to revoke right to a usergroup on a certain date in postgresql?
I'm currently creating a DB with Postgresql. First time, be gentle. ;)
I have a table project that stores submission date among other things.
I have 2 groups of users. One can only consult the ...
1
vote
2answers
25 views
Rails Database Design with Similar models with different attributes
I am designing an application that has many types of "requests" the requests are to be handled in a very similar nature to one another but they contain different data.
They each have about 1/3 of the ...
0
votes
1answer
33 views
Database Design - Party Object Model and Relating Index's correctly [closed]
Im a developer by trade and I am currently dabbling in database design.
I have been advised on StackOverflow to use the Party Model to achieve the ability to relate customers that may be people or ...
-2
votes
0answers
31 views
Product database design for relations [closed]
We're trying to design a database and system for products.
We have a products table:
Products_table
ProdID|OtherInfo
We have a supplier table.
Supplier_products_1
ID|Price|OtherInfo
...
3
votes
4answers
47 views
Is it possible to use a PG sequence on a per record label?
Does PostgreSQL 9.2+ provide any functionality to make it possible to generate a sequence that is namespaced to a particular value? For example:
.. | user_id | seq_id | body | ...
...
0
votes
1answer
48 views
Foreign key matching in PostgreSQL
Just curious, if I have this table:
CREATE TABLE "post" (
"id" SERIAL,
"revision" INTEGER NOT NULL DEFAULT 0,
"summary" CHARACTER VARYING NOT NULL,
"description" TEXT NOT NULL,
...
1
vote
1answer
40 views
Postgresql: optimizing columns size for numeric fields
I dont understand how Postgresql (9.2) calculate the column size (in kb), I have this tables:
Table d2:
Column | Type |
---------+---------------|
id | serial |
n | ...
-4
votes
1answer
36 views
How to store metadata using foreign key? [closed]
I am using PostgreSQL. I have two types of databases, A & B containing different types of data. However, my task is to store metadata of data contained in these two databases, A & B. So in ...
1
vote
1answer
54 views
indexing multi-column index with Rails / Postgresql
I have a doubt about correct indexing. I use rails 3.2.13, using Posgresql behind. BTW, Probably more a relational databases/indexing question...
I have this table:
# Table name: exams
#
# id ...
1
vote
1answer
161 views
“ERROR: extra data after last expected column” when using PostgreSQL COPY
Please bear with me as this is my first post.
I'm trying to run the COPY command in PostgreSQL-9.2 to add a tab delimited table from a .txt file to a PostgreSQL database such as:
COPY raw_data FROM ...
-1
votes
1answer
28 views
Selecting the surrounding points of a single point [closed]
I am using Postgresql and PostGIS(coordinates)
Example case: A database holds information about restaurants and each restaurant has unique coordinates on a map. I give coordinates to the database, ...
0
votes
0answers
29 views
Database design - People and Organisations [migrated]
Databases are not my primary skill and I am finding I need some assistance please.
The software we are building has "Customers".
The Customer could either be a Person or an Organisation.
I really ...
2
votes
2answers
61 views
Database Logic VS Application Logic
Sorry if this is a dumb question. I've been out of the game for a while.
I am deciding whether to use PostgresSQL for a web app. I have never used it before.
Having done my research, I really like ...
0
votes
1answer
70 views
Is it faster to LIKE 2 columns on 100k records or one column on 200k records? [closed]
Suppose I have a table with column_a and column_b, containing 100k records.
I need to match either column_a OR column_b.
Will it be faster to create a new table containing only one column to match ...
0
votes
4answers
89 views
Schema design: How do you incorporate “total”?
I am currently building a scoring table for my items.
Each item has a score so the database (postgres) can sort the item by scores and return them to the user.
Currently, the total score of the ...
1
vote
1answer
79 views
postgresql: data type for md5 message digest?
I want to use the MD5 message digest of some string as the primary key of a table. What datatype should i use for such a field? What select and insert statements should i write for the field?
1
vote
2answers
200 views
How to get column attributes query from table name using PostgreSQL?
I have a project and I need a query to get all attributes of the columns (Column Name, Position, Data Type, Not Null? and Comments) all this using table name.
I achieved get Column Name, Position ...
1
vote
4answers
78 views
Ensure that a column can only be modified if it IS NULL and only by some users
I have a column A (type int) in a table for which data is not available at insert time of the other values. I do not want to split the table, as there is no other real reason to do so. I am ...
-1
votes
2answers
38 views
Database table design for situation dependent data
Let's say you have a table that will contain data that is very situation dependent.
An example would be a collection of games where the player makes a selection to win. This selection should be ...
1
vote
2answers
28 views
Use RETURNING without explicitly naming the columns
I have a table with more than a few columns and want to return the id from the INSERT query using RETURNING. Unfortunately it seems that in this case I have to specify ALL columns
INSERT INTO "user" ...
1
vote
2answers
82 views
Dropping column in Postgres on a large dataset
So I have a table with a large dataset and this table has a three columns that I would like to drop.
The question is: how will Postgres deal with it?
Will it walk through every entry or will it just ...
3
votes
2answers
195 views
Foreign key contraints in many-to-many relationships
Context
We're building a blog for an intro. to databases course project.
In our blog, we want to be able to set Labels on Posts. The Labels can't exist by themselves, they only do so if they are ...
2
votes
0answers
147 views
Template DB schema for Web Analytics workload (AWS RedShift) [closed]
Are there any existing schema template for general web analytics workload, e.g. similar to Google Analytcis, it probably have at least the following tables
users
sites
requests
Since I don't want ...
3
votes
2answers
75 views
Improve PostgreSQL query performance
When running this query in my server it's very slow, and I can't understand why. Can anyone help me figure it out?
Query:
SELECT
"t_dat"."t_year" AS "c0",
"t_dat"."t_month" AS "c1",
...
1
vote
1answer
56 views
Check statistics targets in PostgreSQL
I have searched but been unable to find any simple, straight forward answer to this. How do I check the current statistics targets used by ANALYZE?
1
vote
1answer
105 views
Choosing indexes and primary keys for performance
I am new to database design and I am having a lot of trouble on designing a PostgreSQL database for a combat game.
On this game, players will fight between them, gaining resources to buy better ...
2
votes
1answer
126 views
Fetching rows from multiple tables with UNION ALL or using one table in production?
I know that for relational database like Postgresql using separated tables would be more efficient but I'm concerning for performance issues because the most executed query will fetch rows from ...
