Select (retrieve) all records from multiple schemas using Postgres

by

With inheritance like @Denis mentioned, this would be very simple. Works for Postgres 8.4, too. Be sure to consider the limitations.

Basically, you would have a master table, I suppose in a master schema:

CREATE TABLE master.product (title text);

And all other tables in various schemata inherit from it, possibly adding more local columns:

CREATE TABLE a.product (product_id serial PRIMARY KEY, col2 text)
INHERITS (master.product);

CREATE TABLE b.product (product_id serial PRIMARY KEY, col2 text, col3 text)
INHERITS (master.product);

etc.

The tables don’t have to share the same name or schema.
Then you can query all tables in a single fell swoop:

SELECT title, tableoid::regclass::text AS source
FROM   master.product
WHERE  title ILIKE '%test%';

tableoid::regclass::text?
That’s a handy way to tell the source of each row. Details:

SQL Fiddle.

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. Create unique constraint with null columns
  3. Split comma separated column data into additional columns
  4. Custom SERIAL / autoincrement per group of values
  5. Safely rename tables using serial primary key columns
  6. Serial numbers per group of rows for compound key
  7. PostgreSQL: Which Datatype should be used for Currency?
  8. Postgres constraint for unique datetime range
  9. Which datatype should be used for currency?
  10. Use email address as primary key?
  11. PostgreSQL Index on JSON
  12. Temporal database design, with a twist (live vs draft rows)
  13. Fetch records that are non zero after the decimal point in PostgreSQL
  14. Select rows which are not present in other table
  15. Optimize GROUP BY query to retrieve latest row per user
  16. Check if value exists in Postgres array
  17. What is the difference between single quotes and double quotes in PostgreSQL?
  18. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  19. Best way to count rows by arbitrary time intervals
  20. PostgreSQL sequence based on another column
  21. Reference alias in WHERE clause
  22. Sort NULL values to the end of a table
  23. How to compare the current row with next and previous row in PostgreSQL?
  24. ERROR: functions in index expression must be marked IMMUTABLE in Postgres
  25. Continuing a transaction after primary key violation error
  26. SQL column reference “id” is ambiguous
  27. postgres – where in (list) – column does not exist
  28. incomplete information from query on pg_views [duplicate]
  29. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  30. Return rows from INSERT with ON CONFLICT without needing to update

Leave a Comment