Re: [GENERAL] "Shared strings"-style table - Mailing list pgsql-general
On Fri, Oct 13, 2017 at 12:12 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Theoretically / blue sky, could there be a table or column type that
transparently handles "shared strings" like this, reducing size on disk
at the cost of lookup overhead for all queries?
(I guess maybe it's like TOAST, but content-hashed and de-duped and not
only for large objects?)Row-independence is baked into PostgreSQL pretty deeply...I think an enum type is about as close are you are likely to get if you don't wish to setup your own foreign-key relationships with surrogate keys.David J.
I STRONGLY advise againt the use of ENUMS.
What was described is exactly what relations and Foreign Keys are for.
Example:
CREATE TABLE residence_type
(
residence_type_id INTEGER NOT NULL,
residence_type_desc TEXT NOT NULL,
CONSTRAINT residence_type_pk PRIMARY KEY (residence_type_id)
);
CREATE TABLE state
(
state_id CHAR(02) NOT NULL,
state_name TEXT NOT NULL,
CONSTRAINT state_pk PRIMARY KEY (state_id)
);
CREATE TABLE residence
(
residence_id BIGINT NOT NULL,
residence_type_id INTEGER,
street_num CHARACTER(10),
street_name CHARACTER(20),
city CHARACTER(40),
state_id CHAR(02),
CONSTRAINT residence_pk PRIMARY KEY (residence_id),
CONSTRAINT fk_residence_state FOREIGN KEY (state_id)
REFERENCES state (state_id),
CONSTRAINT fk_residence_type FOREIGN KEY (residence_type_id)
REFERENCES residence_type (residence_type_id)
);
SELECT t.residence_type_desc,
r.street_num,
r.street_name,
r.city,
s.state_name
FROM residence r
JOIN residence_type t ON t.residence_id = r.residence_id
JOIN state s ON s.state_id = r.state_id
WHERE residence_id = 12345;
Example:
CREATE TABLE residence_type
(
residence_type_id INTEGER NOT NULL,
residence_type_desc TEXT NOT NULL,
CONSTRAINT residence_type_pk PRIMARY KEY (residence_type_id)
);
CREATE TABLE state
(
state_id CHAR(02) NOT NULL,
state_name TEXT NOT NULL,
CONSTRAINT state_pk PRIMARY KEY (state_id)
);
CREATE TABLE residence
(
residence_id BIGINT NOT NULL,
residence_type_id INTEGER,
street_num CHARACTER(10),
street_name CHARACTER(20),
city CHARACTER(40),
state_id CHAR(02),
CONSTRAINT residence_pk PRIMARY KEY (residence_id),
CONSTRAINT fk_residence_state FOREIGN KEY (state_id)
REFERENCES state (state_id),
CONSTRAINT fk_residence_type FOREIGN KEY (residence_type_id)
REFERENCES residence_type (residence_type_id)
);
SELECT t.residence_type_desc,
r.street_num,
r.street_name,
r.city,
s.state_name
FROM residence r
JOIN residence_type t ON t.residence_id = r.residence_id
JOIN state s ON s.state_id = r.state_id
WHERE residence_id = 12345;
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: