Tagged Questions
12
votes
1answer
2k views
Best practices for schema changes and data migrations to a live database without downtime?
How do you make schema changes to a live database without downtime?
For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
9
votes
3answers
299 views
How-to implement an entity with an unknown maximum number of attributes?
I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
6
votes
1answer
361 views
Concept of Schema in PostgreSQL
I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design.
Why should I use it?
Can it affect me in future if I decide to not think ...
6
votes
3answers
2k views
How to version control PostgreSQL schema with comments?
I version control most of my work with Git: code, documentation, system configuration.
I am able to do that because all my valuable work is stored as text files.
I have also been writing and dealing ...
5
votes
4answers
2k views
Finding empty columns of a table in PostgreSQL
What query would return the name of the columns of a table where all rows are NULL?
4
votes
2answers
1k views
PostgreSQL: Creation date of a table
I've recently finished a project, during which many DB tables were created.
Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables.
Is there a ...
3
votes
2answers
852 views
List primary keys for all tables - Postgresql
Is there a query that will do that?
I found some queries that can do this for one table, but I wasn't able to modify it so I can see:
tablename | column | type
3
votes
1answer
320 views
How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?
I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for ...
3
votes
1answer
134 views
Dump PostgreSQL schema with foreign keys and referenced data
The official PostgreSQL documentation states that pg_dump can use the --schema=SCHEMA option to selectively dump a given schema.
However according to the same documentation (read Note on ...
3
votes
1answer
216 views
Distinguish between schema and database
I had the error cross-database references are not implemented: scan.location.alias with this PL/pgSQL code:
DECLARE
v_tmp_host scan.location.alias%TYPE;
v_ip_address character varying;
BEGIN
...
2
votes
2answers
67 views
PostgreSQL and default Schemas
Whenever I create a brand new database in PostgreSQL Maestro it creates the following list of default schemas:
Now from my understanding Schemas are like folders for organization, etc. So the ...
2
votes
2answers
467 views
PostgreSQL: Separate tables vs single table to perserve disk space?
I have 2 tables with the below schemas with equal number of rows. When I run the SELECT relname, relpages FROM pg_class ORDER BY relpages DESC command, they show up as 23GB each even though the data ...
2
votes
1answer
209 views
PostgreSQL schemas and search_path
I'm reading Oreilly's PostgreSQL: Up and Running, and in the section Organizing Your Database Using Schemas it says the search_path may be set at the database level:
For example, if we wanted all ...
2
votes
1answer
99 views
Will table inheritance in PostgreSql suit this situation?
Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL.
So my main aim is to have a Main schema in this DB, which I am going to use to ...
2
votes
0answers
33 views
Splitting a wide table [duplicate]
Possible Duplicate:
Database Design: New Table versus New Columns
I'm in the process of building a DB schema that contains information about something we call "blocks" (not very important ...
