-- Sample table with 1 million rowsCREATE TABLE large_table (
id INT,
name VARCHAR(50),
address VARCHAR(100)
);
-- Insert 1 million rows INSERT INTO large_table
SELECT id,
concat('Name ', id),
concat('Address ', id)
FROM generate_series(1, 1000000) AS id;
attendance değerlerini günlük olarak görmek istersek şöyle yaparız. FROM içindeki SELECT cümlesinde attendance tablosundaki en küçük ve en büyük created_at değerleri alınıyor. Bu değerler generate_series()'e veriliyor. Bu çağrı sonucunda g g(days) ile days isimli bir tablo oluşturuluyor. Bu tablonun date isimli bir sütunu var. Dışarıdaki SELECT içinde bu sütuna erişiliyor.
SELECT
days::date AS created_date,
e.*
FROM (SELECT MIN(created_at), MAX(created_at) FROM attendance) AS r(startdate,enddate), generate_series(startdate::timestamp,enddate::timestamp,interval '1 day') g(days)
CROSS JOIN employee e
translate metodu - string + from + to
Açıklaması şöyle.
Any character in string that matches a character in the from set is replaced by the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are removed.
Örnek
Y yerine Z, Z yerine Y koymak için şöyle yaparız.
Giriş
Pattern matching veya full text search başlığı altına girer. Açıklaması şöyle. trgm kısaltması trigrams anlamına gelir.
The pg_trgmmodule provides functions and operators for determining the similarity of alphanumeric text based on trigram matching
trigram için açıklaması şöyle. Yani 3 karakter anlamına gelir.
Postgres provides a module called pg_trgm which ships different functions to work with trigrams matching. A trigram is a group of three consecutive characters taken from a string. Based on this we can measure the similarity of two strings by counting the number of trigrams they share and estimating how similar they are on a scale between 0 and 1. For example, the string “hello” would be represented by the following set of trigrams:
“ h”, “ he”, “hel”, “ell”, “llo”, “lo “
In order to make use of this module we need to enable it using the following statement:
CREATE EXTENSION pg_trgm;
pg_trm module için açıklama şöyle. Yani diğer araçlar kadar gelişmiş olmayabilir
PostgreSQL has a lot of advanced extensions and indexes to speed up the same. But one should keep in mind that these have their own limitations in terms of speed and might not work with languages other than English/multi-byte encoding, such as UTF-8. However, these fill the critical gap where the functionalities mentioned are necessary, but we cannot go for a full-fledged search solution such as Solr/Elasticsearch.
By now, we know that the tsvector is not a standard text column, and there is a lot of pre-processing that happens for easier search. We need to use a particular function called tsquery to search against the tsvector column.
The output as we see is simply true or false. In this case, note that we searched for the word scares, but still, the result was true. That was because of the normalization we saw earlier. The @@ operator is used for direct matches. We have other operators that perform more sophisticated matches.
CREATE TABLE IF NOT EXISTS PEOPLE (
ID VARCHAR(36) PRIMARY KEY,
NICKNAME VARCHAR(32) UNIQUE NOT NULL,
NAME VARCHAR(100) NOT NULL,
BIRTHDATE VARCHAR(10) NOT NULL,
STACK TEXT[],
SEARCH_TRGM TEXT
);
CREATE EXTENSION IF NOT EXISTS PG_TRGM;
CREATE INDEX CONCURRENTLY IF NOT EXISTS IDX_PEAPLE_SEARCH_TGRM
ON PEOPLE USING GIST (SEARCH_TRGM GIST_TRGM_OPS);
GIN Index Yaratma - Generalized Inverted Index
gin biraz daha hızlı bir Full Text Search index galiba.
ALTER TABLE storm_events ADD COLUMN episode_narrative_tsv tsvector;
UPDATE storm_events SET episode_narrative_tsv = to_tsvector(episode_narrative);
CREATE INDEX tsv_idx ON storm_events USING gin(episode_narrative_tsv);
The SIMILARITY function returns what is called a score. We can adjust this score according to the matches we need. The default score is 0.3 And if we are not worried about the score, then we can use the below shorthand.
SELECT * FROM storm_events WHERE event_type % 'Drot'
CREATE SEQUENCE todo_id_seq
START WITH 1
INCREMENT BY 10
NO MINVALUE
NO MAXVALUE CACHE 1;
CREATE TABLE t_todo
(
id BIGINT NOT NULL DEFAULT nextval('todo_id_seq'),
ext_ref CHARACTER VARYING(255) NOT NULL,
title CHARACTER VARYING(255) NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE (ext_ref),
UNIQUE (title)
);
One more thing…the % operator uses the default similarity threshold having a value of 0.3 but if we need to adjust it (e.g 0.5) we can use the SIMILARITY function:
Using the stuff built-in to your database is typically easier, e.g. Postgres lets you pick which columns and tables to encrypt, and their design only briefly has the keys and decrypted data on the server. Other databases are probably similar, but that's the only one I'm familiar with.
