> -----Original Message----- > From: Sergey Konoplev [mailto:gray.ru@xxxxxxxxx] > Sent: Thursday, December 06, 2012 4:52 PM > To: Igor Neyman > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: unlooged tables > > Hi, > > On Thu, Dec 6, 2012 at 7:08 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> > wrote: > > Is there any way to change "regular" table to "unlogged" without > > dropping said table and recreating it as unlogged? > > AFAIK it is impossible currently. > > The best way to do such transformation that comes to my mind is: > > CREATE TABLE table1 ( > id bigserial PRIMARY KEY, > data text > ); > > INSERT INTO table1 (data) > SELECT 'bla' || i::text > FROM generate_series(1, 10) AS i; > > SELECT * FROM table1; > > CREATE UNLOGGED TABLE tmp (LIKE table1 INCLUDING ALL); ALTER TABLE > table1 INHERIT tmp; > > BEGIN; > ALTER TABLE table1 RENAME TO table1_old; ALTER TABLE tmp RENAME TO > table1; END; > > So new rows will be inserted into the new unlogged table and old rows > will be available from the old one. > > INSERT INTO table1 (data) > SELECT 'bla' || i::text > FROM generate_series(11, 15) AS i; > > UPDATE table1 SET data = 'mla' || i::text WHERE i <= 5; > > SELECT * FROM table1; > > And then all we need is move the data to the new table and finish with > the old one. > > ALTER SEQUENCE table1_id_seq OWNED BY table1.id; > > BEGIN; > INSERT INTO table1 SELECT * FROM table1_old; DROP TABLE table1_old > CASCADE; END; > > SELECT * FROM table1; > > Correct me if I misunderstand something, please. > > > > > Didn't find the answer in the docs. Looks like "alter table ..." does > > not support "unlogged. > > > > > > > > TIA, > > > > Igor Neyman > > > > -- > Sergey Konoplev > Database and Software Architect > http://www.linkedin.com/in/grayhemp I was hoping, may be hacking pg_catalog, like setting pg_class.relpersistence to 'u' will do the trick (or something like this). b.t.w. there will be no other active connections, so there is no risk of needing to add/update/delete records in the table while changing to "unlogged". Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
