The OpenNET Project / Index page

[ новости /+++ | форум | теги | ]

PostgreSQL специфика

   Корень / Программисту и web-разработчику / SQL и базы данных / PostgreSQL специфика
PlPerl и PlSQL [6]
Оптимизация и администрирование PostgreSQL [29]

----* Настройка СУБД Postgresql для аутентификации пользователей через Active Directory   Автор: Slonik  [комментарии]
  В статье расскажу про мой опыт настройки СУБД Postgresql для включения аутентификации пользователей через Active Directory с помощью протокола GSSAPI.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
 
----* Настройка СУБД PostgreSQL 13 под управлением Pacemaker/Corosync в Debian 11   Автор: Slonik  [комментарии]
  В статье расскажу про мой опыт настройки Postgresql для работы под контролем кластерной службы Pacemaker
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
 
----* Добавление поддержки SSL в pgbouncer при помощи stunnel   Автор: umask  [комментарии]
  Для быстрого старта pgbouncer c поддержкой SSL можно использовать вот такой конфигурационный файл stunnel:
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
 
----* Установка Londiste, системы асинхронной мастер-слэйв репликации PostgreSQL (доп. ссылка 1)   Автор: Sergey Konoplev  [комментарии]
  Инструкция содержит подробное пошаговое описание процесса настройки репликации на основе Londiste, системы асинхронной мастер-слэйв репликации из пакета SkyTools от Skype.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
 
----* Определение размеров объектов БД в PostgreSQL (доп. ссылка 1)   Автор: Konstantin A Mironov  [комментарии]
 
Размер БД:
   select pg_database_size('имя базы');

Размер таблицы БД:
   select select pg_relation_size('имя таблицы');

Полный размер таблицы с индексами:
   select pg_total_relation_size('имя таблицы');

Размер столбца:
   select pg_column_size('имя стобца') from 'имя таблицы';

Состояние всех настроек:
   select pg_show_all_settings();
 
----* Решение проблем с наличием в MySQL записей с битой кодировкой   [обсудить]
 
Способ перекодирования выборочных записей в MySQL, содержащих данные в битой кодировке.
Перекодирование ошибочно добавленных нескольких записей с UTF-8 текстом 
в таблицу в которой данные находятся в кодировке cp1251 (DEFAULT CHARSET cp1251).


UPDATE table SET column=CONVERT(CONVERT(CONVERT(column USING binary) USING
utf8) USING cp1251) WHERE id=123;

Сокращенный вариант, внешний CONVERT можно убрать, MySQL знает, что данные в таблице в cp1251:

UPDATE table SET column=CONVERT(CONVERT(column USING binary) USING utf8) WHERE id=123;
 
----* Как добиться, чтобы в запросах LIKE 'что%' использовался индекс ? (доп. ссылка 1)   Автор: Олег Бартунов  [комментарии]
 
Из-за сложности и многообразия locale в постгресе запрещено использовать индекс
для запросов вида LIKE 'что%' для всех locale кроме 'C'. А что делать если хочется ? 
В 8.01 стало возможным использовать operator class [1] !  Мы будем использовать
varchar_pattern_ops, B-tree индекс
в этом случае, будет строиться без использования collation правил из locale, а
на основе сравнения буквы с буквой.

   test=#  \d ru_words 
       w      | text | 
       Indexes:
          "w_idx" btree (lower(w) varchar_pattern_ops)

   test=# create index w_idx on ru_words (lower(w) varchar_pattern_ops);
      CREATE INDEX

   test=# vacuum analyze test;

   test=# explain analyze select w from ru_words where lower(w) like 'что%';

       Index Scan using w_idx on ru_words...
         Index Cond: ((lower(w) ~>=~ 'что'::character varying) AND (lower(w) ~<~ 'чтп'::character varying))
         Filter: (lower(w) ~~ 'что%'::text)
 
----* Функции для преобразования unix timestamp в Pg timestamp для PostgreSQL (доп. ссылка 1)   Автор: Олег Бартунов  [обсудить]
 
    SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1109796233 * INTERVAL '1 second';

