Sütun Tipleri - uuid

Giriş

Açıklaması şöyle

PostgreSQL allows you to store and compare UUID values, but it doesn't have any built-in methods for creating them.

UUID üretmek için şu modüller kullanılabilir.

1. uuid-ossp Module'deki uuid_generate_v1(),uuid_generate_v4() kullanılabilir

2. pgcrypto Module'deki gen_random_uuid() kullanılabilir

3. UUIDv7 üretmek için pg_uuidv7 extension kullanılır

VARCHAR Sütun Tipi

UUID saklamak için bazen VARCHAR sütun tipi kullanılıyor.

Örnek

Şöyle yaparız. Burada id alanı UUID ama VARCHAR olarak saklanıyor

CREATE TABLE test.speed_uuid
(
    id       varchar(36) PRIMARY KEY,
    name    varchar(50),
    created timestamp
);

1. uuid-ossp Module

Açıklaması şöyle

If using Azure Database for PostgreSQL, this can be enabled by going to Server Parameters → azure.extensions → Choose uuid-ossp. User needs to have admin credentials to do this.

Örnek

Eğer kurulu değilse kurmak için şöyle yaparız

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Örnek

Şöyle yaparız

CREATE TABLE books (
  id              UUID DEFAULT uuid_generate_v4 (),
  title           VARCHAR(100) NOT NULL,
  primary_author  VARCHAR(100) NULL,
  PRIMARY KEY (id)
);

Örnek

Şöyle yaparız

CREATE EXTENSION "uuid-ossp";

INSERT INTO tenant (id, name) VALUES (uuid_generate_v4(), 'Company 1');
INSERT INTO tenant (id, name) VALUES (uuid_generate_v4(), 'Company 2');

2. pgcrypto Module

Örnek

Şöyle yaparız

CREATE TABLE thingie (
  id UUID PRIMARY KEY DEFAULT public.gen_random.uuid(),
  foo VARCHAR,
  bar VARCHAR,
);  

3. UUID7

Açıklaması şöyle

Similar to UUID v4, UUID v7 is a 128-bit identifier represented as a 32-character sequence of letters and numbers, formatted as 8–4–4–4–12. The distinctive feature of UUID v7 lies in its nature as a time-ordered UUID, encoding a Unix timestamp with millisecond precision in the most significant 48 bits. In alignment with UUID formats, 4 bits designate the UUID version, and 2 bits denote the variant. The remaining 74 bits are generated randomly, contributing to the uniqueness of this identifier.

Şeklen şöyle

Örnek

Şöyle yaparız

CREATE EXTENSION IF NOT EXISTS pg_uuidv7;
CREATE TABLE examples (
  example_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP WITH TIME ZONE,
  created_by UUID NOT NULL,
  updated_by UUID NOT NULL,
  FOREIGN KEY (created_by) REFERENCES users(user_id),
  FOREIGN KEY (updated_by) REFERENCES users(user_id)
);

Sütun Tipleri - Array

Giriş

Oracle'daki VARARRAY tipi gibidir.

Neden Lazım

Normalization'dan kurtabilir. 

Örnek

Elimizde foreign key table ile birleştirilen iki tablo olsun

create table users (
    user_id int not null primary key generated always as identity,
    name text not null unique
);

create table roles (
    role_id int not null primary key generated always as identity,
    name text not null unique
);

create table users_roles (
    user_id int not null references users,
    role_id int not null references roles,
    primary key (user_id, role_id)
);

Aslında rolleri array olarak ta saklayabiliriz. Şöyle yaparız

create table users (
    user_id int not null primary key generated always as identity,
    name text not null unique,
    roles text[] not null,
    constraint roles_check check (roles <@ array['admin', 'user', 'guest'])
);

Açıklaması şöyle

The constraint roles_check check (roles <@ array['admin', 'user', 'guest']) uses <@ array operator to ensure that every insert and update can only have array values that exist on the right side array ['admin', 'user', 'guest'].

Eğer constraint değiştirmek istersek şöyle yaparız

begin;

alter table users drop constraint roles_check;
alter table users add constraint roles_check check
  (roles <@ array['super', 'admin', 'user']); 

end;

Ancak mevcut kayıtların bazısı yeni kısıtı ihlal edebilir. Bu yüzden not valid kullanmak gerekiyor. Şöyle yaparız

begin;

alter table users drop constraint roles_check;
alter table users add constraint roles_check check
  (roles <@ array['super', 'admin', 'user']) not valid; 

end;

not_valid kullanılmasının sebebi şöyle

• Records with invalid role guest will remain intact. That small deviation from the data integrity can be safely ignored (if business rules permit).
• All new inserts and updates will enforce new data integrity rules — roles must exist in a new array: ['super', 'admin', 'user'].
• The script that updates the new check constraint will run fast, even on big tables and it will not lock or block anyone or anything.

TEXT Array

Örnek 

Şöyle yaparız.

CREATE TABLE event (
  id INT8 NOT NULL,
  version INT4,
  sensor_names TEXT[],
  sensor_values INTEGER[],
  PRIMARY KEY (id)
)

Örnek

Şöyle yaparız

CREATE TABLE product (
  id        BIGINT,
  images    TEXT[]
)
INSERT INTO product(id, images) VALUES (1, '{"url1", "url2", "url3"}');

SELECT * FROM product WHERE images @> ARRAY['url2']

Sütun Tipleri - OID

Giriş

Açıklaması şöyle.

A column of type Oid is just a reference to the binary contents which are actually stored in the system's pg_largeobject table. In terms of storage, an Oid a 4 byte integer. On the other hand, a column of type bytea is the actual contents.

PG_LARGEOBJECT Sistem Tablosu yazısına bakabilirsiniz.

Örnek

Eğer OID tipindeki sütunları görmek istersek şöyle yaparız

SELECT * FROM information_schema.columns WHERE data_type = 'oid';

Sütun Tipleri

C# Eşleşmesi

Postgre ile C# arasındaki eşleşme şöyle

Postgresql  .Net System Type
----------  ------------ ------------------ ----------------
int8        Int64
bool        Boolean
bytea       Byte[]
date        DateTime
float8      Double
int4        Int32
money       Decimal
numeric     Decimal
float4      Single
int2        Int16
text        String
time        DateTime
timetz      DateTime
timestamp   DateTime
timestamptz DateTime
interval    TimeSpan
varchar     String
inet        IPAddress
bit         Boolean
uuid        Guid
array       Array

Metin Tipleri
Metin tipleri arasında en uygun olanı her zaman "text" tipi. varchar tipinin aksine metin için üst sınır tanımlamıyor.

array tipi
Sütun Tipleri - Array yazısına taşıdım.

bigserial
Sütun Tipleri - Serial yazısına taşıdım.

boolean

true, false ve null değeri alabilir.

bytea
PostgreSQL iki çeşit BLOB sütunu sağlar. Bunlar şöyle. bytea binary string anlamına gelir.

bytea - data stored in table
oid - table holds just identifier to data stored elsewhere

date - LocalDate
Sütun Tipleri - DATE yazısına taşıdım.

float8
float8 aynı zamanda "double precision" olarak ta bilinir

int8/bigint tipi
8 byte uzunluğundadır.

integer/int/int4 tipi
Aynı zamanda int ve int4 olarak ta bilir. 4 byte uzunluğundadır.

interval

Date/Time Sütun tiplerinden bir tanesidir

smallint/int2 tipi

2 byte uzunluğundadır

json ve jsonb
Sütun Tipleri - json yazısına taşıdım.

jsonb
Sütun Tipleri - jsonb yazısına taşıdım.

location
Şöyle yaparız.

CREATE TABLE tweets (
  id bigint,
  location point
)

oid
Sütun Tipleri - OID yazısına taşıdım.

real
real gibi tipler kullanılmamalı. Açıklaması şöyle.

nexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:

• If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.
• If you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.
• Comparing two floating-point values for equality might not always work as expected.

Şöyle yaparız

CREATE TABLE test (id integer, value real);
INSERT INTO test VALUES (1, 0.1);

Şöyle yaparız.

SELECT * FROM test where value = '0.1';
 id | value
----+-------
  1 |   0.1
(1 row)

Şöyle yaparız.

SELECT * FROM test where value = 0.1::real;

serial
Sütun Tipleri - Serial yazısına taşıdım

text
Sütun Tipleri - Text yazısına taşıdım.

TIME WITH TIME ZONE
 Java'daki java.time.OffsetTime sınıfına denk gelir.

TIME WITHOUT TIME ZONE - LocalTime
 Java'daki java.time.LocalTime sınıfına denk gelir.

TIMESTAMP WITH TIME ZONE
Date/Time Sütun Tipleri - TIMESTAMP WITH TIME ZONE yazısına taşıdım.

TIMESTAMP WITHOUT TIME ZONE - LocalDateTime
Date/Time Sütun Tipleri - TIMESTAMP WITHOUT TIME ZONE yazısına taşıdım.

uuid

Sütun Tipleri - uuid yazısına taşıdım.

varchar
Sütun Tipleri - varchar yazısına taşıdım.

Sütun Tipleri - Serial (Otomatik Sayı Üretir) - Kullanmayın

Giriş

Açıklaması şöyle. Yani SERIAL yerine IDENTITY kullanılırsa daha iyi

It is recommended to use IDENTITY instead since SERIAL has some weird behaviors.

Serial iki çeşit. Bunlar

1. SERIAL

2. BIGSERIAL

Bu sütunlara NOT NULL + PRIMARY KEY + UNIQUE gibi özellikler de atanabilir.

Diğer

Eğer serial veya bigserial yerine sequence kullanmak istersek şöyle yaparız

CREATE TABLE public.contacts
(
  contactid integer NOT NULL DEFAULT nextval('contacts_contactid_seq'::regclass),
  ...
);

1. SERIAL Sütun Tipi

Açıklaması şöyle

the keyword serial is PostgreSQL specific and it set up an auto-incrementing value and that is the typical way for the primary-key.

Açıklaması şöyle. 4 byte uzunluğundadır. Yani integer ile aynıdır.

Serial is just syntactic sugaring on top of an int column that takes its value from a sequence.

Açıklaması şöyle. Eğer transaction başarısız olsa bile serial numarası artmaya devam eder.

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

Örnek
Şöyle yaparız.

create table testtable(
  id serial primary key,
  data integer not null
);

Şöyle yaparız.

insert into testtable ( data ) values ( 4 ), ( 5 ), ( 6 ), ( 7 );

Örnek

Şöyle yaparız

CREATE SCHEMA retail;
CREATE TABLE retail.orders_info (
  orderid SERIAL NOT NULL PRIMARY KEY,
  ...
);

Cache Parametresi
Açıklaması şöyle.

SERIAL columns are implemented using standard SQL sequences, which might generate out-of-order values when used by multiple concurrent sessions if the CACHE parameter is set to something more than 1

Daha detaylı açıklama şöyle.

Although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially.

2. BIGSERIAL Sütun Tipi
Örnek

Şöyle yaparız

CREATE TABLE foo (
  id BIGSERIAL PRIMARY KEY,
  ...
);

CREATE TABLE bar (
  foo_id BIGINT UNIQUE,
  ...
);

Örnek

Şöyle yaparız.

CREATE TABLE category
(
  id bigserial NOT NULL PRIMARY KEY,
  ...
)

Date/Time Sütun Tipleri - TIMESTAMP WITH TIME ZONE (timestamptz ) - UTC Zamanı Saklar

Giriş
Not : DATE sütun tipine de bakabilirsiniz.

TIMESTAMP WITHOUT TIME ZONE sütun tipine de bakabilirsiniz.

SQL cümlesindeki veya bağlantıdaki saat dilimi bilgisini kullanarak girdiyi UTC saatine çevirir ve saat dilimi bilgisini saklamaz. Yani sadece UTC bilgisini saklar. Açıklaması şöyle.

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

