This query on the system catalog creates the necessary DDL script:
SELECT string_agg(format('DROP SCHEMA %I CASCADE;', nspname), E'\n')
FROM pg_namespace
WHERE nspname LIKE 'ceu_shard_test_merge_%';
Note the use of format()
to escape identifiers if necessary.
format()
requires PostgreSQL 9.1+.
Replace with quote_ident()
in older versions.
string_agg()
requires PostgreSQL 9.0+.
Replace with array_to_string(array_agg(...), E'\n')
in older versions.
For Postgres 8.4, that would be:
SELECT array_to_string(
array_agg('DROP SCHEMA ' || quote_ident(nspname) || ' CASCADE;')
, E'\n')
FROM ...
Returns:
DROP SCHEMA ceu_shard_test_merge_1 CASCADE;
DROP SCHEMA ceu_shard_test_merge_2 CASCADE;
...
Which you can inspect before executing.
You can put it all into a DO
command for automatic execution or create a function for repeated use. Consider this closely related answer for a complete code example:
Or this related answer on SO.
Output in psql
In response to @Stew's comment: To display unadorned text as result in psql you can use the \pset
meta-command:
\pset format unaligned
\pset tuples_only
Or use the short commands \x \a
The "magic" regexp to do what you want is:
^[\s\t]*INSERT( INTO|)[\s\t\r\n]*[A-Za-z0-9_."\[\]]*[\s\t\r\n]*(SELECT|VALUES)\s
I tested some scenarios in "The Regex Coach" (a free software to test regexp). I made sure it also works for tables like [dbo].[tablename] or "tablename" etc.
Best Answer
Your query is pretty much the optimum. Syntax won't get much shorter, query won't get much faster:
If you really want to shorten the syntax, use a regular expression with branches:
Or slightly faster, with a character class:
A quick test without index yields faster results than for
SIMILAR TO
in either case for me.With an appropriate B-Tree index in place,
LIKE
wins this race by orders of magnitude.Read the basics about pattern matching in the manual.
Index for superior performance
If you are concerned with performance, create an index like this for bigger tables:
Makes this kind of query faster by orders of magnitude. Special considerations apply for locale-specific sort order. Read more about operator classes in the manual. If you are using the standard "C" locale (most people don't), a plain index (with default operator class) will do.
Such an index is only good for left-anchored patterns (matching from the start of the string).
SIMILAR TO
or regular expressions with basic left-anchored expressions can use this index, too. But not with branches(B|D)
or character classes[BD]
(at least in my tests on PostgreSQL 9.0).Trigram matches or text search use special GIN or GiST indexes.
Overview of pattern matching operators
LIKE
(~~
) is simple and fast but limited in its capabilities.ILIKE
(~~*
) the case insensitive variant.pg_trgm extends index support for both.
~
(regular expression match) is powerful but more complex and may be slow for anything more than basic expressions.SIMILAR TO
is just pointless. A peculiar halfbreed ofLIKE
and regular expressions. I never use it. See below.% is the "similarity" operator, provided by the additional module
pg_trgm
. See below.@@
is the text search operator. See below.pg_trgm - trigram matching
Beginning with PostgreSQL 9.1 you can facilitate the extension
pg_trgm
to provide index support for anyLIKE
/ILIKE
pattern (and simple regexp patterns with~
) using a GIN or GiST index.Details, example and links:
pg_trgm
also provides these operators:%
- the "similarity" operator<%
(commutator:%>
) - the "word_similarity" operator in Postgres 9.6 or later<<%
(commutator:%>>
) - the "strict_word_similarity" operator in Postgres 11 or laterText search
Is a special type of pattern matching with separate infrastructure and index types. It uses dictionaries and stemming and is a great tool to find words in documents, especially for natural languages.
Prefix matching is also supported:
As well as phrase search since Postgres 9.6:
Consider the introduction in the manual and the overview of operators and functions.
Additional tools for fuzzy string matching
The additional module fuzzystrmatch offers some more options, but performance is generally inferior to all of the above.
In particular, various implementations of the
levenshtein()
function may be instrumental.Why are regular expressions (
~
) always faster thanSIMILAR TO
?The answer is simple.
SIMILAR TO
expressions are rewritten into regular expressions internally. So, for everySIMILAR TO
expression, there is at least one faster regular expression (that saves the overhead of rewriting the expression). There is no performance gain in usingSIMILAR TO
ever.And simple expressions that can be done with
LIKE
(~~
) are faster withLIKE
anyway.SIMILAR TO
is only supported in PostgreSQL because it ended up in early drafts of the SQL standard. They still haven't gotten rid of it. But there are plans to remove it and include regexp matches instead - or so I heard.EXPLAIN ANALYZE
reveals it. Just try with any table yourself!Reveals:
SIMILAR TO
has been rewritten with a regular expression (~
).Ultimate performance for this particular case
But
EXPLAIN ANALYZE
reveals more. Try, with the afore-mentioned index in place:Reveals:
Internally, with an index that is not locale-aware (
text_pattern_ops
or using localeC
) simple left-anchored expressions are rewritten with these text pattern operators:~>=~
,~<=~
,~>~
,~<~
. This is the case for~
,~~
orSIMILAR TO
alike.The same is true for indexes on
varchar
types withvarchar_pattern_ops
orchar
withbpchar_pattern_ops
.So, applied to the original question, this is the fastest possible way:
Of course, if you should happen to search for adjacent initials, you can simplify further:
The gain over plain use of
~
or~~
is tiny. If performance isn't your paramount requirement, you should just stick with the standard operators - arriving at what you already have in the question.