5

I have a database with many tables that are used as a reference for valid values in the other tables. These reference tables are all named valid[table], so I can display them in psql with \dt valid*.

I'd like to be able to select all the non-reference tables (which are more variably named, but none of them start with valid), but can't figure out how. I've tried various things like \dt !valid* , \dt !~valid* , \dt NOT LIKE 'valid%', but these either error or don't find a match.

Any suggestions would be appreciated!

jacel
  • 53
  • 4

1 Answers1

4

According to the manual, psql supports these patterns. Normally you could use regular expression negative lookahead (?!valid)* but ? cannot be used in regular expression with psql because it is translated to .. It is probably easier to do a query from INFROMATION SCHEMA:

SELECT table_name FROM information_schema.tables
  WHERE table_schema NOT IN ('pg_catalog', 'information_schema') --exclude system tables
    AND table_type = 'BASE TABLE' -- only tables
    AND table_name NOT LIKE 'valid%';
Simo Kivistö
  • 4,247
  • 3
  • 38
  • 42
  • Thanks Simo, that works perfectly! I didn't know you could access an information schema like that, very cool. – jacel Mar 25 '15 at 19:46