Database Programming

PostgreSQL LEFT() and RIGHT() functions revisitied – String comparison use case.

In my day job (Pipeline Survey Data Analyst) I sometimes have the opportunity to write custom SQL queries in an MS Access database, which is the back end of one of the proprietary GIS solutions, my colleagues use. Although I feel that Access’s SQL implementation is not quite as robust as other SQL dialects, it does get the job done in certain situations (the visual interface continues to grow on me). For a learning experiment, I decided to reproduce – and solve – the same requirements using PostgreSQL, as that I had in the MS Access environment. However, I discovered an all-together different challenge.

Both MS Access and MySQL provide several string functions. One of those is a particularly useful string comparing function. MySQL has STRCMP() while in Access, there is a similar StrComp(). Postgres does not have its own version that I am aware of. I leaned heavily on this type of function in MS Access. Hopefully, readers will clue me in on what Postgres-specific string function I could use in its stead. Meanwhile, read on to see the workaround I used…

[Head this way for great PostgresSQL blogging >>>]
Advertisements

Pandas concat() then to_sql() – CSV upload to PostgreSQL

Recently, I wrote a post about inserting pandas DataFrame values into a PostgreSQL table, using SQLAlchemy data types to type cast them to the desired data type. In this post, I’ll explore – and leverage – pandas.concat() and get a two for one by merging 2 pandas DataFrames into 1, prior to uploading.

[Head this way for great PostgresSQL blogging >>>]

Pandas to SQL – importing CSV data files into PostgreSQL

My goal with this post is to cover what I have learned while inserting pandas DataFrame values into a PostgreSQL table using SQLAlchemy. Interested in learning about this yourself? Want to see a simple example? You are in the right place so keep reading and learn with me…

[Head this way for great PostgresSQL blogging >>>]

Postgres, Python, and Psycopg2 – executemany() method CSV upload example.

Having previously covered a couple of different ways to upload a CSV’s worth of data into a PostgreSQL table, this post will be quite similar to the second one, with a slight change in the psycopg2 method used. Visit COPY and CAST() – Bulk uploads in PostgreSQL, and Python and psycopg2 for CSV bulk uploads in PostgreSQL – with examples to get up to speed. Aside from that, read on to see the differences between the methods used…

[Head this way for great PostgresSQL blogging >>>]

Python and psycopg2 for CSV bulk upload in PostgreSQL – with examples…

In a previous post, I explored using both the COPY command and the CAST() function together in order to upload an entire CSV file’s data into a PostgreSQL database table. This post is a continuation, of sorts. However, I will use Python and the psycopg2 library, in a simple script, to handle these uploads instead of SQL. While I am still a novice with operations such as these, I feel that writing about it is a fantastic way to learn and obtain valuable feedback…

[Head this way for great PostgresSQL blogging >>>]