Sprawdzanie permutacji ciągu znaków w PostgreSQL

Permutacja w języku matematycznym to „wzajemnie jednoznaczne przekształcenie pewnego zbioru na siebie”. Używając języka mniej technicznego permutacje, na przykładzie liter w słowie, to wszystkie możliwe ustawienia literek w słowie, zatem dla słowa „marcin” permutacjami będą: „amrcin”, „mracin”, „mrcain” etc… w słowie 6 znakowym będzie ich łącznie 6!.

Problem

W bazie danych mamy pole tekstowe z zapisanymi pewnymi ciągami znaków, chcemy sprawdzić te dane pod kątem występowania w nich permutacji pewnego stringu. Czyli mając w danych wejściowych np. „mrcain” chcemy sprawdzić czy w bazie nie ma „marcin” lub innej permutacji. Czytaj dalej „Sprawdzanie permutacji ciągu znaków w PostgreSQL”

UPDATE z ORDER BY w jednym zapytaniu na przykładzie orderingu danych

Wstęp

Mamy przykładową tabelę z userami:

DROP TABLE users;
CREATE TABLE users(
	id SERIAL PRIMARY KEY,
	name VARCHAR(50),
	created TIMESTAMP DEFAULT NOW()
);

INSERT INTO users(name) VALUES('Marcin');
INSERT INTO users(name) VALUES('Bartek');
INSERT INTO users(name) VALUES('Agnieszka');
INSERT INTO users(name) VALUES('Dominika');

Na tabeli wykonywane były wielokrotnie operacje INSERT/UPDATE/DELETE przykładowo:

UPDATE users SET name = 'Ania' WHERE name = 'Bartek';
UPDATE users SET name = 'Dalia' WHERE name = 'Marcin';
DELETE FROM users WHERE name = 'Ania';

Zatem SELECT bez orderu wyświetli nam dane posortowane względem kolejności modyfikacji:

SELECT * FROM users;

psql=> select * from users;
 id |   name    |          created
----+-----------+----------------------------
  3 | Agnieszka | 2009-12-19 10:41:49.324851
  4 | Dominika  | 2009-12-19 10:41:49.958792
  1 | Dalia     | 2009-12-19 10:41:49.297223
(3 rows)

W interface aplikacji chcemy oprogramować własną obsługę kolejności rekordów. Dodajemy zatem kolumnę, która będzie obsługiwać tą funkcjonalność.

ALTER TABLE users ADD COLUMN order_id INTEGER;

Problem

Musimy teraz uzupełnić kolumne order_id o poprawne wartości. Kolejność orderu danych, które znajdują się w bazie powinna być kolejności wprowadzania tych danych do tabeli (czyli zgodna z kolejnością pola id).

Aby zrealizować poprawne uzupełnienie orderu tworzymy pomocniczą sekwencję, która wykorzystamy do wygenerowania kolejnych liczb do orderu:

DROP SEQUENCE order_id_seq;
CREATE SEQUENCE order_id_seq;

Wykonujemy oczywiste zapytanie przypisujące rekordom z tabeli users kolejne wartości sekwencji:

UPDATE users SET order_id = NEXTVAL('order_id_seq');  

Niestety przez to, że dane nie były ustawione w dobrej kolejności ORDER ustawiony jest niepoprawnie.

psql=> SELECT * FROM users;
 id |   name    |          created           | order_id
----+-----------+----------------------------+----------
  3 | Agnieszka | 2009-12-19 10:41:49.324851 |        1
  4 | Dominika  | 2009-12-19 10:41:49.958792 |        2
  1 | Dalia     | 2009-12-19 10:41:49.297223 |        4
(3 rows)

Rozwiązanie

Aby UPDATE poprawnie ustawił kolejność orderu należy nieco zmodyfikować zapytanie i nadać danym z tabeli users odpowiednią kolejność.

W tym celu tworzymy podzapytanie sorted_ids, w którym poprzez ORDER BY ustalamy kolejność rekordów, które następnie w zapytaniu nadrzędnym zostaną zmodyfikowane właśnie w tej kolejności.

UPDATE users
	SET order_id = correct_order_id
