Archiwum kategorii: PostgreSQL

PostgreSQL w tej kategorii znajdują się wpisy wyjaśniające zawiłości serwera PostgreSQL i języka SQL

PostgreSQL. Jak obliczyć różnicę wyrażoną w latach, miesiącach i dniach, pomiędzy dwoma datami? Funkcje AGE() i DATE_PART().

Witam Was serdecznie w kolejnym wpisie dotyczący PostgerSQL-a. Dzisiaj zajmiemy się obliczaniem różnicy pomiędzy dwoma datami, wyrażoną w latach, miesiącach lub dniach. Tak naprawdę, żeby zrealizować nasz cel  skorzystamy tylko z dwóch funkcji:  AGE() i DATE_PART().

Pierwsza z nich, funkcja AGE(), zwróci nam różnicę pomiędzy dwoma datami. Funkcja ta zwróci nam wynik w postaci interwału czasowego np. 9 years 1 mons 2 days.

Możemy z niej skorzystać na dwa sposoby.

W pierwszym przypadku podajemy dwa argumenty, które są datami. Wtedy funkcja AGE() zwróci nam różnicę pomiędzy nimi. Dodatkowa zasada jest taka, żeby pierwsza data była tą "starszą" a druga "młodszą" (bliższą dacie dzisiejszej), bo inaczej otrzymamy wynik ujemny.

Składnia:

age(timestamp, timestamp)

Przykład:

SELECT  AGE( TIMESTAMP '2010-01-01' , TIMESTAMP '2000-12-31' )

Wynik:

9 years 1 day

W drugim przypadku podajemy jeden argument, który także jest datą. Wtedy funkcja AGE() zwróci nam różnicę między podaną datą a datą dzisiejszą.

Składnia:

age(timestamp)

Przykład:

SELECT    AGE( TIMESTAMP '2010-01-01' ) 

Wynik:

11 years 6 mons 17 days  <- bo zapytanie wykonałem 18 lipca 2021

 

OK. Korzystać z funkcji AGE() już potrafimy.

Teraz weźmy się za funkcję DATE_PART(). Funkcja ta "wyciągnie" nam interesującą część daty z daty w postaci "timestamp", np. rok, miesiąc, dzień lub z Interwału.

Z tej funkcji także możemy skorzystać na dwa sposoby.

W pierwszym sposobie podajemy dwa argumenty: text i timestamp a w drugiem: text i interval.

No to przykład dotyczący pierwszego sposobu, czyli podajemy dwa parametry: text i datę w formacie timestamp.

Składnia:

date_part(text, timestamp)

Przykład:

SELECT DATE_PART( 'year' , TIMESTAMP'2000-01-01' )

Wynik:

2000

oczywiście w miejsce 'year' możemy podać np. 'month' lub 'day' i wtedy otrzymamy interesującą nas część daty.

Drugi sposób to podanie dwóch argumentów: części daty która nas interesuje i interwału czasowego.

Składnia:

date_part(text, interval)

Przykład:

SELECT DATE_PART( 'year' , interval '10 years 4 month 19 days' )

Wynik:

10

Skoro jako drugi argument funkcji DATE_PART() możemy podać interwał czasowy to możemy skorzystać z funkcji AGE() która zwraca nam właśnie taki typ.

Składnia:

date_part(text, AGE())

Przykład:

SELECT DATE_PART( 'year' , AGE( '2020-06-10' , '2008-01-24' ) )

Wynik:

12

Mamy już pełną wiedzę na temat wykorzystania funkcji AGE() i DATE_PART(). Ale chciałem Ci jeszcze zwrócić uwagę na pewiem mały szkopół.

Może posłużmy się przykładem. Wykonaj coś takiego.

Przykład:

SELECT
    DATE_PART( 'year' , AGE( '2020-06-10' , '2008-01-24' ) ),
    DATE_PART( 'year' , AGE( '2020-06-10' , '2008-12-24' ) )

Wynik:

12 , 11

