Postgres and Emptiness
Just came across the opportunity to Postgresql, which seemed funny to me. For whom the "Bayan" respect to you, I have several years of working with Postgres and still not stumbled on such a thing.
select;
without specifying fields, tables and conditions returns one row. But this string has no fields:
the
=> select;
--
(1 row)
For comparison:
the
=> select null;
?column?
----------
(1 row)
=> select null where 0=1;
?column?
----------
(0 rows)
And if we can create a table from such an "empty" request? Table without fields.
Yes please
the
=> create table t as select;
SELECT 1
=> \d+ t
Table "t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------+-----------+---------+--------------+-------------
=> select * from t;
--
(1 row)
can we insert it?
Easy:
the
=> insert into t select;
INSERT 0 1
=> insert into t select;
INSERT 0 1
=> select * from t;
--
(3 rows)
=> select count(*) from t;
count
-------
3
MORE!
the
=> insert into t select from generate_series(1,1000000);
INSERT 0 1000000
I Wonder if Postgresql scanning a table?
the
=> explain analyze select * from t;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..13438.67 rows=1000167 width=0) (actual time=0.018..96.389 rows=1000003 loops=1)
Planning time: 0.024 ms
Execution time: 134.654 ms
(3 rows)
Yeah, honestly scans. More than 100 ms is quite a noticeable time.
Well, to make sure that everything is fair, look at how much space our super-helpful table:
the
=> select pg_size_pretty(pg_total_relation_size('t'));
pg_size_pretty
----------------
27 MB
(1 row)
That is, the table takes disk space, in blocks of different service data is stored, but the fact that there are no fields — it happens, it happens!
the
=> select t.xmin, t.ctid from t limit 10;
xmin | ctid
---------+--------
1029645 | (0,1)
1029647 | (0,2)
1029648 | (0,3)
1029649 | (0,4)
1029649 | (0,5)
1029649 | (0,6)
1029649 | (0,7)
1029649 | (0,8)
1029649 | (0,9)
1029649 | (0,10)
(10 rows)
I figured it out, why would you need such a table. But the opportunity is there, and it's good!
the I use Postgresql 9.6. As noted below in lower versions it doesn't work. 9.3 issues a syntax error
. 9.4, 9.5 on hand to check.
Комментарии
Отправить комментарий