CREATE OR REPLACE FUNCTION ts2int(timestamp without time zone) RETURNS int AS
$$
select extract('epoch' from $1)::integer;
$$ LANGUAGE SQL STRICT STABLE;

CREATE OR REPLACE FUNCTION int2ts(integer) RETURNS timestamp AS
$$
SELECT ( TIMESTAMP WITH TIME ZONE 'epoch' + $1 * INTERVAL '1second')::timestamp without time zone;
$$ LANGUAGE SQL STRICT STABLE;
 
----* Полнотекстовый поиск в PostgreSQL (Tsearch2) (доп. ссылка 1)   [комментарии]
 
  ALTER TABLE companies 
  ADD COLUMN fti_business tsvector;

  UPDATE companies SET fti_business = to_tsvector('default',business_model);

  VACUUM FULL ANALYZE companies;

  CREATE INDEX idx_fti_business ON companies USING gist(fti_business);

  CREATE TRIGGER tg_fti_companies 
  BEFORE UPDATE OR INSERT ON companies
  FOR EACH ROW EXECUTE PROCEDURE
    tsearch2(fti_business, business_model);

  SELECT company_name, business_model
  FROM companies
  WHERE fti_business @@
    to_tsquery('default','bushing | engine');
 
----* Как реализовать "COPY table TO stdout" на perl используя модуль Pg.   [обсудить]
 
Для просмотра всего содержимого таблицы оптимальнее использовать COPY TO, вместо SELECT.
$conn->exec('COPY table (in, out) TO stdout');
die $conn->errorMessage if($conn->errorMessage);
$conn->getline($cur_line, 512);
while ($cur_line ne '\\.'){
      my ($in, $out) = split(/\t/, $cur_line);
      ....
      $conn->getline($cur_line, 512); 
}
$conn->endcopy;
 
----* Как реализовать "COPY table FROM stdin" на perl используя модуль Pg.   [обсудить]
 
COPY FROM вместо INSERT позволяет значительно оптимизировать помещение данных в базу.
$conn->exec('COPY traffic (src_ip, dst_ip, in_octets, out_octets) FROM stdin;');
die $conn->errorMessage if($conn->errorMessage);
while(...) { 
   $conn->putline("$src\t$dst\t$in\t$out\n");
}
$conn->putline("\\.\n");
$conn->endcopy;
 
----* Как через SELECT запрос в PostgreSQL посмотреть структуру таблицы.   [обсудить]
 
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum 
  FROM pg_class c, pg_attribute a 
  WHERE c.relname ='имя таблицы' AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum;
 
----* Как поменять или установить пароль для пользователя в PostgreSQL   [комментарии]
 
ALTER USER имя_пользователя WITH ENCRYPTED PASSWORD 'пароль';
В pg_hba.conf в качестве метода аутентификации должен использоваться md5.
 
----* Как выполнить в PostgreSQL запрос вида "pivot table" и использовать условие при выводе данных. (доп. ссылка 1)   [обсудить]
 
Если значение поля vendor = 1,2 или 3 суммируем только значения sales для этих номеров.
SELECT product,
  SUM(CASE vendor WHEN 1 THEN sales ELSE 0 END) AS "pink ",  
  SUM(CASE vendor WHEN 2 THEN sales ELSE 0 END) AS "brown",  
  SUM(CASE vendor WHEN 3 THEN sales ELSE 0 END) AS "green",
  SUM(sales) AS "sum of sales" 
      FROM sales GROUP BY product;
Если необходимо сделать выборку по промежутку, то нужно использовать:
    CASE WHEN vendor > 1 AND vendor < 5 THEN sales ELSE 0 END
 
----* Как добавить комментарии к таблицам в PostgreSQL   [обсудить]
 
COMMENT ON test IS 'Это тестовая таблица.';
COMMENT ON DATABASE test IS 'Тестовая БД';
COMMENT ON INDEX test_index IS 'Индекс тестовой базы по id';
COMMENT ON COLUMN test.id IS 'Ключевое поле';
 
----* Как в Shell выполнить SQL запрос или получить список баз и таблиц (PostgreSQL)   [комментарии]
 
