
> > Do we want the above syntax, or this syntax:
> >
> > ALTER TABLE blah ALTER COLUMN col SET NOT NULL;
> > ALTER TABLE blah ALTER COLUMN col SET NULL;
> 
> My only objection to the second command is that it's plain wrong.  You
> don't set anything to NULL, so don't make the command look like it.

Imho it would be nice if the command would look exactly like a create 
table. It is simply convenient to use cut and paste :-) And I haven't 
seen a keyword yet, that would make it more descriptive, certainly not SET.

ALTER TABLE blah ALTER [COLUMN] col [int4] [NOT NULL] [DEFAULT 32];
ALTER TABLE blah ALTER [COLUMN] col [int8] [NULL] [DEFAULT 32];
maybe even [DEFAULT NULL] to drop the default :-)

Andreas


Fri, 22 Mar 2002 15:42:16 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: SET NULL / SET NOT NULL
Zeugswetter Andreas SB SD wrote:
> 
> Imho it would be nice if the command would look exactly like a create
> table. It is simply convenient to use cut and paste :-) And I haven't
> seen a keyword yet, that would make it more descriptive, certainly not SET.
> 
> ALTER TABLE blah ALTER [COLUMN] col [int4] [NOT NULL] [DEFAULT 32];
> ALTER TABLE blah ALTER [COLUMN] col [int8] [NULL] [DEFAULT 32];
> maybe even [DEFAULT NULL] to drop the default :-)
> 

I like this one.  I would not make COLUMN optional though.

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Fri, 22 Mar 2002 11:54:19 -0500
Fernando Nasser <fnasser@redhat.com>
Re: SET NULL / SET NOT NULL
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> ALTER TABLE blah ALTER [COLUMN] col [int4] [NOT NULL] [DEFAULT 32];
> ALTER TABLE blah ALTER [COLUMN] col [int8] [NULL] [DEFAULT 32];

This cannot work unless you are prepared to turn a lot more keywords
into reserved words.  In the CREATE syntax, the data type is not
optional.  In the above, there will be parse conflicts because the
system won't be able to decide whether a type name is present or not.

You could possibly make it work if you were willing to include the word
TYPE when trying to respecify column type:

ALTER TABLE blah ALTER [COLUMN] col [TYPE int4] [NOT NULL] [DEFAULT 32];

Also I agree with Fernando that trying to make the word COLUMN optional
is likely to lead to conflicts.

			regards, tom lane

Fri, 22 Mar 2002 13:12:09 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET NULL / SET NOT NULL 
On Fri, Mar 22, 2002 at 01:12:09PM -0500, Tom Lane wrote:
> 
> Also I agree with Fernando that trying to make the word COLUMN optional
> is likely to lead to conflicts.

According to the docs, COLUMN is _already_ optional at that point.
Are the changes past that point going to cause different problems? Boy,
parsers make my brain hurt.

BTW, is NULLABLE so ugly that no one wanted to comment on it? It _is_
an sql92 reserved keyword, and it's actual english grammar.

Ross



Fri, 22 Mar 2002 13:00:37 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: SET NULL / SET NOT NULL
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> BTW, is NULLABLE so ugly that no one wanted to comment on it?

I kinda liked it, actually, if we were going to use the SET syntax.
But people seem to be focused in on this "let's make it look like
CREATE" notion.  I'm willing to wait and see how far that can be made
to work.

			regards, tom lane

Fri, 22 Mar 2002 14:07:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET NULL / SET NOT NULL 
On Fri, 2002-03-22 at 14:00, Ross J. Reedstrom wrote:
> BTW, is NULLABLE so ugly that no one wanted to comment on it? It _is_
> an sql92 reserved keyword, and it's actual english grammar.

FWIW, I liked it the best of all the solutions that have been proposed
so far.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC


22 Mar 2002 14:12:02 -0500
Neil Conway <nconway@klamath.dyndns.org>
Re: SET NULL / SET NOT NULL
Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> > BTW, is NULLABLE so ugly that no one wanted to comment on it?
> 
> I kinda liked it, actually, if we were going to use the SET syntax.
> But people seem to be focused in on this "let's make it look like
> CREATE" notion.  I'm willing to wait and see how far that can be made
> to work.

OK, how about:

	SET CONSTRAINT NOT NULL

or

	DROP CONSTRAINT NOT NULL

or simply:

	SET/DROP NOT NULL

I think the problem with trying to get it look like CREATE TABLE is that
the plain NULL parameter to CREATE TABLE is meaningless and probably
should never be used.  I remember at one point pg_dump output NULL in
the schema output and it confused many people. NOT NULL is the
constraint, and I think any solution to remove NOT NULL has to include
the NOT NULL keyword.  I think this is also why SET NULL looks so bad. 
"CREATE TABLE test (x int NULL)" doesn't look great either.  :-)  What
is that NULL doing there?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 22 Mar 2002 14:20:09 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SET NULL / SET NOT NULL
...
> "CREATE TABLE test (x int NULL)" doesn't look great either.  :-)  What
> is that NULL doing there?

Well, because NOT NULL *was* in the standard, and because one should be
able to explicitly negate *that*. The alternative was

  CREATE TABLE test (x int NOT NOT NULL)

:O

                    - Thomas

Fri, 22 Mar 2002 12:53:41 -0800
Thomas Lockhart <lockhart@fourpalms.org>
Re: SET NULL / SET NOT NULL
Thomas Lockhart wrote:
> ...
> > "CREATE TABLE test (x int NULL)" doesn't look great either.  :-)  What
> > is that NULL doing there?
> 
> Well, because NOT NULL *was* in the standard, and because one should be
> able to explicitly negate *that*. The alternative was
> 
>   CREATE TABLE test (x int NOT NOT NULL)
> 
> :O

Yea, what I meant is that NULL doesn't look too clear in CREATE TABLE
either.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 22 Mar 2002 16:24:49 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SET NULL / SET NOT NULL
> You could possibly make it work if you were willing to include the word
> TYPE when trying to respecify column type:
>
> ALTER TABLE blah ALTER [COLUMN] col [TYPE int4] [NOT NULL] [DEFAULT 32];
>
> Also I agree with Fernando that trying to make the word COLUMN optional
> is likely to lead to conflicts.

But all the other ALTER TABLE/Alter Column commands have it optional...

I have throught of at least two problems with changing nullability.  The
first is primary keys.  I have to prevent people setting a column involved
in a PK to null, right?

The second is DOMAINs - what if they change a NOT NULL domain in a colun
to NULL?  Shoudl I just outright prevent people from altering domain-based
columns nullability>

Chris


Sat, 23 Mar 2002 17:36:29 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: SET NULL / SET NOT NULL 
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> I have throught of at least two problems with changing nullability.  The
> first is primary keys.  I have to prevent people setting a column involved
> in a PK to null, right?

Probably so.

> The second is DOMAINs - what if they change a NOT NULL domain in a colun
> to NULL?  Shoudl I just outright prevent people from altering domain-based
> columns nullability>

I don't think you need worry about this.  The prototype DOMAIN
implementation is broken anyway --- it should not be transposing
domain constraints into column constraints, but should keep 'em
separate.  The column-level attnotnull setting should be independent
of whether the domain enforces not-nullness or not.

			regards, tom lane

Sat, 23 Mar 2002 12:17:41 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET NULL / SET NOT NULL 
> OK, how about:
>
> 	SET CONSTRAINT NOT NULL
>
> or
>
> 	DROP CONSTRAINT NOT NULL
>
> or simply:
>
> 	SET/DROP NOT NULL
>
> I think the problem with trying to get it look like CREATE TABLE is that
> the plain NULL parameter to CREATE TABLE is meaningless and probably
> should never be used.  I remember at one point pg_dump output NULL in
> the schema output and it confused many people. NOT NULL is the
> constraint, and I think any solution to remove NOT NULL has to include
> the NOT NULL keyword.  I think this is also why SET NULL looks so bad.
> "CREATE TABLE test (x int NULL)" doesn't look great either.  :-)  What
> is that NULL doing there?

