22 Haziran 2018 Cuma

PostgreSQL

Sütun Tipleri
bigint
Şöyle yaparız.
CREATE TABLE category
(
  id bigserial NOT NULL PRIMARY KEY,
  ...
)
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
location
Şöyle yaparız.
CREATE TABLE tweets (
  id bigint,
  location point
)
oid
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.
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
Açıklaması şöyle.
Serial is just syntactic sugaring on top of an int column that takes its value from a sequence.
Şö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 );
text
Şöyle yaparız.
CREATE TABLE category
(
  name text NOT NULL,
  ...
)
TIMESTAMP WITH TIME ZONE
Saat dilimi bilgisini kullanarak saati UTC saatine çevirir ve saat dilimi bilgisini saklamaz.
TIMESTAMP WITHOUT TIME ZONE
Saatin yerel saat olduğunu varsayar. Şöyle yaparız.
CREATE TABLE reference_values
(
  created_on timestamp without time zone,
  ...
)
Fonksiyonlar
Copy
CSV dosyasını tabloya yüklemek için şöyle yaparız.
COPY Customer
FROM 'C:\Users\Public\Vaseis\Customer.txt' DELIMITER ',' CSV HEADER;
Date
String'den date döndürür. Şöyle yaparız.
UPDATE temp
   SET temp_date = temp_date - (date_trunc('year', temp_date ) - date '2016-1-1')
Date_trunc
Date'in belirtilen kısmını döndürür. Şöyle yaparız.
UPDATE temp
   SET temp_date = temp_date - (date_trunc('year', temp_date ) - date '2016-1-1')
Explain
Şöyle yaparız.
EXPLAIN ANALYZE
CREATE TABLE electrothingy
AS
  SELECT
    x::int AS id,
    (x::int % 20000)::int AS locid,  -- fake location ids in the range of 1-20000
    now() AS tsin,                   -- static timestmap
    97.5::numeric(5,2) AS temp,      -- static temp
    x::int AS usage                  -- usage the same as id not sure what we want here.
  FROM generate_series(1,1728000000) -- for 1.7 billion rows
    AS gs(x);
Having
Having'in uygulanma sırası şöyle.
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
Açıklaması şöyle.
The HAVING clause always applies to aggregated fields, which is all remaining columns post-aggregation.
Dolayısıyla şu iki cümle farklı şeylerdir.
SELECT id, filedate, SUM(amount)
FROM Sales
GROUP BY id, filedate
HAVING id = 123 AND filedate = '1/1/2018'
ve
SELECT id, filedate, SUM(amount)
FROM Sales
WHERE id = 123 AND filedate = '1/1/2018'
GROUP BY id, filedate
Insert Into
Şöyle yaparız.
INSERT INTO answer (userans, username)
  SELECT 'OptionA', username FROM answer WHERE username = 'Some Name';
Interval
String'den date döndürür. Şöyle yaparız.
UPDATE temp SET temp_date = temp_date - interval '2 years'
WHERE EXTRACT (YEAR FROM temp_date) = 2018;
Partition By 
Örnek
Elimizde şöyle bir tablo olsun
id integer | num integer
___________|_____________
1          | 4 
2          | 4
3          | 5
4          | 6
Gruplamak için şöyle yaparız.

SELECT id, COUNT(id) OVER(PARTITION BY num) from test;

id         | count
___________|_____________
1          | 2 
2          | 2
3          | 1
4          | 1
Similar To
Like cümlelerini or'lamak yerine
WHERE state LIKE '%idle%' OR state LIKE '%disabled%'
Şöyle yaparız
WHERE state SIMILAR TO '%(idle|disabled)%'
to_char metodu
Tarihin yıl alanını almak için şöyle yaparız.
select to_char(now(),'YYYY')
UNION ALL
Örnek
Elimizde şöyle bir tablo olsun.
| id  | ad1    | ad2   | ad3    |
| ----|--------| ------| -------|
| 1   |val_1   |NULL   | NULL   |
| 2   |val_2   |val_1  | NULL   |
| 3   |val_3   |NULL   | val_3  |
Tüm sütunlardaki değerleri saymak için şöyle yaparız.
select t.value, count(*)
from (
   select ad1 value from ad_convention
   union all
   select ad2 value from ad_convention
   union all
   select ad3 value from ad_convention
) t
group by t.value
Çıktı olarak şunu alırız.
| value   | times |
| --------| ------|
| val_1   | 2     |
| val_2   | 1     |
| val_3   | 2     |
Trigger
Trigger'ı tabloya eklemek için şöyle yaparız.
CREATE TRIGGER mytrigger_name AFTER UPDATE ON mytable_name
 FOR EACH ROW EXECUTE PROCEDURE mytrigger();
Örnek
Şöyle yaparız
CREATE OR REPLACE FUNCTION prevent_deletion() 
RETURNS trigger AS $$
BEGIN
  IF current_user != 'user1' THEN
    RAISE EXCEPTION 'You cannot delete records from this table!';
  END IF;  
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;
Örnek
Şöyle yaparız.
CREATE OR REPLACE FUNCTION table_update_notify() RETURNS trigger AS $$
DECLARE
  id bigint;
BEGIN
  IF TG_OP = 'INSERT' THEN
    id = NEW.id;
  ELSE
    id = OLD.id;
  END IF;
  PERFORM pg_notify('my_trigger_name',
    json_build_object('table', TG_TABLE_NAME, 'id', id, 'type', TG_OP)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Spatial Özellik
ST_DISTANCE
İki nokta arasındaki mesafeyi şöyle buluruz.
SELECT ST_Distance(ST_GeomFromText('POINT(27.185425 88.124582)',4326),
 ST_GeomFromText('POINT(27.1854258 88.124500)', 4326));