Re: Is this a buggy behavior?

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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"