Hoş geldiniz!

PostgreSQL'de Veriyi İşlemek için Fonksiyonlar

Brian Piccolo

Sr. Director, Digital Strategy

Sakila Veritabanı

Yüksek derecede normalize
Temsili veri tipleri
Özel fonksiyonlar

Konular

PostgreSQL’de yaygın veri tipleri
Tarih ve saat işlevleri ve operatörleri
Metni ayrıştırma ve düzenleme
Tam metin arama ve PostgreSQL uzantıları

Yaygın veri tipleri

Metin veri tipleri
- CHAR, VARCHAR ve TEXT
Sayısal veri tipleri
- INT ve DECIMAL
Tarih/saat veri tipleri
- DATE, TIME, TIMESTAMP, INTERVAL
Diziler

Metin veri tipleri

SELECT title 
FROM film
LIMIT 5

+-------------------+
| title             |
|-------------------|
| ACADEMY DINOSAUR  |
| ACE GOLDFINGER    |
| ADAPTATION HOLES  |
| AFFAIR PREJUDICE  |
| AFRICAN EGG       |
+-------------------+

SELECT description 
FROM film
LIMIT 2

+-----------------------------------------+
| description                             |
|-----------------------------------------|
| A Epic Drama of a Feminist And a Mad    |     
|  Scientist who must Battle a Teacher in |
|  The Canadian Rockies.                  |
| A Astounding Epistle of a Database      |
|  Administrator And a Explorer who       |
|  must Find a Car in Ancient China       |
+-----------------------------------------+

Sayısal veri tipleri

SELECT 
    payment_id 
FROM payment 
LIMIT 5

+-------------+
| payment_id  |
|-------------|
| 1           |
| 2           |
| 3           |
| 4           |
| 5           |
+-------------+

SELECT 
    amount
FROM payment 
LIMIT 5

+--------+
| amount |
|--------|
| 2.99   |
| 0.99   |
| 5.99   |
| 0.99   |
| 9.99   |
+--------+

Var olan tablolardan veri tiplerini belirleme

SELECT 
    title, 
    description, 
    special_features 
FROM FILM 
LIMIT 5

+---------------+------------------+------------------------------+
| title         |  description     | special_features             |
|---------------|------------------|------------------------------|
| ACADEMY D...  | A Epic...        | {Deleted Scenes,Behi...}     |
| ACE GOLD...   | A Astound..      | {Trailers,Deleted Scenes}    |
| AFFAIR PR...  | A Fanciful,..    | {Commentaries,Behind the...} |
+---------------+------------------+------------------------------+

Var olan tablolardan veri tiplerini belirleme

SELECT
    column_name, 
    data_type
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE column_name in ('title','description','special_features')
  AND table_name ='film';

+------------------+-------------------+
| column_name      | data_type         |
|------------------|-------------------|
| title            | character varying |
| description      | text              |
| special_features | ARRAY             |
+------------------+-------------------+

Hadi pratik yapalım!

PostgreSQL'de Veriyi İşlemek için Fonksiyonlar