Re: unlooged tables

Hi,

On Thu, Dec 6, 2012 at 7:08 AM, Igor Neyman <ineyman(at)perceptron(dot)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(dot)ru(at)gmail(dot)com