Список баз:
    psql -A -q -t -c "select datname from pg_database" template1 | grep -v '^template1$'
Список таблиц в базе db_name :
    echo '\d'| psql -A -q -t db_name |cut -d'|' -f1
 
----* Как включить автоматическую проверку значений в PostgreSQL   [обсудить]
 
Для запрещения нулевых значений в поле id в CREATE TABLE:
   CONSTRAINT "test_tab_id" CHECK (id > 0)
Или если таблица уже существует:
   ALTER TABLE test_tab ADD CONSTRAINT "test_tab_id" CHECK (id > 0);
 
----* Как посмотреть в PostgreSQL размер таблиц на диске и число записей в них   [обсудить]
 
SELECT relname, relpages*8192, reltuples FROM pg_class
     WHERE NOT relname LIKE 'pg_%' ORDER BY relpages DESC;
 
----* Как сопоставить в PostgreSQL цифровые имена файлов и директорий с символьными именами таблиц и баз.   [обсудить]
 
Сопоставление имен директорий с названиями баз:
   select oid,datname from pg_database;
Сопоставление имен таблиц в текущей базе к именам файлов:
   select relname, relfilenode from pg_class;
 
----* Как получить уникальный системный номер записи в PostgreSQL   [обсудить]
 
Поле с именем OID всегда содержит уникальный номер записи.
select oid from table;
 
----* маленькая заметка к возрастающим ключам   Автор: Yuri A. Kabaenkov  [обсудить]
 
в последних версиях pgsql существует тип данных serial
которой автоматически создает последовательность.
тоесть CREATE TABLE test (
     a serial
);
 
----* Как осуществить автоматическую проверку новых данных в PostgreSQL   [обсудить]
 
CREATE RULE table_id_update AS ON UPDATE TO table 
   WHERE OLD.id != NEW.id 
   DO INSTEAD NOTHING;
 
----* Как организовать таблицу с подробной историей всех изменений в PostgreSQL   [комментарии]
 
CREATE RULE table_update AS ON UPDATE TO table 
   DO INSERT INTO table_log ( 
      id, 
      titile, 
      contents, 
      pguser, 
      date_modified 
    ) 
    VALUES ( 
       OLD.id, 
       OLD.title, 
       OLD.description, 
       getpgusername(), 
       'now'::text 
   );
 
----* Преобразование дат (сек. с 1970 и timestamp) в PostgreSQL   [комментарии]
 
Из еpoch в timestamp:
   'epoch'::timestamptz  + '$epoch_time sec'::interval
   или $epoch_time::int4::abstime::timestamptz
   или timestamptz 'epoch' + '$epoch_time second'

Из timestamp в epoch:
   date_part('epoch', timestamp_field)
 
----* Как создать индекс в PostgreSQL   [обсудить]
 
Создадим индекс для двух полей login и price в таблице item. 
При использовании операций больше или меньше нужно использовать btree индексы, 
hash для операций '='. Несколько полей для индексирования можно указывать только 
для btree.
Например:
CREATE UNIQUE INDEX "index_item" on "item" using btree ( "login" "varchar_ops",
"price" "integer_ops");
CREATE INDEX "index_item2" on "item" using hash ( "login" );
 
----* Как автоматически генерировать возрастающие ключи   [обсудить]
 
CREATE SEQUENCE next_item start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;
или CREATE SEQUENCE next_item;
CREATE TABLE item (
	"id"                integer DEFAULT nextval('next_item') PRIMARY KEY,
	.....
);
 
----* Как ограничить число элементов выдаваемых SELECT в PostgreSQL   [комментарии]
 
Использовать директиву "LIMIT сколько_записей_выводить OFFSET с_какой_записи_начинать_вывод":
Например, вывести 10 записей удовлетворяющих запросу, начиная с 50:
SELECT * FROM table LIMIT 10 OFFSET 50;
 
----* Импорт КЛАДР в базу данных PostgreSQL   Автор: Легостаев Вениамин  [комментарии]
  ++ Конвертация КЛАДР (классификатор адресов России) в формат sqlite.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение]
 

 Версия для печати

Партнёры:
PostgresPro
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей 		Created 1996-2024 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру