Stories tagged with SQL - DeveloperSitesStories tagged with SQL - DeveloperSites2024-03-28T08:19:39Zhttps://www.developersites.com/Tag/SQLCopyright (c) DeveloperSitesDeveloperSites Atom GeneratorDeveloperSites webmastermatthewpomar@developersites.comhttps://www.developersites.com/Assets/Images/favicon.icohttps://www.developersites.com/Assets/Images/logo2.png12025https://www.developersites.com/The-Many-Faces-of-DISTINCT-in-PostgreSQLThe Many Faces of DISTINCT in PostgreSQL2021-12-10T02:18:07Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://hakibenita.com/the-many-faces-of-distinct-in-postgre-sql&size=screen&instance_id=12&cache=2592000"/></div><div>I started my programming career as an Oracle DBA. It took a few years but eventually I got fed up with the corporate world and I went about doing my own thing. After I gotten over not having proper partitions and MERGE statement, I found some nice unique features in PostgreSQL. Oddly enough, a lot of them contained the word DISTINCT.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/The-Many-Faces-of-DISTINCT-in-PostgreSQL"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2fhakibenita.com%2fthe-many-faces-of-distinct-in-postgre-sql" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/The-Many-Faces-of-DISTINCT-in-PostgreSQL110https://www.developersites.com/9-Django-Tips-for-Working-with-Databases9 Django Tips for Working with Databases2021-12-10T02:14:17Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://hakibenita.com/9-django-tips-for-working-with-databases&size=screen&instance_id=12&cache=2592000"/></div><div>ORMs offer great utility for developers but abstracting access to the database has its costs. Developers who are willing to poke around the database and change some defaults often find that great improvements can be made.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/9-Django-Tips-for-Working-with-Databases"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2fhakibenita.com%2f9-django-tips-for-working-with-databases" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/9-Django-Tips-for-Working-with-Databases110https://www.developersites.com/Be-Careful-With-CTE-in-PostgreSQLBe Careful With CTE in PostgreSQL2021-12-10T01:32:43Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://hakibenita.com/be-careful-with-cte-in-postgre-sql&size=screen&instance_id=12&cache=2592000"/></div><div>PostgreSQL, SQL, PerformanceHow to avoid common pitfalls with common table expressions in PostgreSQL
Common table expressions (CTE), also known as the WITH clause, are a very useful feature. They help break down big queries into smaller pieces which makes it easier to read and understand.
PostgreSQL Version
This article is intended for PostgreSQL versions 11 and prior. Starting at version 12, PostgreSQL changed the way it treats CTE to prevent the issues described in this article.Wha...</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/Be-Careful-With-CTE-in-PostgreSQL"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2fhakibenita.com%2fbe-careful-with-cte-in-postgre-sql" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/Be-Careful-With-CTE-in-PostgreSQL100https://www.developersites.com/How-We-Solved-a-Storage-Problem-in-PostgreSQL-Without-Adding-a-Single-Byte-of-StorageHow We Solved a Storage Problem in PostgreSQL Without Adding a Single Byte of Storage2021-12-10T01:29:44Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://hakibenita.com/how-we-solved-a-storage-problem-in-postgre-sql-without-adding-a-single-bytes-of-storage&size=screen&instance_id=12&cache=2592000"/></div><div>A while back we started getting alerts in the middle of the night on low disk space. A quick investigation led us to one of our ETL tasks. Every night the task was fired to eliminate duplicate dumps, and free up some space. This is a short story about how we found our silver bullet and solved the issue without adding a single byte of storage.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/How-We-Solved-a-Storage-Problem-in-PostgreSQL-Without-Adding-a-Single-Byte-of-Storage"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2fhakibenita.com%2fhow-we-solved-a-storage-problem-in-postgre-sql-without-adding-a-single-bytes-of-storage" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/How-We-Solved-a-Storage-Problem-in-PostgreSQL-Without-Adding-a-Single-Byte-of-Storage100https://www.developersites.com/How-to-Create-an-Index-in-Django-Without-DowntimeHow to Create an Index in Django Without Downtime2021-12-10T01:23:53Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://realpython.com/create-django-index-without-downtime/&size=screen&instance_id=12&cache=2592000"/></div><div>In this step-by-step Python tutorial, you'll get a solid understanding of the limitations of Django migrations by tackling a well known problem: creating an index in Django with no downtime.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/How-to-Create-an-Index-in-Django-Without-Downtime"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2frealpython.com%2fcreate-django-index-without-downtime%2f" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/How-to-Create-an-Index-in-Django-Without-Downtime110https://www.developersites.com/How-to-Get-the-First-or-Last-Value-in-a-Group-Using-Group-By-in-SQLHow to Get the First or Last Value in a Group Using Group By in SQL2021-12-10T01:17:36Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://hakibenita.com/sql-group-by-first-last-value&size=screen&instance_id=12&cache=2592000"/></div><div>Getting the last value of a group in an aggregated query in PostgreSQL is a challenging task. In this article we present a simple way to get the first or last value of a group using group by.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/How-to-Get-the-First-or-Last-Value-in-a-Group-Using-Group-By-in-SQL"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2fhakibenita.com%2fsql-group-by-first-last-value" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/How-to-Get-the-First-or-Last-Value-in-a-Group-Using-Group-By-in-SQL110https://www.developersites.com/How-To-Prevent-SQL-Injection-Attacks-With-PythonHow To Prevent SQL Injection Attacks With Python2021-12-10T01:15:20Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://realpython.com/prevent-python-sql-injection/#understanding-python-sql-injection&size=screen&instance_id=12&cache=2592000"/></div><div>SQL injection attacks are one of the most common web application security threat and one of the easiest to prevent. Web application attackers will surely test your web application for this security vulnerability so you must take steps to ensure that this security hole is plugged, and plugged fast!
SQL injection attacks are made possible by allowing non-escaped single or double quote strings to be included in your database's SQL call. This can be done very easily by an attacker by including a partial SQL statement in a form field on your application, such as your login screen, where if the included text is constructed just right, could be used to change the return results of the back-end query that is called during that operation. In our login form example, one could easily see how that might result in the ability of an attacker logging into your web application without knowing a password. This is just one of many examples and risks of not handling this SQL injection exploit.
In this step-by-step tutorial, you'll learn how you can prevent SQL injection via Python. You'll learn how to compose SQL queries with parameters, as well as how to safely execute those queries in your database to keep your application and data secure.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/How-To-Prevent-SQL-Injection-Attacks-With-Python"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2frealpython.com%2fprevent-python-sql-injection%2f%23understanding-python-sql-injection" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/How-To-Prevent-SQL-Injection-Attacks-With-Python171https://www.developersites.com/12-Common-Mistakes-and-Missed-Optimization-Opportunities-in-SQL12 Common Mistakes and Missed Optimization Opportunities in SQL2021-12-10T01:12:55Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://hakibenita.com/sql-dos-and-donts&size=screen&instance_id=12&cache=2592000"/></div><div>SQL is used by analysts, data scientists, product managers, designers and many others. These professionals have access to databases, but they don't always have the intuition and understanding to write efficient queries. In an effort to make my team write better SQL, I went over reports written by non-developers and code reviews, and gathered common mistakes and missed optimization opportunities in SQL.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/12-Common-Mistakes-and-Missed-Optimization-Opportunities-in-SQL"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2fhakibenita.com%2fsql-dos-and-donts" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/12-Common-Mistakes-and-Missed-Optimization-Opportunities-in-SQL100https://www.developersites.com/The-Surprising-Impact-of-Medium-Size-Texts-on-PostgreSQL-PerformanceThe Surprising Impact of Medium-Size Texts on PostgreSQL Performance2021-12-10T01:00:22Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://hakibenita.com/sql-medium-text-performance&size=screen&instance_id=12&cache=2592000"/></div><div>Any database schema is likely to have plenty of text fields. In this article I demonstrate the surprising impact of medium-size texts on query performance.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/The-Surprising-Impact-of-Medium-Size-Texts-on-PostgreSQL-Performance"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2fhakibenita.com%2fsql-medium-text-performance" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/The-Surprising-Impact-of-Medium-Size-Texts-on-PostgreSQL-Performance100https://www.developersites.com/Re-Introducing-Hash-Indexes-in-PostgreSQLRe-Introducing Hash Indexes in PostgreSQL2021-12-10T12:59:22Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://hakibenita.com/postgresql-hash-index&size=screen&instance_id=12&cache=2592000"/></div><div>There is a type of index you are probably not using, and may have never even heard of. It is wildly unpopular, and until a few PostgreSQL versions ago it was highly discouraged and borderline unusable, but under some circumstances it can out-perform even a B-Tree index.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/Re-Introducing-Hash-Indexes-in-PostgreSQL"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2fhakibenita.com%2fpostgresql-hash-index" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/Re-Introducing-Hash-Indexes-in-PostgreSQL100https://www.developersites.com/Practical-SQL-for-Data-AnalysisPractical SQL for Data Analysis2021-12-10T12:56:24Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://hakibenita.com/sql-for-data-analysis&size=screen&instance_id=12&cache=2592000"/></div><div>Pandas is by far the most popular tool for data analysis. It's packed with useful features, it's battle tested and widely accepted. However, pandas comes at a cost which is often overlooked. SQL databases has been around since the 1970s. They contain many features that most developers never heard of, and I want to bring some of them to light.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/Practical-SQL-for-Data-Analysis"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2fhakibenita.com%2fsql-for-data-analysis" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/Practical-SQL-for-Data-Analysis100https://www.developersites.com/18-Powerful-PostgreSQL-Features-You-Did-Not-Know-Existed18+ Powerful PostgreSQL Features You Didn't Know Existed2021-12-10T12:35:29Z<div><div><div style="float:right"><img alt ="" src="https://api.browshot.com/api/v1/simple?key=eNKbJKLoy1t3xAPvI6Aoee6wJQ2nMGOS&url=https://hakibenita.com/postgresql-unknown-features&size=screen&instance_id=12&cache=2592000"/></div><div>PostgreSQL is a powerful, full featured relational database management system. However, this free and open-source DBMS has a surprising number of fantastic features most PostgreSQL developers don't know anything about.
In this article, Haki Benita, a renown and respected blogger on PostgreSQL, Python, and related technologies, list more than 18 lesser known PostgreSQL features that can improve your productivity and make your life easier as a PostgreSQL developer or admin.</div></div><div style="padding-top:4px"><a rev="vote-for" href="https://www.developersites.com/18-Powerful-PostgreSQL-Features-You-Did-Not-Know-Existed"><img alt="Like" src="https://www.developersites.com/image.axd?url=https%3a%2f%2fhakibenita.com%2fpostgresql-unknown-features" style="border:0px"/></a></div></div>matthewpomarhttps://www.developersites.com/Users/nuEwSCbil0OD3tsnOHx2eg2021-12-19T04:44:10Zhttps://www.developersites.com/18-Powerful-PostgreSQL-Features-You-Did-Not-Know-Existed151