FROM
	(
	SELECT 
		id, nextval('order_id_seq') as correct_order_id
	FROM
		users
  	ORDER BY 
		id ASC
	) as sorted_ids
WHERE
	users.id = sorted_ids.id; 

select * from users order by order_id;

Jak widać dane zostały pięknie zorderowane:

psql=> select * from users order by order_id;
 id |   name    |          created           | order_id
----+-----------+----------------------------+----------
  1 | Dalia     | 2009-12-19 10:41:49.297223 |        1
  3 | Agnieszka | 2009-12-19 10:41:49.324851 |        2
  4 | Dominika  | 2009-12-19 10:41:49.958792 |        3
(3 rows)

Przy okazji wspomnę, że w aplikacji łatwiej obsłużyć ordering odwrotny, tzn. wiersze, które posiadają największą wartość order_id są na czele a nie na końcu listy. Przy takim podejściu przy wstawianiu nowego rekordu pole order_id powinno mieć wartość MAX(order_id) + 1. Dla orderingu odwrotnego należy zmienić w podzapytaniu kolejność sortowania z id ASC na id DESC.

Należy także dbać o to, aby przy usunięciu rekordu prenumerować order_id wszystkich rekordów, które znajdują się 'nad nim’ o -1.

Tani dobry hosting w USA – HostMonster

O tym, że nie ma w Polsce ofert hostingowych o przyzwoitym stosunku cena do możliwości, chyba nie muszę nikogo przekonywać.

Osobiście korzystam z usług dzielonego hostingu na NetArt i jestem względnie zadowolony, jednak limity typu 10gb pojemności konta czy brak dostępu przez ssh są dosyć irytujące.

Kiedyś przez 2 lata korzystałem z usług DreamHost.com, jednak 2-letni promocyjny okres się skończył a dosyć wysoka awaryjność ich serwerów (tempest, gambino) skutecznie zniechęciła mnie do przedłużania umowy.

Po niedługim czasie od wyprowadzki z DreamHost’a zatęskniłem za ssh i parametrami „Unlimited”, zacząłem mały research i moją uwagę przykuł mało znany w Polsce HostMonster.

Serwis hostingowy z typu „amerykańskich gigantów” za cenę $5,95 miesięcznie oferuje naprawdę dużo. Na HM user ma gwarantowane max. 64mb pamięci RAM i 30% mocy procesora. Mi osobiście szczególnie spodobała się obsługa baz danych PostgreSQL, gdyż jest to SZBD, który najbardziej przypadł mi do gustu i głównie na nim opieram swoje aplikacje. (Dreamhost jak i wiele topowych firm hostingowych oferuje jedynie MySQL.)

Muszę przyznać, że HostMonster oferuje nieco mniej fajerwerków niż DreamHost chociażby limit na instalacje niektórych programów unixowych, brak SVN i pewnie jeszcze kilka niekoniecznie ważnych ficzerów. Brak tych wszystkich funkcjonalności rekompensuje stabilność działania serwera a to przecież jest najważniejsze.

Po około trzech miesiącach użytkowania jestem bardzo zadowolony z ich usług, wszystko działa należycie, nie było żadnych padów a support szybciutko odpowiadał na pytania.

Zatem polecam HostMonster jako tani dzielony hosting dla serwisów nie wymagających mega małych pingów. Abonament za hosting można obniżyć do bajecznie niskich $3,95 na miesiąc korzystając z wielu dostępnych w necie HostMonster coupon codes. Za max. 150zł rocznie mocny, stabilny hosting z większością parametrów „unlimited” i dostępem ssh… dla mnie rewelacja.


Aktualny czas a transakcja w PostgreSQL

Ostatnio miałem ciekawy case’ik – musiałem w obrębie jednej transakcji zmienić dane rekordów. Wszystko odbywało się w kilku funkcjach plpgsql, funkcje mogły się wywoływać rekurencyjnie w triggerach a zmiany dotyczyły między innymi pól typu TIMESTAMP. Wartości pól TIMESTAMP rekordów były zmieniane i ich zmiany automatycznie wpływały na w działanie skryptu.

