Background Reviewing some SQL and wanted to point out a tiny dissimilarity between database platforms. Data Our test table is Oracle's SCOTT.EMP Here is what the data looks like :- Substring/Substr The HIREDATE is the only well formatted fixed length string. Its format is DD-MMM-YY. Query Oracle SQL Output Explanation Oracle relies on Substr to … Continue reading ANSI Standards – Database – Substring
Database ( Technical )
Database – Buffer Pool – Clearing
Background During Database performance stressing, data is ingested into memory and written to disk. As one tries out different database table structures and Data Manipulation Languages ( DML ) queries, one often finds it useful to clean the slate in between. Granularity Like any other operation one can target processing against the server, database, … Continue reading Database – Buffer Pool – Clearing
PostgreSQL :- Datatype – bpchar
Background I went back and started reviewing the script uses for creating the customer table in the Northwind database. northwind pthom We are using the script furnished by pthom. It is published via GitHub and availed here. Here is the portion that creates the customer table What is bpchar? The customer_id column is defined as bpchar … Continue reading PostgreSQL :- Datatype – bpchar
PostgreSQL :- List Tables
Background Let us quickly list tables. Outline The Information Schema is an ANSI compliant Schema and works well across all database vendors. pg_catalog is PostgreSQL native. Here are the tables and views that we will use in this exercise. information_schema information_schema.tables pg_catalog pg_catalog.pg_tables pg_catalog.pg_class Query information_schema information_schema.tables SQL Output pg_catalog pg_catalog.pg_tables SQL Output pg_catalog.pg_class SQL … Continue reading PostgreSQL :- List Tables
PostgreSQL :- Sample Database – northwind on Windows
Background Wanted to get understanding of a couple of queries on PostgreSQL database. Download Downloaded and Installed on a MS Windows Machine. Sample Databases Googled for Sample databases. Northwind database Northwind database seems to be most familiar to me. Artifacts pthom user pthom has a working version available on GitHub. Repository The Repository is https://github.com/pthom/northwind_psql … Continue reading PostgreSQL :- Sample Database – northwind on Windows
SQL Beautifier
Background As a Database Administrator get so see a bit of SQL Statements. Avenues The SQL gets to the DBA through various avenues, inclusive :- Developers Submitted to review and execute against Production Database Monitoring Tools In quite a few cases, the first thing one has to do is space things out a bit. … Continue reading SQL Beautifier
dbfiddle – Constraints
Background A couple of posts ago spoke about db<>Fiddle. In this post we will start highlighting features that are not available due to security and feature set constraints in a shared environment. Legend db<>fiddle Initial Link Code Schema Create SQL Fails SQL Error SQL Works SQL Memory Optimized Table Create SQL Fails Error Delimiter/GO SQL … Continue reading dbfiddle – Constraints
