Как разбить вывод из mysql постранично. Функции PostgreSQL Перечень подключенных пользователей

pg_update() заменяет записи в таблице, удовлетворяющие условиям condition данными data . Если задан аргумент options , данные будут преобразованы функцией pg_convert() , которой будут переданы параметры из этого аргумента.

Список параметров

Ресурс соединения с базой данных PostgreSQL.

Table_name

Имя таблицы, в которой обновляются записи.

Массив (array ), ключи которого соответствуют именам колонок таблицы table_name , а значения заменят данные в этих колонках.

Condition

Массив (array ), ключи которого соответствуют именам колонок таблицы table_name . Будут обновлены только те строки, значения полей которых совпадут со значениями массива.

Options

Одна из констант PGSQL_CONV_OPTS , PGSQL_DML_NO_CONV , PGSQL_DML_ESCAPE , PGSQL_DML_EXEC , PGSQL_DML_ASYNC или PGSQL_DML_STRING , либо их комбинация. Если options содержит PGSQL_DML_STRING , функция вернет строку. Если установлены PGSQL_DML_NO_CONV или PGSQL_DML_ESCAPE , то функция pg_convert() внутренне не вызывается.

Возвращаемые значения

Возвращает TRUE в случае успешного завершения или FALSE в случае возникновения ошибки. Функция вернет строку (string ), если константа PGSQL_DML_STRING содержится в options .

Примеры

Пример #1 Пример использования pg_update()

$db = pg_connect ("dbname=foo" );
$data = array("field1" => "AA" , "field2" => "BB" );

// Это безопасно с тех пор как $_POST преобразуется автоматически
$res = pg_update ($db , "post_log" , $_POST , $data );
if ($res ) {
echo "Данные обновлены: $res \n" ;
} else {
echo "Должно быть переданы неверные данные\n" ;
}
?>

С версии 6.3 (от 2 марта 1998 года) PostgreSQL использует сокеты домена UNIX. Этот сокет может быть найден в каталоге /tmp/.s.PGSQL.5432 . Это свойство может быть включено с помощью флага "-i" в postmaster и это означает "прослушивание сокетов TCP/IP", аналогично сокетам домена UNIX.

Таблица 1. Postmaster and PHP

Можно также установить соединение с помощью команды: $conn = pg_Connect("host=localhost port=5432 dbname=chris");

Для использования интерфейса больших объектов (large objects) необходимо заключить их в блок транзакции. Блок транзакции начинается begin и, если транзакция прошла успешно, завершается commit и end . Если транзакция дала сбой, то транзакция должна быть закрыта с помощью abort и rollback .

pg_Close

Описание

Возвращает false, если connection - не правильный номер соединения. Закрывает соединение с базой PostgreSQL, связанное с указанным номером соединения.

pg_cmdTuples

Описание

pg_cmdTuples() возвращает число tuples (требований), вызванных запросами INSERT, UPDATE, и DELETE. Если нет вызванных tuple, то функция вернет 0.

pg_Connect

Описание

Возвращает номер соединения при успехе или false, если соединение не может быть установлено. Открывает соединение с базой PostgreSQL. Каждый из аргументов должен быть в кавычках, включая номер порта. Аргементы options и tty являются дополнительными и могут быть опущены. Эта функция возвращает номер соединения, который необходим другим функциям PostgreSQL. Вы можете иметь одновременно несколько открытых соединений.

Соединение может быть также установлено следующей командой: $conn = pg_connect("dbname=marliese port=5432"); Дополнительные параметры (кроме dbname и port ) - это host , tty и options .

pg_DBname

Описание

Возвращает имя базы данных, с которой соединен указанный номер соединения PostgreSQL, или возвращает false, если указан не правильный номер соединения.

pg_ErrorMessage

Описание