OK, I've decided to go with:

ALTER TABLE blah ALTER [COLUMN] col SET NOT NULL;

and

ALTER TABLE blah ALTER [COLUMN] col DROP NOT NULL;

This is synchronous with the SET/DROP default stuff and is extensible in the
future to fit in with column type changing.

Of course, it can always be changed in the parser without affecting my code.

Chris


Mon, 25 Mar 2002 11:24:35 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: SET NULL / SET NOT NULL
> ALTER TABLE blah ALTER [COLUMN] col SET NOT NULL;
>
> and
>
> ALTER TABLE blah ALTER [COLUMN] col DROP NOT NULL;
>
> This is synchronous with the SET/DROP default stuff and is
> extensible in the
> future to fit in with column type changing.
>
> Of course, it can always be changed in the parser without
> affecting my code.

Also, in the future, once (if) the 'SET TYPE' column type changing function
has been implemented, we can create a meta-command to do it all in one
statement (for reliability and consistency for users).  It could look like
this:

ALTER TABLE blah ALTER [COLUMN] col [SET TYPE type] [{SET | DROP} NOT NULL]
[{SET | DROP} DEFAULT [default]]

And a command like this should be able to just re-use already written code.
However, some interdependency checks might be more efficient if their done
before any changes are actually made!  ie. Changing type to boolean and then
setting default to 'blah' in one statement, etc.

Chris


Mon, 25 Mar 2002 15:38:35 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: SET NULL / SET NOT NULL
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > I have throught of at least two problems with changing nullability.  The
> > first is primary keys.  I have to prevent people setting a
> column involved
> > in a PK to null, right?
>
> Probably so.

What about temporary tables - is there any reason they shouldn't be able to
modify a temporary table?

What about indices?  Will twiddling the nullability break indices on a table
in any way?

And foreign keys - foreign keys only have to reference UNIQUE, right?  The
nullability isn't an issue?

Lastly - in a multicolumn primary key, does EVERY column in the key need to
be NOT NULL?

Chris


Tue, 26 Mar 2002 12:33:33 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: SET NULL / SET NOT NULL 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> What about temporary tables - is there any reason they shouldn't be able to
> modify a temporary table?

I don't see one.

> What about indices?  Will twiddling the nullability break indices on a table
> in any way?

No, not as long as you aren't changing existing data in the table.

> And foreign keys - foreign keys only have to reference UNIQUE, right?  The
> nullability isn't an issue?

Not sure about that --- Stephan or Jan will know.

> Lastly - in a multicolumn primary key, does EVERY column in the key need to
> be NOT NULL?

Yes, I believe so.

			regards, tom lane

Mon, 25 Mar 2002 23:38:44 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET NULL / SET NOT NULL 

On Tue, 26 Mar 2002, Christopher Kings-Lynne wrote:

> > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > > I have throught of at least two problems with changing nullability.  The
> > > first is primary keys.  I have to prevent people setting a
> > column involved
> > > in a PK to null, right?
> >
> > Probably so.
>
> And foreign keys - foreign keys only have to reference UNIQUE, right?  The
> nullability isn't an issue?

That should be fine.

> Lastly - in a multicolumn primary key, does EVERY column in the key need to
> be NOT NULL?

Well, it looks like the primary key will not be satisfied if any of the
values are NULL.

In my SQL 92 draft, 11.7 Syntax Rules 3a says:
    If the <unique specification> specifies PRIMARY KEY, then let
    SC be the <search condition>:

                 UNIQUE ( SELECT UCL FROM TN )
                 AND
                 ( UCL ) IS NOT NULL




Mon, 25 Mar 2002 21:59:06 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: SET NULL / SET NOT NULL 