1

Each table in my database (see schema) has a primary key called id.

I would like to find all records in all tables where id is in the range [1,1000]. How can I write such a query?

I've tried to write a for loop following the PostgreSQL docs

FOR T IN (
  SELECT TABLE_NAME FROM information_schema. TABLES
  WHERE table_schema = 'public'
    )
LOOP
    SELECT id, T FROM T WHERE ID BETWEEN 1 AND 1000;
  RETURN NEXT T;
END LOOP ;

but get "Syntax error at FOR".

2

You can do this all in one statement pretty easily.

SELECT string_agg(statement, E'\nUNION ALL ') || ';'
FROM (
  SELECT FORMAT(
    $$SELECT '%s' AS table_catalog, '%s' AS table_schema, '%s' AS table_name, id FROM %I.%I.%I WHERE %I BETWEEN 1 AND 1000$$,
    table_catalog,
    table_schema,
    table_name,
    table_catalog,
    table_schema,
    table_name,
    column_name
  )
  FROM information_schema.columns
  WHERE
    table_catalog = CURRENT_CATALOG
    AND table_schema = 'public'
    AND column_name = 'id'
) AS t(statement);

That will generate a single statement. In psql, just run \gexec after that.

  • PS for reference here is my final script gist.github.com/dlebauer/f586e380fa4cfadc25551cd1df487b7d. It still required me to 1) split the multipolygon, 2) string substitution like sed 's/({1},//g' and 3) sort by xmax in order to organize EW and 4) concatenating insert statements. I did 1 in psql and 2-4 in google sheets. A solution all in psql would be neat, but not necessary (to me) at this point b/c this solution worked. Thanks again! – David LeBauer May 19 '17 at 16:51
  • I'm not sure how any of that is related to the question, but cool. ;) PostGIS rocks. – Evan Carroll May 19 '17 at 16:59
  • Sorry that was related to a totally different question! – David LeBauer May 21 '17 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.