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

Комментарии

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

Performance comparison of hierarchical models, Django and PostgreSQL

google life search

Transport Tycoon Deluxe / Emscripten part 2