Patrząc na wyniki pojawia się pytanie. Dlaczego mamy inne wyniki w latach (bo pierwszy argument DATE_PART to 'year') skoro w obu wierszach mamy te same daty jeśli chodzi o rok? No to zernijmy na miesiące w obu wierszach. Pierwsza data jest identyczna w obu wierszach i jest nią 10 czerwiec 2020 roku. Natomiast druga data w pierwszym wierszy to 24 styczeń 2008 a w drugim wierszu 24 grudzień 2008. Czyli w pierwszym wierszu styczeń jest przed czerwcem a w drugim wierszu grudzień jest za czerwcem i dlatego funkcja AGE() zwraca inny interwał czasowy. Dla pierwszego wiersza będzie to: "12 years 4 mons 17 days" i dlatego wynikiem działania funkcji DATE_PART jest liczba 12, a w drugiem wierszu funkcja AGE() zwraca: "11 years 5 mons 17 days" dlatego DATE_PART zwróci nam: 11.

Także na poczatku musimy się zastanowić co tak naprawdę nas interesuje. Czy chcemy np. obliczyć różnicę w latach, ale pełnych latach? Czy jeśli rok "nie będzie pełny" to chcemy uzyskać liczbę o jedną mniej niż wynikałoby to lat zapisanych w datach.

Także zwródźcie na to uwagę i miłego programowania.

Dzięki za przeczytanie niniejszego wpisu. Serdecznie zapraszam na kolejne z tej serii.

PostgreSQL. Jak korzystać z interwałów czasowych?

Często w zapytaniach SQL które tworzymy zachodzi potrzeba skorzystania z jakiegoś interwału czasowego a nie wskazywania konkretnych dat. Np. za każdym razem kiedy uruchomię zapytanie w styczniu, marciu czy czerwcu, chcę żeby zapytanie za każdym razem kiedy się uruchoim brało pod uwagę rok wstecz.

I tutaj z pomocą przychodzą interwały. Np. chcemy żeby zapytanie wyświetliło nam wszystkie zdarzenia (np. tabela "events") gdzie data utworzenia tego zdarzenia ("createion_date") była rok wstecz.

Możemy to zrobić np. tak:

 

SELECT *

FROM events

WHERE events.creation_date BETWEEN NOW() – INTERVAL '1 year' AND NOW()

 

Jak wynika z powyższego słówkiem "INTERVAL" określamy interwał czasowy który nas interesuje. W przykładzie jest 1 rok ale oczywiście możemy użyć także "month", "day" itd.

Widzisz, że w bardzo prosty sposob można swoje zapytania tworzyć w bardziej uniwersalny sposób.

PostgreSQL. Jak zresetować hasło dla użytkownika Postgres? (reset password for user postgres)

Każdy kiedyś spotkał się z sytuacją kiedy zapomniał hasła czy to do jakiejś strony www, serwera czy aplikacji. Mi ostatnio przytrafiła się taka sytuacja i zapomniałem hasła do użytkownika Postgres do swojego mało używanego testowego serwera PostgreSQL-a. I już miałem zamiar wszystko zresetować kiedy pomyślałem, że jest to doskonała sytuacja żeby spróbować zresetować jakoś to hasło. Poszukałem chwilkę w sieci i od razu znalazłem ciekawy artykuł który przybliżył mi tą tematykę.  Efektem moich ćwiczeń jest poniższa procedura.

1. Znajdź lokalizację PostgreSQL-a na dysku. U mnie będzie to: C:\Program Files\PostgreSQL\13\data , ponieważ posiadam wersję 13. Wejdź do tej lokalizacji.
2. Znajdź plik pg_hba.conf i zrób jego kopię.
3. Otwórz plik pg_hba.conf w jakimś edytorze tekstów np. Notepad++
4. Zjedź na dół pliku i zmień wszystkie wartości w "kolumnie" METHOD z obecnie wpisanej na trust
5. Zapisz zmiany w pliku i zrestartuj PostgreSQL
    W polu "Wpisz tu wyszukiwane słowa" wpisz "Usługi" i wybierz opcję o tej nazwie z listy.
6. W oknie usługi znajdź usługę "postgresql…"
7. Kliknij w tą pozycję i po lewej stronie kliknij w opcję "Uruchom ponownie".
8. Teraz podaj "master password".
9. Kiedy system zapyta Cię o podanie hasła dla użytkownika "postgres" wciśnij po prostu enter. Powinieneś móc się zalogować pomimo braku hasła.
10. Teraz uruchom okno "Query editor" i zresetuj hasło poleceniem: ALTER USER postgres WITH PASSWORD 'twoje_nowe_hasło'
11. Wróć do lokalizacji z punktu 1 i przywróć stary plik pg_hba.conf i ponownie zrestartuj usługę "postgres…"
12. Teraz już możesz logować się jako użytkownik Postgres swoim nowo ustawionym hasłem.

 

