• EN
    Blog

    Cybertec Logo

    • Services
      • Requirement Analysis
      • PostgreSQL consulting
      • PostgreSQL migration
        • Migration from Oracle
        • Migrating from MySQL / MariaDB
        • CYBERTEC Migrator
      • CYPEX Online Academy
      • PostgreSQL Infrastructure
        • Setup & Installation
        • Kubernetes
        • Database architecture
      • PostgreSQL development
        • Database modeling
        • Functions & Features
      • Update & Upgrade
      • Optimization & Security
        • PostgreSQL Health Check
        • Performance Tuning
        • Enterprise Security
        • Security Audit
      • Troubleshooting & Data Recovery
      • PostgreSQL clustering and HA
        • Clustering and failover
        • High availability with Patroni
        • Synchronous and asynchronous replication
        • Scaling with PL/Proxy
      • Spatial Services
        • GIS Tooling
        • PostGIS OSM Downloader
      • CYBERTEC Partner Network
    • Support
      • Standard PostgreSQL Support
        • Product Support
        • 9/5 Basic Support
        • 24/7 Basic Support
        • 24/7 Enterprise Support
      • Advanced PostgreSQL Support
        • 9/5 Remote DBA
        • 24/7 Remote DBA
        • 9/5 Dedicated DBA
        • 24/7 Cloud-based Support
      • Support for Reseller
        • 3rd Level Support
        • CYBERTEC Partner Network
    • Products
      • Our Products
        • CYPEX – Build Apps & Forms
        • CYBERTEC PostgreSQL Enterprise Edition
        • CYBERTEC Migrator
        • PostgreSQL Transparent Data Encryption
        • Scalefield – Private Cloud
        • Data Masking for PostgreSQL
        • PL/pgSQL_sec – Fully encrypted stored procedures
      • PostgreSQL Tools & Extensions
        • pg_timetable – Advanced Job Scheduling
        • pg_show_plans – Monitoring Execution Plans
        • pgwatch – PostgreSQL Monitoring Tool
        • pg_squeeze – Shrinks Tables
        • Walbouncer – Enterprise Grade Partial Replication
        • PGConfigurator – Visual PostgreSQL Configuration
        • Patroni Environment Setup
      • Assessment Packages
        • Data Science Assessment Package
        • Start-Up Assessment Package
        • Spatial Data Assessment Package
      • CYBERTEC Partner Network
    • Training
    • PostgreSQL
      • Advantages of PostgreSQL
      • PostgreSQL Books
      • Solutions – Who uses PostgreSQL
        • PostgreSQL for Startups
        • PostgreSQL for governments and public services
        • Longlife solutions
      • Business Cases
        • Fraud Detection
        • PostgreSQL for biotech and scientific applications
    • Data Science
      • Data Science Overview
      • Machine Learning
      • Big Data Analytics
    • Contact

    Posts

    Category: SQL help

    17.05.2023 | Laurenz Albe

    PostgreSQL ALTER DEFAULT PRIVILEGES – permissions explained

    © Laurenz Albe 2023 Many people have heard that ALTER DEFAULT PRIVILEGES makes it easy to allow other users access to tables. But then, many people don’t understand the command in depth, and I hear frequent complaints that ALTER DEFAULT PRIVILEGES does not work as expected. Read on if you want to know better! Default […]

    Read more
    04.05.2023 | Laurenz Albe

    Forcing a join order in PostgreSQL

    © Laurenz Albe 2023 Different from many other database systems, PostgreSQL does not support query hints. That makes it difficult to force the hand of the query planner when it comes to a certain join order that you know to be good. This article explains how you can influence execution plans in PostgreSQL. Why no […]

    Read more
    18.04.2023 | Pavlo Golub

    Underscores in numeric constants in PostgreSQL 16

    SQL and numeric constants NEW in PostgreSQL 16 – support for underscores in integer and numeric constants! I already wrote about support for different notations in the upcoming PostgreSQL 16. This new major version also implements the SQL:202x standard (draft), allowing you to use underscore separators for integers and numeric constants. Using underscore separators in […]

    Read more
    14.03.2023 | Laurenz Albe

    Breaking your PostgreSQL database with bad CHECK constraints

    © Laurenz Albe 2023 I am on a spree! After writing about breaking your database and transaction ID wraparound, here I am writing about damage caused by CHECK constraints! Trust me, I don’t want to destroy PostgreSQL databases. It’s just that this Twitter message was brought to my attention and triggered memories of other reports […]

    Read more
    13.09.2022 | Hans-Jürgen Schönig

    What is an inner join in SQL? And what is an outer join?

    A join is a concept in IT which is widely used and often referred to but rarely really understood. What are the differences between inner joins, outer joins, semi joins and so on? Let’s shed some light on them and see how inner and outer joins really work. Producing sample data Before we can get […]

    Read more
    30.08.2022 | Hans-Jürgen Schönig

    PostgreSQL: ALTER TABLE … ADD COLUMN … done right

    Running ALTER TABLE … ADD COLUMN can have side effects which have the potential to cause serious issues in production. Changing data structures is an important issue and happens often, therefore it’s important to understand what is really going on. Let’s dive in and see how to run ALTER TABLE … ADD COLUMN in a […]

    Read more
    05.07.2022 | Hans-Jürgen Schönig

    Handling Bonus Programs in SQL

    Bonus cards, “Miles & more”, bonus points – don’t we all love and hate them at the same time? Recently we had an interesting use case which made me think about sharing some of the techniques we used in this area to reduce client code by writing some clever SQL. This post will show you […]

    Read more
    15.06.2022 | Laurenz Albe

    Case-insensitive pattern matching in PostgreSQL

      © Renée Albe 2022 Case-insensitive search is a much-requested feature, partly (I suspect) to maintain compatibility with Microsoft SQL Server. There are several solutions to the problem, one of which is to use case-insensitive ICU collations. This works like a charm, except if you want to perform pattern matching. So let’s have a closer […]

    Read more
    10.05.2022 | Laurenz Albe

    Time zone management in PostgreSQL

    © Laurenz Albe 2022 Next to character encoding, time zones are among the least-loved topics in computing. In addition, PostgreSQL’s implementation of timestamp with time zone is somewhat surprising. So I thought it might be worth to write up an introduction to time zone management and recommendations for its practical use. Time zones and the […]

    Read more
    03.05.2022 | Hans-Jürgen Schönig

    PostgreSQL 15: Using MERGE in SQL

    It’s been many years since the first ideas to implement the MERGE command surfaced on the PostgreSQL mailing list. Now this important feature has finally made it into PostgreSQL core, in PostgreSQL 15. To show people how this vital command works, I have decided to come up with a technical preview to introduce my readers […]

    Read more
    01.02.2022 | Laurenz Albe

    Automatic partition creation in PostgreSQL

    © Laurenz Albe 2022 Table partitioning is one of the best-liked features out of the more recent PostgreSQL developments. However, there is no support for automatic partition creation yet. This article shows what you can do to remedy that. Use cases for automatic partition creation There are essentially two use cases: Create partitions triggered by […]

    Read more
    25.01.2022 | Michał Małecki

    Practical Examples of Data Normalization in PostgreSQL

    Normalization by Osmosis Part 2: Practical Examples of Data Normalization in PostgreSQL Having gone through a theoretical introduction in part 1, it is now time to discuss some more practical examples. I’ll first talk about the… type modeling … of columns. It is important to realize that during the time Codd formulated the base 3 […]

    Read more

    Posts navigation

    Older posts

    logo

    CYBERTEC PostgreSQL International GmbH
    Römerstraße 19
    2752 Wöllersdorf
    AUSTRIA

    +43 (0) 2622 93022-0
    office@cybertec.at
    twitter.com/PostgresSupport
    github.com/cybertec-postgresql

    Our Services

    • Administration
    • Replication
    • Consulting
    • Database Design
    • Support
    • Migration
    • Development

     

    SUPPORT CUSTOMERS
    Go to the support platform >>

    Newsletter

    Check out previous newsletters!

    Stay well informed about PostgreSQL by subscribing to our newsletter.

    © 2000–2023 CYBERTEC PostgreSQL International GmbH
    • IMPORTANT INFORMATION ABOUT COVID-19
    • Contact
    • Data protection policy
    • Imprint
    • Terms and Conditions