CREATE TABLE

Giriş
qualified Name Nedir?
Açıklaması şöyle

A qualified name is the schema name and the table name separated by a dot. This will specify the schema in which we want to create our table

unqualified Name Nedir?
Açıklaması şöyle

An unqualified name consists of only the table name. This will create the table in the selected database which is public by default. This can be changed via the search_path ...

Örnek - qualified
Şöyle yaparız.

CREATE TABLE schema_1.persons (name text, age int);

Örnek - unqualified
Şöyle yaparız.

CREATE TABLE persons (name text, age int);

DROP SCHEMA

Örnek
Eğer schema boş ise yaparız.

DROP SCHEMA schema_1;

Eğer schema boş değilse şöyle yaparız.

DROP SCHEMA schema_1 CASCADE;

CREATE SCHEMA

Örnek
Şöyle yaparız.

CREATE SCHEMA schema_1;

Örnek 
Bir başka kullanıcıya ait schema yaratmak için şöyle yaparız.

CREATE SCHEMA schema_1 AUTHORIZATION username;

Schema

Schema Nedir?
Açıklaması şöyle.

In Postgres, the schema is also referred to as a namespace. The namespace can be associated with a family name. It is used to identify and differentiate certain objects in the database (tables, views, columns, etc.). It’s not allowed to create two tables with the same name in one schema, but you can do it in two different schemas. For example, we can have two tables both named table1 present in public and in postgres schemas.

Schema Neden Lazım?
Açıklaması şöyle.

Schemas are very useful to organize database objects into logical groups and avoid name collision. Besides this, schemas are often used to allow different users to work with the database without interfering with each other. A common example is when each database user works on its own schema, without interfering with other users and avoiding conflicts. 

Schema İsimleri

Açıklaması şöyle

public — is used by default for the user objects;

pg_catalog — is used for the system catalog tables;

information_schema — is a view of the system catalog defined by SQL standards;

pg_toast — is used for the TOAST objects;

pg_temp — stores temporal tables (note: every user has its own schema for temp tables: pg_temp_N, but pg_temp is used as an alias for every of those);

public İsimli Schema

public Schema yazısına taşıdım

SEARCH_PATH Değişkeni
Açıklaması şöyle

When executing a command with an unqualified name, Postgres follows a search path to determine what schemas to use. By default, the search path is set to the public schema.

Açıklaması şöyle

If a schema name is not specified explicitly, the first matching schema is chosen from the searching path. The searching path is built from the value stored in the search_path parameter, pg_catalog, and pg_temp schemas. It allows a user to store objects with the same names but in different schemas.

Örnek
Şöyle yaparız.

SHOW search_path;

Örnek
Bir başka schema'ya geçmek için şöyle yaparız

SET search_path TO schema_1;

SEARCH_PATH Değişkenini JDBC Connection String İle Atama

Maven'a şu satırı ekleriz

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.2.8</version>
</dependency>

Örnek
Bağlantı sırasında schema belirtmek için şöyle yaparız.

jdbc:postgresql://127.0.0.1:5432/DBNAME?currentSchema=app

Aslında bu parametrenin ismi açıklaması şöyle. Yani aslında tüm search_path'i tanımlar

The parameter name currentSchema is a bit misleading. It takes the whole search_path, not just the "current schema".

Bu yüzden birden fazla schema'yı görebilsin diye şöyle yaparız

jdbc:postgresql://localhost:5432/tst?currentSchema=app1,public

pg_dump komutu

Giriş
Yedek alır. Alınan yedek pg_restore komutu veya psql komutu ile geri yüklenebilir. Açıklaması şöyle.

pg_dump is a regular PostgreSQL client application. This means that you can do this backup procedure from any remote host that has access to the database. pg_dump does not operate with special permissions. In particular, it must have read access to all tables that you want to back up, so in practice, you almost always have to run it as a database superuser.

Dumps created by pg_dump are internally consistent, that is, the dump represents a snapshot of the database as of the time pg_dump begins running. pg_dump does not block other operations on the database while it is working. (Exceptions with an exclusive lock, such as most forms of ALTER TABLE.)

Kullanıcı Bilgileri

pg_dump kullanıcı bilgilerini kaydetmez. Dolayısıyla "create user ..." komutlarını içermez. Açıklaması şöyle

pg_dump can not do that, because pg_dump only dumps a single database and that information is not part of one database, but stored globally in the Postgres "cluster".

You need to use pg_dumpall for that, using the --globals-only option:

Söz Dizimi

Şeklen şöyle. En sona veri tabanı ismi gelmeli. Bu komut çıktıyı ekrana verir. Dolayısıyla dosya yönlendirme ile birlikte kullanılmalı.

pg_dump [OPTION]... [DBNAME]

Mantıken
pd_dump "Bağlantı Seçenekleri" + "Çıktı Seçenekleri" şeklinde kullanılıyor.

Benim Kullandıklarım
Örnek
Şöyle yaptım. Şifre girmeye gerek kalmadı.

pg_dump.exe --dbname=postgresql://myuser:mypassword@myserver:5432/mydb -F c
 -f mybackup

Açıklaması şöyle.

For a one-liner, like migrating a database you can use --dbname followed by a connection string (including the password) as stated in the pg_dump manual

In essence.

pg_dump --dbname=postgresql://username:password@127.0.0.1:5432/mydatabase

Note: Make sure that you use the option --dbname instead of the shorter -d and use a valid URI prefix, postgresql:// or postgres://.

Veri Tabanı İçin

Örnek

Şöyle yaparız. -Fc ile TestDB veri tabanının custom formatta yedeği alınıyor.

[root@blt ~]# su - postgres
[postgres@blt ~]$ pg_dump -Fc TestDB> TestDB.dump

Tablo İçin

Örnek

Şöyle yaparız. -Fc ile postgres veri tabanının t1 tablosunun custom formatta yedeği alınıyor.

[root@blt ~]# su - postgres
[postgres@blt ~]$ pg_dump -Fc — data-only -W -dpostgres -tt1 > t1.dump

1. Bağlantı Seçenekleri
-d seçeneği - database
Şöyle yaparız

$ pg_dump -h $host -U $user -d $db -a -W --format=p >> dump.sql

Şöyle yaparız

# backup
pg_dump -U [username] -d [database] > backup.sql

# restore
psql -U [username] -d [database] < backup.sql

-h seçeneği - host
Açıklaması şöyle. Bağlanılacak sunucunun adresini belirtir.

database server host or socket directory

-p seçeneği
Bağlanılacak sunucunun port numarasını belirtir.

-U seçeneği
user anlamına gelir. Açıklaması şöyle.

connect as specified database user

Şöyle yaparız.

pg_dump -U tad -h localhost -p 5932 -F c -b -v -f /home/dump26072018.dmp openbravo

-w seçeneği

Açıklaması şöyle.

-w

--no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W seçeneği
Örnek ver

2. General Options
-f seçeneği
Çıktı dosyasının ismini belirtir.

Örnek

Şöyle yaparız

#backup
pg_dump -U admin -d company -f company_backup.sql

#restore
psql -d new_company -f company_backup.sql

Örnek

Şöyle yaparız

# Export Data Only
pg_dump -U admin -d company -f company_backup.sql --data-only

#restore
psql -d new_company -f company_backup.sql

Örnek

Şöyle yaparız

# Include Only Some Schemas:
pg_dump -U admin -d company -n 'p*' -f company_backup.sql

#restore
psql -d new_company -f company_backup.sql

-F seçeneği

Kullanılacak format'ı belirtir. 4 format  tipi var.

1. custom format
Bu format sadece pg_restore komutu ile geri yüklenebilir. Açıklaması şöyle.

c custom Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

