StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is not a solution, but a data-modelling work-around</p> <ul> <li>break up the urls into {protocol,hostname,pathname} components.</li> <li>Now you can use exact matches to join om the hostname part, avoiding the leading % in the regex-match.</li> <li>the view is intended to demonstrate that the full_url can be reconstructed if needed.</li> </ul> <p>The update could probably take a few minutes.</p> <pre><code>SET search_path='tmp'; DROP TABLE urls CASCADE; CREATE TABLE urls ( id SERIAL NOT NULL PRIMARY KEY , full_url varchar , proto varchar , hostname varchar , pathname varchar ); INSERT INTO urls(full_url) VALUES ( 'ftp://www.myhost.com/secret.tgz' ) ,( 'http://www.myhost.com/robots.txt' ) ,( 'http://www.myhost.com/index.php' ) ,( 'https://www.myhost.com/index.php' ) ,( 'http://www.myhost.com/subdir/index.php' ) ,( 'https://www.myhost.com/subdir/index.php' ) ,( 'http://www.hishost.com/index.php' ) ,( 'https://www.hishost.com/index.php' ) ,( 'http://www.herhost.com/index.php' ) ,( 'https://www.herhost.com/index.php' ) ; UPDATE urls SET proto = split_part(full_url, '://' , 1) , hostname = split_part(full_url, '://' , 2) ; UPDATE urls SET pathname = substr(hostname, 1+strpos(hostname, '/' )) , hostname = split_part(hostname, '/' , 1) ; -- the full_url field is now redundant: we can drop it ALTER TABLE urls DROP column full_url ; -- and we could always reconstruct the full_url from its components. CREATE VIEW vurls AS ( SELECT id , proto || '://' || hostname || '/' || pathname AS full_url , proto , hostname , pathname FROM urls ); SELECT * FROM urls; ; SELECT * FROM vurls; ; </code></pre> <p>OUTPUT:</p> <pre><code>INSERT 0 10 UPDATE 10 UPDATE 10 ALTER TABLE CREATE VIEW id | proto | hostname | pathname ----+-------+-----------------+------------------ 1 | ftp | www.myhost.com | secret.tgz 2 | http | www.myhost.com | robots.txt 3 | http | www.myhost.com | index.php 4 | https | www.myhost.com | index.php 5 | http | www.myhost.com | subdir/index.php 6 | https | www.myhost.com | subdir/index.php 7 | http | www.hishost.com | index.php 8 | https | www.hishost.com | index.php 9 | http | www.herhost.com | index.php 10 | https | www.herhost.com | index.php (10 rows) id | full_url | proto | hostname | pathname ----+-----------------------------------------+-------+-----------------+------------------ 1 | ftp://www.myhost.com/secret.tgz | ftp | www.myhost.com | secret.tgz 2 | http://www.myhost.com/robots.txt | http | www.myhost.com | robots.txt 3 | http://www.myhost.com/index.php | http | www.myhost.com | index.php 4 | https://www.myhost.com/index.php | https | www.myhost.com | index.php 5 | http://www.myhost.com/subdir/index.php | http | www.myhost.com | subdir/index.php 6 | https://www.myhost.com/subdir/index.php | https | www.myhost.com | subdir/index.php 7 | http://www.hishost.com/index.php | http | www.hishost.com | index.php 8 | https://www.hishost.com/index.php | https | www.hishost.com | index.php 9 | http://www.herhost.com/index.php | http | www.herhost.com | index.php 10 | https://www.herhost.com/index.php | https | www.herhost.com | index.php (10 rows) </code></pre>

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