[GENERAL] Add/Remove Columns
>>> Jackson, DeJuan wrote: >>> > > Martin Schulze wrote: >>> > > > . I wonder how one could add or remove columns from existing tables. >>> > > > >>> > > > With mSQL this was possible with a trick. You had to dump the >>> > > > whole table but you could tell the dump program to add dummy fields >>> > > > or to leave out existing fields. Is there any such possibility >>> > > > with PostgreSQL? >>> >>> > You can do a select into a temp table with the added columns, drop the >>> > old table the rename the temp table to the old name. You'll also have >>> > to recreate your indexes, triggers, and rules. >>> >>> I understand. Looks like PostgreSQL is more preconceived than >>> other databases. I figure out how to do this, though. I you would >>> have an example laying around I'd be very happy receiving it. Why don't you just use ALTER TABLE, eg: ALTER TABLE ADD COLUMN colname check (colname <= 1) and (colname >= 0) or something similar. If the column is a NOT NULL column, then you must have a DEFAULT option, otherwise, add it initially without the NULL option, populate it, and then ALTER TABLE ALTER COLUMN including the null option. Of course for removing columns you ALTER TABLE DELETE COLUMN, or possibly REMOVE COLUMN, I can't remember. MikeA
[GENERAL] System admin
Is there a good guise for system admin? I am stumped on the most mundane problem right now: how do I set user passwords in postgresql? When I createuser I never get prompted for a password, when I run psql with no parameters I get right in, but if I run: "psql -u someone" I get a password prompt - also perl/DBI looks like it wants a password. -- John Guthrie -BEGIN PGP PUBLIC KEY BLOCK- Version: 2.6.2 mQCNAzO9DYwAAAEEALqNTjAdKq5+cGdGTI14O4mfXeL++zZYZ1Ybz9CpQTI+NXWV Y6gy5PzKyCs0T2vlu/NAl4YG8kP2Cj95OSet1e4HFzZMeqryTCu/hwuusifo36DR hGjWqyeNhvhGAm6Sux+xo84ridtCC0u5kilzxswP4RdMz73XHSNa1L76oY0RAAUR tCJKb2huIEQuIEd1dGhyaWUgPGpndXRocmllQGFjbS5vcmc+ =lC2G -END PGP PUBLIC KEY BLOCK-
Re: [GENERAL] Re: More PostgreSQL stuff
On Fri, 2 Oct 1998, Oliver Elphick wrote:
> Copied to PostgreSQL lists, in the hope of comments from the experts...
>
> Martin Schulze wrote:
> >Is there a way to speed up postgres? I'm converting one of my
> >major apps from mSQL to PostgreSQL and PostgreSQL is at least three
> >times slower. That's horrible. With this slowlyness I cannot
> >install PostgreSQL in the office but only at home.
> >
> >So, is there a way to speed it up? I have turned off debugging
> >since I hoped that it was the reason for the slowliness but
> >apparently it isn't.
What version of PostgreSQL is being used? Each one has gotten
progressively more efficient/faster. Also, check out the -B and -S
options...one allows you to increase the SHM_* Buffers used, so that more
'data' gets cached to RAM, and the other increaess the amount of RAM used
for doing sort functions (ORDER BY and GROUP BY)...
Also, use the 'EXPLAIN' function to determine how the query is
being performed...in particular, are there parts that creating an index
would help improve speed and performance, but you don't have an index
created? I hit this one once, where I *thought* I had an index created on
one of the fields used in the query, but turned out I didn't. Performance
difference with it added was dramatic...
>
> It depends what you are doing: every update or insert is a separate
> transaction, unless you declare transactions yourself. So use
> BEGIN TRANSACTION ... COMMIT ... END TRANSACTION to enclose related
> updates and you should get a speed improvement.
>
> If you are loading a lot of items, COPY is much faster than
> successive INSERTs.
>
> Consider whether to disable fsync; balance the speed improvement against the
> slightly increased risk of corrupting your database in the event of a
> system crash.
>
> --
> Oliver Elphick[EMAIL PROTECTED]
> Isle of Wight http://www.lfix.co.uk/oliver
>PGP key from public servers; key ID 32B8FAA1
>
> "Set your affection on things above, not on things on
> the earth." Colossians 3:2
>
>
>
Marc G. Fournier [EMAIL PROTECTED]
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org ICQ#7615664
Re: [GENERAL] Re: More PostgreSQL stuff
On Fri, 2 Oct 1998, Oliver Elphick wrote:
> > . I wonder how one could add or remove columns from existing tables.
> >
> > With mSQL this was possible with a trick. You had to dump the
> > whole table but you could tell the dump program to add dummy fields
> > or to leave out existing fields. Is there any such possibility
> > with PostgreSQL?
chk out the 'ALTER TABLE' man page...you can add columns quite
easily using this. removing coloumsn is a little more tricky at this
point in time, but we're hoping to add it to the ALTER TABLE syntax 'in
the future'.
Basically, to drop a column, do an 'INSERT INTO...SELECT FROM' (my
syntax may be slightly off, please check man pages) from the table you
want to remove the column from, such that you only insert the columns you
want to remain. Then, when finished, do a ... damn, haven't used this one
yet, so don't remember what the 'command' is...but there is one to do a
RENAME of a table...rename the old to something different, and rename the
new to the old...
Marc G. Fournier [EMAIL PROTECTED]
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org ICQ#7615664
Re: [GENERAL] Re: More PostgreSQL stuff
> On Fri, 2 Oct 1998, Oliver Elphick wrote: > > > > . I wonder how one could add or remove columns from existing tables. > > > > > > With mSQL this was possible with a trick. You had to dump the > > > whole table but you could tell the dump program to add dummy fields > > > or to leave out existing fields. Is there any such possibility > > > with PostgreSQL? > > chk out the 'ALTER TABLE' man page...you can add columns quite > easily using this. removing coloumsn is a little more tricky at this > point in time, but we're hoping to add it to the ALTER TABLE syntax 'in > the future'. > > Basically, to drop a column, do an 'INSERT INTO...SELECT FROM' (my > syntax may be slightly off, please check man pages) from the table you > want to remove the column from, such that you only insert the columns you > want to remain. Then, when finished, do a ... damn, haven't used this one > yet, so don't remember what the 'command' is...but there is one to do a > RENAME of a table...rename the old to something different, and rename the > new to the old... This is an FAQ item. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] System admin
John Guthrie wrote: >Is there a good guise for system admin? I am stumped on the most >mundane problem right now: how do I set user passwords in postgresql? >When I createuser I never get prompted for a password, when I run psql >with no parameters I get right in, but if I run: > "psql -u someone" >I get a password prompt - also perl/DBI looks like it wants a password. The external command createuser does not prompt for a password. Use CREATE USER or ALTER USER as SQL commands: template1=> \h create user Command: create user Description: create a new user Syntax: create user [with password ] [createdb | nocreatedb] [createuser | nocreateuser] [in group , ..., ] [valid until '']; template1=> \h alter user Command: alter user Description: alter system information for a user Syntax: alter user [with password ] [createdb | noccreatedb] [createuser | nocreateuser] [in group , ..., ] [valid until '']; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 "For the word of God is quick, and powerful, and sharper than any twoedged sword, piercing even to the dividing asunder of soul and spirit, and of the joints and marrow, and is a discerner of the thoughts and intents of the heart."Hebrews 4:12
[GENERAL] [Question] Help me with programming in C/C++
Hi. I have a question regarding programming in C and postgreSQL. Is there any way that I can save couple of results to one PGresult * ?? Like thisBEGIN;DECLARE portal1 CURSOR FOR select * from item where item = 'aa';DECLARE portal2 CURSOR FOR select * from item where item = 'bb';DECLARE portal3 CURSOR FOR select * from item where item = 'cc'; res = PQexec(conn,"FETCH ALL in portal*"); // Like thisEND;portal1 sould go first.I really need this function.I'm a C programmer but not a expert.If anyone know how to do this in C/C++...Please let me know.. Thanks in advence.
