blob: ef373e6e3c35b3b4229cce191315b40079e3631e (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
--
-- WITHOUT OID
--
CREATE TABLE wi (i INT) WITH OIDS;
CREATE TABLE wo (i INT) WITHOUT OIDS;
INSERT INTO wi VALUES (1); -- 1
INSERT INTO wo SELECT i FROM wi; -- 1
INSERT INTO wo SELECT i+1 FROM wi; -- 1+1=2
INSERT INTO wi SELECT i+1 FROM wo; -- 1+2=3
INSERT INTO wi SELECT i+3 FROM wi; -- 3+3=6
INSERT INTO wo SELECT i+2 FROM wi; -- 2+6=8
INSERT INTO wo SELECT i+8 FROM wo; -- 8+8=16
INSERT INTO wi SELECT i+6 FROM wo; -- 6+16=22
INSERT INTO wi SELECT i+22 FROM wi; -- 22+22=44
INSERT INTO wo SELECT i+16 FROM wi; -- 16+44=60
INSERT INTO wo SELECT i+60 FROM wo; -- 60+60=120
INSERT INTO wi SELECT i+44 FROM wo; -- 44+120=164
INSERT INTO wi SELECT i+164 FROM wi; -- 164+164=328
INSERT INTO wo SELECT i+120 FROM wi; -- 120+328=448
INSERT INTO wo SELECT i+448 FROM wo; -- 448+448=896
INSERT INTO wi SELECT i+328 FROM wo; -- 328+896=1224
INSERT INTO wi SELECT i+1224 FROM wi; -- 1224+1224=2448
INSERT INTO wo SELECT i+896 FROM wi; -- 896+2448=3344
INSERT INTO wo SELECT i+3344 FROM wo; -- 3344+3344=6688
INSERT INTO wi SELECT i+2448 FROM wo; -- 2448+6688=9136
INSERT INTO wo SELECT i+6688 FROM wi WHERE i<=2448; -- 6688+2448=9136
VACUUM ANALYZE wi;
VACUUM ANALYZE wo;
SELECT min(relpages) < max(relpages), min(reltuples) - max(reltuples)
FROM pg_class
WHERE relname IN ('wi', 'wo');
?column? | ?column?
----------+----------
t | 0
(1 row)
DROP TABLE wi;
DROP TABLE wo;
--
-- WITH / WITHOUT OIDS in CREATE TABLE AS
--
CREATE TABLE create_table_test (
a int,
b int
);
COPY create_table_test FROM stdin;
CREATE TABLE create_table_test2 WITH OIDS AS
SELECT a + b AS c1, a - b AS c2 FROM create_table_test;
CREATE TABLE create_table_test3 WITHOUT OIDS AS
SELECT a + b AS c1, a - b AS c2 FROM create_table_test;
SELECT count(oid) FROM create_table_test2;
count
-------
2
(1 row)
-- should fail
SELECT count(oid) FROM create_table_test3;
ERROR: column "oid" does not exist
DROP TABLE create_table_test;
DROP TABLE create_table_test2;
DROP TABLE create_table_test3;
|