Jakież było moje zdziwienie gdy skrypt nie chciał działać, a wszystkie rekordy miały taką zamą wartość pola typu TIMESTAMP. Otóż okazało się, że po rozpoczęciu transakcji funkcje zwracające aktualny czas:

SELECT CURRENT_TIME;
SELECT CURRENT_TIMESTAMP;
SELECT NOW();

przestały zwracać aktualny czas a zamiast tego czas rozpoczęcia transakcji.

I o dziwo jest to rzeczywiście poprawne działanie. Na stronie http://www.postgresql.org/docs/8.3/static/functions-datetime.html można przeczytać:

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the „current” time, so that multiple modifications within the same transaction bear the same time stamp.

Zatem wyjściem z tego problemu jest użycie postgres’owej funkcji timeofday() i zrzutowanie jej wyniku na TIMESTAMP, realizyje to zapytanie:

SELECT cast( timeofday() AS timestamp);

Edit: zamiast timeofday() można uzyć funkcji clock_timestamp(), która zwraca date w postaci TIMESTAMP’a

SELECT clock_timestamp();

Dla ciekawości sprawdziłem jak zachowują się inne dialekty SQL i okazało się, że zarówno MySQL jak i MSSQL realizują tą sytuację odmniennie niż PostgreSQL, zatem:

  • MySQL – w transakcji zwracany jest bieżący czas (nie czas rozpoczęcia transakcji)
  • MSSQL – w transakcji zwracany jest bieżący czas (nie czas rozpoczęcia transakcji)
  • PgSQL – w transakcji zwracany jest czas rozpoczęcia transakcji

Postgres 8.3 a zgodność typów danych

Około rok temu została wypuszczona wersja 8.3 systemu bazodanowego postgreSQL, teraz po roku nadszedł wreszcie czas kiedy to firmy hostingowe zaczynają upgrate’ować bazy do tej wersji.

W wersji 8.3 zostało prowadzonych wiele przydatnych funkcjonalności jak chociażby wyszukiwanie pełnotekstowe TSearch2, jednak nie o tym miałem pisać…

Oprócz dodatkowych funkcjonalności w postgres 8.3 została wprowadzona kontrola zgodności typów danych. Po przejściu providera hostingu z pg 8.x.x na pg 8.3.x istnieje realne zagrożenie błędnego działania naszych aplikacji w przypadku gdy nie zachowywaliśmy zgodości typów danych.

W postgreSQL operatory (np. =, > etc..) i funkcje (np. substr(), regexp_replace() etc…) posiadaja zadeklarowane typy danych na których działają np. INT=INT, substr(TEXT, INT, INT).

Problem

W wersjach przed postgreSQL 8.3 jeżeli operator lub funkcja, która przyjmowała wartości tekstowe została wywołana z wartościami o typie nie tekstowym to wartość parametru był automatycznie przekształcania do typu tekstowego TEXT. Od wersji postgreSQL 8.3 automatyczna konwersja typów została zlikwidowana gdyż jej idea nie jest zgodna z poprawnymi zasadami programowania. To my programiści powinniśmy kontrolować przepływ, wartości i typy danych. Pozostawiona została jedynie automatyczna konwersja w obrębie typów tekstowych (CHAR(X), VARCHAR(X) są CAST’owane do TEXT).

Zatem w najnowszej wersji postgreSQL zapytanie:

SELECT substr(id, 1, 1 ) FROM ...;

gdzie id jest typu integer zwróci błąd „function does not exist” gdyż nie ma wbudowanej funkcji substr(INT, INT, INT)

Analogiczny błąd zwróci operator =

SELECT ... WHERE id = foo;

gdzie id jest typu INTEGER, a foo typu CHAR zwróci błąd „operator does not exist” gdyż nie ma wbudowanego operatora INT = CHAR.

Rozwiązanie

Możliwe są trzy rozwiązania:

  1. Poprawimy zły typ kolumn, które powodują błędy
  2. W każdym zapytaniu zrzutujemy zmienną na dobry typ
  3. Napiszemy własne funkcje, operatory obsługujące te niestandardowe operacje.

1. Poprawa typu kolumny

