I have this:
CREATE TABLE people ( id bigserial, timestamp timestamptz DEFAULT now() NOT NULL, PRIMARY KEY (id) ); CREATE TABLE "personal information" ( id bigserial, "person id" bigint NOT NULL, timestamp timestamptz DEFAULT now() NOT NULL, "data's timestamp" timestamptz, "field" text NOT NULL, "value" text, PRIMARY KEY (id), FOREIGN KEY ("person id") REFERENCES people (id) ON UPDATE CASCADE ON DELETE CASCADE ); INSERT INTO people RETURNING id; -- Syntax error. INSERT INTO people; -- Syntax error.
Why do my INSERTS fail? It's a fact that all the columns in the "people" table are automatic: the
id column is a bigserial, and the
timestamp one is always set to the current time when it was inserted. Thus, there is no need for me to specify any columns to be inserted, as all of them are automatic.
So why is it a syntax error? What does it want me to do? This makes me feel as if I'm not doing this right somehow, but having a bunch of "personal data" fields in the "people" table would be redundant since that data will be stored in the "personal information" table. For example, the "field" might be "social security number" or "first name", so I just grab whatever record is the latest (based on timestamp) for that "person id" to get their social security number or first name.
If I were to store those values as columns in "people", it would be pointless redundancy, and it would make it a major PITA whenever I introduce a new kind of "personal information field" in the future, as I'd have to add columns to the "people" table. So I really hope that my efficient and smart (IMO) structure is not somehow incompatible with PostgreSQL, and that I'm just missing some minor syntax detail.