Örnek

Şöyle yaparız

$ pg_dump \
  -F c \
  -f /var/lib/postgresql/data/2023-04-01-custom.dump \
  -U mypostgresqldumpplanet \
  myjhipsterplanet

2. plain format

Bu format sadece pg_sql komutu ile geri yüklenebilir.
Örnek
plain format için şöyle yaparız.

pg_dump -h remote_address -U username -D dbname -Fp > backup.sql

3. directory format

Bu format sadece pg_restore komutu ile geri yüklenebilir. Belirtilen dizinde her tablo için bir dosya yaratır

Örnek

Şöyle yaparız 

-j ile thread sayısı belirtilir. 

-Fd ile directory format belirtilir.

--quote-all-identifiers ile tüm dosya ve sütün isimleri quote içine alınır

-f ile dizin ismi belirtilir

--blobs nedir bilmiyorum

pg_dump -j 4 -Fd --quote-all-identifiers --compress=0 -f $SCHEMA --blobs

pg_restore -j 4 -d $DATABASE $SCHEMA

Örnek

Şöyle yaparız

$ pg_dump \
  -F d \
  -U mypostgresqldumpplanet \
  myjhipsterplanet > /var/lib/postgresql/data/2023-04-01-directory/

Örnek

Şöyle yaparız

# Produces a company_backup folder with .dat.gz files.
pg_dump -U admin -d company -F d -f company_backup

4. tar format

Bu format sadece pg_restore komutu ile geri yüklenebilir. "-F t" ile tar formatı belirtilir

Örnek

Şöyle yaparız

pg_dump -d postgres -t customer -F t  > /tmp/customer.tar

pg_restore -d postgres -t customer /tmp/customer.tar

Örnek

Şöyle yaparız

$ pg_dump \
  -F t\
  -f /var/lib/postgresql/data/2023-04-01-tar.tar \
  -U mypostgresqldumpplanet \
  myjhipsterplanet

-j seçeneği

Örnek ver

--v seçeneği
Örnek ver

--V seçeneği
Örnek ver

--Z seçeneği
Örnek ver

--lock-wait-time seçeneği
Örnek ver

--no-sync seçeneği
Örnek ver

2. Options Controlling Output Content
-a seçeneği
Örnek ver

-b seçeneği
Örnek ver

-B seçeneği
Örnek ver

-c seçeneği
Örnek ver

-C seçeneği
Açıklaması şöyle. Copy şeklinde satır verisini içeren sql cümleleri oluşturur.

include commands to create database in dump

Copy komutunun açıklaması şöyle

Use COPY to load all the rows in one command, instead of using a series of INSERT commands. The COPY command is optimized for loading large numbers of rows; it is less flexible than INSERT, but incurs significantly less overhead for large data loads. Since COPY is a single command, there is no need to disable autocommit if you use this method to populate a table.

Örnek

İki farklı makine arasında veri tabanı kopyalamak istersek şöyle yaparız. Bu veri tabanı altındaki tüm schemaları kopyalar

pg_dump -C -h localhost -U postgres mydatabase| psql -h remote 
  -U postgres mydatabase

-E seçeneği
Örnek ver

-n seçeneği veya  --schema=SCHEMA

Açıklaması şöyle.

dump the named schema(s) only

Örnek

Şöyle yaparız

# Include Only Some Schemas:
pg_dump -U admin -d company -n 'p*' -f company_backup.sql

#restore
psql -d new_company -f company_backup.sql

-N seçeneği
Örnek ver

-o seçeneği
Açıklaması şöyle.

If your database schema relies on OIDs (for instance as foreign keys), you must instruct pg_dump to dump the OIDs as well. To do this, use the -o command-line option.

-O seçeneği
Örnek ver

-s seçeneği
Açıklaması şöyle. Create Scheme, Create Table şeklinde sql cümleleri oluşturur.

dump only the schema, no data

