Implementing transactions in PostgreSQL using Go lang database/sql package

Introduction

A transaction is a unit of work that you want to treat as “a whole.” It has to either happen in full or not at all. In Go lang, a transaction is essentially an object that reserves a connection to the database. You begin a transaction with a call to db.Begin() and close it with a commit() or rollback() method on the resulting Tx variable.

Continue reading “Implementing transactions in PostgreSQL using Go lang database/sql package”

Querying rows from PostgreSQL from Go lang project

Introduction

In the last post Updating/Deleting rows with Go, we learned to manipulate rows in PostgreSQL database in Go project using database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. In this post, we’ll learn how to query rows i.e. SELECT

Continue reading “Querying rows from PostgreSQL from Go lang project”

Updating and Deleting rows in PostgreSQL from Go lang project

Introduction

In the previous post Inserting records into a database with Go, we learned how to insert rows to PostgreSQL database from Go project using database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. In this post, we’ll learn the remaining two DML (data manipulation language) operations i.e. UPDATE and DELETE.

Continue reading “Updating and Deleting rows in PostgreSQL from Go lang project”

Inserting rows in PostgreSQL db from Go lang project

Introduction

In the previous post Connecting to PostgreSQL from Go lang project, we learned how to connect to the PostgreSQL database from Go lang project using database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. In this post, we’ll learn how to insert data (rows) in a PostgreSQL table.

Continue reading “Inserting rows in PostgreSQL db from Go lang project”

Connecting to PostgreSQL from Go lang project

Introduction

The Go programming language, sometimes referred to as Go lang, is making strong gains in popularity. Chances are if you are a Go developer, you will have to interact with SQL at some point in your project. This blog post will show how to connect to a PostgreSQL database from Go using database/sql package.

Continue reading “Connecting to PostgreSQL from Go lang project”

Connect to PostgreSQL in VS Code

  1. Connect to PostgreSQL instances
  2. View object DDL with ‘Go to Definition’ and ‘Peek Definition’
  3. Write queries with IntelliSense
  4. Run queries and save results as JSON, csv, or Excel

Download link: https://marketplace.visualstudio.com/items?itemName=ms-ossdata.vscode-postgresql

Using VS Code PostgreSQL extension

  1. Open the Command Palette Ctrl + Shift + P  (On mac use  ⌘ + Shift + P)

a

  1. Search and select PostgreSQL: New Query

  2. In the command palette, select Create Connection Profile. Follow the prompts to enter your Postgres instance hostname, database, username, and password.

a

 

 

You are now connected to your Postgres database. You can confirm this via the Status Bar (the ribbon at the bottom of the VS Code window). It will show your connected hostname, database, and user.

Now, let’s try to query database.

  1. Type a query ex. SELECT * FROM pg_stat_activity;

  2. Right-click, select Execute Query / keyboard shortcut [⌘M ⌘R] and the results will show in a new window.

6. You can also save the query results as JSONCSV or Excel.

So now, you can seamlessly code for PostgreSQL from Microsoft VS Code without switching screens, leverage powerful intellisense and execute queries.

Enjoy Coding!

IMP NOTE: Result windows from queries won´t show up again after being closed. This is bug with current version and is being worked by dev team. Workaround is either to keep the result window Open Or close / re-open the VS code window.

Azure Data Studio PostgreSQL Extension – Custom insight dashboard

Azure Data Studio (formerly SQL Operations Studio) is a free Cross-Platform DB management tool for Windows, macOS and Linux.  Staying true to their promise of offering a unified data management experience for developers, Microsoft recently released PostgreSQL Extension for Azure Data Studio

Continue reading “Azure Data Studio PostgreSQL Extension – Custom insight dashboard”

PostgreSQL Table Partitioning Part III – Partition Elimination

Understanding Partition Elimination in PostgreSQL 11

This is Part-III for my series on Postgres Table partitioning. I’ll encourage you to also read Part-I and II on this topic.

Continue reading “PostgreSQL Table Partitioning Part III – Partition Elimination”

PostgreSQL Table Partitioning Part II – Declarative Partitioning

Starting Postgres 10.x and onward, it is now possible to create declarative partitions.

In my previous post ‘postgresql-table-partitioning-part-i-implementation-using-inheritance‘, I discussed about implementing Partitioning in PostgreSQL using ‘Inheritance’. Up until PostgreSQL 9, it was only way to partition tables in PostgreSQL. It was simple to implement, however had some limitations like:

Continue reading “PostgreSQL Table Partitioning Part II – Declarative Partitioning”

PostgreSQL Table Partitioning Part I – Implementation Using Inheritance

In earlier PostgreSQL versions, it was not possible to declare table partitions syntactically. Partitioning can be implemented using table inheritance. The inheritance approach involves creating a single parent table and multiple child tables (aka. Partitions) to hold data in each partition range.

In this post, I’ll discuss the implementation of table partitions using inheritance. However before proceeding, let’s first understand why do we need partitioning? Continue reading “PostgreSQL Table Partitioning Part I – Implementation Using Inheritance”