2

I have an application that CREATE-IF-NOT_EXISTs a long list of tables when it first connects.

Performance is acceptable on the corporate LAN, since it stays connected for a long time between restarts, but it is painfully slow with the added latency on the WAN.

Rather than iterate through a loop of individual creates, I would like to issue one SQL query that will efficiently tell me whether all required tables are present, so I can bypass the optional setup step.

I've found a number of answers on StackOverflow for how to do with with a single table, but I don't know how to expand those examples to use a list of table names.

Can anybody suggest how to efficiently tell me if all the named tables in a list are present? (the query should fail if any of the listed tables does not exist, and succeed otherwise.)

(No discussion on whether creating/checking for tables on each connect is a good idea, please - I can't control this part of the design.)

Improve this question
2
  • What if there's at least 1 table missing, what needs to be done? create the one missing table or the whole thing? Commented Aug 4, 2015 at 18:21
  • @PatB - if at least one table is missing, it likely means the 'init' code never ran, (or errored out part-way for some reason) and so I would just go ahead and run the old create-of-not-exists loop. It's not pretty, but I can live with a slow initial connect [I can force the init to happen on the LAN] -- all I'm trying to avoid here is for every single connect to have to delay again for as long as the initial connect/setup operation. Commented Aug 4, 2015 at 18:28

2 Answers 2

Reset to default
1

The names of existing tables are stored in the relname column of the pg_class table. You can issue a ‘where in’ query for all the required tables and make sure you get the same amount of rows back.

select rn = 3 from ( 
  select count(*) rn from pg_class 
  where relname in ('table1','table2','tablen')
  and relkind = 'r'
) t1

If you can create a table of your required names then a not exists query might be cleaner because you don’t have to keep track of the count.

select exists (
    select 1 from required_tables rt
    where not exists (
        select 1 from pg_class pc where pc.relname = rt.table_name
        and relkind = 'r'
    )
)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This looks pretty good, but pg_class seems to have a bunch of other stuff listed besides tables (e.g. indexes?) -- how do I tighten this up to eliminate false-positives?
Why use pg_class instead of pg_tables?
@jpmc26 pg_tables works too. I prefer pg_class because it isn't a view
I don't understand. Why do you prefer a table over a view? I checked, and the query plan is exactly the same regardless of which one you use. So I don't see the benefit, whereas using the view provides improved readability and simplicity by abstracting implementation details of pg_class out of your query.
0

Since you're already going to have to modify the code to make it skip the current create, use this query to fetch the list of tables:

SELECT schemaname||'.'||tablename FROM pg_tables

Then you just compare this result set to your known list of tables in code. (You can use set difference or loop over a list or whatever your app's language provides.) I prefer this since it keeps my query simple, meaning I don't have to fight with an ORM or other client to get a complex query right. Instead, I'm just fetching some straightforward data, and then the logic is in code.

You can filter down to the schemas you're actually using if you want:

SELECT schemaname||'.'||tablename FROM pg_tables WHERE schemaname IN ('public', 'mycustomschema')
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.