Tagged Questions
13
votes
2answers
2k views
Calculating and saving space in PostgreSQL
I have a table in pg like so:
CREATE TABLE t (
a BIGSERIAL NOT NULL, -- 8 b
b SMALLINT, -- 2 b
c SMALLINT, -- 2 b
d REAL, ...
6
votes
4answers
3k views
Store common query as column?
Using PostgreSQL, I have a number of queries that look like this:
SELECT <col 1>, <col 2>
, (SELECT sum(<col x>)
FROM <otherTable>
WHERE <other ...
144
votes
22answers
11k views
Use email address as primary key?
Is email address a bad candidate for primary when compared to auto incrementing numbers?
Our web application needs the email address to be unique in the system. So, I thought of using email address ...
13
votes
1answer
11k views
Grant all on a specific schema in the db to a group role in PostgreSQL
Using PostgreSQL 9.0, I have a group role called "staff" and would like to grant all (or certain) privileges to this role on tables in a particular schema. None of the following work
GRANT ALL ON ...
1
vote
1answer
7k views
How to implement a many-to-many relationship in PostgreSQL?
I believe the title is self-explanatory. How do you create the table structure in PostgreSQL to make a many-to-many relationship.
My example:
Product(name, price);
Bill(name, date, Products);
7
votes
5answers
2k views
Temporal database design, with a twist (live vs draft rows)
I'm looking into implementing object-versioning with the added twist of needing to have both live and draft objects, and could use the insights from someone experience in this, as I'm beginning to ...
26
votes
4answers
13k views
Postgresql: is better using multiple databases with 1 schema each, or 1 database with multiple schemas?
After this comment to one of my question, I'm thinking if it is better using 1 database with X schemas or viceversa.
My situation: I'm developing a web-app where, when people register, I create ...
2
votes
3answers
2k views
Storing long binary (raw data) strings
I have a quick question about how to store very long binary strings from a device. We are capturing a raw binary string that is variable in size (from 100k to 800k) and we would like to store these ...
6
votes
3answers
1k views
Complex foreign key constraint in SQLAlchemy
I have two tables, SystemVariables and VariableOptions. SystemVariables should be self-explanatory, and VariableOptions contains all of the possible choices for all of the variables.
VariableOptions ...
3
votes
3answers
490 views
Database design - should I use 30 columns or 1 column with all data in form of JSON/XML?
I am doing a project which need to store 30 distinct fields for a business logic which later will be used to generate report for each
The 30 distinct fields are not written at one time, the business ...
31
votes
4answers
7k views
Create unique constraint with null columns
I have a table with this layout:
CREATE TABLE Favorites
(
FavoriteId uuid NOT NULL PRIMARY KEY,
UserId uuid NOT NULL,
RecipeId uuid NOT NULL,
MenuId uuid
)
I want to create a unique ...
9
votes
2answers
2k views
Making sense of Postgres row sizes
I got a large (>100M rows) Postgres table with structure {integer, integer, integer, timestamp without time zone}. I expected the size of a row to be 3*integer + 1*timestamp = 3*4 + 1*8 = 20 bytes.
...
4
votes
4answers
145 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 | ...
...
2
votes
2answers
175 views
Retrieve all records from multiple schemas in a PostgreSQL database
I have a Postgres 8.4 database with some schemas, like below:
My_Database
|-> Schemas
|-> AccountA
|-> AccountB
|-> AccountC
|-> AccountD
|-> AccountE
...
...
4
votes
11answers
939 views
What is the best way to enforce a 'subset' relationship with integrity constraints
For example, given 3 tables:
gastropod
snail
slug
and assuming we want to enforce that
every row in 'gastropod' has exactly one corresponding row in 'snail' or 'slug' (but not both)
every row in ...
4
votes
1answer
2k views
Do I need a primary key for my table, which has a UNIQUE (composite 4-columns), one of which can be NULL?
I have the following table (PostgreSQL 8.3) which stores prices of some products. The prices are synchronised with another database, basically most of the fields below (apart from one) are not updated ...
17
votes
7answers
7k views
Good strategy for leaving an audit trail/change history for DB applications?
What are some strategies that people have had success with for maintaining a change history for data in a fairly complex database. One of the applications that I frequently use and develop for could ...
3
votes
3answers
2k views
Design Relational Database - Use hierarchical datamodels or avoid them?
I'm designing a Database and I have some doubts on using Hierarchical datamodels in relational databases.
If I want to deal with categories, subcategories and parent categories it is possible not to ...
3
votes
1answer
446 views
Table size with page layout
I'm using PostgreSQL 9.2 on Oracle Linux Server release 6.3.
According to the storage layout documentation, a page layout holds:
PageHeaderData(24 byte)
n number of points to item(index item / ...
16
votes
8answers
2k views
Is a 'blackhole' table evil?
Reading to this question i've just learned the existence of the blackhole table trick: basically consist in using a single table to insert data, and then a trigger that split the data in many other ...
1
vote
1answer
1k views
How many records can I store in 5 MB of PostgreSQL on Heroku?
I'm going to store records in a single table with 2 fields:
id -> 4 characters
password_hash -> 64 characters
How many records like the one above will I be able to store in a 5mb PostgreSQL on ...
5
votes
1answer
185 views
Table indexes for Text[] array columns
I have a PostgreSQL database table with text[] (array) columns defined on it. I'm using these columns to search for a specific record in the database in this way:
select obj from business
where ...
2
votes
7answers
1k views
Database speed optimization: few tables with many rows, or many tables with few rows?
i have a doubt (big).
Lets take as example a database for, dunno, a whatever company's orders.
Lets say that this company make around 2000 orders per month, so, around 24K order per year, and they ...
1
vote
2answers
180 views
Increment value of a table ID for each INSERT
I'm using PostgreSQL with all my tables setup. I currently have a table called comments with a primary key called comment_id which is a VARCHAR of length 4.
I have a form setup to insert a new ...
1
vote
2answers
516 views
Many-to-many relation filter
I need to filter my query with categories table which has many2many relation with another table. Is it possible to filter query with many2many relation? Everything I tried with it, didn't give any ...
0
votes
1answer
257 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
votes
4answers
263 views
Database design for constraint enforcing pairing
How do I best design a database where I have one table of players (with primary key player_id) which I want to pair into teams of two so that the database can enforce the constraint that each team ...
10
votes
4answers
3k views
is EAV - Hybrid a bad database design choice
We have to redesign a legacy POI database from MySQL to PostgreSQL. Currently all entities have 80-120+ attributes that represent individual properties.
We have been asked to consider flexibility as ...
13
votes
5answers
7k views
PostgreSQL Index Usage Analysis
Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the ...
11
votes
4answers
9k views
High-quality ERD generator for PostgresQL under Linux?
Background
MySQL Workbench can produce appealing and high-quality ERDs such as:
Research
What PostgreSQL ERD tools are available that meet the requirements? The following are unsuitable:
...
3
votes
3answers
3k views
Multi-tenant rails application: what are the pros and cons of different techniques?
I originally wrote my Ruby on Rails application for one client. Now, I am changing it so that it can be used for different clients. My end-goal is that some user (not me) can click a button and create ...
6
votes
6answers
6k views
PostgreSQL: Defining a primary key on a large database
I am planing a database to store lots of text. (blog posts, news articles, etc.) The database needs to have the title, content (50k characters max), date, link and language fields. The same content ...
2
votes
4answers
572 views
cloning hierarchical data
let's assume i have a self referencing hierarchical table build the classical way like this one:
CREATE TABLE test
(name text,id serial primary key,parent_id integer
references test);
insert into ...
4
votes
2answers
863 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?
4
votes
1answer
3k views
Safely and cleanly rename tables that use serial primary key columns in Postgres?
I know that PostgreSQL tables that use a SERIAL primary key end up with an an implicit index, sequence and constraint being created by PostgreSQL. The question is how to rename these implicit objects ...
3
votes
2answers
4k 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 ...
3
votes
7answers
3k views
SQL: Advantages of an ENUM vs. a one-to-many relationship?
I very rarely see ENUM datatypes used in the wild; a developer almost always just uses a secondary table that looks like this:
CREATE TABLE officer_ranks (
id int PRIMARY KEY
,title varchar NOT NULL ...
3
votes
4answers
345 views
Database performance: filtering on column vs. seperate table
I was wondering what the best approach would be for the following situation:
I have an Orders table in a database that obviously contains all orders. But these are literally ALL orders, so including ...
3
votes
2answers
4k views
Does a Foreign Key referencing PK need the NOT NULL constraint?
Does a Foreign Key referencing a Primary Key need the NOT NULL constraint in a PostgreSQL database?
The database is highly normalized and will be very large. I do not wish to add extra constraints ...
2
votes
1answer
301 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 ...
2
votes
4answers
369 views
How to create multiple sequences in one table?
I have a table "receipts". I have columns customer_id (who had the receipt) and receipt_number. The receipt_number should start on 1 for each customer and be a sequence. This means that customer_id ...
1
vote
1answer
84 views
Design approaches for storing emails in an application
I want to discuss design approaches for an application we need to implement. The application needs to store and later process emails. We have about 2000 emails a day, the average size including ...
1
vote
1answer
271 views
Should I add a type column to design inheritance in postgreSQL?
I am planning to create a web application using spring/hibernate.
How should I design my postgresql database in order to support inheritance?
Assume that I have the following tables: super_table, ...
1
vote
2answers
1k views
PostgreSQL analog of SQL Server index
Trying to recreate my SQL Server database on PostgreSQL. Everything is ok except I can't find how to recreate this index:
USE [mytablename]
GO
CREATE NONCLUSTERED INDEX [myindex]
ON ...
6
votes
3answers
754 views
Converting a many-to-many relationship to one-to-many in PostgreSQL
I have a many-to-many between foo and bar modeled as a table foo_bar with foo_id and bar_id.
I'd now like to model this as a one-to-many (which my data allows).
I've added a foo_id column to bar but ...
6
votes
3answers
2k views
Does setting “NOT NULL” on a column in postgresql increase performance?
I know this is a good idea in MySQL. If I recall correctly, in MySQL it allows indexes to work more efficiently.
5
votes
2answers
817 views
Does the order of columns in a postgres table impact performance?
In Postgres does the order of columns in a CREATE TABLE statement impact performance? Consider the following two cases:
CREATE TABLE foo (
a TEXT,
B VARCHAR(512),
pkey INTEGER ...
4
votes
1answer
80 views
Enforcing supertype & subtype data integrity without stored procedures, triggers, or UDFs
I run a small food production business and I need to manage customer orders. I have built a conceptual data model of this aspect of my business but I need some pointers on how to fully implement this ...
3
votes
2answers
1k views
Designing a database with periodic sensor data
I'm designing a PostgreSQL database that takes in readings from many sensor sources. I've done a lot of research into the design and I'm looking for some fresh input to help get me out of a rut here.
...
2
votes
2answers
63 views
Database design - should two projects share the same table?
Background:
Two projects (A & B) under design at the same time both needs a new table(called DocumentStore) to store document/file under postgres.
But business logic around the document storage ...
