Sütun Tipleri - Text - Variable Length Standart Olmayan Sütun Tipi

Giriş
PostgreSQL'de metin saklamak için şu tipler kullanılabilir.
- char(n)
- varchar(n)
- varchar
- text

- Bu yazıyla ilgili olarak Sütun Tipleri - varchar yazısına da bakabilirsiniz.

- text tipi standart SQL tipi değildir.

Altta Kullanılan Tip

Açıklaması şöyle

Regardless of whether we choose char, varchar or text , the underlying structure PostgreSQL uses is varlena

varlena için açıklama şöyle

Note: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) it has performance advantages in some other database systems, PostgreSQL has no such advantage.  In fact, character(n) it is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

1. Fixed Length Alanlar
char(n) ve varchar(n) belirtilen uzunluğu geçemezler yani fixed length kabul edilirler. char(n) belirtilen uzunluktan daha kısa değerleri blank karakter ile doldurur yani padler.  Fixed Length Alanlar mümkünse tercih edilmemeli. Açıklaması şöyle

A common mistake is to choose a restrictive data type that doesn’t scale in the future. For example: A column containing some string can be of 3 types in PostgreSQL - varchar(n), character(n), text. When choosing the former two, you are restricted to the number of characters you can use. Choosing text type for data is usually harmless and is scalable to accommodate more characters in the future. And if need be, you can add check constraints to restrict data input.

2. Variable Length Alanlar

varchar ve text ise bir uzunluğa sahip değildirler yani variable length kabul edilirler. Açıklaması şöyle.

If you do not specify the n integer for the varchar data type, it behaves like the text data type. The performance of the varchar (without n) and text are the same.

text'in tercih edilmesinin sebebi isminin daha ayırd edici olması. Açıklaması şöyle.

text – for me a winner – over (n) data types because it lacks their problems, and over varchar – because it has distinct name

3. En Büyük Text Alanı
Açıklaması şöyle.

In any case, the longest possible character string that can be stored is about 1 GB.

Örnek
Şöyle yaparız.

CREATE TABLE category
(
  name text NOT NULL,
  ...
)

Örnek
Şöyle yaparız.

create table mytable (
    id INTEGER PRIMARY KEY,
    data TEXT
);
INSERT INTO mytable VALUES
    (0, 'a'),
    (1, 'b'),
    (2, 'c'),
    (3, 'd'),
    (4, 'e'),
    (5, 'f'),
    (6, 'g'),
    (7, 'h'),
    (8, 'i'),
    (9, 'j');

ORDER BY + LIMIT + OFFSET - Sayfalama İçindir

ORDER BY Nedir?
Order by kullanılmazsa veri tabanı sonucu istediği şekilde sıralayarak döndürür. Açıklaması şöyle.

After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

Bir başka açıklama şöyle

If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

LIMIT Nedir

Açıklaması şöyle

To limit the result, Postgres uses limit ... (SQL-server uses TOP keyword)

Örnek

Şöyle yaparız.

SELECT rental_duration FROM films
  ORDER BY length
  LIMIT 5 OFFSET 498 AS A

Crosstab

Giriş
pivot tablo oluşturmak içindir. Açıklaması şöyle.

Unless you add a WHERE clause to ensure not more than N values are returned by the inner query, more values will need to be added as your database grows.

Örnek
Elimizde şöyle bir kod olsun.

create table reader_event
(
   reader_event_id serial,
   reader_name text,
   event_type varchar(25),
   event_date timestamp,
   metric_key text,
   metric_value bigint
)

Şöyle yaparız.
reader_name ilk sütun olur.
İkinci sütun metric_key = metric_value1 olan satırladır. ve değeri metric_value değeridir.
Üçüncü sütun metric_key = metric_value2 olan satırladır. ve değeri metric_value değeridir.
Dödüncü sütun metric_key = metric_value3 olan satırladır. ve değeri metric_value değeridir.

select * from crosstab('select b.reader_name, b.metric_key, b.metric_value from b') 
AS Final Result (reader_name text,
                 metric_value1 bigint,
                 metric_value2 bigint,
                 metric_value3 bigint)

CASE...WHEN

Giriş
Sözdizimi şöyle

CASE WHEN Sütun=Değer THEN YeniDeğer END

Örnek

Açıklaması şöyle

You see in the CASE statement below, we’re categorizing users based on if they are paying customers or not. We then apply a sum() since it’s a quick way to count the number of paying customers vs non-paying customers in one simple query. If we did not have the CASE statement, it would take us two queries to find both numbers.

Şöyle yaparız

SELECT 
  date, 
  SUM(
    CASE WHEN paying_customer = 'yes' THEN downloads END
  ) AS paying, 
  SUM(
    CASE WHEN paying_customer = 'no' THEN downloads END
  ) AS non_paying 
FROM 
  ms_user_dimension a

Örnek
Şöyle yaparız.

SELECT top, nd, 
       CASE WHEN top = 1 THEN 'T' 
            WHEN nd = 1 THEN 'N'
       END AS topnd
FROM table1

Çıktı olarak şunu alırız.

top     nd  topnd
1       0   T
0       1   N
0       1   N
1       1   T

Örnek
Şöyle yaparız.

select (case when col0 = 'a' then 1 end) as alarm_ID, 
       (case when col0 = 'c' then 1 end) as CCTV_ID, 
       q.last_maintenance_date as Date 
from ...;

CTID Sistem Sütunu

Giriş
Bu sütun her tabloda vardır. Oracle'daki ROWID gibidir. Bu sayı sabit değildir ve vacuum gibi işlemler sonunda değişebilir. Açıklaması şöyle.

Rowid and ctid are physical row/tuple identifiers => can change after rebuild/vacuum.

Açıklaması şöyle

Every row carries a few invisible columns: xmin (the transaction that created this version), xmax (the transaction that killed it), and ctid (the exact physical address, block and offset, where this version lives on disk).

Run an UPDATE, and Postgres never touches the original row. It stamps xmax on the old version and inserts a brand new tuple with a fresh xmin and its own ctid. Query ctid before and after your UPDATE and you'll watch the address change - that's the new tuple, sitting somewhere else entirely. The old row is still there at its old ctid, just invisible to anyone whose snapshot no longer includes it.

Örnek
Şöyle yaparız.

SELECT MAX(CTID) FROM YOUR_TABLE;

PG_LARGEOBJECT Sistem Tablosu

Giriş
Bu tablo veriyi 2 KB'lik satırlara böler. Tabloda 3 sütun var.
1.loid
2.pageno
3.data

Bu tablodaki oprhan satırları silmek için vacuumlo komutu kullanılabilir.

Açıklaması şöyle. Yani TOAST binary veri için değil de TEXT gibi verile için kullanılıyor

TOAST is a built-in mechanism for efficiently handling large values within regular tables, primarily for variable-length data types, whereas pg_largeobject is a separate system table designed specifically for managing large binary objects like images or audio files. 

Örnek
psql komutunu kullanarak şöyle yaparız. lo_export metoduna loid değerini veririz

"C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h 192.168.1.101 -p 5432 -d mDB
 -U mYadmin -c  "\lo_export 19135 'C://leeImage.jpeg' "

TRUNCATE

Giriş
Şöyle yaparız

TRUNCATE Tabloİsmi CASCADE;