Возвращает строку, содержащую сообщение об ошибке, или false при сбое. Детали об ошибке, вероятно, не могут быть получены с помощью pg_errormessage(), если ошибка произошла при последнем обращении к базе данных, для которой существует верный номер соединения, то функция вернет строку, содержащую сообщение об ошибке, выданное сервером.

pg_Exec

Описание

Возвращает признак результата, если запрос может быть запущен, false при ошибке или при неверном номере соединения. Детали об ошибке могут получены с помощью функции , если номер соединения верный. Функция посылает SQL запрос базе данных PostgreSQL, определяемой номером соединения. Connection должен быть верным номером соединения, который возвращается функцией . Возвращаемое этой функцией значение является необходимым для доступа к результатам других функций PostgreSQL.

Замечание: PHP2 возвращает 1, если запрос не должен возвращать данных (например, при вставке или обновлении) и больше 1, даже при выборках, которые ничего не возвращают. Таких допущений нет в PHP3.

pg_Fetch_Array

Описание

Возвращает массив, который ссылается на полученную запись, или false если больше нет записей.

pg_fetch_array() является расширенной версией . В дополнение к хранению данных под числовыми индексами в результирующем массиве, функция также хранит данные под ассоциативными индексами, используя номера полей в качестве ключей.

Необходимо отметить, что функция pg_fetch_array() практически одинакова по скорости с , хотя и предоставляет дополнительные возможности.

Для дополнительной информации см.

pg_Fetch_Object

Описание

Возвращает объект со свойствами, которые ссылаются на полученную запись, или false если нет больше записей.

$item."\n";
 endwhile;
 $row++;
endwhile;
echo "----------\n"; ?>

pg_Fetch_Row

Описание

Возвращает массив, который ссылается на полученную запись, или false, если больше нет записей.

pg_fetch_row() возвращает одну запись данных из результата, определяемого указанным идентификатором результата. Запись возвращается как массив. Каждый столбец результата храниться в элементе массива, начиная с 0.

Последующий вызов функции pg_fetch_row() возвратит следующую запись в результирующем наборе, или false, если записей больше нет.

pg_FieldIsNull

Описание

Проверяет нулевое (NULL) значение у поля или нет. Возвращает 0, если поле в указанной записи не нулевое. Возвращает 1, если поле ы указанное записи нулевое. Поле может определяться номером или именем. Нумерация записей начинается с 0.

pg_FieldName

Описание

pg_FieldName() возвращет имя поля, соответствующее указанному номеру столбца в указанном идентификаторе результата PostgreSQL. Нумерация полей начинается с 0.

pg_FieldNum

Описание

pg_FieldNum() возвращает номер столбца, соответствующий указанному имени поля в указанном идентификаторе результата PosgreSQL. Нумерация полей начинается с 0. Функция вернет -1 при ошибке.

pg_FieldPrtLen

Описание

pg_FieldPrtLen() возвращает действительную печатную длину (число символов) указанных данных в результате PosgreSQL. Нумерация записей начинается с 0. При ошибке функция вернет -1.

pg_FieldSize

Описание

pg_FieldSize() возвращает внутренний размер хранения указанного именованного поля в указанном результате PosgreSQL. Размер поля -1 говорит о переменной длине поля. Эта функция вернет false при ошибке.

pg_FieldType

Описание

pg_FieldType() возвращает строку, содержащую имя типа указанного поля в указанном идентификаторе результата PosgreSQL. Нумерация полей начинается с 0.

pg_FreeResult

Описание

pg_locreate

Описание

pg_locreate() создает отображение большого объекта (ILO) и возвращает идентификатор большого объекта. conn определяет верное соединение с базой данных. Режимы доступа PostgreSQL INV_READ, INV_WRITE, и INV_ARCHIVE не поддерживаются, объект создается всегда и для чтения и для записи. INV_ARCHIVE был удален из самой PostgreSQL (версии 6.3 и выше).

pg_loopen

Описание

pg_loopen() открывает отображение большого объекта (ILO) и возвращает дескриптор файла большого объекта. Дескриптор файла содержит информацию о соединении. Не закрывайте соединение до закрытия дескриптора файла большого объекта. objoid определяет верный идентификатор большого объекта и mode может быть одним из "r", "w", or "rw".

pg_loread

Описание

pg_loread() считывает len байтов из большого объекта и возвращает их в виде строки. fd определяет верный декскриптор файла большого объекта len определяет максимально возможный размер сегмента большого объекта.

pg_loreadall

Описание

pg_loreadall() считывает большой объект и отправляет его прямо в броузер, после всех необходимых заголовков. В основном применяется для отправки двоичных данных, таких как изображения и звуковые файлы.

pg_lounlink

Описание

pg_lounlink() удаляет большой объект с идентификатором большого объекта lobjid .

pg_lowrite

Описание

pg_lowrite() записывает данные в большой объект из переменной buf и возвращает число записанных данных или false при ошибке. fd является дескриптором файла большого объекта из функции .

pg_NumFields

Описание

pg_NumFields() возвращает количестов полей (столбцов) в результате PostgreSQL. Параметр является верным идентификатором результата, полученным функцией . Эта функция вернет -1 при ошибке.

pg_NumRows

Описание

Соединение может также быть установлено следующей командой: $conn = pg_pconnect("dbname=marliese port=5432"); Остальные параметры, кроме dbname и port , - это host , tty и options .

pg_Port

Описание

pg_Port() возвращает номер порта, с которым соединен указанный идентификатор соединения PostgreSQL.

pg_Result

Описание

pg_Result() возвращает данные из идентификатора результата, полученного . Параметры row_number и fieldname определяют, из какой ячейки таблицы будут взяты данные. Нумерация записей и полей начинается с 0. Вместо имени поля вы можете использовать номер поля (без кавычек).

PostgreSQL имеет много видов данных, но здесь поддерживаются только основные. Все виды целых и логических типов, типов идентификаторов возвращаются как целые числа. Все виды типов с плавающей точкой и действительных чисел возвращаются как тип double. Все осталные типы, включая массивы возвращаются как строки, отформатированные в том же виде, какой вы видели в программе psql .

pg_tty

Описание

pg_tty() возвращает имя tty, которое сервер дает указанному идентификатору соединения PostgreSQL.

Как сделать постраничный вывод из mysql "как в яндексе"?
по 10 записей на страницу, внизу - ссылки на остальные страницы?

Сначала научимся получать из базы нужные записи.
Их получение в mysql обеспечивается оператором LIMIT, который вызывается с двумя параметрами - с какой записи начинать, и сколько выводить (внимание! не по какую, а сколько!)
SELECT * FROM table LIMIT 0,10
этот запрос вернет записи с первой по 10, поскольку нумерация начинается с 0
соответственно, запрос для третьей страницы будет выглядеть, как
SELECT * FROM table LIMIT 20,10
получается, что нам всего лишь надо передать в скрипт число, которое потом подставить в запрос.
Этим будет заниматься код, который выводит ссылки на страницы.
Естественно, в цикле.
Для цикла нам понадобится количество записей, которое возвращает запрос без лимита.
Это число можно получить двумя путями. Либо отдельным запросом, в котором отсутствует оператор LIMIT, а вместо перечисления полей после оператора SELECT запрашивается только count(*):
$q = "SELECT count(*) FROM table" ;
$res = mysql_query ($q );
$row = mysql_fetch_row ($res );
$total_rows = $row [ 0 ];

Либо, если версия mysql больше 4.0, то общее количество строк можно запросить в том же запросе. См. документацию mysql по функции FOUND_ROWS()
Однако, первый сособ представляется более удобным, хотя и немного более медленным.

Для тех, кто предпочитает думать самостоятельно, этой информации достаточно.
Вывод ссылок - это простейшая арифметическая операция и никаких особенных знаний для нее не требуется.
Для тех, же, кто ищет готовый код, продолжим.

Для начала определим, сколько всего получится страниц. Для этого надо поделить общее число записей на количество оных на одной странице и округлить результат в большую сторону. Таким округлением занимается в пхп функция ceil()
$num_pages = ceil ($total_rows / $per_page );
В этом выражении участвует переменная $per_page , в которую мы положим количество выводимых на странице записей.
Ведь, если это количество изменится, мы же не хотим ползать по всему коду и исправлять цифры? проще сделать это один раз в начале скрипта при объявлении переменной. В запрос, вторым параметром LIMIT, подставлять нужно, конечно же, тоже ее.

Ну, а дальше, собственно, вывод ссылок.
for($i = 1 ; $i <= $num_pages ; $i ++) {
echo "" . $i . "\n" ;
}
в цикле от 1 до $num_pages выводим ссылку с параметром num, равным числу, которое надо передать в LIMIT, а в тексте ссылки пишем номер страницы, поскольку людям понятнее видеть номер страницы, а не записи. На код это не влияет, а людям приятно.

Дальше пойдут украшения.
Во-первых, некрасиво, что номер страницы не совпадает с тем, что видно в адресной строке. Эту проблему можно решить, передавая по ссылке номер страницы в человекопонятном формате, а в скрипте вычислять первый операнд для LIMIT.
Во-вторых, мы явно захотим выделить текущую страницу, не оформляя ее ссылкой.
В-третьих, мы захотим нумеровать записи

Разумеется, вышеприведённый код подходит только как учебное пособие. С его помощью становится понятным принцип, но в реальных условиях мы сразу же столкнемся, как минимум, с двумя проблемами:
Во-первых, кроме переменной $page нашему крипту явно будут переданы и другие переменные, да и адрес может совсем не совпадать с именем скрипта. А мы это при формировании ссылок не учитываем.
Во-вторых, нормальный современный сайт немыслим без шаблонов. И такая ужасная лапша из SQL запросов, PHP кода и HTML тегов никуда не годится.

Плюс надо избавляться от устаревшего расширения mysql и организовывать работу с БД более интеллектуально.

Займемся решением этих проблем.
Первая решается очень просто при использовании функции http_build_query()

Вторая - тоже несложно. Шаблонизаторов много, но мы воспользуемся самым универсальным - PHP.

Что же у нас получилось? А получился у нас - рефакторинг ! Переделка старого кода в соответствии с требованиями современности, плюс мелкое причесывание:

include "safemysql.class.php" ;
$db = new safeMysql ();

$per_page = 10 ;

//получаем номер страницы и значение для лимита
$cur_page = 1 ;
if (isset($_GET [ "page" ]) && $_GET [ "page" ] > 0 )
{
$cur_page = $_GET [ "page" ];
}
$start = ($cur_page - 1 ) * $per_page ;

//выполняем запрос и получаем данные для вывода
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM Board LIMIT ?i, ?i" ;
$data = $db -> getAll ($sql , $start , $per_page );
$rows = $db -> getOne ("SELECT FOUND_ROWS()" );

//узнаем общее количество страниц и заполняем массив со ссылками
$num_pages = ceil ($rows / $per_page );

// зададим переменную, которую будем использовать для вывода номеров страниц
$page = 0 ;

//а дальше выводим в шаблоне днные и навигацию:
?>
Найдено сообщений:



. ">

Страницы:




">

Стандартная библиотека шаблонов (Standard Template Library , STL) входит в стандартную библиотеку языка C++. В неё включены реализации наиболее часто используемых контейнеров и алгоритмов, что избавляет программистов от рутинного переписывания их снова и снова. При разработке контейнеров и применяемых к ним алгоритмов (таких как удаление одинаковых элементов, сортировка, поиск и т. д.) часто приходится приносить в жертву либо универсальность, либо быстродействие. Однако разработчики STL поставили перед собой задачу:сделать библиотеку одновременно эффективной и универсальной. Для ее решения были использованы такие универсальные средстваязыка C++, как шаблоны и перегрузка операторов. В последующем изложении будем опираться на реализацию STL, поставляемую фирмой Microsoft вместе с компилятором Visual C++ 6.0. Тем не менее большая часть сказанного будет справедлива и для реализаций STL другими компиляторами.

Основными понятиями в STL являются понятия контейнера (container), алгоритма (algorithm) и итератора (iterator).

Контейнер - это хранилище объектов (как встроенных, так и определённых пользователем типов). Как правило, контейнеры реализуются в виде шаблонов классов. Простейшие виды контейнеров (статические и динамические массивы) встроены непосредственно в язык C++. Кроме того, стандартная библиотека включает в себя реализации таких контейнеров, как вектор (vector), список (list), очередь (deque), ассоциативный массив (map), множество (set) и некоторых других.

Алгоритм - это функция для манипулирования объектами, содержащимися в контейнере. Типичные примеры алгоритмов - сортировка и поиск. В STL реализовано порядка 60 алгоритмов, которые можно применять к различным контейнерам, в том числе к массивам, встроенным в язык C++.

Итератор - это абстракция указателя, то есть объект, который может ссылаться на другие объекты, содержащиеся в контейнере. Основные функции итератора - обеспечение доступа к объекту, на который он ссылается (разыменование), и переход от одного элемента контейнера к другому (итерация, отсюда и название итератора). Для встроенных контейнеров в качестве итераторов используются обычные указатели. В случае с более сложными контейнерами итераторы реализуются в виде классов с набором перегруженных операторов.

Помимо отмеченных элементов в STL есть ряд вспомогательных понятий ; с некоторыми из них следует также познакомиться.

Аллокатор (allocator) - это объект, отвечающий за распределение памяти для элементов контейнера. С каждым стандартным контейнером связывается аллокатор (его тип передаётся как один из параметров шаблона). Если какому-то алгоритму требуется распределять память для элементов, он обязан делать это через аллокатор. В этом случае можно быть уверенным, что распределённые объекты будут уничтожены правильно.

В состав STL входит стандартный класс allocator (описан в файле xmemory). Именно его по умолчанию используют все контейнеры, реализованные в STL. Однако пользователь может реализовать собственный класс. Необходимость в этом возникает очень редко, но иногда это можно сделать из соображений эффективности или в отладочных целях.

Остановимся более подробно на рассмотрении введенных понятий.

Контейнеры . Каждый контейнер предоставляет строго определённый интерфейс, через который с ним будут взаимодействовать алгоритмы. Этот интерфейс обеспечивают соответствующие контейнеру итераторы. Важно подчеркнуть, что никакие дополнительные функции-члены для взаимодействия алгоритмов и контейнеров не используются. Это сделано потому, что стандартные алгоритмы должны работать, в том числе со встроенными контейнерами языка C++, у которых есть итераторы (указатели), но нет ничего, кроме них. Таким образом, при создании собственного контейнера реализация итератора - необходимый минимум.

Каждый контейнер реализует определённый тип итераторов. При этом выбирается наиболее функциональный тип итератора, который может быть эффективно реализован для данного контейнера. "Эффективно" означает, что скорость выполнения операций над итератором не должна зависеть от количества элементов в контейнере. Например, для вектора реализуется итератор с произвольным доступом, а для списка - двунаправленный. Поскольку скорость выполнения операции для списка линейно зависит от его длины, итератор с произвольным доступом для списка не реализуется.

Вне зависимости от фактической организации контейнера (вектор, список, дерево) хранящиеся в нём элементы можно рассматривать как последовательность. Итератор первого элемента в этой последовательности возвращает функция begin(), а итератор элемента, следующего за последним, - функция end(). Это очень важно, так как все алгоритмы в STL работают именно с последовательностями, заданными итераторами начала и конца.

Кроме обычных итераторов в STL существуют обратные итераторы (reverse iterator ). Обратный итератор отличается тем, что просматривает последовательность элементов в контейнере в обратном порядке. Другими словами, операции + и - у него меняются местами. Это позволяет применять алгоритмы как к прямой, так и к обратной последовательности элементов. Например, с помощью функции find можно искать элементы как "с начала", так и "с конца" контейнера.

В STL контейнеры делятся на три основные группы (табл. 2): контейнеры последовательностей, ассоциативные контейнеры и адаптеры контейнеров. Первые две группы объединяются в контейнеры первого класса.

Таблица 2

Каждый класс контейнера, реализованный в STL , описывает набор типов, связанных с контейнером. При написании собственных контейнеров следует придерживаться этой же практики. Вот список наиболее важных типов:

value _ type - тип элемента;

size _ type - тип для хранения числа элементов (обычно size _ t );

iterator - итератор для элементов контейнера;

key _ type - тип ключа (в ассоциативном контейнере).

Помимо типов можно выделить набор функций, которые реализует почти каждый контейнер в STL (табл. 3). Они не требуются для взаимодействия с алгоритмами, но их реализация улучшает взаимозаменяемость контейнеров в программе. STL разработана с тем расчетом, чтобы контейнеры обеспечивали аналогичные функциональные возможности.

Таблица 3

Итераторы обычно создаются как друзья классов, с которыми они работают, что позволяет выполнить прямой доступ к частным данным этих классов. С одним контейнером может быть связано несколько итераторов, каждый из которыхподдерживаетсвою собственную «позиционную информацию»(табл. 4).

Таблица 4

В сети много руководств по PostgreSQL, которые описывают основные команды. Но при погружении глубже в работу возникают такие практические вопросы, для которых требуются продвинутые команды.

Такие команды, или сниппеты , редко описаны в документации. Рассмотрим несколько на примерах, полезных как для разработчиков, так и для администраторов баз данных.

Получение информации о базе данных

Размер базы данных

Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:

SELECT pg_database_size(current_database());

Результат будет представлен как число вида 41809016 .

current_database() - функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:

SELECT pg_database_size("my_database");

Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty:

SELECT pg_size_pretty(pg_database_size(current_database()));

В результате получим информацию вида 40 Mb .

Перечень таблиц

Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:

SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ("information_schema","pg_catalog");

information_schema - стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.

Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:

SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ("information_schema", "pg_catalog") AND table_schema IN("public", "myschema");

В последнем условии IN можно указать имя определенной схемы.

Размер таблицы

По аналогии с получением размера базы данных размер данных таблицы можно вычислить с помощью соответствующей функции:

SELECT pg_relation_size("accounts");

Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.

Имя самой большой таблицы

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

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;

relname - имя таблицы, индекса, представления и т.п.
relpages - размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class - системная таблица, которая содержит информацию о связях таблиц базы данных.

Перечень подключенных пользователей

Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;

Активность пользователя

Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:

SELECT datname FROM pg_stat_activity WHERE usename = "devuser";

Работа с данными и полями таблиц

Удаление одинаковых строк

Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:

• дублирующиеся строки,
• ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).

Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).

Удалить все дубликаты поможет следующий запрос:

DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customers.*);

Уникальное для каждой записи поле ctid по умолчанию скрыто, но оно есть в каждой таблице.

Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.

Теперь рассмотрим случай, когда повторяются значения полей.

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

DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);

Если данные важны, то сначала нужно найти записи с дубликатами:

SELECT * FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);

Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.

Общая форма запроса на удаление описанных выше записей выглядит следующим образом:

DELETE FROM table_name WHERE ctid NOT IN (SELECT max(ctid) FROM table_name GROUP BY column1, );

Безопасное изменение типа поля

Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER . Давайте для примера снова рассмотрим таблицу с покупателями.

Для поля customer_id используется строковый тип данных varchar . Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат integer . Использование varchar неоправданно. Попробуем исправить это недоразумение с помощью команды ALTER:

ALTER TABLE customers ALTER COLUMN customer_id TYPE integer;

Но в результате выполнения получим ошибку:

ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar , СУБД не может определить принадлежность значения к integer . Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING , чтобы корректно преобразовать наши данные в integer:

ALTER TABLE customers ALTER COLUMN customer_id TYPE integer USING (customer_id::integer);

В результате всё прошло без ошибок:

Обратите внимание, что при использовании USING кроме конкретного выражения возможно использование функций, других полей и операторов.

Например, преобразуем поле customer_id обратно в varchar , но с преобразованием формата данных:

ALTER TABLE customers ALTER COLUMN customer_id TYPE varchar USING (customer_id || "-" || first_name);

В результате таблица примет следующий вид:

Поиск «потерянных» значений

Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.

Рассмотрим два варианта поиска.

Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:

SELECT customer_id + 1 FROM customers mo WHERE NOT EXISTS (SELECT NULL FROM customers mi WHERE mi.customer_id = mo.customer_id + 1) ORDER BY customer_id;

В результате получим значения: 5 , 9 и 11 .

Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:

WITH seq_max AS (SELECT max(customer_id) FROM customers), seq_min AS (SELECT min(customer_id) FROM customers) SELECT * FROM generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max)) EXCEPT SELECT customer_id FROM customers;

В результате видим следующий результат: 5 , 9 и 6 .

Второй способ
Получаем имя последовательности, связанной с customer_id:

SELECT pg_get_serial_sequence("customers", "customer_id");

И находим все пропущенные идентификаторы:

WITH sequence_info AS (SELECT start_value, last_value FROM "SchemaName"."SequenceName") SELECT generate_series ((sequence_info.start_value), (sequence_info.last_value)) FROM sequence_info EXCEPT SELECT customer_id FROM customers;

Подсчёт количества строк в таблице

Количество строк вычисляется стандартной функцией count , но её можно использовать с дополнительными условиями.

Общее количество строк в таблице:

SELECT count(*) FROM table;

Количество строк при условии, что указанное поле не содержит NULL:

SELECT count(col_name) FROM table;

Количество уникальных строк по указанному полю:

SELECT count(distinct col_name) FROM table;

Использование транзакций

Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.

Начнём транзакцию с помощью команды BEGIN .

Для того, чтобы откатить все операции, расположенные после BEGIN , используем команду ROLLBACK .

А чтобы применить - команду COMMIT .

Просмотр и завершение исполняемых запросов

Для того, чтобы получить информацию о запросах, выполним следующую команду:

SELECT pid, age(query_start, clock_timestamp()), usename, query FROM pg_stat_activity WHERE query != "" AND query NOT ILIKE "%pg_stat_activity%" ORDER BY query_start desc;

Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):

SELECT pg_cancel_backend(procpid);

Для того, чтобы прекратить работу запроса, выполним:

SELECT pg_terminate_backend(procpid);

Работа с конфигурацией

Поиск и изменение расположения экземпляра кластера

Возможна ситуация, когда на одной операционной системе настроено несколько экземпляров PostgreSQL, которые «сидят» на различных портах. В этом случае поиск пути к физическому размещению каждого экземпляра - достаточно нервная задача. Для того, чтобы получить эту информацию, выполним следующий запрос для любой базы данных интересующего кластера:

SHOW data_directory;

Изменим расположение на другое с помощью команды:

SET data_directory to new_directory_path;

Но для того, чтобы изменения вступили в силу, требуется перезагрузка.

Получение перечня доступных типов данных

Получим перечень доступных типов данных с помощью команды:

SELECT typname, typlen from pg_type where typtype="b";

typname - имя типа данных.
typlen - размер типа данных.

Изменение настроек СУБД без перезагрузки

Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf и pg_hba.conf . После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:

SELECT pg_reload_conf();

Но, к сожалению, она применима не ко всем параметрам. В некоторых случаях для применения настроек перезагрузка обязательна.