Re: [HACKERS] SET NULL / SET NOT NULL
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SET NULL / SET NOT NULL
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SET NULL / SET NOT NULL
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] SET NULL/SET NOT NULL
seems like other systems keep very similar syntax to the CREATE TABLE command. i.e. ALTER TABLE blah ALTER COLUMN col datatype (precision.scale) NULL ALTER TABLE blah ALTER COLUMN col datatype (precision.scale) NOT NULL Dwayne ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SET NULL / SET NOT NULL
On March 22, 2002 01:31 am, Peter Eisentraut wrote:
Christopher Kings-Lynne writes:
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.
How about this?
ALTER TABLE blah ALTER COLUMN col UNSET NOT NULL;
I would almost think that it should be NOTNULL anyway to make it clear that we
are setting (or unsetting) one thing and that it is not a weird way of saying
...NOT SET NULL or NOT UNSET NULL but I realize that it should also look
more like the NOT NULL clause we already have in the CREATE TABLE query.
--
D'Arcy J.M. Cain darcy@{druid|vex}.net | Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] SET NULL / SET NOT NULL
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SET NULL / SET NOT NULL
Christopher Kings-Lynne writes: So then how is it any more wrong than SET NOT NULL? You're right. It should almost be ADD NOT NULL ... I like that. It also makes sense because the standard syntax is to ADD/DROP CHECK constraints, to which NOT NULL constraints are equivalent. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] SET NULL / SET NOT NULL
Zeugswetter Andreas SB SD [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SET NULL / SET NOT NULL
Ross J. Reedstrom [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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 [EMAIL PROTECTED] PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SET NULL / SET NOT NULL
Tom Lane wrote: Ross J. Reedstrom [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] SET NULL / SET NOT NULL
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Say in the future we want to support changing column type as well. How would we work that in? ALTER TABLE blah ALTER COLUMN col SET int4; ?? Seems one keyword shy of a load; I'd prefer ALTER TABLE blah ALTER COLUMN col SET TYPE int4; Otherwise, every keyword that might appear after SET will have to be fully reserved (else it couldn't be distinguished from a type name). I like the SET NULL/SET NOT NULL variant better than SET/DROP, even though SET NULL is perhaps open to misinterpretation. DROP NOT NULL seems just as confusing for anyone who's not read the documentation :-( regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SET NULL / SET NOT NULL
Seems one keyword shy of a load; I'd prefer ALTER TABLE blah ALTER COLUMN col SET TYPE int4; Otherwise, every keyword that might appear after SET will have to be fully reserved (else it couldn't be distinguished from a type name). I like that... So would you then envisage something like this: ALTER TABLE blah ALTER COLUMN col SET TYPE int4 DEFAULT 3 NOT NULL; or ALTER TABLE blah ALTER COLUMN col SET DEFAULT 3 TYPE int4 NULL; etc. ie. Order wouldn't matter and you could do them all at once for convenience? This seems like a cool idea to me. Problem with all this, of course, is that it's different to everyone else's syntax, but then they're all different to each other. There's no standard for it, but if there's a new standard - I wonder what they would specify? Since altering a column is a not oft used operation, I would expect that the punters wouldn't have a problem looking in the docs for how to do it, for each different DBMS they use... Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SET NULL / SET NOT NULL
Christopher Kings-Lynne writes: 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. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SET NULL / SET NOT NULL
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: Say in the future we want to support changing column type as well. How would we work that in? ALTER TABLE blah ALTER COLUMN col SET int4; ?? Seems one keyword shy of a load; I'd prefer ALTER TABLE blah ALTER COLUMN col SET TYPE int4; Otherwise, every keyword that might appear after SET will have to be fully reserved (else it couldn't be distinguished from a type name). I like the SET NULL/SET NOT NULL variant better than SET/DROP, even though SET NULL is perhaps open to misinterpretation. DROP NOT NULL seems just as confusing for anyone who's not read the documentation :-( Yes, DROP NOT NULL does have a weird twist to it. However, does SET NULL sound to much like you are setting all the values to NULL? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] SET NULL / SET NOT NULL
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. So then how is it any more wrong than SET NOT NULL? It should almost be ADD NOT NULL ... Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SET NULL / SET NOT NULL
On Fri, Mar 22, 2002 at 02:34:57PM +0800, Christopher Kings-Lynne wrote: 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. So then how is it any more wrong than SET NOT NULL? It should almost be ADD NOT NULL ... Hmm, there's this SQL92 keyword here: what do people thing of NULLABLE? SET NOT NULLABLE SET NULLABLE Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
