NEW BOT Телеграм, страница

15 viewsedited 22:00

#query

Don't use BETWEEN (especially with timestamps)

SELECT * FROM blah WHERE timestampcol BETWEEN '2018-06-01' AND '2018-06-08'

This will include results where the timestamp is exactly 2018-06-08 00:00:00.000000, but not timestamps later in that same day

SELECT * FROM blah WHERE timestampcol >= '2018-06-01' AND timestampcol < '2018-06-08'

BETWEEN is safe for discrete quantities like integers or dates, as long as you remember that both ends of the range are included in the result. But it's a bad habit to get into.

11 views13:46

postgresources

#query #lock “out of shared memory”: When it happens

https://www.cybertec-postgresql.com/en/postgresql-you-might-need-to-increase-max_locks_per_transaction/

CYBERTEC PostgreSQL | Services & Support

PostgreSQL: You might need to increase max_locks_per_transaction

How can you prevent the error "out of shared memory" in PostgreSQL? max_locks_per_transaction is the critical configuration parameter.

12 views08:23

postgresources

#roles #security https://tightlycoupled.io/my-goto-postgres-configuration-for-web-services/

tightlycoupled.io

My GOTO Postgres Configuration for Web Services

My guide to setting up postgres for web services.

13 views08:31

postgresources

#query

When dealing with NULL values in SQL, you have to use things like IS NULL or IS NOT NULL in your predicates:

SELECT * FROM people WHERE age IS NOT NULL;

But did you know that Postgres supports a shorter (though non-standard) approach if you want to save some characters?

SELECT * FROM people WHERE age NOTNULL;

There's also ISNULL for the IS NULL case, although the numbers of characters saved is somewhat reduced ;-)

And if you've ever wondered why you can't just go age = NULL or age != NULL, this is because NULL is defined to be an 'unknown' value and the SQL standard dictates that you can't compare an 'unknown' value with another.

14 views16:09

postgresources

#storage http://rachbelaid.com/introduction-to-postgres-physical-storage/

15 views19:30

postgresources

#config https://postgresqlco.nf/en/doc/param/

postgresqlco.nf

PostgresqlCO.NF: PostgreSQL configuration for humans

PostgresqlCO.NF is a configuration manager for all PostgreSQL related configuration files. It provides multilanguage documentation for postgresql.conf parameters, lets the user upload his configurations and gives him recommendations on them

14 views15:41

postgresources

#query

Want to quickly see how big a database is? You can do it in a single query:

SELECT pg_size_pretty(pg_database_size('n2'));

select pg_size_pretty(pg_database_size('n2'));

 pg_size_pretty
----------------
 446 MB
(1 row)

12 views15:54

postgresources

#index #performance #storage

CLUSTER: IMPROVING POSTGRESQL PERFORMANCE

https://www.cybertec-postgresql.com/en/cluster-improving-postgresql-performance/

11 views10:50

postgresources

#query #function https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions

10 views14:51

postgresources

#index #performance #vacuum https://www.cybertec-postgresql.com/en/postgresql-autovacuum-insert-only-tables/

CYBERTEC PostgreSQL | Services & Support

PostgreSQL v13 new feature: tuning autovacuum on insert-only tables

PostgreSQL v13 introduces autovacuum for insert-only tables. This article tells you why and how to make use of this new feature.

10 views18:33

postgresources

#vacuum #core https://www.cybertec-postgresql.com/en/autovacuum-wraparound-protection-in-postgresql/

CYBERTEC PostgreSQL | Services & Support

Autovacuum wraparound protection in PostgreSQL

Learn about one of the most misunderstood features in the PostgreSQL world: AUTOVACUUM and its “to prevent wraparound” notice.

9 views12:21

postgresources

#type

create type dimensions as (
  width integer,
  height integer,
  depth integer
);

create table moving_boxes (
  id serial primary key,
  dims dimensions not null
);

insert into moving_boxes (dims) values (row(3,4,5)::dimensions);

7 views12:27

postgresources

#query #size

> select pg_database_size('hr_hotels');
 pg_database_size
------------------
          8249516

> select pg_relation_size('reservations');
 pg_relation_size
------------------
          1531904

> select pg_size_pretty(pg_relation_size('users_pkey'));
 pg_size_pretty
----------------
 240 kB