Default Değer İle Kullanımı

Şöyle yaparız

CREATE TABLE users(
  id SERIAL PRIMARY KEY NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  firstName VARCHAR(50),
  lastName VARCHAR(50),
  registerdAt TIMESTAMPTZ DEFAULT now()
);

Çözünürlük

Mikrosaniye çözünürlük kullanılır. Açıklaması şöyle.

Any date-time value submitted with a time zone or offset-from-UTC is processed by using that zone/offset to determine a value in UTC. That UTC value is then stored in the database, with a resolution of microseconds (not the milliseconds of legacy Java java.util.Date & Calendar classes, and not the nanoseconds of java.time classes). After the UTC adjustment is made, the zone/offset information is discarded. If you care about the original zone/offset, store that in a separate column explicitly.

When retrieved, the date-time value is sent out from Postgres in UTC. An intervening tool such as psql or pgAdmin may confusingly apply a default time zone. While well-intentioned, such a feature creates the illusion of the stored value carrying a time zone when in fact it does not. In contrast, a JDBC driver compliant with JDBC 4.2 and later will handle the UTC and zone automatically for you.

Precision

Açıklaması şöyle. Precision 0 ve 6 arasında bir sayı alabilir.

time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6.

Precision verilmesinin sebebi sürekli yuvarlama yapılmak istenmemesi. Açıklaması şöyle.

If you need rounded values, storing them rounded is going to be faster and less error-prone than dynamically rounding each time you access it.

And having less precision means more ties so more opportunity for index duplicate compression (on versions new enough to offer that). It would also offer better compression for the datafiles when they are offline (like in backups) and possibly when online if your FS offers built-in compression.

Örnek

Elimizde şöyle bir tablo olsun

CREATE TABLE BOOKMARK.TEMP_TABLE
(
   mindatetime timestamp,
   ...
);

Çıktı olarak şunu alırız. Mikro saniye olduğu için 1 milyona kadardır.

mindatetime          
2019-05-08 08:29:50.0
2019-08-08 20:04:14.994077
2019-08-08 21:40:08.362082
2019-08-08 23:03:04.270083
2019-08-09 00:31:20.487717
2019-08-19 15:08:41.167284
2019-08-09 00:31:20.487717
2019-08-19 15:08:41.167284

Gösterimde Yerel Saate Çevrilir
Örnek

Veri tabanına şöyle bir kayıt ekleyelim.

INSERT INTO messages ( user_id, message, left_at )
VALUES ( 3, 'Howdy!', '2011-09-27 17:17:25' );

America/New_York zaman dilimindeki birisi sorgularsa şunu görür.

user_id | 3
message | Howdy!
left_at | 2011-09-27 20:17:25-04

Europe/Stockholm zaman dilimindeki birisi sorgularsa şunu görür.

user_id | 3
message | Howdy!
left_at | 2011-09-28 02:17:25+02

Java 7 Kullanımı
java.util.Date, java.sql.Timestamp, java.util.GregorianCalendar ve javax.xml.datatype.XMLGregorianCalendar sınıfları ile bu sütun tipi kullanılabilir.

Örnek
Hibernate/JPA ile şöyle yaparız.

@Column(name = "run_from", columnDefinition= "TIMESTAMP WITH TIME ZONE")
@Temporal(TemporalType.TIMESTAMP)
private Date runFrom;

- Hibernate java.util.Date tipindeki alan için veri tabanında DATE tipinde sütun yaratır. Yani sadece tarih bilgisini saklar. @Column anotasyonuna yaptığımız ekleme ile TIMESTAMP WITH TIME ZONE tipinde sütun yaratılır.

- @Temporal.TIMESTAMP ile hem tarih hem de saat bilgisini saklarız.

Java 8 Kullanımı
java.time.Instant, java.time.OffsetDateTime, java.time.ZonedDateTime sınıfları ile bu sütun tipi kullanılabilir.