PostgreSQL создание, удаление, изменение таблиц

Создание таблицы:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

Значение по умолчанию при создании таблиц:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 9.99
);

Значением по умолчанию может быть выражение, которое будет оцениваться при вставке значения по умолчанию (а не при создании таблицы). Типичным примером является то, что timestampстолбец имеет значение по умолчанию CURRENT_TIMESTAMP, поэтому для него устанавливается время вставки строки.

Или может потребоваться генерация серийного номера, например так:

CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'),
    ...
);

или так:

CREATE TABLE products (
    product_no SERIAL,
    ...
);

NOT NULL – проверка на заполенность:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

Проверка на уникальность:

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

Уникальные и заполненные

Можно записать так:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

Или так:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Ссылки на другие таблицы

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

Ссылки на другие таблицы, отношение многие ко многим

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Запрещение удаления если есть ссылки и каскадное удаление

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

RESTRICT – запрещает удаление, если есть ссылки

CASCADE – каскадное удаление, и ссылающихся

Удаление таблицы

DROP TABLE products;

Также можно использовать DROP TABLE IF EXISTS – для того чтобы не выбрасывалась ошибка, если базы не существует.

Изменение таблиц

Добавление столбца

ALTER TABLE products ADD COLUMN description text;

Удаление столбца

ALTER TABLE products DROP COLUMN description;

Однако если на столбец есть ссылки, тогда он не будет удален, можно использовать CASCADE и для удаления всех ссылок

ALTER TABLE products DROP COLUMN description CASCADE;

Добавление ограничений столбцу

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

Удаление ограничений у столбца

ALTER TABLE products DROP CONSTRAINT some_name;

Чтобы удалить не нулевое ограничение:

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

Изменение значения по умолчанию у столбца

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Для удаления значения по умолчанию у столбца:

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

Изменение типа данных столбца

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

Переименование столбца

ALTER TABLE products RENAME COLUMN product_no TO product_number;

Переименование таблицы

ALTER TABLE products RENAME TO items;

Пример создания таблицы users из Node.js

const db = require('./db');

let zapros = 'CREATE TABLE IF NOT EXISTS users (id integer PRIMARY KEY, email text UNIQUE, firstName text, lastName text, auth_platform text)';

db.query(zapros, [], (err, res) => {
  if (err) {
    console.log('err:');
    console.log(err);
  } else {
    console.log('res:');
    console.log(res);
  }

})

Информационная схема

В базе PostgreSQL есть информационная схема и в ней несколько таблиц.

Ниже опишу некоторые из них самые важные:

tables – позволяет получить список таблиц

например такой запрос:

SELECT * FROM information_schema.tables  WHERE table_schema='public'

Вернет все добавленные таблицы, без служебных таблиц posgres