Hi,
On Thu, Dec 6, 2012 at 7:08 AM, Igor Neyman <ineyman@perceptron.com> 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
Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com
