Главная » Uncategorized » SQL, PostgreSQL

SQL, PostgreSQL

====== noob ======
Created Friday 22 July 2011

PostgreSQL для чайника. Slackware 12.0

sskirtochenko — Posted on 15 Апрель 2008

PostgreSQL — свободная открытая СУБД.
Сайт — http://www.postgresql.org/
Википедия — http://ru.wikipedia.org/wiki/PostgreSQL

1. Запуск PostgreSQL в Slackware 12.0

Под root’ом набираем в консоли:

**#/etc/rc.d/rc.postgresql start**

Если база данных не существует в консоли выводится сообщение:

psql: could not connect to server: В соединении отказано
Is the server running locally and accepting
connections on Unix domain socket «/tmp/.s.PGSQL.5432»?

Если базы данных не существует, её необходимо создать.
Запускаем в консоли, например, так:

**initdb -D —/home/postgres/data**

А затем запустить:

**/usr/bin/pg_ctl start -D /home/postgres/data**

2. Создаём пользователя postgres
3. Заходим под ним в консоли

**#su postgres**

4. Смотрим список баз данных

**$ psql -l**
List of databases
Name | Owner | Encoding
————+———-+————
postdb | postgres | SQL_ASCII
postgres | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(4 rows)

Пояснение psql — утилита для работы с базами данных PostgreSQL

5. Попробуем зайти в одну из них

**$ psql postdb**
Welcome to psql 8.3.0, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postdb=#

6. Список таблиц в открытой базе можно посмотреть так:

**postdb=# \dt**
List of relations
Schema | Name | Type | Owner
———+———+——-+———-
public | cities | table | postgres
public | weather | table | postgres
(2 rows)

7. Хотим создать свою таблицу:

postdb=# create table org (kod varchar(80), —код организации
postdb(# name varchar(80) —название организации);

8. Добавляем записи в таблицу:
8.1. Добавляем записи из консоли:

**postdb=# INSERT INTO org VALUES** **(‘КодОрганизация1′,’ИмяОрганизация1’);**

8.2. Добавялем записи из файла

**postdb=# copy org (kod, name) from ‘/media/TEMP/2’;**
**COPY 2**

Примечание:

содержимое файла /media/TEMP/2 таково:

**$ cat /media/TEMP/2**
КодОрганизация1 ИмяОрганизация1
КодОрганизация2 ИмяОрганизация2

где между, например, КодОрганизация1 ИмяОрганизация1
стоит символ табуляции.

Посмотреть содержимое таблицы org можно так:

**postdb=# select * from org;**
kod | name
——————————-+——————————-
КодОрганизация1 | ИмяОрганизация1
КодОрганизация1 | ИмяОрганизация1
КодОрганизация2 | ИмяОрганизация2
(3 rows)

9. Очистить таблицу можно
9.1. так:

**postdb=# delete from org *;**
**DELETE 3**

9.2. или так:

**postdb=# Truncate org;**
**TRUNCATE TABLE**

10. Добавить колонку в таблицу можно так:

**postdb=# ALTER TABLE org ADD COLUMN okpo varchar(80);**
**ALTER TABLE**

Смотрим таблицу:

postdb=# select * from org;
kod | name | okpo
——+——+——
(0 rows)

11. Удалить колонку можно так:

**postdb=# ALTER TABLE org DROP COLUMN okpo;**
**ALTER TABLE**

12. Переименовать колонку можно так:

**postdb=# ALTER TABLE org RENAME COLUMN okpo TO okpo1;**
**ALTER TABLE**

Смотрим, что получилось:

postdb=# select * from org;
kod | name | okpo1
——+——+——
(0 rows)

13. Изменить тип колонки можно так:
**postdb=# ALTER TABLE org ALTER COLUMN name TYPE varchar(250);**

Замечание:
при попытке залить в базу данные в кодировке UTF8 я не учел большего размера данных в этой кодировке (по крайней мере в 2 раза мне пришлось увеличить размер поля).

Прикрутил гуёвину pgAdmin III. При установке на Слакваре 12.0 ругался на отсутствие либ. Помогла установка пакетов wxgtk-2.8.7-i486-2kjz.tgz и wxwidgets-2.8.7-i486-1gds.tgz (спасибо Podosinnikovу Leonidу).

Главная » soft, programming » SQL, PostgreSQL
SQL, PostgreSQL
Posted on Декабрь 13, 2012 by kdiv

SQL Server BI development questions

What are the main types of indexes in SQL Server 2005?

What is a common table expression?

What is Service Broker used for?

When is a ‘group by’ statement needed in a SQL Query? What is a ‘having’ statement used for in SQL?

What is the performance impact of putting a function in a where clause?

===============================================================
SELECT *
FROM table table_alias

JOIN table2 table2_alias
ON table2_alias.some_field_id = table_alias.corresponding_field_id
WHERE table_alias.field = value
AND table_alias.some_field = value;
===============================================================
UPDATE table
SET field = value, field2 = value2
WHERE field_id = value AND field = value;
===============================================================
PL_SQL Scripting example:

<code>SET serveroutput ON
DECLARE
updatedRows number;
BEGIN

dbms_output.enable;
dbms_output.put_line(‘ > Исправление записи 1, 2, 3′);

dbms_output.put_line(‘ > Исправление записи 1 ‘);
update
rki_request_info
set modification_date = to_date(‘02.08.2012′,’dd.mm.yyyy’),
registration_date = to_date(‘02.08.2012′,’dd.mm.yyyy’)
where req_number = ‘1’;
updatedRows := sql%rowcount;
dbms_output.put_line(‘ >>> Исправлено: ‘ || to_char(updatedRows) || ‘ строк’);
dbms_output.put_line(‘ > Исправление документа 2 ‘);
update
rki_request_info
set modification_date = to_date(‘02.08.2012′,’dd.mm.yyyy’),
registration_date = to_date(‘02.08.2012′,’dd.mm.yyyy’)
where req_number = ‘1’;

updatedRows := sql%rowcount;
dbms_output.put_line(‘ >>> Исправлено: ‘ || to_char(updatedRows) || ‘ строк’);

dbms_output.put_line(‘ > Исправление запроса 01-12-69270 ‘);
update
rki_request_info
set modification_date = to_date(‘02.08.2012′,’dd.mm.yyyy’),
registration_date = to_date(‘02.08.2012′,’dd.mm.yyyy’)
where req_number = ‘2’;
updatedRows := sql%rowcount;
dbms_output.put_line(‘ >>> Исправлено: ‘ || to_char(updatedRows) || ‘ строк’);

dbms_output.put_line(‘ > Исправление важного документа 3 ‘);
update
rki_request_info
set modification_date = to_date(‘02.08.2012′,’dd.mm.yyyy’),
registration_date = to_date(‘02.08.2012′,’dd.mm.yyyy’)
where req_number = ‘3’;

updatedRows := sql%rowcount;
dbms_output.put_line(‘ >>> Исправлено: ‘ || to_char(updatedRows) || ‘ строк’);

— fix change info value
update rki_change_info
set new_value = ‘02.08.2012’
where id = 59103;

updatedRows := sql%rowcount;
dbms_output.put_line(‘ >>> Исправлено в истории изменений: ‘ || to_char(updatedRows) || ‘ строк’);

— set modif date for change info
update rki_change_description
set modification_date = to_date(‘02.08.2012′,’dd.mm.yyyy’)
where id in
(select
descr.id
from rki_engineer eng
join rki_change_info info on info.engineer_id = eng.id
join rki_change_description descr on descr.id = info.description_id
where eng.reg_number = ‘14996’ and descr.modification_date = to_date(‘03.08.2012′,’dd.mm.yyyy’)
);

updatedRows := sql%rowcount;
dbms_output.put_line(‘ >>> Исправлено в истории комментариев: ‘ || to_char(updatedRows) || ‘ строк’);

dbms_output.put_line(‘ > Исправление даты в документе 4′);
—set cancel date
update rki_certificate_history
set cancel_date = to_date(‘02.08.2012′,’dd.mm.yyyy’)
where id_number = ‘4’ and is_valid = 0;

updatedRows := sql%rowcount;
dbms_output.put_line(‘ >>> Исправлено: ‘ || to_char(updatedRows) || ‘ строк’);

COMMIT;

dbms_output.put_line(‘ Changes committed successfully.’);
dbms_output.put_line(‘Have a nice day! :)’);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Finished with ERRORS.’);
dbms_output.put_line(‘ > Rolling back all changes…’);

ROLLBACK;

dbms_output.put_line(‘ All changes rolled back.’);
dbms_output.put_line(‘Error stack:’);
DBMS_OUTPUT.put_line (‘ ‘ || DBMS_UTILITY.FORMAT_ERROR_STACK);
END;</code>============================================================================================= Add User
user $ psql -U postgres -d postgres

psql (9.1.1)
Type «help» for help.

postgres=# CREATE ROLE username WITH LOGIN;
CREATE ROLE
postgres=# \password username
Enter new password:
Enter it again:
postgres=# CREATE DATABASE testdb WITH OWNER username; — username has all privileges on testdb
CREATE DATABASE
postgres=# GRANT CONNECT ON DATABASE otherdb TO username; — username can now connect to otherdb
GRANT
postgres=# \c otherdb
You are now connected to database «otherdb» as user «postgres».
otherdb=# GRANT SELECT ON test TO username; — username can now query (SELECT statements) the test table on otherdb.
GRANT

See Chapter 20. Database Roles of the official PostgreSQL documentation for more on role management.

See the PostgreSQL documentation for more on GRANT and REVOKE,
Changing the default encoding of new databases to UTF-8

When creating a new database (e.g. with createdb mydb) PostgreSQL actually copies a template database. There are two predefined templates: template0 is vanilla, while template1 is meant as an on-site template changeable by the administrator and is used by default. In order to change the default encoding of new databases, one of the options is to change on-site template1. To do this, log into PostgresSQL shell (psql) and execute the following:

1. First we need to drop template1. As templates cannot be dropped, we first need to change it to an ordinary database:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = ‘template1′;

2. After that, it is possible to drop it:

DROP DATABASE template1;

3. The next step is to create a new database from template0 with a new default encoding. (Gotcha: In PostgreSQL, Unicode is synonymous with UTF-8.)

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = ‘UNICODE’;

4. Now we need to change template1 back to the template:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = ‘template1′;

5. (OPTIONAL) If you do not want anyone connecting to this template, set datallowconn to FALSE:

UPDATE pg_database SET datallowconn = FALSE WHERE datname = ‘template1′;

Now you can create a new database by running from regular shell:
user $ createdb -U postgres testdb

If you log in back to psql and check the databases, you should see the proper encoding of your new database:
user $ psql -U postgres -d postgres

psql (9.1.1)
Type «help» for help.

postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
———-+———-+————+————+——-+———————-
testdb | postgres | UTF8 | C | C |
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | C | C |
——————————————————————
сброс пароля!
sudo -u postgres psql -c «ALTER USER postgres PASSWORD ‘postgres’;»—————————————————————— сброс пароля! sudo -u postgres psql -c «ALTER USER postgres PASSWORD ‘postgres’;»
================================================================================================
$ sudo -u postgres psql

postgres=# CREATE DATABASE mine_database;
CREATE DATABASE
postgres=# CREATE USER mine_user WITH password ‘qwerty’;
CREATE ROLE
postgres=# GRANT ALL privileges ON DATABASE mine_database TO mine_user;
GRANT
postgres=# \q

touch /var/lib/postgresql/.psql_history
touch /home/mine/.psql_history

$ psql -h localhost mine_database mine_user
Password for user mine_user:
psql (9.3.5)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type «help» for help.

mine_database=>

Теперь попробуем поработать с созданной базой данных от имени test_user:
psql -h localhost test_database test_user

Создадим новую таблицу:
test_database=> CREATE SEQUENCE user_ids;
CREATE SEQUENCE

test_database=> CREATE TABLE users (id INTEGER PRIMARY KEY DEFAULT NEXTVAL(‘user_ids’), login CHAR(64), password CHAR(64));
NOTICE: CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX «users_pkey» FOR TABLE «users»
CREATE TABLE

Обратите внимание, что в отличие от некоторых других СУБД, в PostgreSQL нет столбцов со свойством auto_increment. Вместо этого в постгресе используются последовательности (sequences). На данный момент достаточно знать, что с помощью функции nextval мы можем получать уникальные числа для заданной последовательности:
test_database=> SELECT NEXTVAL(‘user_ids’);
NEXTVAL
———
1
(1 ROW)

test_database=> SELECT NEXTVAL(‘user_ids’);
NEXTVAL
———
2
(1 ROW)

Прописав в качестве значения по умолчанию для поля id таблицы users значение NEXTVAL(‘user_ids’), мы добились того же эффекта, что дает auto_increment. При добавлении новых записей в таблицу мы можем не указывать id, потому что уникальный id будет сгенерирован автоматически. Несколько таблиц могут использовать одну и ту же последовательность. Таким образом мы сможем гарантировать, что значения некоторых полей у этих таблиц не пересекаются. В этом смысле последовательности более гибки, чем auto_increment.

Точно такую же таблицу можно создать и при помощи всего лишь одной команды:
test_database=> CREATE TABLE users2 (id SERIAL PRIMARY KEY, login CHAR(64), password CHAR(64));
NOTICE: CREATE TABLE will CREATE implicit SEQUENCE «users2_id_seq» FOR serial COLUMN «users2.id»
NOTICE: CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX «users2_pkey» FOR TABLE «users2»
CREATE TABLE

Как видите, последовательность для поля id была создана автоматически.

Теперь с помощью команды \d можно ознакомиться со списком всех доступных таблиц, а с помощью \d users — увидеть описание таблицы users. Если вы не получили интересующую вас информацию, попробуйте \d+ вместо \d. Для отображения справки по командам скажите \h.

Важно отметить, что в PostgreSQL по умолчанию имена таблиц и столбцов приводятся к нижнему регистру. Если это поведение нежелательно, можно воспользоваться двойными кавычками:
test_database=> CREATE TABLE «anotherTable» («someValue» VARCHAR(64));
CREATE TABLE

test_database=> SELECT * FROM anotherTable;
ERROR: relation «anothertable» does NOT exist
LINE 1: SELECT * FROM anotherTable;
^

test_database=> SELECT * FROM «anotherTable»;
someValue
————
(0 ROWS)

В остальном работа с PostgreSQL мало чем отличается от работы с любой другой реляционной СУБД:
test_database=> INSERT INTO users (login, password) VALUES (‘afiskon’, ‘123456’);
INSERT 0 1

test_database=> SELECT * FROM users;

Если сейчас вы попытаетесь подключиться к постгресу с другой машины, то потерпите неудачу:
psql -h 192.168.0.1 test_database test_user

psql: could not connect to server: Connection refused
Is the server running on host «192.168.0.1» and accepting
TCP/IP connections on port 5432?

Чтобы исправить это, добавьте строку:
listen_addresses = ‘localhost,192.168.0.1’

… в файл /etc/postgresql/9.2/main/postgresql.conf, а также:
host all all 192.168.0.1/16 md5

… в файл /etc/postgresql/9.2/main/pg_hba.conf и скажите:
sudo service postgresql restart

Теперь все должно работать.

Резервное копирование в PostgreSQL выглядит примерно так:
pg_dump -c -h 192.168.0.1 -U test_user test_database > ./dump.sql

Если у вас большая база данных, обратите также внимание на поддержку утилитой pg_dump флага -Fc.

Восстановление из резервной копии:
cat dump.sql | psql -h 192.168.0.1 test_database test_user

Во время создания резервной копии вы можете получить ошибку вроде такой:
pg_dump: server version: 9.2.4; pg_dump version: 9.1.9
pg_dump: aborting because of server version mismatch

Насколько мне известно, единственное нормальное решение этой проблемы — честно держать всюду одну и ту же версию PostgreSQL.

Учтите, что настройки PostgreSQL по умолчанию предполагают, что вы пытаетесь запустить его на микроволновке. Перед использованием PostgreSQL в боевых условиях эти настройки обязательно нужно изменить под ваше железо и ваше приложение.
====================================================================
$pg_lsclusters
=====================================================================
Oracle version

show parameter compatible;

select * from v$version;

Реклама

Добавить комментарий

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

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s