Как использовать конструктор запросов для PostgreSQL: лучшие визуальные инструменты и подключение к 1С через внешние источники данных

При интеграции систем часто возникает необходимость работать с внешними базами данных, например, когда данные из 1С выгружаются во внешнюю СУБД PostgreSQL. Разработчики, привыкшие к удобному визуальному конструктору запросов в среде 1С, при переходе на стандартные инструменты администрирования баз данных (такие как pgAdmin) часто сталкиваются со сложностями. Писать тяжелые многострочные SQL-запросы вручную — процесс трудоемкий и требующий много времени.

В этой статье мы подробно разберем, как решить проблему визуального построения запросов к СУБД PostgreSQL. Мы рассмотрим как интеграционный метод с использованием платформы 1С, так и специализированные сторонние программные продукты, способные облегчить жизнь разработчику.

Решение 1. Подключение PostgreSQL к 1С через «Внешние источники данных»

Если перед нами стоит задача регулярной работы с внешней базой данных PostgreSQL, а под рукой всегда есть платформа 1С, самым эффективным решением будет использование механизма ВнешниеИсточникиДанных. Это позволяет подключить таблицы сторонней базы данных непосредственно в дерево конфигурации 1С и использовать для построения запросов наш привычный, мощный и очень удобный визуальный Конструктор запросов 1С.

Разберем этот процесс по шагам:

1. Установка СУБД-драйвера (ODBC). Для того чтобы 1С могла взаимодействовать с PostgreSQL напрямую, на компьютере (или на сервере приложений 1С) должен быть установлен соответствующий драйвер psqlODBC. Очень важно соблюдать разрядность (Bitness):
   • Если вы используете 64-разрядный сервер приложений 1С (клиент-серверный вариант работы), то на сервере обязательно должен быть установлен 64-битный драйвер ODBC.
   • Если работа идет в файловом режиме под 32-битным клиентом 1С, потребуется 32-битный ODBC-драйвер.
2. Создание объекта в конфигураторе. Откроем конфигуратор нашей информационной базы, найдем ветку ВнешниеИсточникиДанных, нажмем кнопку «Добавить» и создадим новый источник.
3. Настройка строки подключения. Нам необходимо явно указать строку соединения, чтобы избежать ошибок автоматического определения драйверов в Windows. Проанализируем пример надежной строки подключения:

Driver={PostgreSQL UNICODE(x64)};Server=127.0.0.1;Port=5432;Database=external_db;Uid=postgres;Pwd=my_secure_password;

Обратите внимание на использование драйвера PostgreSQL UNICODE(x64) вместо ANSI. Выбор кодировки Unicode гарантирует, что русскоязычные данные (кириллица) будут передаваться между базами корректно и без искажений.

4. Импорт таблиц. После настройки подключения перейдем на вкладку «Таблицы» во внешнем источнике данных, нажмем кнопку «Обновить список таблиц», выберем нужные нам таблицы из PostgreSQL и добавим их в конфигурацию.
5. Использование конструктора. Теперь мы можем открыть любую обработку, консоль запросов или создать отчет на СКД. При создании запроса нам будут доступны таблицы из внешнего источника. Мы можем перетаскивать их в визуальный конструктор, связывать по ключам, настраивать группировки и сортировки точно так же, как если бы это были стандартные справочники или документы 1С!

Решение 2. Использование специализированных СУБД-клиентов с конструктором

Если вариант с подключением через 1С по каким-то причинам не подходит (например, база PostgreSQL администрируется сторонними специалистами без доступа к 1С), рассмотрим альтернативные инструменты визуального проектирования.

1. dbForge Studio for PostgreSQL

Это один из лучших инструментов на рынке от компании Devart. Он обладает мощным графическим интерфейсом. В его состав входит визуальный дизайнер запросов (Query Builder), который позволяет:

• Визуально объединять таблицы перетаскиванием (Drag-and-Drop).
• Автоматически создавать связи JOIN на основе внешних ключей (Foreign Keys).
• Удобно настраивать условия фильтрации WHERE, группировки GROUP BY и сортировки ORDER BY в табличной панели внизу экрана.

Для базовых задач существует бесплатная версия Express, однако полноценный конструктор доступен в коммерческих редакциях.

2. Navicat for PostgreSQL

Профессиональная утилита администрирования, завоевавшая огромную популярность среди веб-разработчиков и администраторов баз данных. Обладает интуитивно понятным графическим конструктором запросов. Navicat отлично оптимизирован для работы со сложными связями, умеет строить ER-диаграммы и позволяет экспортировать результаты запросов в десятки различных форматов. Программа платная, но доступна триальная версия для ознакомления.

3. DBeaver (версии Lite / EE)

Популярнейший универсальный менеджер баз данных DBeaver имеет мощный инструмент Visual Query Builder (VQB). Мы можем переносить таблицы на интерактивный холст, выбирать галочками нужные колонки, и программа сама сгенерирует сложный SQL-код. Важно учитывать, что данный конструктор доступен только в платных коммерческих дистрибутивах (Lite, Enterprise, Ultimate). В бесплатной версии Community Edition визуальный конструктор отсутствует (доступно только чтение ER-диаграмм связей).

Решение 3. Исправление проблемы с кодировкой кириллицы (кракозябрами) в CSV

Часто при выгрузке результатов запроса из бесплатного инструмента pgAdmin 4 во внешние файлы CSV пользователи сталкиваются со следующей проблемой: при открытии файла в Microsoft Excel русские буквы превращаются в нечитаемый набор символов. Выясним причину этой проблемы и рассмотрим способы её решения.

Причина кроется в том, что pgAdmin 4 выгружает файлы в кодировке UTF-8 без сигнатуры BOM (Byte Order Mark). Excel на русскоязычных операционных системах по умолчанию ожидает кодировку Windows-1251 (CP1251) и отображает данные неверно.

Разберем три простых метода исправления этой ситуации:

Метод А. Корректный импорт данных в Microsoft Excel

Вместо открытия файла двойным щелчком мыши, сделаем следующее:

1. Запустим пустую книгу Microsoft Excel.
2. Перейдем на вкладку «Данные».
3. В левой части панели выберем команду «Из текста/CSV» (в старых версиях Excel — «Получить внешние данные» -> «Из текста»).
4. Укажем путь к нашему CSV-файлу.
5. В появившемся диалоговом окне в выпадающем списке «Источник файла» выберем кодировку «65001: Юникод (UTF-8)». Данные в окне предпросмотра мгновенно примут нормальный читаемый вид.
6. Нажмем кнопку «Загрузить».

Метод Б. Преобразование файла через текстовый редактор Notepad++

Если нам нужно, чтобы файл открывался обычным двойным кликом без дополнительных настроек импорта в Excel:

1. Откроем полученный CSV-файл в бесплатном текстовом редакторе Notepad++.
2. В главном меню перейдем в раздел «Кодировки».
3. Выберем пункт «Преобразовать в UTF-8-BOM».
4. Сохраним файл нажатием сочетания клавиш Ctrl + S.

После добавления сигнатуры BOM операционная система Windows и Microsoft Excel будут корректно определять кодировку UTF-8 автоматически.

Метод В. Настройка кодировки экспорта в pgAdmin

Мы также можем повлиять на кодировку непосредственно в момент выгрузки из СУБД. При использовании инструмента импорта/экспорта (Import/Export) в утилите pgAdmin 4 перейдите к окну настроек параметров выгрузки. В строке «Encoding» (Кодировка) явно пропишите значение WIN1251 (или CP1251). Экспортированный таким образом файл откроется в русскоязычном Excel идеально, так как кодировка будет полностью соответствовать системной локали Windows.