# Four PostgreSQL Tips

DevFeed: [Four PostgreSQL Tips](<https://devfeed.tech/articles/four-postgresql-tips-21064.md>)

Original publisher: [Read original article](<https://jakeyesbeck.com/2016/02/21/four-postgresql-tips/>)

Published: 2016-02-21T12:00:00Z

Content type: article

Language: en

Sources: [Jake Yesbeck](<https://devfeed.tech/sources/jake-yesbeck.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Command-line interface](<https://devfeed.tech/topics/cli.md>), [Object-relational mapping](<https://devfeed.tech/topics/orm.md>), [web applications](<https://devfeed.tech/topics/web-applications.md>)

Tags: [command-line](<https://devfeed.tech/tags/command-line.md>), [database](<https://devfeed.tech/tags/database.md>), [open-source](<https://devfeed.tech/tags/open-source.md>), [orm](<https://devfeed.tech/tags/orm.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [production](<https://devfeed.tech/tags/production.md>), [sql](<https://devfeed.tech/tags/sql.md>)

## AI overview

This article presents PostgreSQL tips for detecting duplicate rows with window functions, filtering queries by time ranges, and executing queries remotely through the command-line interface. It also introduces PostgreSQL as an open-source object-relational database commonly used by production web applications.

## Source excerpt

PostgreSQL is an open source object-relational database used to power many production web applications. While many web applications interact with relational databases through Object Relational Mappers (ORM), direct SQL queries via a command line interface or graphical client are still common. When writing these queries, these four tips may come in handy. All examples will assume the presence of a simple users table: Column | Type ---------------+----------------------------- id | integer first_name | character varying(255) last_name | character varying(255) email | character varying(255) created_at | timestamp without time zone updated_at | timestamp without time zone password | character varying registered | boolean registered_at | timestamp without time zone Indexes: "users_pkey" PRIMARY KEY, btree (id) 1. Finding Duplicate Rows A common mechanism for defending against duplicate rows in database tables are unique indexes. However, at the time of table creation, a unique index or two may have been forgotten. Duplicates in a table must be removed before a unique index may be added. A great way to detect duplicates in PostgreSQL is by using window functions. Window functions are similar to aggregates; but, instead of grouping rows for the response, it maintains each row's individuality. Desired query: Find all duplicate users with the same first_name, last_name, and email, returning duplicate ids only (do not return the oldest id). SELECT id from ( SELECT id, ROW_NUMBER() OVER( PARTITION BY first_name, last_name, email ORDER BY id ) AS user_row_number FROM users ) duplicates WHERE duplicates.user_row_number > 1 This query will identify all the rows of the users table which share the same defined columns and return the primary key (id) of rows after the first via the duplicates.user_row_number > 1 condition. The result of this query can then be fed into a DELETE query to remove the duplicates. The ROW_NUMBER() built-in function returns an incremented value assigned to