#json or
row_to_json(q1, true)CREATE TABLE people
(name text, age int, interests text[]);
INSERT INTO people (name, age, interests)
VALUES
('Jon', 12, ARRAY ['Golf', 'Food']),
('Jane', 45, ARRAY ['Art']);
SELECT row_to_json(ROW(name, age, interests)) FROM people;
row_to_json
-----------------------------------
{"f1":"Jon","f2":12,"f3":["Golf","Food"]}
{"f1":"Jane","f2":45,"f3":["Art"]}
SELECT row_to_json(q1) FROM
(SELECT * FROM people LIMIT 1) q1;
row_to_json
-----------------------------------
{"name":"Jon","age":12,"interests":["Golf","Food"]}
#query
Don't use BETWEEN (especially with timestamps)
This will include results where the timestamp is exactly 2018-06-08 00:00:00.000000, but not timestamps later in that same day
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.
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.
#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/
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.
#query
When dealing with NULL values in SQL, you have to use things like IS NULL or IS NOT NULL in your predicates:
But did you know that Postgres supports a shorter (though non-standard) approach if you want to save some characters?
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.
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.
#query
Want to quickly see how big a database is? You can do it in a single 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)#index #performance #storage
CLUSTER: IMPROVING POSTGRESQL PERFORMANCE
https://www.cybertec-postgresql.com/en/cluster-improving-postgresql-performance/
CLUSTER: IMPROVING POSTGRESQL PERFORMANCE
https://www.cybertec-postgresql.com/en/cluster-improving-postgresql-performance/
#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.
#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.
#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);