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.

Article based on information from habrahabr.ru

Комментарии

Популярные сообщения из этого блога

Python-digest #8. News, interesting projects, articles and interviews [20 Dec 2013 — 27 Dec 2013]

Performance comparison of hierarchical models, Django and PostgreSQL

google life search