6

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.

  • 4
    your "efficient and smart" structure is called EAV. This has well known problems – Martin Smith Aug 2 at 19:53
  • 3
    @AdamBarnes incorrect datatypes (a person's date of birth should not be stored as a string). Difficulty enforcing mandatory attributes. Cumbersome queries (with multiple joins or cross tab aggregation/pivot) to present a person's attributes in a single row, Potential issues with cardinality estimates as the types of values are correlated with "field" – Martin Smith Aug 3 at 7:30
23

The syntax rules require at least one target column to be specified. If you want to let the defaults "kick in" for all columns, you can use the default values clause.

INSERT INTO people DEFAULT VALUES;

another alternative is to provide one column with the DEFAULT clause:

insert into people ("timestamp") values (default);
| improve this answer | |
4

One way of doing it is the following:

CREATE TABLE people
(
    id              bigserial,
    timestamp       timestamptz DEFAULT now() NOT NULL,
    PRIMARY KEY     (id)
);

and then:

INSERT INTO people (timestamptz) VALUES (NOW()) RETURNING id;

It appears that an INSERT clause has to have at least one value, although as you point out, it makes the DEFAULT NOW() in your table declaration redundant in this particular case!

It appears that you have found a (very) edge case bug in PostgreSQL (+1)!

Update:

Following @a_horse_with_no_name's stellar answer, for demonstration purposes, I've included his solutions into a new fiddle here.

A couple of points:

  • You should (for your own sanity) NEVER use SQL keywords as table or column names

  • Your EAV system (as pointed out by @Martin Smith) is flawed - if, say, you have a field called age_in_years - you can make it a SMALLINT and further add CHECK constraints (age_in_years >= 0 and <= 120), the optimizer can then (generally) generate FAR better plans for any queries involving that field - this applies across the board for virtually all fields, plus the fact (already mentioned) that your queries will become horribly cumbersome very quickly!

p.s. as usual, welcome to the forum!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy