от ILuxWiki

Направо към: навигация, търсене

Реших да си поиграя по-сериозно с PostgreSQL, тук има някои записки свързани с елементарни действия при работата с този вид SQL.

думички: postgre, postgres, postgresql, sql, Pg

Съдържание

1 Сравнителна таблица между команди на mySQL и PostgreSQL
2 инсталиране на библиотека за работа с Perl
3 Добавяне на потребител
4 Създаване на функция
5 Спиране на pager(досадното --more--) в psql
6 Промяна, добавяне на коментар към таблица
7 Изтриване на дублирани записи
8 Работа с времеви стойности
- 8.1 unixtime
9 Разни хитринки за манипулиране на SELECT резултатите
- 9.1 case
- 9.2 coalesce
10 WHERE умнотийки
- 10.1 CAST и REGEXP търсене
11 Tрикове
12 Препратки

Сравнителна таблица между команди на mySQL и PostgreSQL

описание	mySQL	PostgreSQL
списък с наличните бази	show databases;	\l
списък с таблиците	show tables;	\d
избор на database	use database	\c database

инсталиране на библиотека за работа с Perl

 pkg_add -r p5-DBD-Pg

източник: http://www.freebsddiary.org/postgresql-perl.php

Добавяне на потребител

към pg_hba.conf се добавя следния ред:

 host    all         all         10.210.0.211/32       md5

 # /usr/local/etc/rc.d/postgresql reload

 # su - pgsql 

 $ psql template1
 ...
 template1=# create user username with password 'somesecret';
 template1=# grant all privileges on database databasename to username;

Създаване на функция

 create or replace function ip2net(i inet, mask text)  returns text as $$
   declare tst text;
   begin
     select network( inet(host(i) || mask) ) into tst;
     return tst;
   end;
 $$ language plpgsql;

източник: http://www.postgresql.org/docs/9.0/static/sql-createfunction.html

Спиране на pager(досадното --more--) в psql

 netflow=> \pset pager
 Pager usage is off.
 netflow=> \pset pager
 Pager is used for long output.

Промяна, добавяне на коментар към таблица

Ако сте пропуснали да добавите comment на таблицата за да имате информация когато изпълните \dt+, ето как става това

 netflow=> \dt+
                                  List of relations
  Schema |            Name             | Type  | Owner |    Size    |    Description    
 --------+-----------------------------+-------+-------+------------+-------------------
  public | d_net                       | table | root  | 96 kB      | 
  public | dump                        | table | root  | 578 MB     | 
  public | s_net                       | table | root  | 8192 bytes | 
  public | servers                     | table | root  | 8192 bytes | 
  public | services                    | table | root  | 152 kB     | 
  public | source_dest_known_ports     | table | root  | 472 kB     | 
  public | source_dest_known_ports_old | table | root  | 2032 kB    | 
  public | source_dest_port            | table | root  | 1736 kB    | 
  public | subnets                     | table | root  | 24 kB      | from subnets.xlsx

 >> comment on table subnets is 'from subnets.xlsx';

Изтриване на дублирани записи

 DELETE
 FROM 	sometable
 WHERE 	someuniquekey NOT IN
       (SELECT 	MAX(dup.someuniquekey)
       FROM   		sometable As dup
       GROUP BY 	dup.dupcolumn1, dup.dupcolumn2, dup.dupcolum3)

източник: http://www.postgresonline.com/journal/archives/22-Deleting-Duplicate-Records-in-a-Table.html

Работа с времеви стойности

unixtime

 SELECT EXTRACT('epoch' FROM NOW()) as unixtime;

Разни хитринки за манипулиране на SELECT резултатите

източник: http://www.postgresql.org/docs/7.4/static/functions-conditional.html

case

 SELECT a,
      CASE WHEN a=1 THEN 'one'
           WHEN a=2 THEN 'two'
           ELSE 'other'
      END
  FROM test;

coalesce

 SELECT COALESCE(description, short_description, '(none)') ...

WHERE умнотийки

CAST и REGEXP търсене

 netflow=> \d ivo
     Table "public.ivo"
  Column | Type | Modifiers 
 --------+------+-----------
  ip     | inet |

 netflow=> select * from ivo where (ip)::text ~ '128.3';
       ip       
 ---------------
  172.16.128.30

Tрикове

Създаване на архив и на functions

 pg_dump -s -Fc > db.schema

А така си записване функциите в sql файлче:

 pg_restore -l db.schema | grep FUNCTION > functionlist
 pg_restore -L functionlist db.schema > functions.sql

Измерване времето за отговор при заявка

Първо го стартираме с командата "\timing"

 cheaterz=# \timing

След, като бъде изпълнена вече каквато и да е заявка, ще Ви изкара времето за което е минала процедурата.

 cheaterz=# SELECT count (*) hackers;
 hackers 
 Time: 24.747 ms

Извличане на записите в cvs файл

\copy (select * from tablename) to  '/tmp/filename.csv' with csv header;

Препратки

http://en.wikibooks.org/wiki/Programming:Converting_MySQL_to_PostgreSQL

PostgreSQL