Tag: collation

Feed: AWS Big Data Blog. Amazon Redshift is a fast, fully managed, cloud-native data warehouse. Tens of thousands of customers have successfully migrated their workloads to Amazon Redshift. We hear from customers that they need case-insensitive collation for strings in Amazon Redshift in order to maintain the same functionality and meet their performance goals when they migrate their existing workloads from legacy, on-premises data warehouses like Teradata, Oracle, or IBM. With that goal in mind, AWS provides an option to create case-insensitive and case-sensitive collation. In this post, we discuss how to use case-insensitive collation and how to override the ... Read More

Feed: Recent Announcements. Amazon Redshift, a fully-managed cloud data warehouse, now supports case-insensitive collation with column and expression level overrides. Starting today, you can use the COLLATE clause within a CREATE DATABASE statement to specify the default collation for all CHAR and VARCHAR columns in the database as case-sensitive or case-insensitive. You can also specify a COLLATE clause in a CREATE TABLE statement to specify collation for columns in that table. In addition, the COLLATE() function allows you to override the collation of a string column or an expression. Collation is a set of rules that defines how database engines ... Read More

Feed: Planet PostgreSQL. PostgreSQL is a secure database and we want to keep it that way. It makes sense, then, to consider SSL to encrypt the connection between client and server. This posting will help you to set up SSL authentication for PostgreSQL properly, and hopefully also to understand some background information to make your database more secure.At the end of this post, you should be able to configure PostgreSQL and handle secure client server connections in the easiest way possible.Configuring PostgreSQL for OpenSSLThe first thing we have to do to set up OpenSSL is to change postgresql.conf. There are ... Read More

Feed: Planet PostgreSQL. Just like any advanced relational database, PostgreSQL uses a cost-based query optimizer that tries to turn your SQL queries into something efficient that executes in as little time as possible. For many people, the workings of the optimizer itself remain a mystery, so we have decided to give users some insight into what is really going on behind the scenes.So let’s take a tour through the PostgreSQL optimizer and get an overview of some of the most important techniques the optimizer uses to speed up queries. Note that the techniques listed here are in no way complete ... Read More

Feed: Planet PostgreSQL. On 19th of March 2021, Robert Haas committed patch: Allow configurable LZ4 TOAST compression.   There is now a per-column COMPRESSION option which can be set to pglz (the default, and the only option in up until now) or lz4. Or, if you like, you can set the new default_toast_compression GUC to lz4, and then that will be the default for new table columns for which no value is specified. We don't have lz4 support in the PostgreSQL code, so to use lz4 compression, PostgreSQL must be built --with-lz4.   In general, TOAST compression means compression of ... Read More

Feed: Planet PostgreSQL. One of the many useful features on FreshPorts is: what port[s] install[s] this file? That’s the pkg-plist search option. pkg-plist is a file which “lists all the files installed by the port”. However not all ports have a pkg-plist file because the list is compiled automatically. That is why the configure_plist table was created to replace the ports.pkg_plist column. The creation of that table broke the search function because it was then working on outdated data. Last week, I blogged about how I created a new a new stored procedure for pulling back the pkg-plist information. By ... Read More

Feed: Planet PostgreSQL. Citus 10 is out! Check out the Citus 10 blog post for all the details. Citus is an open source extension to Postgres (not a fork) that enables scale-out, but offers other great features, too. See the Citus docs and the Citus github repo and README. This post will highlight Citus Columnar, one of the big new features in Citus 10. You can also take a look at the columnar documentation. Citus Columnar can be used with or without the scale-out features of Citus. Postgres typically stores data using the heap access method, which is row-based storage ... Read More

Feed: MariaDB Knowledge Base Article Feed. Author: . 저장 프로시저는 CALL문에 의해 호출되는 루틴입니다. 입력 매개변수, 출력 매개변수 혹은 입력이면서 동시에 출력인 매개변수를 가질 수 있습니다 저장 프로시저 생성 다음은 저장 프로시저 작동을 확인하는 스켈레톤 예제입니다: DELIMITER // CREATE PROCEDURE Reset_animal_count() MODIFIES SQL DATA UPDATE animal_count SET animals = 0; // DELIMITER ; 우선, 프로시저 정의 내에 기존의 구분기호인 세미콜론이 포함되기 때문에 구분기호(delimiter)를 변경합니다. 프로시저는 Reset_animal_count로 명명되었고, MODIFIES SQL DATA는 프로시저가 일종의 쓰기 동작을 수행하고 데이터를 변경할 것이라는 것을 알려줍니다.(MODIFIES SQL DATA는 단순 정보 제공용으로, 쿼리 동작에 실제로 영향을 미치지 않습니다.) 마지막으로 실제 SQL문인 UPDATE문이 위치합니다. SELECT * FROM animal_count; ... Read More

Feed: Planet PostgreSQL. 03 Mar 2021 Are you tired of this syntax in PostgreSQL? SELECT jsonb_column->'key' FROM table; UPDATE table SET jsonb_column = jsonb_set(jsonb_column, '{"key"}', '"value"'); The select part is actually fine. But for updates, especially for complex updates, it could be pretty verbose and far from being ergonomic. What would you say to this syntax instead? SELECT jsonb_column['key'] FROM table; UPDATE table SET jsonb_column['key'] = '"value"'; With subscripting it looks more concise and probably even familiar for developers due to its “pythonic” style. If you like this syntax more I have good news for you, recently a patch implementing ... Read More

Feed: Planet PostgreSQL. In this post I will show you how I discovered that the backend was putting data into a new table (generate_plist) but the website was still using the ports.pkg_plist column. The result was stale data being shown on the website (for older ports) or no data at all (for newer ports). How long? Since Oct 2017, because that’s when configure_plist showed up on the website. Nobody noticed until recently. I can only guess that I made the backend changes with intent to change the frontend later. I should have created myself a ticket. Why was the change ... Read More