postgresources – Telegram
postgresources
@postgresources
19 subscribers
8 photos
149 links
halp: @olegkovalov
Download Telegram
About
Blog
Apps
Platform
Join
postgresources
19 subscribers
postgresources
Channel created
postgresources
#math 

SELECT ROUND(123.456);
  # => 123

SELECT ROUND(123.456, 2);
  # => 123.46

SELECT ROUND(123.456, -2);
  # => 100
  
SELECT ROUND(123.456, -1);
  # => 120

# This also works in MySQL, Oracle and SQL Server, though not SQLite.
11 viewsedited  
postgresources
#test Create one million rows

insert into towns (
    code, article, name, department
)
select
    left(md5(i::text), 10),
    md5(random()::text),
    md5(random()::text),
    left(md5(random()::text), 4)
from generate_series(1, 1000000) s(i)
12 viewsedited  
postgresources
#analyze See how much space your tables (and indexes!) are taking up

SELECT
   relname AS table_name,
   pg_size_pretty(pg_total_relation_size(relid)) AS total,
   pg_size_pretty(pg_relation_size(relid)) AS internal,
   pg_size_pretty(pg_table_size(relid) - pg_relation_size(relid)) AS external,
   pg_size_pretty(pg_indexes_size(relid)) AS indexes
    FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
12 viewsedited  
postgresources
#conf How to see the elapsed time for your queries in psql

\timing

If you really like this and want it to be permanent, create a .psqlrc
12 viewsedited  
postgresources
#analyze The IO statistics for each of your tables, including how many 'blocks' of data have been served from the cache (i.e. heap_blks_hit).

SELECT * FROM pg_statio_user_tables;

WITH y AS (
  SELECT
  sum(heap_blks_read) AS read,
  sum(heap_blks_hit) AS hit
  FROM pg_statio_user_tables
) SELECT read, hit, hit / (hit+read)
  AS ratio FROM y;

Our new query has added together all of the relevant numbers to show how many cache hits occurred, how often the cache *didn't* deliver (heap_blks_read is the number of disk blocks read) and we calculate the ratio of hits to the total number of blocks read.

In this case, our cache is working effectively as the hit rate is *almost* 100%. But in a low memory environment or when a server is under particular strain, you may see lower numbers. Take note of these and be prepared to upgrade your database server.
13 viewsedited  
postgresources
#rand Selecting a 'sample' of a table's rows

create a table that contains 1000 rows — the numbers 1 to 1000:

CREATE TABLE numbers (number int);
INSERT INTO numbers (number)
  SELECT id FROM generate_series(1,1000) AS id;

Postgres 9.5 introduced a new TABLESAMPLE clause that lets you sample tables in different ways (2 ways by default, but more can be added via extensions). Let's give it a go at retrieving a random 0.5% of the rows from our table

SELECT * FROM numbers TABLESAMPLE BERNOULLI (0.5);
 number 
--------
    101
    213
    278
    433

The BERNOULLI here selects rows from the table with a 0.5% chance (so statistically we'd get 5 rows on average), but there's also a more efficient SYSTEMmethod that uses a cruder block-based technique (with the possible downside that it tends to return ranges/groups of rows within the table).

You can also use TABLESAMPLE with other SQL statements like UPDATE or `DELETE`if, say, you wanted to delete 30% of a table's rows for whatever reason.
14 viewsedited  
postgresources
#json

# “is a subset of”
SELECT * FROM json_test WHERE data @> '{"a":1}';

SELECT * FROM json_test WHERE data <@ '{"a":1}';

# test for the existence of a key
SELECT * FROM json_test WHERE data ? 'a';

# test for objects that have any of a list of keys:
SELECT * FROM json_test WHERE data ?| array['a', 'b'];
15 viewsedited  
postgresources
#array In Postgres syntax, the part '{1,2,3}' is a plain string, :: is the Postgres typecast operator, and int[] is the type (an array of integers). The expression '{1,2,3}'::int[] reads as “typecast the string {1,2,3} into an array of integers”.

The string notation of curly brackets and comma-separated values is an array value constructor recognized by Postgres.

test=# SELECT '{1,2,3}'::int[] ; -- an array of integers
  int4
---------
 {1,2,3}
(1 row)

test=# SELECT '{"hello","world"}'::text[] ; -- an array of strings
     text
---------------
 {hello,world}
(1 row)
16 views
postgresources
#array 

test=# SELECT array[1,2,3];
    array
  ---------
   {1,2,3}
  (1 row)
  
  test=# SELECT array['hello', 'world'];
       array
  ---------------
   {hello,world}
  (1 row)
  
  test=# SELECT array[1, 'world'];
  ERROR:  invalid input syntax for integer: "world"
  LINE 1: SELECT array[1, 'world'];
14 viewsedited  
postgresources
#array Here is how you can have an array as a column type in a table:

test=# CREATE TABLE posts (
test(#     noscript text NOT NULL PRIMARY KEY,
test(#     tagz  text[]
test(#     tags  text[] NOT NULL DEFAULT '{}'::text[]
test(# );
CREATE TABLE

test=# INSERT INTO posts (noscript, tags) VALUES
test-# ('Using PostgreSQL Arrays with Golang', '{"postgres","golang"}');
INSERT 0 1
15 views