CREATE DOMAIN mydomain AS int;
CREATE TABLE foo(bar) AS SELECT 42::mydomain;
SELECT f1.bar AS f1, f2.bar AS f2, pg_typeof(f1.bar), pg_typeof(f2.bar)
FROM foo AS f1
LEFT JOIN foo AS f2
ON false; f1 | f2 | pg_typeof | pg_typeof
----+----+-----------+-----------
42 | | mydomain | mydomain
CREATE TYPE order_status AS ENUM(
'Ordered',
'Baking',
'Delivering',
'YummyInMyTummy');
CREATE TABLE pizza_order (
id INT PRIMARY KEY,
status order_status NOT NULL,
order_time TIMESTAMP NOT NULL DEFAULT now()
);Şu SQL çalışır, çünkü status tipi olarak CREATE TYPE ile belirtilen bir string verdik> INSERT INTO pizza_order (id, status, order_time)
VALUES (1, 'Ordered', now());
CREATE CAST (varchar AS order_status) WITH INOUT AS IMPLICIT;CREATE TYPE address AS (
city TEXT,
address_line TEXT,
zip_code INT
);CREATE DOMAIN address_domain AS address
check (
(value).city is not null and
(value).address_line is not null and
(value).zip_code is not null
);> CREATE TABLE test_address_domain (a address_domain);
CREATE TABLE
> INSERT INTO test_address_domain VALUES (('foo', 'bar', 11));
INSERT 0 1
> INSERT INTO test_address_domain VALUES (('foo', 'bar', null)); -- fails
ERROR: value for domain address_domain violates check constraint "address_domain_check"The time returned by NOW(), and other date time functions, is derived from the start time of the query.
Default TimestampsIt is always better to have the two timestamptz fields created_at and updated_at columns with default now() .... Storing what time a record was created or modified would be very useful in the future when going over some analytics or reporting. Audit logs might be required and timestamps are key.
MariaDB [test]> select now(),sleep(10),now();
+---------------------+-----------+---------------------+
| now() | sleep(10) | now() |
+---------------------+-----------+---------------------+
| 2021-03-22 14:17:05 | 0 | 2021-03-22 14:17:05 |
+---------------------+-----------+---------------------+
Unlike SQL Server, MySQL does not have a distinct JOIN type for FULL OUTER JOIN. You may, however, combine LEFT OUTER JOIN and RIGHT OUTER JOIN to get the same effects as FULL OUTER JOIN.
SELECT * FROM tableALEFT JOIN tableBON tableA.id = tableB.idUNIONSELECT * FROM tableARIGHT JOIN tableBON tableA.id = tableB.id
SELECT FROM tableA a FULL OUTER JOIN tableB b ON a.key = b.key
-- Retrieve all employees and department names, including those -- without a department and departments without employees SELECT employees.employee_name, departments.department_name FROM employees FULL JOIN departments ON employees.department_id = departments.department_id;
SELECT FROM tableA a FULL OUTER JOIN tableB b on a.key = b.key
WHERE a.key IS NULL OR b.key IS NULL
2. RIGHT JOIN (IF NULL)SELECT from tableA A RIGHT JOIN tableB B on A.key = B.key
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ızcreate 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ı şöyleThe 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'].
begin; alter table users drop constraint roles_check; alter table users add constraint roles_check check (roles <@ array['super', 'admin', 'user']); end;
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.
ÖrnekCREATE TABLE event (id INT8 NOT NULL,version INT4,sensor_names TEXT[],sensor_values INTEGER[],PRIMARY KEY (id))
CREATE TABLE product (id BIGINT,images TEXT[])INSERT INTO product(id, images) VALUES (1, '{"url1", "url2", "url3"}');SELECT * FROM product WHERE images @> ARRAY['url2']
select 1 from t_book where (select pg_sleep(1)) is not null limit 3;