Thread: Adding an extra boolean column to "information_schema.columns" or "pg_attribute"
I would like to add an extra boolean attribute to table columns, something like NULL. Unfortunately Pg does not support such a feature: ADD ATTRIBUTE <attribute_name> TYPE <type_of_attribute> TO COLUMN <schema.table.column_name>; For example: ADD ATTRIBUTE is_displayed TYPE boolean TO COLUMN world.human.feature; so that I can write: SELECT column_name FROM information_schema.columns WHERE table_schema = world AND table_name = 'human' AND is_displayed = 'Yes'; Please let me know if there would be any failure of an internal part or other real technical problem in case I add this extra column by hand and all relevant stuffs (index, maybe constraint) to either "information_schema.columns" or "pg_attribute". I have already been suggested to use VIEW or dynamic SQL but I am looking something different. Tia
Re: Adding an extra boolean column to "information_schema.columns"or "pg_attribute"
From
Karsten Hilbert
Date:
On Sat, Dec 30, 2017 at 08:17:34PM +0100, GPT wrote: > I would like to add an extra boolean attribute to table columns, > something like NULL. Unfortunately Pg does not support such a feature: > > ADD ATTRIBUTE <attribute_name> TYPE <type_of_attribute> TO COLUMN > <schema.table.column_name>; ... > I have already been suggested to use VIEW or dynamic SQL but I am > looking something different. Can you explain why ? One thing that comes to mind is complex user types. Or columns of type table. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Please visit the following link: https://stackoverflow.com/q/48028501/8895614 but thing may go further/deeper than it seems. Krds On 12/30/17, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > On Sat, Dec 30, 2017 at 08:17:34PM +0100, GPT wrote: > >> I would like to add an extra boolean attribute to table columns, >> something like NULL. Unfortunately Pg does not support such a feature: >> >> ADD ATTRIBUTE <attribute_name> TYPE <type_of_attribute> TO COLUMN >> <schema.table.column_name>; > ... >> I have already been suggested to use VIEW or dynamic SQL but I am >> looking something different. > > Can you explain why ? > > One thing that comes to mind is complex user types. > > Or columns of type table. > > Karsten > -- > GPG key ID E4071346 @ eu.pool.sks-keyservers.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > >
On Sat, 30 Dec 2017 20:33:28 +0100 GPT <gptmailinglists@gmail.com> wrote: > Please visit the following link: > > https://stackoverflow.com/q/48028501/8895614 > > but thing may go further/deeper than it seems. Adding columns to a built-in system table is liable to cause all sorts of problems in the future: when you upgrade, migrate to another server, need to create dev environments, etc. The view technique described in the stackunderwhelm article seems like a good solution to me. Maybe if you could explain why you are against it? > Krds > > On 12/30/17, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > > On Sat, Dec 30, 2017 at 08:17:34PM +0100, GPT wrote: > > > >> I would like to add an extra boolean attribute to table columns, > >> something like NULL. Unfortunately Pg does not support such a feature: > >> > >> ADD ATTRIBUTE <attribute_name> TYPE <type_of_attribute> TO COLUMN > >> <schema.table.column_name>; > > ... > >> I have already been suggested to use VIEW or dynamic SQL but I am > >> looking something different. > > > > Can you explain why ? > > > > One thing that comes to mind is complex user types. > > > > Or columns of type table. > > > > Karsten > > -- > > GPG key ID E4071346 @ eu.pool.sks-keyservers.net > > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > > > > -- Bill Moran
Re: Adding an extra boolean column to "information_schema.columns" or "pg_attribute"
From
Tom Lane
Date:
GPT <gptmailinglists@gmail.com> writes:
> I would like to add an extra boolean attribute to table columns,
The contents of the information_schema views are specified by the SQL
standard (ISO 9075). Since the entire point of having those views is to
have a standard-conformant way of accessing the information in the system
catalogs, adding nonstandard columns would rather defeat the purpose.
If you can persuade the relevant ISO standards committee that this is a
good idea, we will adopt the change when it appears in a published version
of that standard --- otherwise, it seems quite unlikely that we'd accept
a change to the view definition.
You'd probably be better off to define your own nonstandard view, IMO.
regards, tom lane
Future is always administerable especially when there are not problems in the present! The reasons I am not in favour of it: 1) Writing for second time the column names is a "repeat yourself" (that makes me really sick!), 2) The possibility of adding attributes into columns is a good feature (at least not bad one) which can help a lot, 3) Imagine that is_nullable (NULL attribute) did not exist. I am sure that you could survive; but how easier it makes your life, that now it does exist, 4) Some columns additional to the existing ones cost nothing when there is a good purpose, especially when there ara plenty of them in the vicinity which most of the times are empty but, of course, serving a good purpose! 5) Kind dispute is always a good resource of new ideas, 6)There is always a space of improvement and better solution (Ah, how haven't I thought it before?) especially in IT "art" sector. So, adding an extra boolean column to "information_schema.columns" or "pg_attribute" will cause any "fatal" problem in the present? Krgds On 12/30/17, PT <wmoran@potentialtech.com> wrote: > On Sat, 30 Dec 2017 20:33:28 +0100 > GPT <gptmailinglists@gmail.com> wrote: > >> Please visit the following link: >> >> https://stackoverflow.com/q/48028501/8895614 >> >> but thing may go further/deeper than it seems. > > Adding columns to a built-in system table is liable to cause all sorts of > problems in the future: when you upgrade, migrate to another server, need > to > create dev environments, etc. > > The view technique described in the stackunderwhelm article seems like > a good solution to me. Maybe if you could explain why you are against > it? > >> Krds >> >> On 12/30/17, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: >> > On Sat, Dec 30, 2017 at 08:17:34PM +0100, GPT wrote: >> > >> >> I would like to add an extra boolean attribute to table columns, >> >> something like NULL. Unfortunately Pg does not support such a feature: >> >> >> >> ADD ATTRIBUTE <attribute_name> TYPE <type_of_attribute> TO COLUMN >> >> <schema.table.column_name>; >> > ... >> >> I have already been suggested to use VIEW or dynamic SQL but I am >> >> looking something different. >> > >> > Can you explain why ? >> > >> > One thing that comes to mind is complex user types. >> > >> > Or columns of type table. >> > >> > Karsten >> > -- >> > GPG key ID E4071346 @ eu.pool.sks-keyservers.net >> > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 >> > >> > >> > > > -- > Bill Moran >
I do not have any intention to persuade anyone. The time runs faster than any committee! My question was very clear because I do not know the Pg's internal and I would like to zero the side (fatal) (if any) effect. So please, if you really know, tell me if there will be any problem in case I define my own nonstandard view. Krgds On 12/30/17, Tom Lane <tgl@sss.pgh.pa.us> wrote: > GPT <gptmailinglists@gmail.com> writes: >> I would like to add an extra boolean attribute to table columns, > > The contents of the information_schema views are specified by the SQL > standard (ISO 9075). Since the entire point of having those views is to > have a standard-conformant way of accessing the information in the system > catalogs, adding nonstandard columns would rather defeat the purpose. > If you can persuade the relevant ISO standards committee that this is a > good idea, we will adopt the change when it appears in a published version > of that standard --- otherwise, it seems quite unlikely that we'd accept > a change to the view definition. > > You'd probably be better off to define your own nonstandard view, IMO. > > regards, tom lane >