Ответы пользователя по тегу PostgreSQL
  • Как отсортировать числовые строки вида "Х.Х.Х"?

    Assargin
    @Assargin
    Перед ответом смотрю наличие ✔ в ваших вопросах
    Чтобы не заниматься программированием в PostgreSQL, в котором натуральной сортировки для такого случая я не нашёл, решением может быть хранить ваши строки не as is, а отдельно буквы и массив цифр, т.е. вместо одного поля VARCHAR для всей строки использовать 2 поля: строка VARCHAR для букв и массив чисел INTEGER[]:

    CREATE TABLE test (
      "letters" VARCHAR(20),
      "digits" INTEGER[]
    );
    
    INSERT INTO test
      ("letters", "digits")
    VALUES
      ('ЧС', '{1, 1}'),
      ('ЧС', '{1, 2}'),
      ('ЧС', '{1, 3, 1}'),
      ('ЧС', '{1, 10}'),
      ('ЧС', '{1, 11, 1}'),
      ('П', '{1, 1}'),
      ('П', '{1, 2}'),
      ('П', '{2, 10}');


    SQL тогда получается банальным - сортировка просто по этим двум полям, а собрать нужную строку можно прямо в запросе:
    SELECT letters || '.' || array_to_string(digits, '.') from test order by letters, digits;


    Поиграться: https://www.db-fiddle.com/f/kGBapu86EXBuMZ6Bh6mGfe/1
    Ответ написан
    Комментировать
  • Архитектура связей между таблицами в БД?

    Assargin
    @Assargin
    Перед ответом смотрю наличие ✔ в ваших вопросах
    events:
      id (PK)
      ....
    
    users:
      id (PK)
      ...
    
    event_users
      event_id (PK, FK to events)
      user_id (PK, FK to users)
    
    feedbacks:
      id (PK)
      event_id (FK to events)
      user_id_from (FK to users)
      user_id_to (FK to users)
      
      idx_unique (event_id, user_id_from, user_id_to) - уникальный индекс


    В event_users каждый пользователь на каждое событие может быть записан только 1 раз (т.к. оба поля входят в PK)
    В feedbacks на уровне приложения позволяете добавлять только те записи, где и user_id_from и user_id_to присутствуют в event_users к данному event.
    Ответ написан
    Комментировать
  • Что можно дописать в запрос что бы получить результат?

    Assargin
    @Assargin
    Перед ответом смотрю наличие ✔ в ваших вопросах
    Обычно в таких случаях в запрос добавляют группировку и используют агрегатные функции. Попробуйте вот так:
    SELECT SUM(bonus), nlevel(tree) - nlevel('2.383') AS level
    FROM "user" 
    INNER JOIN "bonus_item" 
    ON user_from = "user".id 
    AND user_to = 383
    WHERE (tree ~ '2.383.*{0,5}') 
    AND ("mounth" = '9:2017')
    GROUP BY (nlevel(tree) - nlevel('2.383'))
    order by nl;
    Ответ написан
    1 комментарий
  • Чем бекапить все базы Postgres без прерывания доступа к сервису?

    Assargin
    @Assargin
    Перед ответом смотрю наличие ✔ в ваших вопросах
    Стандартная утилита pg_dump
    • есть везде, где есть Postgres
    • не блокирует БД: приложения могут читать/писать данные, но запросы на изменение структуры, скорее всего, будут ждать окончания бэкапа
    • восстанавливает настолько быстро, насколько быстро выполняются обычные SQL-запросы.
    Ответ написан
    Комментировать
  • Как перевести БД MySQL на БД PostgreSQL?

    Assargin
    @Assargin
    Перед ответом смотрю наличие ✔ в ваших вопросах
    Если никаких фич, кроме непосредственно таблиц и данных в них, вы не используете (хранимых процедур, триггеров и т.п.), то, насколько я помню, в phpMyAdmin есть функция экспорта, в которой можно указать, синтаксис какой СУБД использовать для результирующего дампа в SQL. И Postgres там был.
    UPD:
    Посмотрел в демо phpMyAdmin, ошибался, нет там экспорта в формат постгреса
    Попробуйте посмотреть что-нибудь из этого списка
    UPD2:
    У стандартной команды mysqldump есть опция --compatible, где можно указать значение postgresql: https://dev.mysql.com/doc/refman/5.7/en/mysqldump....
    Ответ написан
    Комментировать
  • В чем проблема с таймзоной?

    Assargin
    @Assargin
    Перед ответом смотрю наличие ✔ в ваших вопросах
    А с чего вы взяли, что время должно быть 17:54?
    2017-09-05T14:54:36+0300 стоит читать как "5 сентября 2017 года, время 14:54:36 по часовому поясу UTC+03"
    То же самое время в UTC будет выглядеть вот так: 2017-09-05T11:54:36+0000
    Ответ написан
    2 комментария
  • Как организовать схему БД?

    Assargin
    @Assargin
    Перед ответом смотрю наличие ✔ в ваших вопросах
    Что является более грамотным - довольно скользкая тема.
    Более правильное академически - нормальные формы и т.п.?
    На практике частенько приходится отходить от норм реляционной теории данных. Например, дублировать данные, чтобы местами избежать джойнов в запросах, которые будут выполняться очень часто.

    Если комментарии и к вопросам и к ответам - одинаковая сущность, то я бы сделал их одной таблицей. И в ней 2 поля (помимо прочих):
    question_id - связь с вопросом; всегда существует.
    answer_id - связь с ответом; NULL если был прокомментирован именно вопрос.
    Таким образом, мы можем запрашивать комментарии без необходимости джойнить таблицы, как ко всему вопросу и его ответам, так и отдельно к вопросу, и к конкретному ответу на вопрос.
    Ответ написан
    Комментировать
  • Как правильно делать бекапы базы Postgresql на продашн сервере?

    Assargin
    @Assargin
    Перед ответом смотрю наличие ✔ в ваших вопросах
    По крону, примерно раз в день?

    Можно и чаще, смотря, насколько чувствительно будет потерять данные между бэкапами. Если совсем-совсем чувствительно, нужно задуматься о поднятии реплики или о постоянном бэкапе (используя WAL-журналы).

    И потом их закачивать на aws и dropbox?

    Хоть куда, но обязательно куда-нибудь закачивать на совершенно отдельный сервер/сервис, с проверкой, что всё успешно закачалось.
    И лучше - на 2 разных хранилища.

    Может, на мыло тоже отправлять на всякий случай?

    Ну, чисто факультативно можно, хотя смысла в этом я лично не вижу, если вы закачиваете бэкап на другие сервера/сервисы.
    Т.е. на мыло вы можете слать, а можете и не слать, но закачивать бэкап на 1-2 других сервера/сервиса - необходимо.

    Из личного опыта. Тру-админы, просьба поправить, если где-то косячу.
    Написал bash-скрипт, который запускаю по крону раз в 3 часа. Скрипт
    • делает бэкап обычным pg_dump, в несколько потоков, в формате папки (--format=d)
    • проводит тестовое восстановление бэкапа. Чисто в лоб - завершится ли pg_restore с нулевым кодом возврата
    • сжимает бэкап БД и файлов в архив
    • отправляет архив на отдельное ftp-хранилище
    • отправляет архив на ещё одно ftp-хранилище
    • отправляет краткий лог всего процесса (с метками времени) в корпоративный slack

    Пока храню всё, не вычищая старого (с местом проблем нет). В идеале, видимо, нужно хранить по схеме
    • N (где N - несколько десятков) последних
    • 4-6 последних еженедельных
    • 2-4 последних ежемесячных.
    Ответ написан
    3 комментария