Thread: Novice Question
I am new to postgresql, having previously worked with mysql mostly. What kind of command would I run if I wanted to copy an entire table (along with renaming it, and, of course, all data from the first table - some of which is binary)? Thanks, -Mike
Sorry, I meant to ask about copying databases, not tables (including all data in the database as per below). Thanks, -Mike Michael Romagnoli wrote: > > I am new to postgresql, having previously worked with mysql mostly. > > What kind of command would I run if I wanted to copy an entire table > (along with renaming it, and, of course, all data from the first table > - some of which is binary)? > > Thanks, > > -Mike > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
On Mar 1, 2005, at 4:23 PM, Michael Romagnoli wrote: > > I am new to postgresql, having previously worked with mysql mostly. > > What kind of command would I run if I wanted to copy an entire table > (along with renaming it, and, of course, all data from the first table > - some of which is binary)? > > Thanks, > > -Mike > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly select * into table new_table from old_table; That's it. Sean
Sean Davis wrote: > > On Mar 1, 2005, at 4:23 PM, Michael Romagnoli wrote: > >> >> I am new to postgresql, having previously worked with mysql mostly. >> >> What kind of command would I run if I wanted to copy an entire table >> (along with renaming it, and, of course, all data from the first table >> - some of which is binary)? >> >> Thanks, >> >> -Mike >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly > > > select * into table new_table from old_table; > > That's it. > > Sean > you sure about that syntax? How about: create table new_table as select * from old_table; -- _______________________________ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. _______________________________
On Tue, 01 Mar 2005 16:30:19 -0500, Michael Romagnoli <michael@houseind.com> wrote: > > Sorry, I meant to ask about copying databases, not tables (including all > data in the database as per below). you can do a pg_dump your_database>your_database.dump.sql and then createdb to create your new database, and finally pgsql -f your_database.dump.sql new_database and that's it. you should probably use -Upostgres depending on what kind of security you use. javier
create database newdb template olddb; works as well. -Chris On Tuesday 01 March 2005 05:08 pm, javier wilson wrote: > On Tue, 01 Mar 2005 16:30:19 -0500, Michael Romagnoli > > <michael@houseind.com> wrote: > > Sorry, I meant to ask about copying databases, not tables (including all > > data in the database as per below). > > you can do a pg_dump your_database>your_database.dump.sql > and then createdb to create your new database, and finally > pgsql -f your_database.dump.sql new_database > > and that's it. you should probably use -Upostgres > depending on what kind of security you use. > > javier > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
michael@houseind.com (Michael Romagnoli) writes: > What kind of command would I run if I wanted to copy an entire table > (along with renaming it, and, of course, all data from the first table > - > some of which is binary)? SELECT * INTO newtable FROM oldtable; Note that this doesn't construct indexes, Foreign keys, constraints, etc. If by 'binary data' you mean BLOBs, I'd expect the above to work. Other than that, AFAIUI you have no reasonable expectation that your data is stored in any meaningful binary format by the database. All data could be internally stored as strings (though that might be very slow). -- Remove -42 for email