StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can do this with substring(), and you'll also probably want to use an expression index. Here's an example (I tweaked the regex to match what I think you want):</p> <pre><code>CREATE TABLE whitelist_domain_data( id SERIAL PRIMARY KEY, url TEXT NOT NULL ); CREATE INDEX domain_index ON whitelist_domain_data (substring(url from 'https?\:\/\/([a-z0-9\.]+)')); INSERT into whitelist_domain_data (url) VALUES ('http://www.dailystrength.org/c/Hidradenitis_Suppurativa/forum/8870995-solodyn-135-mg-works'), ('http://au.answers.yahoo.com/question/index?qid=20090325215905AA6UVOa'), ('http://navaspot.wordpress.com'); SELECT * FROM whitelist_domain_data WHERE substring(url from 'https?\:\/\/([a-z0-9\.]+)') = 'www.dailystrength.org'; </code></pre> <p>Now this query can use the index. If this is something you plan on using a lot, you might consider also creating a specific function for it:</p> <pre><code>CREATE FUNCTION get_domain(text) RETURNS text LANGUAGE SQL AS $$ SELECT substring($1 from 'https?\:\/\/([a-z0-9\.]+)'); $$; </code></pre> <p>Then the above becomes:</p> <pre><code>CREATE INDEX domain_index ON whitelist_domain_data (get_domain(url)); SELECT * FROM whitelist_domain_data WHERE get_domain(url) = 'www.dailystrength.org'; </code></pre> <p>So then if you ever want to change what a domain is (to ignore subdomains, or whatever) you can just change the function and your queries will all still work. I think you'll have to reindex at that point, though.</p> <p>I checked that this all works on Postgres 9.1, but it should be compatible with any recent version. Expression indexes and substring() both go back to the 7.x days.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload