How does PostgreSQL parse URLs

author ： Wu Cong

Even though PostgreSQL A large number of data types are supported in , But for URL There seems to be no corresponding type to store . So for URL How do we deal with the data in the database ？

First of all, it depends on what you want to store URL What data is used for , If it's just for printing out when you query later , Then use varchar perhaps text And so on ？

But sometimes we're concerned about these URL Data is not just a simple query , We may hope that through these URL Be able to query some additional information , for example URL The protocol used 、 Host name and so on . Here we can use ts_debug Function to analyze .

function ts_debug Allows you to simply test a text search configuration .

ts_debug([ config regconfig, ] document text,
         OUT alias text,
         OUT description text,
         OUT token text,
         OUT dictionaries regdictionary[],
         OUT dictionary regdictionary,
         OUT lexemes text[])
         returns setof record

ts_debug Returns a line for each token identified by the parser in the text . The returned column is ：

• alias text ： Short name of token type
• description text ： Description of token type
• token text ： Marked text
• dictionaries regdictionary[] ： Configure the dictionary selected for this token type
• dictionary regdictionary ： A dictionary that identifies the mark , If no dictionary can recognize it, it is NULL
• lexemes text[] ：
  The word position produced by the dictionary that recognizes the mark , If no dictionary can recognize it, it is NULL; An empty array （{}） Indicates that the token is recognized as a stop word

So we can use the following ways to URL To analyze ：

bill@bill=>SELECT
bill-# alias
bill-# ,description
bill-# ,token
bill-# FROM ts_debug('https://blog.csdn.net/weixin_39540651');
  alias   |  description  |             token
----------+---------------+-------------------------------
 protocol | Protocol head | https://
 url      | URL           | blog.csdn.net/weixin_39540651
 host     | Host          | blog.csdn.net
 url_path | URL path      | /weixin_39540651
(4 rows)

Reference link ：

https://stackoverflow.com/questions/41633436/datatype-for-a-url-in-postgresql
http://www.postgres.cn/docs/13/textsearch-debugging.html#TEXTSEARCH-CONFIGURATION-TESTING