Weird
I'm baffled by the DDL in this StackExchange. That is, I don't care about the syntax error the O.P. is complaining about; I do care how/why there could be a table definition in which every column is defaulted. Shouldn't SQL reject a table declaration with all default-valued columns?
- What does it mean to insert a row with all-default values?
- How could a table value with a single all-default row be different from that same table empty?
- What could be the table's external predicate? (Yes the poster is a newbie, so probably doesn't know that tables are supposed to have external predicates, let alone how to frame one.)
Fortunately there's already enough responses telling the O.P. this the opposite of a "smart" design, let alone efficient. But none of them have nailed why IMO. I'm not going to join another forum just to tell them.
I've seen all-default tables used to record system heartbeats, where absence of an expected heartbeat within a specified time window typically implies some system failure.
E.g., something like:
CREATE TABLE heartbeats
(
timestamp timestamptz DEFAULT now() NOT NULL,
PRIMARY KEY (timestamp)
);
The predicate being, "A system heartbeat occurred at timestamp time."
As for whether it's valid or not, that's almost certainly implementation-dependent. Does any SQL implementation fully adhere to an official spec, and does the official spec preclude tables whose attributes all have a default?
Quote from AntC on August 3, 2020, 11:54 amI'm baffled by the DDL in this StackExchange. That is, I don't care about the syntax error the O.P. is complaining about; I do care how/why there could be a table definition in which every column is defaulted. Shouldn't SQL reject a table declaration with all default-valued columns?
- What does it mean to insert a row with all-default values?
- How could a table value with a single all-default row be different from that same table empty?
- What could be the table's external predicate? (Yes the poster is a newbie, so probably doesn't know that tables are supposed to have external predicates, let alone how to frame one.)
Fortunately there's already enough responses telling the O.P. this the opposite of a "smart" design, let alone efficient. But none of them have nailed why IMO. I'm not going to join another forum just to tell them.
I can't say that I agree with your interpretation. The ordinary default is a fixed single value, and it would indeed be hard to find any meaning for a table containing rows that are all exactly the same except for the system-allocated unique key. But this is not that case.
Here you have the DEFAULT keyword used to serve a different purpose: to insert a row as a time marker. Every row is different (down to the resolution of the timestamp) and every row satisfies the predicate "Row [id] was inserted at [timestamp]". Other tables could have an FK reference to [id], which would serve to synchronise them to the same timestamp.
I'm not defending the design and of course there are other ways to do it, but I do think it has a meaningful interpretation.
Quote from AntC on August 3, 2020, 11:54 amI'm baffled by the DDL in this StackExchange. That is, I don't care about the syntax error the O.P. is complaining about; I do care how/why there could be a table definition in which every column is defaulted. Shouldn't SQL reject a table declaration with all default-valued columns?
- What does it mean to insert a row with all-default values?
- How could a table value with a single all-default row be different from that same table empty?
- What could be the table's external predicate? (Yes the poster is a newbie, so probably doesn't know that tables are supposed to have external predicates, let alone how to frame one.)
Fortunately there's already enough responses telling the O.P. this the opposite of a "smart" design, let alone efficient. But none of them have nailed why IMO. I'm not going to join another forum just to tell them.
What does it mean to insert a row with all-default values? What could be the table's external predicate? If the fields are IDENTITY or GUID and CURRENT_TIME, then it means: there's a new widget that became relevant to the system at this particular time, and the system assigned this particular value to distinguish it from all other widgets.
How could a table value with a single all-default row be different from that same table empty? If the table's external predicate is "Month-end posting is in progress." Then when the table is empty, the value means: "Month-end posting is not in progress." With such a predicate the column values are completely irrelevant. All that matters is whether there is a row in the table or not.
Brian
I agree. The first case applies to every use of DEFAULT where not so-called default value is different each time it is applied. You chose 3 of them, but it's easy to imagine (for example) a default value for a password field, or a counter, or even just a random number, where each one is different and that meaning is captured in the predicate. If DEFAULT means a single fixed value for a column, there is no predicate to distinguish between rows; if not, there is.
The second case is just DEE vs DUM expressing a boolean value. The predicate is whatever you like, if it has a yes/no answer.