Data integration between PostgreSQL and MS SQL Server

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.

Installing tds_fdw

tds_fdw is used for the integration between PostgreSQL and MS SQL Server. This module communicates with external database using TDS protocol (Tabular Data Stream). TDS is used by such DBMS as MS SQL Server and Sybase SQL Server.

Fuzzy substring searching with the pg_trgm extension

Introduction

In the previous topic I mentioned that we have some operators for the pattern matching (LIKE (~~), ILIKE (~~*), ~, ~* and SIMILAR TO) and an operator for the text search (@@). If you know exactly what you want to find this operators are good choise.

But if you know a search query only approximately then a trigram matching can help you. A trigram matching is provided by the pg_trgm module in PostgreSQL. For example, you can execute the following query:

CREATE TABLE clients(name varchar);
INSERT INTO clients VALUES('Devereaux'), ('Devereux');
SELECT * FROM clients WHERE name % 'Devereux';

And you will get the following people with the Devereux surname:

   name  
-----------
 Devereaux
 Devereux
(2 rows)

Dictionaries and PostgreSQL FTS. Part 2

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 FLAG and AF parameters of Hunspell and about a patch which helps PostgreSQL to load dictionaries with this parameters.

Dictionaries and PostgreSQL FTS

Introduction

There are some text search operator in PostgreSQL: LIKE (~~), ILIKE (~~*), ~, ~* and SIMILAR TO. But they have important disadvantage - they do not provide linguistic support. For example, you will not find the string "great abilities", if you will execute the following query:

CREATE TABLE table1 (col1 varchar);
INSERT INTO table1 VALUES ('great abilities');
SELECT col1 FROM table1 WHERE col1 LIKE '%ability%';

To avoid this drawback use tsvector and tsquery data types. These data types consist of lexemes. Moreover tsvector can store a position and a rank of lexemes.

XQuery для начинающих. Часть 1 [перевод]

От переводчика. Это начало перевода цикла статьей XQuery for the Non-Expert. В данном переводе отсутствует статья "XQuery for the Non-Expert – Terminology", описывающая что такое элементы, атрибуты XML, XML-документ и т.д. Мне кажется данные понятия должны быть понятными для многих IT специалистов.

Пространства имен XML

Давайте поговорим о пространствах имен XML. Вы взволнованы? Ощущаете ли вы мурашки по коже от волнения или скорее от беспокойства при мысли использования данного оператора T-SQL?

Целью статьи является сделать XQuery и его функции и операторы более легкими для усвоения. Надеюсь, что после нескольких примеров и некоторых аналогий, вы будете понимать их на профессиональном уровне.