tag:blogger.com,1999:blog-76826208547380998282024-03-13T06:21:33.448+03:00Arthur's blogUnknownnoreply@blogger.comBlogger5125tag:blogger.com,1999:blog-7682620854738099828.post-16118666121901241832016-09-07T19:57:00.000+03:002016-09-07T23:46:04.512+03:00Data integration between PostgreSQL and MS SQL Server<p><a href="https://habrastorage.org/files/dd9/dfe/899/dd9dfe8999054d26ab80d555ca0b5d97.png" imageanchor="1" ><img border="0" src="https://habrastorage.org/files/dd9/dfe/899/dd9dfe8999054d26ab80d555ca0b5d97.png"></a></p>
<p>In this topic I would like to describe setup of PostgreSQL integration, which works on a Linux, with MS SQL Server. As well as how to import all tables from a specific schema of MS SQL Server database into PostgreSQL without having to specify the structure of each table.</p>
<h1>Installing tds_fdw</h1>
<p><a href="https://github.com/tds-fdw/tds_fdw">tds_fdw</a> is used for the integration between PostgreSQL and MS SQL Server. This module communicates with external database using <a href="https://en.wikipedia.org/wiki/Tabular_Data_Stream">TDS</a> protocol (Tabular Data Stream). TDS is used by such DBMS as MS SQL Server and Sybase SQL Server.</p>
<a href="http://azakirov.blogspot.com/2016/09/data-integration-between-postgresql-and.html#more">Continue reading...</a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7682620854738099828.post-13065945818885657222015-12-27T00:39:00.001+03:002016-06-01T18:00:28.878+03:00Fuzzy substring searching with the pg_trgm extension<h1>Introduction</h1>
<p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqcApjjDYCGsZsiBcd3Jmb5Q0AlzMQswklyipnue8Vk0UiGwwA1yd1D5pqmVQQJPza4Wh05siDxcAJxo4n0-2kLoNdDn3ILaSktaTIF_u7QzhDWtwsOawr02vFrptSknZZN_ULnAV4IoDK/s1600/elephant.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqcApjjDYCGsZsiBcd3Jmb5Q0AlzMQswklyipnue8Vk0UiGwwA1yd1D5pqmVQQJPza4Wh05siDxcAJxo4n0-2kLoNdDn3ILaSktaTIF_u7QzhDWtwsOawr02vFrptSknZZN_ULnAV4IoDK/s320/elephant.png"></a></p>
<p>In <a href="http://azakirov.blogspot.ru/2015/12/dictionaries-and-postgresql-fts.html">the previous topic</a> I mentioned that we have some operators for the pattern matching (<b><i>LIKE</i></b> (<b><i>~~</i></b>), <b><i>ILIKE</i></b> (<b><i>~~*</i></b>), <b><i>~</i></b>, <b><i>~*</i></b> and <b><i>SIMILAR TO</i></b>) and an operator for the text search (<b><i>@@</i></b>). If you know exactly what you want to find this operators are good choise.</p>
<p>But if you know a search query only approximately then a trigram matching can help you. A trigram matching is provided by the <b><i>pg_trgm</i></b> module in PostgreSQL. For example, you can execute the following query:</p>
<pre class="brush:sql">
CREATE TABLE clients(name varchar);
INSERT INTO clients VALUES('Devereaux'), ('Devereux');
SELECT * FROM clients WHERE name % 'Devereux';
</pre>
<p>And you will get the following people with <a href="https://en.wikipedia.org/wiki/Devereux">the Devereux</a> surname:</p>
<pre>
name
-----------
Devereaux
Devereux
(2 rows)
</pre>
<a href="http://azakirov.blogspot.com/2015/12/fuzzy-substring-searching-with-pgtrgm.html#more">Continue reading...</a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7682620854738099828.post-32993929435003355362015-12-22T18:34:00.000+03:002015-12-23T00:01:37.325+03:00Dictionaries and PostgreSQL FTS. Part 2<p>This is the second part of the topic about using Ispell and Hunspell dictionaries within PostgreSQL. In this topic I want to give some information about <b>FLAG</b> and <b>AF</b> parameters of Hunspell and about a patch which helps PostgreSQL to load dictionaries with this parameters.</p>
<a href="http://azakirov.blogspot.com/2015/12/dictionaries-and-postgresql-fts-part-2.html#more">Continue reading...</a>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7682620854738099828.post-49550675473043182162015-12-07T12:28:00.000+03:002015-12-27T00:02:22.798+03:00Dictionaries and PostgreSQL FTS<h1>Introduction</h1>
<p>There are some text search operator in PostgreSQL: <b><i>LIKE</i></b> (<b><i>~~</i></b>), <b><i>ILIKE</i></b> (<b><i>~~*</i></b>), <b><i>~</i></b>, <b><i>~*</i></b> and <b><i>SIMILAR TO</i></b>. But they have important disadvantage - they do not provide linguistic support. For example, you will not find the string <b>"great abilities"</b>, if you will execute the following query:</p>
<pre class="brush: sql">
CREATE TABLE table1 (col1 varchar);
INSERT INTO table1 VALUES ('great abilities');
SELECT col1 FROM table1 WHERE col1 LIKE '%ability%';
</pre>
<p>To avoid this drawback use <b><i>tsvector</i></b> and <b><i>tsquery</i></b> data types. These data types consist of lexemes. Moreover <b><i>tsvector</i></b> can store a position and a rank of lexemes.</p>
<a href="http://azakirov.blogspot.com/2015/12/dictionaries-and-postgresql-fts.html#more">Continue reading...</a>Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-7682620854738099828.post-23451198318969468112015-05-24T15:37:00.000+03:002015-12-01T15:02:18.743+03:00XQuery для начинающих. Часть 1 [перевод]<p><i>От переводчика. Это начало перевода цикла статьей <a href="http://www.jasonstrate.com/2010/11/xquery-for-the-non-expert-resources/" target="_blank">XQuery for the Non-Expert</a>. В данном переводе отсутствует статья "XQuery for the Non-Expert – Terminology", описывающая что такое элементы, атрибуты XML, XML-документ и т.д. Мне кажется данные понятия должны быть понятными для многих IT специалистов.</i></p>
<h1>Пространства имен XML</h1>
<p>Давайте поговорим о пространствах имен XML. Вы взволнованы? Ощущаете ли вы мурашки по коже от волнения или скорее от беспокойства при мысли использования данного оператора T-SQL?</p>
<p>Целью статьи является сделать XQuery и его функции и операторы более легкими для усвоения. Надеюсь, что после нескольких примеров и некоторых аналогий, вы будете понимать их на профессиональном уровне.</p>
<a href="http://azakirov.blogspot.com/2015/05/xquery-1.html#more">Continue reading...</a>Unknownnoreply@blogger.com0