Możemy zmodyfikować typ kolumny np. z CHAR na INT i wtedy zapytanie wykorzystujące operator INT = INT zadziała poprawnie.

ALTER TABLE sample ALTER COLUMN foo TYPE INTEGER;

Jednak operacja ta nie zawsze może być możliwa. W przypadku źle zaaprojektowanej aplikacji, konwersja taka może okazać się niemożliwa. Co w wypadku gdy pole 'foo’ zawiera ciąg liczb zaczynających się od zera? Przed konwersją przykładowy ciąg może wyglądać '0012388′ po konwersji na typ liczbowy ayutomatycznie zmieni się na '12388′ gdyż INTEGER nie może posiadać zer na przedzie. Ta zmiana może spododować, że kolejne zapytania pomimo prawidłowej składni syntaktycznej zwrócą nieprawidłowe wyniki, pozatym możemy stracić w bazie pewne informacje (zera na przedzie).

2. Castowanie typów w zapytaniu

Gdy zmiana typu kolumny jest niemożliwa pozostaje nam dostosować naszą aplikację do wymagań postgresa. W zapytaniu możemy zrzutować wartość zmiennej na żądany typ

SELECT ... WHERE id = foo::integer;

lub odpowiednik

SELECT ... WHERE id = CAST(foo AS integer);

Minusem takiego rozwiązania jest to, że kolumne 'foo’ należy zrzutować w każdym zapytaniu.

3. Własna funkcje lub operator

Zamiast poprawiać wszystkie zapytania, możemy napisać funkcje/operator który obsłuży żądaną operację.

Tworzenie operatora składa się z dwóch etapów: stworzenie funkcji w języku proceduralnym pl/pgSQL, która obsługuje operację i definicję samego operatora.

Funkcja INTEGER = CHAR

CREATE OR REPLACE FUNCTION equal_integer_vs_char(integer, char) RETURNS bool AS $$
DECLARE
    left_arg ALIAS FOR $1;
    right_arg ALIAS FOR $2;
BEGIN
    IF left_arg = CAST( right_arg AS INTEGER ) THEN
        RETURN true;
    ELSE
        RETURN false;
    END IF;
END;
$$ LANGUAGE plpgsql;

Definicja operatora INTEGER = CHAR, przeczytaj także w manualu.

CREATE OPERATOR = (
    LEFTARG = integer ,
    RIGHTARG = char,
    PROCEDURE = equal_integer_vs_char
);

W przypadku potrzeby posiadania funkcji substr(INT, INT, INT) musimy zdefiniować funkcję przyjmującą odpowiednie parametry.

CREATE OR REPLACE FUNCTION substr(integer, integer, integer) RETURNS text AS $$
DECLARE
       ...
BEGIN
         ...

        RETURN foo;
END;
$$ LANGUAGE plpgsql;

Sortowanie względem pola nie będącego w DISTINCT ON

Problem:
Chcemy wyświetlić jedynie unikalne rekordy względem konkretnego pola/pól i posortować innym polu. W naszym przykładzie chcemy otrzymać zestawienie id_user – ostatnie logowanie

Posiadane dane:
Tabela z datami logowań użytkowników aplikacji w tabeli postaci:

CREATE TABLE logs(
id SERIAL PRIMARY KEY, -- unikalny klucz główny
id_user INTEGER, -- klucz obcy tabeli użytkowników
date TIMESTAMP -- data logowania
);

Przykładowe rekordy:

