Adding fields on the fly

[Gail Zacharias]

I am investigating the possibility of using pgsql as the database in an application. I have some unusual requirements that I'd like to ask you all about. I apologize in advance if my terminology is a little "off", I'm not familiar with pgsql (yet).

My first requirement is that I need to be able to add new columns to any database table on the fly. By this I mean that I need this to happen interactively -- a user will drag some UI widget from here to there and boom! my application will request a new column to be added to a table, and wait for it to happen before giving visual feedback to the user. My questions are:

Is this even possible? Is ALTER TABLE supported?
If so, what performance characteristics can I expect? Compared to, say, inserting a record, can I expect this to be 10 times slower? 1000 times slower?
What does the performance depend on? Number of rows? Number of columns? Number of tables? anything else?
I'll send my other question in a separate message to keep the threads separate.

Thanks in advance,

Gail Zacharias
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Greg Patnude]

Its in the docs.... Note that you will have to add the column and the
column constraints in two steps --

-- And -- When adding fields to an existing table the new field contains
null -- so you probably don't want to specify "not null" or you'll have to
go back and modify all of your "INSERT" statements on that table...

Additionally -- Unless you have a really smart application parsing
catalogs -- you'll either need to keep track of the new columns so you can
retrieve the data in them or SELECT * FROM mytable and have your application
sift through the fields...

ALTER TABLE mytable ADD COLUMN mynewcolumn datatype;
ALTER TABLE mytable ALTER COLUMN mynewcolumn SET default "TRUE";

Another trick I use when allowing users to dynamically (and usually
unwittingly) add columns to a table is to specify a comment on the column:

COMMENT ON mytable.mynewco lumn IS 'user-defined';

If you add a comment -- you can find the user-added columns in
pg_description

--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
[phone]

"Gail Zacharias" <[e-mail] > wrote in message
news:5.******** *************** ********@127.0. 0.1...

I am investigating the possibility of using pgsql as the database in an application. I have some unusual requirements that I'd like to ask you all
about. I apologize in advance if my terminology is a little "off", I'm not
familiar with pgsql (yet).
My first requirement is that I need to be able to add new columns to any database table on the fly. By this I mean that I need this to happen
interactively -- a user will drag some UI widget from here to there and
boom! my application will request a new column to be added to a table, and
wait for it to happen before giving visual feedback to the user. My
questions are:
Is this even possible? Is ALTER TABLE supported?
If so, what performance characteristics can I expect? Compared to, say, inserting a record, can I expect this to be 10 times slower? 1000 times
slower? What does the performance depend on? Number of rows? Number of columns? Number of tables? anything else?

I'll send my other question in a separate message to keep the threads separate.
Thanks in advance,

Gail Zacharias
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Peter Eisentraut]

Gail Zacharias writes:

Is this even possible? Is ALTER TABLE supported?
Yes. (RTFM)
If so, what performance characteristics can I expect? Compared to, say,
inserting a record, can I expect this to be 10 times slower? 1000 times
slower? What does the performance depend on? Number of rows? Number of
columns? Number of tables? anything else?

Adding a column runs in constant time.

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings