0
votes
1answer
25 views

Ensuring that 1 row has 5 specific records referencing it

Context: a TEST has many CASEs. Typical. However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5). I could do something like this: create table TEST ( ID ...
0
votes
2answers
40 views

PostgreSQL : dependencies on a schema

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a ...
1
vote
1answer
27 views

PostgreSQL complaining about shared memory, but shared memory seems to be OK

I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..: WARNING: out of shared memory ERROR: out of shared memory HINT: You might need to ...
1
vote
1answer
32 views

Permission for sequence in another schema

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
1
vote
1answer
56 views

Handling user logins while using a multiple schema design to support multi-tenancy

I'm creating a web application (in PHP and PostreSQL) that will be used by organisations and their staff and I want to use multiple schemas to support multi-tenancy. There will be one database and I ...
1
vote
1answer
113 views

PostgreSQL: schema diff/patch tool

Consider the following setting: A production DB A dev db, on which schema changes are made to enable new features When the development of a new feature is complete, I have to manually update the ...
0
votes
2answers
80 views

How to change schema so that account_id reference is unique among 3 tables

Original image: Updated to have more-correct terminology and an 'is_debit' column: I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting. 'jackpots', ...
0
votes
1answer
171 views

Postgres 9.3 changes the standard_conforming_strings value

I'm migrating a legacy application's database from Postgres 8.1 to Postgres 9.3 In order to avoid compatibility issues, I'd like to keep the standard_conforming_strings parameter to it's default ...
1
vote
1answer
61 views

What will GRANT <privilege> ON ALL TABLES IN SCHEMA <scheme> TO <role> do?

It seems obvious that PostgreSQL will GRANT some privileges ON ALL TABLES IN some schema TO some role when I'll command. But, will it make it permanent for the schema that on all tables within it, ...
2
votes
2answers
866 views

Postgresql: Query across schemas

I have multiple schemas that I need to query across. The schemas are not know in advance for example: U111 U222 U333 I need to select across these three schemas from a specific table in each of ...
1
vote
0answers
60 views

Do supertables produce bottlenecks?

There are several alternatives to using polymorphic associations and one of them is to use the so-called "concrete supertable". From my point of view, this is the neatest solution and is pretty easy ...
0
votes
2answers
97 views

Schema for lists of relationship differences

Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item ...
0
votes
1answer
192 views

Automatically generate ALTER TABLE to syncronize schema on PostgreSQL

i Have 2 different database schema in PostgreSQL sql file from pg_dump, is there automated way to generate alter table commands so it would be in sync, without inspecting one by one? this is the ...
1
vote
2answers
300 views

How to create a blank version of a PostgreSQL database

I've been using pgAdmin to create a database and test it. I'm at a point now where I need to install this db on different servers. I've been using pg_dump to do this and I've been able to muddle my ...
1
vote
2answers
163 views

Postgres sequence usage

I have an Postgres database with two users: dev and junit. Junit and dev objects are dropped and created regularly. They both have the same objects unshared for tables, however, sequences are shared, ...
1
vote
0answers
82 views

Cloning Postgres schema/tables across multiple servers

I'm working on a project that has databases across multiple servers, with one server for each business client. The project has some identical 'base' tables that are present in all the databases (for ...
2
votes
3answers
358 views

Many to many link tables with history? [closed]

While creating a PostgreSQL database of customer subscribed services with DNS domains, I want to: enable customers to subscribe to multiple services each service can have multiple domain names per ...
0
votes
2answers
315 views

Which schema is better for a shopping project? [closed]

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
0
votes
1answer
81 views

is there a recommended way to update many postgres schemes

I have a database in postgres which has 15 schemes, the schemes are identical with the same tables, views and functions. the system uses one scheme at a time, as if they were separate databases. So ...
0
votes
1answer
199 views

Postgres - Schema information within Trigger?

Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x). Reviewing patterns for the following problem: I ...
2
votes
2answers
202 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 ...
22
votes
3answers
35k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
4
votes
1answer
698 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
1answer
120 views

Tools and methodologies to keep to DBs aligned [closed]

2 DBs having schemas that represent the same semantic objects. The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres. Once in a while ...
5
votes
1answer
924 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
688 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 ...
3
votes
1answer
519 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 ...
-1
votes
1answer
287 views

Database for an application similar to Chatroulette

I am making an app that is similar to Chatroulette. When a user starts the app it finds someone that is not currently talking to anyone and have them talk to each other. At any point a user could ...
3
votes
1answer
2k 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
0answers
36 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 ...
1
vote
1answer
329 views

PostgreSQL schemas, select same table from all schemas

My app is used by about 80k users every month. I want to create separate PostgreSQL schemas for each user (a-la SaaS, for protection). My app allows users to search other user's profiles, via: ...
3
votes
2answers
3k 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
17
votes
1answer
4k 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 ...
7
votes
1answer
563 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 ...
2
votes
2answers
631 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 ...
6
votes
3answers
3k 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 ...
7
votes
4answers
3k 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?
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 ...
9
votes
3answers
373 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 ...