EAV in postgres

down.sql

DROP TABLE attributes_array_smallint;
DROP TABLE attributes_array_int;
DROP TABLE attributes_array_bigint;
DROP TABLE attributes_timestamp;
DROP TABLE attributes_inet;
DROP TABLE attributes_boolean;
DROP TABLE attributes_varchar_2;
DROP TABLE attributes_varchar_4;
DROP TABLE attributes_varchar_8;
DROP TABLE attributes_varchar_16;
DROP TABLE attributes_varchar_32;
DROP TABLE attributes_varchar_64;
DROP TABLE attributes_varchar_128;
DROP TABLE attributes_varchar_255;
DROP TABLE attributes_varchar_500;
DROP TABLE attributes_text;
DROP TABLE attributes_smallint;
DROP TABLE attributes_int;
DROP TABLE attributes_bigint;

up.sql

CREATE TABLE entities
(
    entity_type  VARCHAR(500),
    entity_id    BIGINT,
    date_created TIMESTAMP DEFAULT NOW(),
    date_updated TIMESTAMP DEFAULT NULL,
    PRIMARY KEY (entity_type, entity_id)
);
CREATE TABLE attributes_bigint
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value BIGINT DEFAULT NULL,
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_int
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value INT,
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_smallint
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value SMALLINT,
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_text
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value TEXT,
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_varchar_500
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value VARCHAR(500),
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_varchar_255
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value VARCHAR(255),
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_varchar_128
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value VARCHAR(128),
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_varchar_64
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value VARCHAR(64),
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_varchar_32
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value VARCHAR(32),
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_varchar_16
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value VARCHAR(16),
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_varchar_8
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value VARCHAR(8),
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_varchar_4
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value VARCHAR(4),
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_varchar_2
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value VARCHAR(2),
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_boolean
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value BOOLEAN,
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_inet
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value INET,
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_timestamp
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value TIMESTAMP,
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_array_bigint
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value BIGINT[],
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_array_int
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value INT[],
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);
CREATE TABLE attributes_array_smallint
(
    entity_type     VARCHAR(500) NOT NULL,
    entity_id       BIGINT       NOT NULL,
    attribute_name  VARCHAR(500) NOT NULL,
    attribute_value SMALLINT[],
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);

CREATE INDEX attributes_bigint_attribute_name ON attributes_bigint (attribute_name);
CREATE INDEX attributes_int_attribute_name ON attributes_int (attribute_name);
CREATE INDEX attributes_smallint_attribute_name ON attributes_smallint (attribute_name);
CREATE INDEX attributes_text_attribute_name ON attributes_text (attribute_name);
CREATE INDEX attributes_varchar_500_attribute_name ON attributes_varchar_500 (attribute_name);
CREATE INDEX attributes_varchar_255_attribute_name ON attributes_varchar_255 (attribute_name);
CREATE INDEX attributes_varchar_128_attribute_name ON attributes_varchar_128 (attribute_name);
CREATE INDEX attributes_varchar_64_attribute_name ON attributes_varchar_64 (attribute_name);
CREATE INDEX attributes_varchar_32_attribute_name ON attributes_varchar_32 (attribute_name);
CREATE INDEX attributes_varchar_16_attribute_name ON attributes_varchar_16 (attribute_name);
CREATE INDEX attributes_varchar_8_attribute_name ON attributes_varchar_8 (attribute_name);
CREATE INDEX attributes_varchar_4_attribute_name ON attributes_varchar_4 (attribute_name);
CREATE INDEX attributes_varchar_2_attribute_name ON attributes_varchar_2 (attribute_name);
CREATE INDEX attributes_boolean_attribute_name ON attributes_boolean (attribute_name);
CREATE INDEX attributes_inet_attribute_name ON attributes_inet (attribute_name);
CREATE INDEX attributes_timestamp_attribute_name ON attributes_timestamp (attribute_name);
CREATE INDEX attributes_array_bigint_attribute_name ON attributes_array_bigint (attribute_name);
CREATE INDEX attributes_array_int_attribute_name ON attributes_array_int (attribute_name);
CREATE INDEX attributes_array_smallint_attribute_name ON attributes_array_smallint (attribute_name);