• 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: Development

    21.02.2023 | Pavlo Golub

    ER diagrams with SQL and Mermaid

    What does ER stand for? An Entity Relationship (ER) diagram is one of the most important tools for database design. It helps you visualize the relationships between different entities and how they interact with each other. Many GUI tools have their own tools to build ER diagrams, e.g. pgAdmin IV, DBeaver, etc. In this blog […]

    Read more
    15.02.2023 | Pavlo Golub

    Go & PostgreSQL: FOSDEM 2023 talk

    Intro On the 5th of February 2023, I had the privilege of giving a talk at the PostgreSQL devroom during FOSDEM 2023 titled “When it all GOes right.” My talk focused on the process of creating programs in the Go language using a PostgreSQL database. It was an incredible opportunity to share my insights and […]

    Read more
    24.01.2023 | Laurenz Albe

    A unique constraint where NULL conflicts with everything

    © Laurenz Albe 2022 I have been faced with a request for an unusual unique constraint that puzzled me for a while. Since the solution I came up with is a nice show-case for range data types, I’ll share it with you. Also, it allows me to rant some about NULL, which is a temptation […]

    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
    16.11.2021 | Pavlo Golub

    PostgreSQL on WSL2 for Windows: Install and setup

    UPDATE on 23.02.2023: This post explains how to install PostgreSQL on WSL2 for Windows, apply the necessary changes to PostgreSQL settings, and access the database from the Windows host. Even though this knowledge can be found in different bits and pieces spread out all over the internet, I want to compile a short and straightforward […]

    Read more
    30.09.2021 | Laurenz Albe

    Gaps in sequences in PostgreSQL

    © Laurenz Albe 2021 Most database tables have an artificial numeric primary key, and that number is usually generated automatically using a sequence. I wrote about auto-generated primary keys in some detail in a previous article. Occasionally, gaps in these primary key sequences can occur – which might come as a surprise to you. This […]

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

    Building an Oracle to PostgreSQL migrator: Lessons learned

    Today, I want to share some of the lessons learned when it comes to actually building an Oracle to PostgreSQL migration tool. Moving from Oracle to PostgreSQL has become a popular sport, widely adopted by many who want to free themselves from license costs, hefty support costs and also technical limitations on the Oracle side. […]

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

    PostgreSQL: Create indexes after bulk loading

    Over the years, many of our PostgreSQL clients have asked whether it makes sense to create indexes before – or after – importing data. Does it make sense to disable indexes when bulk loading data, or is it better to keep them enabled? This is an important question for people involved in data warehousing and […]

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

    Understanding LATERAL joins in PostgreSQL

    LATERAL joins are one of the lesser-known features of PostgreSQL and other relational databases such as Oracle, DB2 and MS SQL. However, LATERAL joins are a really useful feature, and it makes sense to take a look at what you can accomplish with them. Inspecting FROM more closely Before we dive into LATERAL, it makes […]

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

    Creating and refreshing materialized views in PostgreSQL

    A “materialized view” is a database object which stores the result of a precalculated database query and makes it easy to refresh this result as needed. Materialized views are an integral feature of pretty much all advanced database systems. Naturally, PostgreSQL also provides support for materialized views, and offers the end-user a powerful tool to […]

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

    PostgreSQL: Sophisticating temporary tables

    Temporary tables have been around forever and are widely used by application developers. However, there is more to temporary tables than meets the eye. PostgreSQL allows you to configure the lifespan of a temporary table in a nice way and helps to avoid some common pitfalls. CREATE TEMPORARY TABLE … By default, a temporary table […]

    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