Cümleler şunun gibidir

CREATE SCHEMA rdstest;
ALTER SCHEMA rdstest OWNER TO rdstest;

Örnek
Veri olmadan sadece tabloları oluşturmak için şöyle yaparız.

pg_dump -U postgres -n myschema -s mydatabase > schema.sql

-S seçeneği
Örnek ver

-t seçeneği
tablo ismini belirtir

Örnek

Şöyle yaparız

pg_dump -d postgres -t customer > /tmp/customer.sql

-T seçeneği
Örnek ver

-x seçeneği
Örnek ver

LEFT JOIN - İki Tablonun Kesişimi Yoksa Sağdaki Satırı Null Döner

Giriş

Join tiplerini görsel olarak gösteren resimler burada.

Şeklen şöyle.

Şeklen şöyle.

İki çeşit LEFT JOIN var.

1. LEFT JOIN

2. LEFT JOIN (IF NULL)

1. LEFT JOIN
Soldaki tablonun tamamını ve sağdaki tablonun kesişimini verir. Dolayısıyla soldaki tablo ile kesişimi olmayan satırlar için bazı sütunlar null gelir.

Söz dizimi şöyle. 

SELECT ... FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.company_id
WHERE ...

SELECT ile görmek istediğimiz sütunları belirtiriz. FROM kelimesinden sonra sol taraftaki tabloyu belirtiriz. LEFT OUTER JOIN kelimesinden sonra sağ taraftaki tabloyu belirtiriz. ON kelimesinden sonra birleştirme sütununu belirtiriz.

WHERE Koşulu
Açıklaması şöyle. Soldaki veya sağdaki tablo için where koşulu yazılabilir.

This ensures that the filtering occurs before the LEFT JOIN, not after which would completely change the data set returned.

Örnek
İki tablo birleşirken sağdaki tabloda kesişim yoksa satırlar null gelir. Şöyle yaparız.

X     |    Y    |   Z
----------------------
u1    |    abc1 |   dallas

u2    |    abc1 |   dallas

u3    |    abc2 |   erika

u4    |   NULL  |  NULL

Örnek
Şöyle yaparız. Where koşulu soldaki tablo üzerinde. Ayrıca LEFT JOIN ile and bile kullanılıyor

SELECT p.LastName,
        ft.TransactionAmount,
        ft.TransactionDate,
        ft.TransactionTime
FROM Personnel.Person AS p
    LEFT JOIN Finance.FinancialTransaction AS ft
        ON p.PersonID = ft.PersonID
        AND ft.TransactionAmount > 14
WHERE p.DateOfBirth < '1/1/1980';

Örnek

Şöyle yaparız. Where koşulu sağdaki tablo üzerinde

SELECT c.*
FROM company c
LEFT JOIN owner o
    ON c.id = o.company_id
WHERE
    o.company_id IS NULL OR    -- companies without owners
    o.verified IS NOT NULL;    -- companies with verified owners

2. LEFT JOIN (IF NULL)

Soldaki tablonun, sağdaki tablo ile kesişmeyen kısımlarını verir.

Aslında kesişmeyen kısımları bulmak için 3 tane yöntem var

1. LEFT JOIN (IF NULL)

2. NOT EXISTS

3. NOT IN

Örnek

Şöyle yaparız

-- LEFT JOIN IS NULL
SELECT s.name FROM students s
LEFT JOIN courses c ON s.student_id = c.student_id
WHERE c.student_id IS NULL;

-- NOT EXISTS
SELECT s.name FROM students s
WHERE NOT EXISTS (
    SELECT 1
    FROM courses c
    WHERE c.student_id = s.student_id
);

-- NOT IN
SELECT name FROM students
WHERE student_id NOT IN (
    SELECT student_id
    FROM courses
);

Örnek

Şöyle yaparız

SELECT from tableA A LEFT JOIN tableB B on A.key = B.key  WhERE B.key is NULL;