planet.postgresql.org

Denish Patel: Clone schema in Postgres

Postgres doesn’t have built in function to clone schema. However,  may of backend developers would like to perform a number of operations at schema level directly from command line tools such as psql or any database developer tools (DBeaver). One of the common operation is to copy or clone the entire schema. While researching any existing solutions, I stumbled upon  postgres-general thread where one of the developer(s) provided clone_schema.sql function. I uploaded the existing function provided in thread to my git repo here. However,...

planet.postgresql.org

Johann Oskarsson: Porting a PostgreSQL Extension from Unix to Windows 10

2ndQuadrant has written before about how to build your own extension for Windows. This time we take a look at what it takes to port one written by someone else. I am building this for PostgreSQL 11 on Windows using Visual Studio 2019 and clang. Here we take are working with the hashtypes extension. Unfortunately, at the time of writing, version 0.1.5 has not been released with the support for PostgreSQL 11. So we build one from git. git clone https://github.com/adjust/hashtypes.git The first thing we do is to take a look at the C source...

planet.postgresql.org

Hans-Juergen Schoenig: Timeseries: EXCLUDE TIES, CURRENT ROW and GROUP

Windowing functions and analytics have been around for quite some time and many people already make use of this awesome stuff in the PostgreSQL world. Timeseries  are an especially important area in this context. However, not all features have been widely adopted and thus many developers have to implement functionality at the application level in a painful way instead of just using some of the more advanced SQL techniques. The idea of this blog is to demonstrate some of the advanced stuff so that more people out there can make use of...

planet.postgresql.org

Hans-Juergen Schoenig: PostgreSQL: Trivial timeseries examples

Timeseries are an increasingly important topic – not just in PostgreSQL. Recently I gave a presentation @AGIT in Salzburg about timeseries and I demonstrated some super simple examples. The presentation was well received so I decided to share this stuff in the form of a blog PostgreSQL, so that more people can learn about windowing functions and SQL in general. A link to the video is available at the end of the post so that you can listen to the original material in German. Loading timeseries data the easy way To show how data can be...

planet.postgresql.org

Regina Obe: PostGIS 3.0.0alpha4, 2.5.3, 2.4.8, 2.3.10 Released

The PostGIS development team is pleased to provide enhancements/features 3.0.0alpha4 for 3.0 feature major branch bug fix 2.5.3, 2.4.8, and 2.3.10 for the 2.5, 2.4, and 2.3 stable branches. 3.0.0alpha4 This release works with PostgreSQL 9.5-12beta3 and GEOS >= 3.6 Best served with PostgreSQL 12beta3. Designed to take advantage of features in PostgreSQL 12 and Proj 6 source download NEWS PDF docs en html doc download epub doc download Changelog 2.5.3 This release supports PostgreSQL 9.3-12 You are encouraged to use the PostGIS 3.0 unreleased...

planet.postgresql.org

ahsan hadi: Horizontal scalability with Sharding in PostgreSQL – Where it is going Part 1 of 3

I recently had the opportunity of taking part in couple of interesting talks on the future of sharding in PostgreSQL. The first talk was delivered by Bruce Momjain in PostgreSQL conf Ottawa (May 2019) in which he presented the future of Sharding in PostgreSQL and talked about current state and future of built-in sharding in PostgreSQL. The second talk was presented by myself in PostgreSQL conference in Beijing (China – July 2019) in which i also talked about the current state and future of built-in sharding in PG. I essentially used the...

planet.postgresql.org

Tatsuo Ishii: Automatically updating materialized views

Materialized views are convenient and efficient way to retrieve information from database. Unlike ordinary views, materialized views save the query result and provide faster access to the data. For example, user can create a simple materialized view containing the number of rows in a table: CREATE MATERIALIZED VIEW mv1 AS SELECT count(*) FROM pgbench_accounts;Obtaining the number of rows using the materialized view mv1 is much faster than  directly accessing number of rows in pgbench_accounts.test=# SELECT * from v1; count ...

planet.postgresql.org

Hugo Dias: Comparing Temporary Tables for PostgreSQL & Oracle GTT

The temporary tables are a useful concept present in most SGBDs, even though they often work differently. This blog describes the technical features for this kind of tables either in PostgreSQL (version 11) or Oracle (version 12c) databases with some specific examples. Although the purpose of these tables could be the same for all SGBD’s, their specifics, or the way of implementation and manipulation, are completely different. This feature could be used both by developers or database administrators to store intermediate results that will be...

planet.postgresql.org

Abbas Butt: How to display encoded byte stream of a varchar column in PostgreSQL

CREATE TABLE varchar_demo(description varchar(200));INSERT INTO varchar_demo VALUES('こんにちは');INSERT INTO varchar_demo VALUES('ABCDE');SELECT * FROM varchar_demo; description ------------- こんにちは ABCDE(2 rows)CREATE OR REPLACE FUNCTION dump(columnname TEXT, tablename TEXT,                                rowid INT) RETURNS TEXTLANGUAGE plpgsqlAS$function$DECLARE   hex_str TEXT;  hex_byte TEXT;  query TEXT;  i INT;  len INT;BEGIN  query := format('SELECT octet_length(%I) FROM %I offset %s       ...

planet.postgresql.org

Abbas Butt: Comparing NVARCHAR2 with VARCHAR2 and NCLOB with CLOB in Oracle

In Oracle by default VARCHAR2/CLOB data is encoded using UTF-8, that can result in a character take up to 4 bytes of storage. However NVARCHAR/NCLOB data is encoded in UTF-16, that results in a character taking 2 bytes each.First consider the relevant portion of the UTF-8 encoding table along with UNICODE-----------------------------------------------------Unicode  Character   UTF-8       Character Name-----------------------------------------------------U+3053     こ     e3 81 93 HIRAGANA LETTER KOU+3093    ん     e3 82 93...