INSERT INTO logs(id_user,date) VALUES(1,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(1,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(4,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(2,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(3,'2008-04-01');
INSERT INTO logs(id_user,date) VALUES(7,'2008-04-01');
INSERT INTO logs(id_user,date) VALUES(1,'2008-05-01');
INSERT INTO logs(id_user,date) VALUES(4,'2008-05-01');
INSERT INTO logs(id_user,date) VALUES(5,'2008-06-01');
INSERT INTO logs(id_user,date) VALUES(5,'2008-08-01');
INSERT INTO logs(id_user,date) VALUES(7,'2008-10-01');

Błędne zapytanie nr 1:
W pierwszym momencie wielu początkujących programistów pomyśli o zapytaniu:

SELECT
 DISTINCT ON (id_user)
 *
FROM
 logs
ORDER BY
 date DESC;

Niestety nie jest to poprawne zapytanie, postgres wyrzuci błąd:

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Oznacza to, że pola, które zawiera klauzula ORDER BY muszą się znaleźć na początku klauzuli DISTINCT ON.
W tym przypadku poprawne zapytanie musiało by zawierać DISTINCT ON (date, id_user).
Niestety wtedy nie dostaniemy pożądanego rezultatu (unikalność po kolumnie id_user) gdyż badana będzie
unikalność względem dwóch pól.

Błędne zapytanie nr 2:

SELECT
 *
FROM
 (SELECT
  DISTINCT ON (id_user)
  *
 FROM
  logs
) AS sub
ORDER BY
 sub.date DESC;

Idąc tropem unikalności można użyć podzapytania, które zwraca unikalne względem id_user rekordy a dopiero później, zwrócone przez nie rekordy posortować. Powyższe zapytanie wykona się i zwróci pewne rezultaty.

id id_user date
11 7 2008-10-01 00:00:00
9 5 2008-06-01 00:00:00
8 4 2008-05-01 00:00:00
5 3 2008-04-01 00:00:00
2 1 2008-01-01 00:00:00
4 2 2008-01-01 00:00:00

Niestety nie będą one do końca poprawne gdyż podzapytanie DISTINCT pozostawia pierwszy napotkany rekord zawierający unikalną wartość id_user. Zatem jeżeli dane nie są posortowane względem id_user i daty chronologicznie to uzyskamy wyniki nieprawidłowe. W naszym przypadku id_user=1 została zwrócona data=2008-01-01 gdyż jest ona bliżej początku niż rekord z datą=2008-05-01.

Rozwiązanie:

SELECT
 *
FROM
 (SELECT
  DISTINCT ON (sub2.id_user)
  sub2.*
 FROM
  (SELECT
  *
  FROM
  logs
  ORDER BY
  id_user ASC,
  date DESC
  ) AS sub2
) AS sub
ORDER BY
 sub.date DESC;

W podzapytaniu sub2 sortujemy dane po id_user i date malejąco tak aby każdy id_user na czele miał rekord z datą najpóźniejszą. Takie dane przekazujemy zapytaniu wcześniejszemu, które najpierw wyciąga rekordy unikalne względem kolumny id_user a następnie tak ograniczony zbiór sortuje względem kolumny date. Jako że dostarczyliśmy dane posortowane otrzymane rezultaty są zgodne z prawdą:

id id_user date
11 7 2008-10-01 00:00:00
10 5 2008-08-01 00:00:00
7 1 2008-05-01 00:00:00
8 4 2008-05-01 00:00:00
5 3 2008-04-01 00:00:00
4 2 2008-01-01 00:00:00

Zapytanie grupujące minimalnne wartości w grupie danych

Problem:
Chcemy wyświetlić miesięczne zestawienie liczby nowo zarejestrowanych
użytkowników mając jedynie daty ich logowań.

Posiadane dane:
Tabela z datami logowań użytkowników aplikacji w tabeli postaci:

	CREATE TABLE logs(
		id SERIAL PRIMARY KEY, -- unikalny klucz główny
		id_user INTEGER, -- klucz obcy tabeli użytkowników
		date TIMESTAMP -- data logowania
	);

Przykładowe rekordy:

INSERT INTO logs(id_user,date) VALUES(1,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(1,'2008-01-05');
INSERT INTO logs(id_user,date) VALUES(4,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(2,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(3,'2008-04-01');
INSERT INTO logs(id_user,date) VALUES(7,'2008-04-01');
INSERT INTO logs(id_user,date) VALUES(1,'2008-05-01');
INSERT INTO logs(id_user,date) VALUES(4,'2008-05-01');
INSERT INTO logs(id_user,date) VALUES(5,'2008-06-01');
INSERT INTO logs(id_user,date) VALUES(5,'2008-08-01');
INSERT INTO logs(id_user,date) VALUES(7,'2008-10-01');

Rozwiązanie:
Rejestracje można utożsamić z pierwszymi logowaniami, zatem:
Najpierw grupujemy dane według użytkownika i dla każdego id_user znajdujemy najwcześniejszą datę logowania. Następnie otrzymane dane grupujemy po roku i miesiącu.

Realizuje to podwójnie grupujące zapytanie:

SELECT 
	extract(year from date) || '-' || extract(month from date) AS month, 
	count(*) AS amount 
FROM 
	logs 
WHERE date IN (
	SELECT 
		min(date) 
	FROM 
		logs 
	GROUP BY 
		id_user 
) 
GROUP BY 
	extract(year from date) || '-' || extract(month from date)
ORDER BY
	amount DESC;

Można też ciut lepiej zoptymalizować zapytanie i operować bezpośrednio na wynikach zwróconych
przez podzapytanie:

	
SELECT 
	extract(year from sub.date) || '-' || extract(month from sub.date) AS month, 
	count(*) AS amount 
FROM 
 (
	SELECT 
		min(date) AS date
	FROM 
		logs 
	GROUP BY 
		id_user 
) AS sub
GROUP BY 
	extract(year from sub.date) || '-' || extract(month from sub.date)
ORDER BY
	amount DESC;	

W przypadku gdy zapytanie to będzie wykonywane często warto założyć index na kolumne z datą:

CREATE INDEX date_idx ON logs(date);

Jak usunąć zduplikowane rekody w tabeli bazy danych?

Czasami zdarza się że, poprzez nie do końca poprawną walidację danych wejściowych lub z powodu błędnego działania aplikacji stworzą nam się w bazie danych zduplikowane rekordy.

Takie niepożądane zduplikowane dane w 90% będą nam przeszkadzać i w najlepszym razie mogą powodować niepotrzebny zamęt w aplikacji. Skutki mogą takżę spowodować dużo poważniejsze problemy jak chociażby rozsynchronizowanie się danych w powiązanych tabelach w bazie.

Co należy robić w przypadku wykrycia zduplikowanych rekordów?

W pierwszej kolejności trzeba dojść do tego w jaki sposób powstały a następnie trzeba usprawnić aplikację w taki sposób aby powstawanie duplikatów było już niemożliwe.
Następnie trzeba wziąć się za zapisane dane i należy usunąć z nich nadmiarowe rekordy.

Usuwanie zduplikowanych rekordów

Usuwać duplikaty można na conajmniej kilka sposobów. Najczęstszą radą spotykaną w internecie to przeniesienie unikalnych rekordów do nowej tymczasowej tabeli, skasowanie wszystkich danych ze starej tabeli, a następnie zgranie z powrotem rekordy unikalne.

Niestety nie jest to metoda ani szybka, ani skuteczna ani bezpieczna.

Na prościutkim przykładzie rzedstawię poniżej najlepszy sposób na pozbycie się duplikatów

Tworzymy przykładową tabelę:

CREATE TABLE people(
	id SERIAL PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	surname VARCHAR(100) NOT NULL,
	phone VARCHAR(100) NOT NULL,
	email VARCHAR(100) NOT NULL
);

Tabela zawiera autoinkrementowane pole id, które jest kluczem jej głównym i kilka pól przetrzymujących dane.

Wstawiamy zduplikowane rekordy:

INSERT INTO people(name,surname,phone,email) VALUES('Marcin','Maczka','660111111','marcin@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Marcin','Maczka','660111111','marcin@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Agnieszka','Czopek','550123123','aga@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Jas','Kowalski','555123147','jas@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Jas','Kowalski','555123147','jas@nospam.pl');

Wyświetlamy zawartość tabeli:

SELECT * FROM people;
 id |   name    | surname  |   phone   |      email       
----+-----------+----------+-----------+------------------
  1 | Marcin    | Maczka   | 660111111 | marcin@nospam.pl
  2 | Marcin    | Maczka   | 660111111 | marcin@nospam.pl
  3 | Agnieszka | Czopek   | 550123123 | aga@nospam.pl
  4 | Jas       | Kowalski | 555123147 | jas@nospam.pl
  5 | Jas       | Kowalski | 555123147 | jas@nospam.pl
(5 rows)

Jak widzimy mamy zduplikowane 2 rekordy, pierwszym krokiem do usunięcia rekordów będzie napisanie zapytania, które je wyświetli a następnie
zmienimy SELECT NA DELETE.

SELECT
	id, name, surname, phone, email
FROM
	people
WHERE
	EXISTS (
		SELECT
			NULL
		FROM
			people AS P
		WHERE
			people.name = P.name
			AND people.surname = P.surname
			AND people.phone = P.phone
			AND people.email = P.email
		GROUP BY
			P.name, P.surname, P.phone, P.email
		HAVING
			people.id < MAX(P.id)
	);

Wynikiem zapytania będzie:

 id |  name  | surname  |   phone   |      email       
----+--------+----------+-----------+------------------
  1 | Marcin | Maczka   | 660111111 | marcin@nospam.pl
  4 | Jas    | Kowalski | 555123147 | jas@nospam.pl
(2 rows)

Wyjaśnienie zapytania:

  • SELECT _FIELDS_ FROM people – deklarujemy pola, które checmy wyświetlić
  • WHERE EXISTS( _CONDITION_ ) – które spełniają warunek _CONDITION_
  • SELECT NULL FROM people AS P – w podzapytaniu nic nie wyświetlamy, wykorzystujemy je jedynie do złączenia
  • WHERE _CONDITION_ – joinujemy po wszystkich polach, które mają tworzyć unikalny klucz
  • GROUP BY _FIELDS_ – grupujemy wg. unikalnych pól
  • HAVING people.id < MAX(P.id) – warunek na id

Modyfikujemy teraz powyższe zapytanie SELECT _FIELDS_ na DELETE

DELETE
FROM
	people
WHERE
	EXISTS (
		SELECT
			NULL
		FROM
			people AS P
		WHERE
			people.name = P.name
			AND people.surname = P.surname
			AND people.phone = P.phone
			AND people.email = P.email
		GROUP BY
			P.name, P.surname, P.phone, P.email
		HAVING
			people.id < MAX(P.id)
	);

I otrzymujemy tabele wyczyszczoną z nadmiarowych rekordów.

Jakie to proste.

**********************edit**********************

Jak słusznie zauważył kolega Wild Child powyższe zapytanie nie działa w mySQL.
Przyznaje się bez bicia, że testowałem jedynie w PostgreSQL gdyż powyższy kod SQL
wyglądał na uniwersalny.

Okazało się jednak, że mySQL nie pozwala na jednoczesne wyświetlanie i modyfikowanie
zawartości tabeli. (jednoczesny DELETE w zapytaniu głównym i SELECT w podzapytaniu)

Posiedziałem trochę i wymyśliłem alternatywne zapytanie:

SELECT 
	D.*
FROM 
	people as U
INNER JOIN 
	people AS D ON (
	   	D.name = U.name AND
   		D.surname = U.surname AND
	   	D.phone = U.phone AND
	   	D.email = U.email
	   )
AND 
	D.id > U.id;

Konstrukcja tego zapytania jest nieco inna:

  • Robimy JOINA wewnątrz tabeli po polach, które mają być unikalne
  • Zostawiamy rekord o najniższym id, a resztę kasujemy (warunek AND D.id > U.id)
  • Skróty: D – duplicate, U – unique
  • Oczywiście aby usunąć rekordy należy zamienić słowo SELECT na DELETE

Dumpowanie bazy danych w PostgreSQL (pg_dump)

W trakcie tworzenia aplikacji internetowej początkowo projekt powinien powstawać w lokalnym środowisku programistycznym. Może to być wewnętrzny serwer firmowy, czy po prostu domowy komputer.
Dzięki temu możemy pracować na optymalnej konfiguracji serwerowej, która może nam ułatwiać niektóre czynności. Oszczędzamy także na czasie połączenia ze zdalnym serwerem bazodanowym.
Dopiero gdy aplikacja będzie miała wersję w miarę stabilna (nazwijmy ją beta czy RCx) to możemy ją przenieść na serwer docelowy i dalsze testy przeprowadzać już tam.

W trakcie przenoszenia aplikacji, będziemy musieli oprócz plików przenieść zawartość bazy danych, poniżej przedstawiam opis wszystkich dostępnych parametrów dumpowania dla bazy PostgreSQL 8.2.

Użycie: 
  pg_dump [OPTION]... [DBNAME] 

Podstawowe opcje: 
  -f, --file=FILENAME      nazwa pliku, w którym zapisany będzie dump 
  -F, --format=c|t|p       format pliku (dowolny, tar, tekstowy) 
  -i, --ignore-version     wymuś działanie nawet gdy wersja psql jest inna od wersji pg_dump 
  -v, --verbose            verbose mode 
  -Z, --compress=0-9       poziom kompresji (dla formatów skompresowanych) 
  --help                   po dumpowaniu wyświetl pomoc 
  --version                po dumpowaniu wyświetl informację o wersji 

Opcje kontrolujące dumpowane dane: 
  -a, --data-only             dumpuj tylko dane, bez schematu bazy danych 
  -b, --blobs                 dumpuj także pola tylu blobs 
  -c, --clean                 w dumpie najpierw wyczyć strukture (poprzez dropy) 
  -C, --create                zamieść w dumpie komendę tworzenia bazy danych 
  -d, --inserts               dumpuj dane jako oddzielne komendy INSERT commands (domyślnie dumpowanie jest poprzez COPY) 
  -D, --column-inserts        dumpuj dane jako oddzielne komendy INSERT commands z nazwami kolumn (domyślnie dumpowanie jest poprzez COPY) 
  -E, --encoding=ENCODING     dumpuj dane używając kodowania ENCODING 
  -n, --schema=SCHEMA         dumpuj tylko schemat(y) SCHEMA 
  -N, --exclude-schema=SCHEMA dumpuj wszystko oprócz schamat(ów) SCHEMA 
  -o, --oids                  dumpuje wraz z numerami OID 
  -O, --no-owner              pomiń zapis odnośnie właściciela bazy danych 
  -s, --schema-only           dumuj tylko strukture bazy danych 
  -S, --superuser=NAME        w dumpie będzie zawarta nazwa superusera 
  -t, --table=TABLE           dumpuj jedynie tabele TABLE z bazy danych 
  -T, --exclude-table=TABLE   dumpuj wszystkie tabele oprócz TABLE 
  -x, --no-privileges         dumpuj z pominięciem praw dostępu (grant/revoke) 
  --disable-dollar-quoting    dumpuj z pominięciem 'dollar quoting', zostanie użyte standardowy ' 
  --disable-triggers          dumpuj z pominięciem procedur wyzwalanych automatycznie (triggers) 

Opcje potrzebne do połączenia: 
  -h, --host=HOSTNAME      serwer na którym postawiona jest baza 
  -p, --port=PORT          port serwera pod którym baza jest dostępna (domyślnie: "5432") 
  -U, --username=NAME      nazwa użytkownika bazy danych 
  -W, --password           wymuszenie podania hasła

Osobiście używam polecenia:

pg_dump -c -O -U USERNAME DBNAME -h HOSTNAME -p 5432 --disable-dollar-quoting -i > DBNAME-YYYY-MM-DD.sql

Otrzymany dump najpierw zawiera wyczyszczenie struktury bazy danych (-c) (jest to przydatne gdy nadpisujemy istniejącą już bazę danych), pomijam zapis dotyczący właściciela bazy danych (-O) (gdyż na 99% właściciel bazy danych źródłowej i docelowej będzie inny), pomijam parametry -d -D i dumpuję baze poprzez COPY (takie dumpowanie znacznie przyspiesza wczytywanie zdumpowanych danych) wczytuje użytkownika jakim się łączę (-U USERNAME), nazwę bazy danych (DBNAME), host serwera na którym jest baza danych (-h HOSTNAME), port na którym się łączę z bazą danych (-p 5432), wymuszam standardowe apostrofy (–disable-dollar-quoting) i na wszelki wypadek ignoruję różnice wersji (-i). Dane zapisuję w pliku o nazwie równoważnej z nazwą bazy danych w raz z bieżącą datą (> DBNAME-YYYY-MM-DD.sql)