Разбор на примерах некоторых возможностей появившихся в PostgreSQL 7.4. Кратко, главное:
-
Определение типов данных используя "CREATE DOMAIN":
CREATE DOMAIN ZIP_CODE CHAR(5) NOT NULL
DEFAULT '00000'
CHECK ( VALUE ~ '^[0-9]{5}$'
AND ( ( VALUE = '00000' ) OR
( VALUE BETWEEN '00210' AND '99950'
) ) );
CREATE TABLE contact_addresses (
contact_id INT NOT NULL
REFERENCES contacts(contact_id),
...
state STATE_CODE,
zip ZIP_CODE );
-
Вычисляемые индексы:
CREATE INDEX idx_compensation
ON personnel(
(salary + bonus + commission) );
Оптимально для SELECT в котором содержится (salary + bonus + commission)
-
SQL 99 массивы
:
SELECT ARRAY[2 * 3, 4 * 5, 6 * 7];
SELECT ARRAY[unique1, unique2, even]
SELECT ARRAY['hello world','happy birthday'];
-
Полнотекстовые индексы через подсистему Tsearch2
:
ALTER TABLE companies
ADD COLUMN fti_business tsvector;
UPDATE companies SET fti_business = to_tsvector('default',business_model);
VACUUM FULL ANALYZE companies;
CREATE INDEX idx_fti_business ON companies USING gist(fti_business);
CREATE TRIGGER tg_fti_companies
BEFORE UPDATE OR INSERT ON companies
FOR EACH ROW EXECUTE PROCEDURE
tsearch2(fti_business, business_model);
SELECT company_name, business_model
FROM companies
WHERE fti_business @@
to_tsquery('default','bushing | engine');
URL: http://www.linux-mag.com/2004-02/postgresql_01.html
Новость: https://www.opennet.ru/opennews/art.shtml?num=3801