On 2024-03-24 21:05:04 +0530, sud wrote:
> Do you specifically mean that 'null' keyword is just not making any sense here
> in postgres. But even if that is the case , i tried inserting nothing (hoping
> "nothing" is "null" in true sense),
This is a strange hope.
> but then too it failed in the first statement while inserting which is
> fine as per the PK.
>
> But don't you think,in the first place it shouldn't have been allowed to create
> the table with one of the composite PK columns being defined as NULL.
It doesn't. Your statement
> CREATE TABLE test1
> (
> c1 numeric NULL ,
> c2 varchar(36) NOT NULL ,
> CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> ) ;
creates the table with both columns being defined as NOT NULL:
hjp=> CREATE TABLE test1
(
c1 numeric NULL ,
c2 varchar(36) NOT NULL ,
CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
) ;
CREATE TABLE
Time: 16.815 ms
hjp=> \d test1
Table "hjp.test1"
╔════════╤═══════════════════════╤═══════════╤══════════╤═════════╗
║ Column │ Type │ Collation │ Nullable │ Default ║
╟────────┼───────────────────────┼───────────┼──────────┼─────────╢
║ c1 │ numeric │ │ not null │ ║
║ c2 │ character varying(36) │ │ not null │ ║
╚════════╧═══════════════════════╧═══════════╧══════════╧═════════╝
Indexes:
"test1_pk" PRIMARY KEY, btree (c1, c2)
> And then , while inserting the null record, it should say that the PK
> constraint is violated but not the "not null constraint" violated.
That may just be an artifact of the implementation. You can check
whether a value to be inserted is null or not without searching the
table, so that is done first. Only then you have to check the index for
a possible duplicate value, so that's done later.
But as a user I actually prefer it that way. The more precisely the
database can tell me why the insert failed, the better.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"