PostgreSQL funkcja ASCII() – zwróć kod ASCII pierwszego znaku ciągu.

Tym razem na "warsztat" bierzemy funkcję ASCII(). Funkcja zwróci nam kod ASCII znaku lub ciągu znaków które umieścimy jako jej argument. W przypadku pojedynczego znaku sprawa jest prosta bo mamy tylko jeden znak i jego kod ASCII będzie zwrócony. W przypadku ciągu znaków otrzymamy kod ASCII pierwszego znaku w ciągu. Pamiętajmy, że np. znak 'A' to nie to samo co znak 'a'.
Zwracany typ: INT

PostgreSQL ASCII() składnia

ASCII(  'łańcuch_znaków'  )


Przykład zastosowania funkcji ASCII()

SELECT
    ASCII('A')

Wynik

65

Widzimy, że kod ASCII znaku 'A' to 65.


Teraz sprawdźmy to samo dla znaku 'a'

SELECT
    ASCII('a')

Wynik

97

Tutaj już widzimy różnicę. Kod ASCII znaku 'a' to 97.


Teraz sprawdźmy dla ciągu znaków. Znamy już kod ASCII znaku 'A' który jest równy 65. Teraz jako argument do funkcji podstawimy ciąg znaków np. 'ASCII'. Na początku ciągu jest znak 'A' czyli funkcja powinna zwrócić jego kod, czyli 65.

SELECT
    ASCII('ASCII')

Wynik

65

Wynik to 65 więc funkcja prawidłowo zwróciła kod ASCII pierwszego znaku ciągu czyli 'A'.


postgresqlpostgresql

PostgreSQL funkcja REPLACE() – zamiana jednego ciągu na drugi.

Dzisiaj omówimy funkcję REPLACE() która służy do zamiany jednego ciągu znaków na drugi. Bardzo przydatna funkcja w sytuacji kiedy hurtowo musimy zamienić ciągi znaków.
Zwracany typ: TEXT

PostgreSQL REPLACE() składnia

REPLACE( 'łańcuch_znaków' , 'ciąg_który_zamieniamy' , 'ciąg_na_który_zamieniamy' )

Przykład zastosowania funkcji REPLACE()

SELECT
    REPLACE('Ala ma kota.', 'kota', 'psa')

Wynik

Ala ma psa.

Co oznacza że słowo 'kota' zostało podmienione na słowo 'psa'.


postgresqlpostgresqlpostgresql

Kurs PostgreSQL (tutorial)

Z uwagi na to, że wpisów dot. PostgreSQL jest coraz więcej postanowiłem skopować wpisy z ogólnego wpisu dotyczcego SQL-a i stworzyć nowy osobny wpis i stworzyć pewnego rodzaju kurs (tutorial) PosgreSQL-a.

Kurs PostgreSQL spis treści

 

 

kurs postgresql tutorial sql PostgreSQL download. Skąd ściągnąć krok po kroku.   PostgreSQL download. Skąd ściągnąć krok po kroku.
kurs postgresql tutorial sql PostgreSQL instalacja krok po kroku.   PostgreSQL instalacja krok po kroku.
    Funkcje i operatory dot. ciągów znaków
sql, postgresql, łaczenie stringów   PostgreSQL łączenie ciągów znaków.
sql, postgresql, łaczenie stringów i innych typów danych   PostgreSQL łączenie ciągów znaków i innych typów danych.
sql, postgresql, funkcja bit_length ilość bitów w łańcuchu   PostgreSQL funkcja BIT_LENGTH() ile bitów w łańcuchu
sql, postgresql, funkcja char_length() character_length() ile znaków w łańcuchu   PostgreSQL funkcja CHAR_LENGTH() ile znaków w łańcuchu
sql, postgresql, funkcja lower() zamiana tekstu na małe litery   PostgreSQL funkcja LOWER() zamiana tekstu na małe litery
sql, postgresql, funkcja upper() zamiana tekstu na duże litery   PostgreSQL funkcja UPPER() zamiana tekstu na duże litery
sql, postgresql, funkcja substring wycinanie podłańcucha z łańcucha znaków   PostgreSQL funkcja SUBSTRING() podłańcuch z łańcucha znaków
sql, postgresql, funkcja substring wyrażenia regularne wycinanie podłańcucha z łańcucha znaków   PostgreSQL funkcja SUBSTRING() + wyrażenia regularne
podłańcuch z łańcucha znaków
sql, postgresql, funkcja position szukamy pozycji podłańcucha w łańcuchu znaków   PostgreSQL funkcja POSITION() pozycja podłańcucha w łańcuchu
sql, postgresql, funkcja trim leading trailing both ucinamy znaki z łańcucha głównego   PostgreSQL funkcja TRIM() leading, trailing, both ucinamy znaki z łańcucha głównego
sql, postgresql, funkcja OVERLAY zamiana ciagów znaków   PostgreSQL funkcja OVERLAY() zamiana ciągów znaków
sql, PostgreSQL funkcja CONCAT() – łączenie stringów   PostgreSQL funkcja CONCAT() łączenie stringów
postgresql sql funkcja left function   PostgreSQL funkcja LEFT() wycinamy string ze stringu od lewej
postgresql sql funkcja right function   PostgreSQL funkcja RIGHT() wycinamy string ze stringu od prawej
postgresql sql funkcja function OCTET_LENGTH() – liczba bajtów w stringu   PostgreSQL funkcja OCTET_LENGTH() liczba bajtów w stringu
    Inne funkcje
    PostgreSQL funkcja EXISTS() sprawdzamy czy zapytanie zwraca wynik
    PostgreSQL funkcja CAST() konwertujemy łańcuch znaków na liczbę
    PostgreSQL funkcja ASCII() zwracam kod znaku
     
    Wróć do nas za jakiś czas, już niedługo nowy wpis.

PostgreSQL funkcja OCTET_LENGTH() – liczba bajtów w stringu

Poznamy dzisiaj nową funkcję PostgreSQL a mianowicie OCTET_LENGTH() która zwraca liczbę bajtów w stringu. Funkcja ma jeden argument który będzie stringiem i to dla niego funkcja zwróci liczbę bajtów.
Zwracany typ: INT

PostgreSQL OCTET_LENGTH() składnia

OCTET_LENGTH( łańcuch_znaków)

Przykład zastosowania funkcji OCTET_LENGTH()

SELECT
    OCTET_LENGTH( 'Ala ma kota.' )

Wynik

12

Co oznacza że ciąg 'Ala ma kota.' ma 12 bajtów.


postgresqlpostgresqlpostgresql

PostgreSQL funkcja ASCII() – zwracamy kod znaku

Dzisiaj zajmiemy się funkcją ASCII(). Argumentem tej funkcji jest znak (tekst) który chcemy zamienić na kod ASCII. W przypadku wpisania większej ilości znaków niż jeden funkcja zwróci nam kod ASCII pierwszego znaku w ciągu. Pamiętaj znak 'a' i 'A' to dwar różne znaki, wielkość znaków ma tutaj znaczenie.
Zwracany typ: INT

PostgreSQL ASCII() składnia

ASCII(argument)

argument – znak lub ciąg znaków

Przykład zastosowania funkcji ASCII()

Przeanalizujmy działanie funkcji ASCII() w trzech konfiguracjach. Na początku odczytamy kod ASCII znaku 'a' później znaku 'A' a na koniec do argumentu funkcji wstawimy ciąg znaków 'Ala'

Kod znaku 'a'

SELECT
    ASCII( 'a' )

Wynik

97

Teraz odczytajmy kod znaku 'A'

SELECT
    ASCII( 'A' )

Wynik

65

A teraz sprubujmy wstawić do argumentu cały ciąg 'Ala'

Uzupełniając inforamcje z dokumentacji, dla znaków w UTF-8 funkcja zwróci kod Unicode dla danego znaku. W przypadku innych kodowań wielobajtowych argument musi być znakiem ASCII.

SELECT
    ASCII( 'Ala' )

Wynik

65

Widzimy więc, że funkcja zwróciła kod pierwszego znaku z ciągu.


postgresqlpostgresqlpostgresql

PostgreSQL funkcja RIGHT() – wycinamy string ze stringu od prawej

Dzisiaj zapoznamy się z funkcją RIGHT(), która zwróci nam tyle znaków z końca łąńcucha głównego ile zadeklarujemy. Inaczej mówiąc deklarujemy ile znaków z końca łańcucha głównego chcemy "wyciąć". Działanie tej funkcji obrazują poniższe przykłady.
Zwracany typ: TEXT

PostgreSQL RIGHT() składnia

RIGHT( łańcuch_główny , ilość_znaków_do_wycięcia )

np.

postgresql sql funkcja right function

Tak jak przy bliźniaczej funkcji LEFT() przeanalizujmy trzy przypadki. Pierwszy gdzie zdefiniowana liczba znaków jest mniejsza od liczby znaków w łańcuchu głównym. Drugi przypadek będzie taki gdzie liczba zdefiniowanych znaków będzie większa niż liczba znaków w łańcuchu głównym. W trzecim przypadki sprawdzimy co się stanie jeżeli w miejsce zdefiniowanej ilości znaków wstawimy 0 lub NULL.

Zdefiniowana liczba znaków jest mniejsza niż liczba znaków w łańcuchu głównym.

SELECT
    RIGHT( 'Ala ma kota.' , 3 )

Wynik

ta.

Zdefiniowana liczba znaków jest większa niż liczba znaków w łańcuchu głównym.

SELECT
    RIGHT( 'Ala ma kota.' , 15 )

Wynik

Ala na kota.

Ponieważ zdanie 'Ala ma kota.' włączając spacje ma 12 znaków a my zdefiniowaliśmy 15 znaków do "wycięcia" funkcja zwróciła nam tylko cały łańcuch główny i nic więcej.

Zdefiniowana ilości znaków do wycięcia równa 0 lub NULL.123

SELECT
    RIGHT( 'Ala ma kota.' , 0 )

Wynik

Funkcja nic nie zwróci

Funkcja się wykona ale nic nie zwróci.

To samo stanie się w przypadku gdy zamiast 0 wstawimy NULL.

SELECT
    RIGHT( 'Ala ma kota.' , NULL )

Wynik

Funkcja nic nie zwróci


postgresqlpostgresqlpostgresql

PostgreSQL funkcja LEFT() – wycinamy string ze stringu od lewej

Co zrobić kiedy chcemy "uciąć" kilka znaków od początku łąńcucha znaków. Możemy skorzystać z funkcji LEFT(), która zwróci nam tyle znaków z początku łąńcucha ile zadeklarujemy. Żeby lepiej przybliżyć działanie tej funkcji zobaczmy przykłady poniżej.
Zwracany typ: TEXT

PostgreSQL LEFT() składnia

LEFT( łańcuch_główny , ilość_znaków_do_wycięcia )

np.

postgresql sql function left funkcja

Przeanalizujmy poniżej trzy przypadki. Pierwszy gdzie zdefiniowana liczba znaków jest mniejsza od liczby znaków w łańcuchu głównym. Drugi przypadek będzie taki gdzie liczba zdefiniowanych znaków będzie większa niż liczba znaków w łańcuchu głównym. W trzecim przypadki sprawdzimy co się stanie jeżeli w miejsce zdefiniowanej ilości znaków wstawimy 0 lub NULL.

Zdefiniowana liczba znaków jest mniejsza niż liczba znaków w łańcuchu głównym.

SELECT
    LEFT( 'Ala ma kota.' , 3 )

Wynik

    Ala

Zdefiniowana liczba znaków jest większa niż liczba znaków w łańcuchu głównym.123

SELECT
    LEFT( 'Ala ma kota.' , 15 )

Wynik

 Ala na kota.

Ponieważ zdanie 'Ala ma kota.' włączając spacje ma 12 znaków a my zdefiniowaliśmy 15 znaków do "wycięcia" funkcja zwróciła nam tylko cały łańcuch główny i nic więcej.

Zdefiniowana ilości znaków do wycięcia równa 0 lub NULL.123

SELECT
    LEFT( 'Ala ma kota.' , 0 )

Wynik

Funkcja nic nie zwraca

Funkcja się wykona ale nic nie zwróci.

To samo stanie się w przypadku gdy zamiast 0 wstawimy NULL.

SELECT
    LEFT( 'Ala ma kota.' , NULL )

Wynik

Funkcja nic nie zwraca


postgresqlpostgresqlpostgresql