Re: Is this a buggy behavior?

On Mon, Mar 25, 2024 at 9:49 AM Christophe Pettus wrote: > > > > On Mar 25, 2024, at 02:50, Thiemo Kellner > wrote: > > My bad. I was under the impression that the create table statement was > an atomic process/transaction with all its bells and whistles for > constraints and keys, instead of a

Re: Is this a buggy behavior?

> On Mar 25, 2024, at 02:50, Thiemo Kellner wrote: > My bad. I was under the impression that the create table statement was an > atomic process/transaction with all its bells and whistles for constraints > and keys, instead of a succession of alter statements. That may be a bit judgmental.

Re: Is this a buggy behavior?

Am 25.03.2024 um 07:59 schrieb Laurenz Albe: On Sun, 2024-03-24 at 17:32 +0100, Thiemo Kellner wrote: How can that be forgotten? This information ends up in the data catalogue eventually! It *is* stored in the catalog. But if you add a primary key, that is tantamount to saying ALTER

Re: Is this a buggy behavior?

On Sun, 2024-03-24 at 17:32 +0100, Thiemo Kellner wrote: > > The reason it doesn't give you a warning is that by the time it would > > be in a position to, it's forgotten that you explicitly said NULL. > > How can that be forgotten? This information ends up in the data > catalogue eventually!

Re: Is this a buggy behavior?

On 2024-03-24 11:23:22 -0700, David G. Johnston wrote: > On Sun, Mar 24, 2024 at 11:14 AM Peter J. Holzer wrote: > It doesn't. Your statement > > > CREATE TABLE test1 > > ( > > c1 numeric   NULL , > > c2 varchar(36)  NOT NULL , > > CONSTRAINT test1_PK PRIMARY KEY (c1,c2)

Re: Is this a buggy behavior?

On Sun, Mar 24, 2024 at 11:14 AM Peter J. Holzer wrote: > > 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: > >

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

Re: Is this a buggy behavior?

Am 24.03.2024 um 17:43 schrieb Christophe Pettus: The situation is much more like the customer saying, "I understand that the standard paint for this car is red, but I wish it painted blue instead." Not in the least. Declaring the column to be NULL is explicitly requesting the car be blue.

Re: Is this a buggy behavior?

> On Mar 24, 2024, at 09:32, Thiemo Kellner wrote: > Am 24.03.2024 um 17:15 schrieb Christophe Pettus: >> I think the point is that it's not really doing anything "silently." You >> are asking for a PRIMARY KEY constraint on a column, and it's giving it to >> you. One of the effects (not

Re: Is this a buggy behavior?

Am 24.03.2024 um 17:15 schrieb Christophe Pettus: I think the point is that it's not really doing anything "silently." You are asking for a PRIMARY KEY constraint on a column, and it's giving it to you. One of the effects (not even really a side-effect) of that request is that the column

Re: Is this a buggy behavior?

On 3/24/24 08:28, Thiemo Kellner wrote: > Sure, my example has lots more side effect than silently do the right thing. I think the point is that it's not really doing anything "silently." You are asking for a PRIMARY KEY constraint on a column, and it's giving it to you. One of the effects

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:44 schrieb Andreas Kretschmer: postgres=# create table bla(i int null primary key); CREATE TABLE postgres=# \d bla     Table "public.bla"  Column |  Type   | Collation | Nullable | Default +-+---+--+-  i  | integer

Re: Is this a buggy behavior?

On 3/24/24 08:28, Thiemo Kellner wrote: Am 24.03.2024 um 16:17 schrieb Tom Lane: To do that, we'd have to remember that you'd said NULL, which we don't: the word is just discarded as a noise clause.  Considering that this usage of NULL isn't even permitted by the SQL standard, that seems like

Re: Is this a buggy behavior?

On 2024-03-24 16:28 +0100, Thiemo Kellner wrote: > Am 24.03.2024 um 16:17 schrieb Tom Lane: > > > To do that, we'd have to remember that you'd said NULL, which we > > don't: the word is just discarded as a noise clause. Considering > > that this usage of NULL isn't even permitted by the SQL

Re: Is this a buggy behavior?

Am 24.03.24 um 16:41 schrieb Thiemo Kellner: Am 24.03.2024 um 16:36 schrieb Andreas Kretschmer: the null-able constraint addition to a column is pointless because by default all columns are nullable. definition as a primary key adds the not null constraint. While this is certainly true,

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:35 schrieb sud: On Sun, Mar 24, 2024 at 8:47 PM Tom Lane > 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"

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:39 schrieb Erik Wienhold: And that's also possible in Postgres with UNIQUE constraints if you're looking for that behavior. Sort of the distinction between PK and UQ.

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:36 schrieb Andreas Kretschmer: the null-able constraint addition to a column is pointless because by default all columns are nullable. definition as a primary key adds the not null constraint. While this is certainly true, I do not see why the information that a not

Re: Is this a buggy behavior?

I wrote: > Do you come from sqlite? That allows NULL in primary key columns > without an explicit NOT NULL constraint. And that's also possible in Postgres with UNIQUE constraints if you're looking for that behavior. -- Erik

Re: Is this a buggy behavior?

Am 24.03.24 um 16:28 schrieb Thiemo Kellner: Am 24.03.2024 um 16:17 schrieb Tom Lane: To do that, we'd have to remember that you'd said NULL, which we don't: the word is just discarded as a noise clause. Considering that this usage of NULL isn't even permitted by the SQL standard, that

Re: Is this a buggy behavior?

On Sun, Mar 24, 2024 at 8:47 PM Tom Lane wrote: > Thiemo Kellner writes: > > Am 24.03.2024 um 15:54 schrieb Erik Wienhold: > >> This is required by the SQL standard: columns of a primary key must be > >> NOT NULL. Postgres automatically adds the missing NOT NULL constraints > >> when defining

Re: Is this a buggy behavior?

Am 24.03.2024 um 16:17 schrieb Tom Lane: To do that, we'd have to remember that you'd said NULL, which we don't: the word is just discarded as a noise clause. Considering that this usage of NULL isn't even permitted by the SQL standard, that seems like a bit too much work. If I understood

Re: Is this a buggy behavior?

Thiemo Kellner writes: > Am 24.03.2024 um 15:54 schrieb Erik Wienhold: >> This is required by the SQL standard: columns of a primary key must be >> NOT NULL. Postgres automatically adds the missing NOT NULL constraints >> when defining a primary key. You can verify that with \d test1 in psql.

Re: Is this a buggy behavior?

Am 24.03.2024 um 15:54 schrieb Erik Wienhold: This is required by the SQL standard: columns of a primary key must be NOT NULL. Postgres automatically adds the missing NOT NULL constraints when defining a primary key. You can verify that with \d test1 in psql. To me, this behaviour, while

Re: Is this a buggy behavior?

On 2024-03-24 15:25 +0100, sud wrote: > Create a table and composite primary key. But to my surprise it allowed me > to have the composite primary key created even if one of the columns was > defined as nullable. But then inserting the NULL into that column erroring > out at the first record

Is this a buggy behavior?

Hello All, Create a table and composite primary key. But to my surprise it allowed me to have the composite primary key created even if one of the columns was defined as nullable. But then inserting the NULL into that column erroring out at the first record itself , stating "not null constraint"