# psql Published articles for psql. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## Waiting for PostgreSQL 19 - psql: Add %i prompt escape to indicate hot standby status. DevFeed: [Waiting for PostgreSQL 19 - psql: Add %i prompt escape to indicate hot standby status.]() Original publisher: [Read original article]() Author: depesz Published: 2026-02-21T14:51:47Z Content type: article Language: en Sources: [select \* from depesz;]() Topics: [PostgreSQL](), [Replication]() Tags: [2026](), [commit](), [pg19](), [postgresql](), [primary](), [psql](), [replication](), [standby](), [uncategorized](), [waiting]() ### AI overview The article explains a PostgreSQL 19 patch that adds the %i prompt escape to psql. The escape indicates whether the connected server is a primary or operating in hot standby mode, making replication status visible in the psql prompt. ### Source excerpt On 3rd of February 2026, Fujii Masao committed patch: psql: Add %i prompt escape to indicate hot standby status. This commit introduces a new prompt escape %i for psql, which shows whether the connected server is operating in hot standby mode. It expands to standby if the server reports in_hot_standby = on, and primary ... Continue reading "Waiting for PostgreSQL 19 - psql: Add %i prompt escape to indicate hot standby status." ## Waiting for PostgreSQL 19 - Sequence synchronization in logical replication. DevFeed: [Waiting for PostgreSQL 19 - Sequence synchronization in logical replication.]() Original publisher: [Read original article]() Author: depesz Published: 2025-11-11T12:24:23Z Content type: tutorial Language: en Sources: [select \* from depesz;]() Topics: [PostgreSQL](), [Replication](), [Sequences]() Tags: [benchmark](), [logical](), [pg19](), [postgresql](), [psql](), [publications](), [replication](), [sequence](), [sequences](), [synchronization](), [uncategorized](), [upgrade](), [upgrades](), [waiting]() ### AI overview This article demonstrates PostgreSQL 19's planned sequence synchronization for logical replication. It sets up source and destination databases, tests sequence synchronization, finds that resynchronization must be manually invoked, and reports that synchronizing 10,000 sequences took about one second in the author's test. ### Source excerpt First, on 9th of October 2025, Amit Kapila committed patch: Add "ALL SEQUENCES" support to publications. This patch adds support for the ALL SEQUENCES clause in publications, enabling synchronization/replication of all sequences that is useful for upgrades. Publications can now include all sequences via FOR ALL SEQUENCES. psql enhancements: \d shows publications for ... Continue reading "Waiting for PostgreSQL 19 - Sequence synchronization in logical replication." ## Waiting for PostgreSQL 19 - Add psql PROMPT variable for search\_path. DevFeed: [Waiting for PostgreSQL 19 - Add psql PROMPT variable for search\_path.]() Original publisher: [Read original article]() Author: depesz Published: 2025-10-29T11:59:17Z Content type: release Language: en Sources: [select \* from depesz;]() Topics: [PostgreSQL]() Tags: [commit](), [pg19](), [postgresql](), [prompt](), [prompt1](), [psql](), [query](), [schema](), [schemas](), [search-path](), [uncategorized](), [waiting]() ### AI overview A PostgreSQL patch adds a psql PROMPT variable for displaying the current search_path. The %S substitution works when connected to Postgres 18 or newer, because search_path was first marked as GUC_REPORT in a prior commit. ### Source excerpt On 28th of October 2025, Nathan Bossart committed patch: Add psql PROMPT variable for search_path. The new %S substitution shows the current value of search_path. Note that this only works when connected to Postgres v18 or newer, since search_path was first marked as GUC_REPORT in commit 28a1121fd9. On older versions that don't report search_path, ... Continue reading "Waiting for PostgreSQL 19 - Add psql PROMPT variable for search_path." ## Getting comfortable with psql DevFeed: [Getting comfortable with psql]() Original publisher: [Read original article]() Author: Map Published: 2024-11-11T15:57:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [Command-line interface](), [Database]() Tags: [cli](), [commands](), [config](), [database](), [pager](), [postgres](), [psql](), [query](), [tables](), [timing](), [vim]() ### AI overview A practical introduction to getting comfortable with psql, the PostgreSQL command-line interface. It covers utility commands, describing tables, configuration options, query timing, output formatting, pager settings, and editing queries with an external editor. ### Source excerpt psql is a CLI editor that ships with Postgres. It's incredibly powerful for working with Postgres, and doesn't take too much of a learning curve to start to get comfortable so you can really feel like an expert working with your database. Just a rundown of a few things to get you started: Once connected in psql you can get an idea of all utility commands available with: \? A handy thing I use all the time is \d. \d will describe the tables within database. You can also add a table/index/etc. onto it to describe that specific table such as: \d accounts There are a number of options you can set in your psqlrc (config) file to customize your CLI experience. But you can also toggle those when directly working in psql. \timing will give you the time it took to run your query \x auto will autoformat your text output \pset pager 0 turns off your pager or 1 to turn it back on Oh and for editing a query in your editor of choice. Make sure you set your $EDITOR enviroment variable to the editor of your choice, though the only right choice is vim: \e Just a few things to get you started working with psql. Your browser does not support video ## PostgreSQL DevFeed: [PostgreSQL]() Original publisher: [Read original article]() Author: Derek Lee Published: 2022-03-01T00:00:00Z Content type: tutorial Language: en Sources: [art and science of coding]() Topics: [PostgreSQL](), [Databases](), [SQL](), [configuration](), [timezone](), [JSON]() Tags: [configuration](), [database](), [dev-recipes](), [installation](), [json](), [postgresql](), [psql](), [setup](), [sql](), [timezone](), [troubleshooting]() ### AI overview A reference guide to PostgreSQL installation, database and user management, SQL statements, configuration, time zone settings, psql commands, JSON fields, and troubleshooting connection problems. ### Source excerpt Installation / Setup ## Exploring a new Postgres database DevFeed: [Exploring a new Postgres database]() Original publisher: [Read original article]() Author: Map Published: 2020-11-14T19:30:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [Database](), [Command-line interface](), [file](), [Sequences](), [Heroku]() Tags: [cli](), [cloud](), [databases](), [file](), [heroku](), [postgres](), [postgresql](), [psql](), [sequences]() ### AI overview A practical guide to exploring an unfamiliar Postgres database using the psql command-line client. It covers configuring psql defaults, inspecting database objects and tables, examining data, formatting output, and working with JSONB. ### Source excerpt At past jobs I'd estimate we had 100 different production apps that in some way were powering key production systems. Sure some were backend/internal apps while others key production apps such as the dashboard itself. At other companies we had a smaller handful of Heroku apps that powered our cloud service, about 5-10 in total. Even just working with those internal apps it's a number of things to keep context on. But when it comes to interacting with something you don't know getting a lay of the land quickly is key. In helping a customer optimize and tune, or even just understand what is going on in their app an understanding of the data model is key. As I just started a few months back at Crunchy Data I found myself digging into a lot of new systems and quickly trying to ramp up and get a feel for them. Over the past 10 years I've pretty well codified my steps to getting a feel for a new database. While I'm not a DBA and a small portion of my job is spent inside a database being able to quickly navigate one saves me hours each month and days out of the year. I'm sure my process isn't perfect, but hopefully it helps other when navigating a new Postgres database for the first time. First the tooling For any new database my go to tool is psql. The built-in Postgres CLI is going to be the quickest thing for me to navigate around. If you use a CLI for anything else then this should be your preference here as well. I'm also going to have a psqlrc file setup that has some good defaults. My go to defaults in my psqlrc are: -- Automatically format output based on result length and screen \x auto -- Prettier nulls \pset null '#' -- Save history based on database name \set HISTFILE ~/.psql_history- :DBNAME -- Turn on automatic query timing \timing Getting a feel for the tables The first thing I'm going to do is just table a look at which objects exist within the database with \d. This will spit out a mix of tables, views, sequences all within your database. A cleaner version ## Postgres Connection Strings and psql DevFeed: [Postgres Connection Strings and psql]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2019-09-04T09:38:02Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Command-line interface](), [Terminal]() Tags: [cli](), [command-line](), [environment](), [postgres](), [postgresql](), [psql](), [terminal]() ### AI overview This short tutorial explains how PostgreSQL connection strings can use key-value notation, a URI scheme, or psql command-line options with environment variables. It shows that psql can accept a complete connection string, allowing developers to reuse an application's connection string at the console to test a database connection. ### Source excerpt PostgreSQL connection strings embedded in your application can take two different forms: the key-value notation or the postgresql:// URI scheme. When it comes to using psql though, another form of connection string is introduced, with command line options -h -p -U and environment variable support. In this short article you will learn that you can use either of the three different forms in psql and thus easily copy & paste you application connection string right at the console to test it! ## Postgres hidden gems DevFeed: [Postgres hidden gems]() Original publisher: [Read original article]() Author: Map Published: 2018-01-31T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Database](), [Replication](), [Network Operations](), [Statistics]() Tags: [functions](), [jsonb](), [lateral](), [network-operations](), [postgres](), [postgresql](), [psql](), [replication](), [statistics](), [text](), [unicode](), [window-functions]() ### AI overview The article collects PostgreSQL features that the author and contributors consider useful or easily overlooked. It highlights capabilities including ltree, citext, date arithmetic, jsonb, lateral joins, window functions, custom foreign data wrappers, replication slots, range types, statistics, logical decoding, and psql features. ### Source excerpt Postgres has a rich set of features, even when working everyday with it you may not discover all it has to offer. In hopes of learning some new features that I didn't know about myself as well as seeing what small gems people found joy in I tweeted out to see what people came back from. The response was impressive, and rather than have it lost into ether of twitter I'm capturing some of the responses here along with some resources many of the features. @listrophy - $ brew postgresql-update database Though personally I prefer Postgres.app ;) @pat_shaugnessy - ltree Pat has a great post that walks through ltree @billyfung - citext A really handy datatype for case insensitive text @eeeebbbbrrrr - date math with intervals I couldn't agree more on this one, working with time in Postgres is the easiest time I've every had @DataMiller - The jsonb datatype and lateral joins I'd argue it's hard to claim now JSONB is a hidden gem, but lateral joins are certain a great one @ideasasylum - row_number() over(partition http://orders.site_id order by orders.created_at) Window functions are definitely a handy feature was my hidden (to me) discovery this week @franckverrot - Index access method, and custom FDWs @jonjensen0 - Set-returning functions and custom aggregate functions can be very helpful. @ascherbaum - psql -x Psql is indeed awesome and can be well tuned @Abstr_ct - The fact that the docs are fantastic and all hidden gems are actually readily available. Oh, and pl/brainfuck obviously @Halpin_IO - Subnetting and network operations @jkatz05 - Replication slots, both physical and logical. They've made setting up replication infinitely easier. And range types. Because they're awesome. @petereisentraut - Unicode table borders @_avichalp Notify/listen @simonw - The fact that GIN indices can make LIKE queries run fast even if the % isn't just at the end of the string @javisantana - it has a statistics system to plan queries that can be used by the user when accuracy does not matt ## Setting up psql, the PostgreSQL CLI DevFeed: [Setting up psql, the PostgreSQL CLI]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2017-12-22T14:23:43Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Command-line interface](), [console](), [Scripting](), [SQL](), [Environment Variables]() Tags: [cli](), [console](), [environment-variables](), [history](), [input](), [interactive](), [keyboard](), [less](), [pager](), [postgresql](), [psql](), [redirection](), [repl](), [scripting](), [setup]() ### AI overview A tutorial on setting up and using psql, PostgreSQL's command-line console. It explains interactive and scripting features, the REPL workflow, and configuration through the psqlrc file and environment variables. ### Source excerpt PostgreSQL ships with an interactive console with the command line tool named psql. It can be used both for scripting and interactive usage and is moreover quite a powerful tool. Interactive features includes autocompletion, readline support (history searches, modern keyboard movements, etc), input and output redirection, formatted output, and more. ## SQL Regression Tests DevFeed: [SQL Regression Tests]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2017-08-08T15:55:51Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [SQL](), [Testing](), [PostgreSQL](), [Unit testing]() Tags: [postgresql](), [psql](), [regression](), [sql](), [testing](), [tests]() ### AI overview This article explains how to unit test and regression test SQL queries. It distinguishes unit tests against static arguments and data sets from regression tests that verify a query continues to meet the same contract after changes. It also discusses integrity testing and introduces pgTAP for PostgreSQL unit tests. ### Source excerpt In a previous article here we saw How to Write SQL in your application code. The main idea in that article is to maintain your queries in separate SQL files, where it's easier to maintain them. In particular if you want to be able to test them again in production, and when you have to work and rewrite queries. ## How to Write SQL DevFeed: [How to Write SQL]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2017-06-08T11:23:26Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [SQL](), [Code](), [Python](), [Clojure](), [PostgreSQL]() Tags: [code](), [how-to](), [orm](), [postgresql](), [psql](), [python](), [repl](), [sql]() ### AI overview A tutorial on writing SQL as repository files rather than embedding queries as strings or relying entirely on an ORM. It discusses maintaining and debugging raw SQL, demonstrates a query against the Chinook database, and explains how psql variables can be used to run and edit queries interactively. ### Source excerpt Kris Jenkins cooked up a very nice way to embed SQL in your code: YeSQL for Clojure. The main idea is that you should be writing your SQL queries in .sql files in your code repository and maintain them there. The idea is very good and it is now possible to find alternative implementations of the Clojure yesql library in other languages. Today, we are going to have a look at one of them for the python programming language: anosql. ## A year's look at Postgres DevFeed: [A year's look at Postgres]() Original publisher: [Read original article]() Author: Map Published: 2014-03-26T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Command-line interface](), [data type](), [Security](), [Vulnerabilities]() Tags: [data-type](), [postgres](), [postgresql](), [psql](), [security]() ### AI overview The article looks back at highlights from the first year of Postgres Weekly, including PostgreSQL features, the postgresql-hll extension, the psql command-line editor, and coverage of a PostgreSQL security vulnerability. ### Source excerpt A couple years back I started more regularly blogging, though I've done this off and on before, this time I kept some regularity. A common theme started to emerge with some content on Postgres about once a month because most of what was out there was much more reference oriented. A bit after that I connected with petercooper, who runs quite a few weekly email newsletters. As someone thats been interested helping give others a good reason to create content the obvious idea of Postgres Weekly emerged. Since then we've now had the newsletter running for over a year, helped surface quite a bit of content, and grown to over 5,000 subscribers. First if you're not subscribed, then go subscribe now. And if you need some inspiration or just want to reminisce with me... here's a look back at a few highlights over the past year: The inagural issue Postgres: The Bits You Haven't Found A slide-deck from a presentation at Heroku's Waza conference that highlights many of the more unknown and rare features within Postgres, including 'WITH', arrays, pub/sub, and hstore. Open Source Release:postgresql-hll Aggregate Knowledge released Postgres HyperLogLog, which is a new Postgres datatype hll that strikes a balance between HyperLogLog and a simple set. This data type solves the problem of calculating uniques for a given data set efficiently both in performance and storage. The above is still one of my favorite extensions that most of the world doesn't know about How I Work with Postgres - Psql, My PostgreSQL Admin A common question for anyone new or even experienced with Postgres is whats the best editor out there? Most when they are asking this are asking for a GUI editor, this post highlights much of the power in the CLI 'psql' editor. A mix of notable entries Issue 6 Dissecting PostgreSQL CVE-2013-1899 After the heavily publicized and very serious security vulnerability was patched last week Blackwing intelligence took the chance to dig in. Read more on the details of the vulnerabili ## Getting more out of psql (The PostgreSQL CLI) DevFeed: [Getting more out of psql (The PostgreSQL CLI)]() Original publisher: [Read original article]() Author: Map Published: 2013-02-21T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Command-line interface](), [Environment Variables](), [Terminal](), [GUI]() Tags: [cli](), [database](), [environment-variables](), [gui](), [postgres](), [postgresql](), [psql](), [terminal]() ### AI overview A tutorial on customizing PostgreSQL's psql command-line client. It covers terminal presentation, environment variables, prompt colors, query timing, pager settings, configuration files, and aliases. ### Source excerpt After my last post I had a variety of readers reach out about many different tweaks they'd made to their workflows using with psql. One people Grégoire Hubert had a wondeful extensive list of items. Grégoire has been a freelance in web development and he has worked with Postgresql for some time now in addition to being the author of Pomm. Without further ado heres what he has to say on how he uses psql: Get the most of psql Psql, the CLI postgreSQL client, is a powerful tool. Sadly, lot of developers are not aware of the features and instead look for a GUI to provide what they need. Let's fly over what can psql do for you. Feel yourself at home One of the most common misconception people have about CLI is "They are a poor user interface". C'mon, the CLI is the most efficient user interface ever. There is nothing to disturb you from what you are doing and you are by far fastest without switching to your mouse all the time. Let's see how we can configure psql at our convenience. First, you'll have managed to choose a nice and fancy terminal font like monofur or inconsolata. Do not underestimate the power of the font The nice line style shown above can be set with \pset linestyle unicode and \pset border 2. This is just an example of the many environment variables you can play with to get your preferred style of working out of psql. For example, I found the character ¤ the most accurate to express nullity (instead of default NULL). Let's just \pset null ¤ and here it is: SELECT * FROM very_interesting_stat; ┌──────┬──────┬──────┬──────┬──────┐ │ a │ b │ c │ d │ e │ ├──────┼──────┼──────┼──────┼──────┤ │ 9.06 │ ¤ │ ¤ │ ¤ │ ¤ │ │ 7.30 │ 3.55 │ 7.57 │ 3.31 │ ¤ │ │ 7.20 │ 5.08 │ ¤ │ 6.58 │ 5.90 │ ... Another hugely value to get environment variables is colors in the prompt. Colors in the prompt are important because it makes easier to spot where output starts and ends between two interactions at the console. The PROMPT1 environment variable will even let you set an indicat ## How I work with Postgres - psql, My PostgreSQL Admin DevFeed: [How I work with Postgres - psql, My PostgreSQL Admin]() Original publisher: [Read original article]() Author: Map Published: 2013-02-13T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Databases](), [SQL]() Tags: [database](), [editor](), [postgres](), [postgresql](), [psql](), [sql]() ### AI overview A practical guide to using PostgreSQL's psql as a database interface. It covers setting an editor, using \e, enabling \x auto, keeping unlimited history, and exploring database objects with \d. ### Source excerpt On at least a weekly basis and not uncommonly multiple times in a single week I get this question: @neilmiddleton I've been hunting for a nice PG interface that works within other things. PGAdmin kinda works, except the SQL editor is a piece of shit Sometimes it leans more to, what is the Sequel Pro equivilant for Postgres. My default answer is I just use psql, though I do have to then go on to explain how I use it. For those just interested you can read more below or just get the highlights here: Set your default EDITOR then use \e On postgres 9.2 and up \x auto is your friend Set history to unlimited \d all the things Before going into detail on why psql works perfectly fine as an interface I want to rant for a minute about what the problems with current editors are and where I expect them to go in the future. First this is not a knock on the work thats been done on previous ones, for their time PgAdmin, phpPgAdmin, and others were valuable tools, but we're coming to a point where theres a broader set of users of databases than ever before and empowering them is becoming ever more important. Empowering developers, DBA's, product people, marketers and others to be comfortable with their database will lead to more people taking advantage of whats in their data. pg_stat_statements was a great start to this laying a great foundation for valuable information being captured. Even with all of the powerful stats being captured in the statistics of PostgreSQL so many are still terrified when they see something like: QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Hash Join (cost=4.25..8.62 rows=100 width=107) (actual time=0.126..0.230 rows=100 loops=1) Hash Cond: (purchases.user_id = users.id) -> Seq Scan on purchases (cost=0.00..3.00 rows=100 width=84) (actual time=0.012..0.035 rows=100 loops=1) -> Hash (cost=3.00..3.00 rows=100 width=27) (actual time=0.097..0.097 rows=100 loops=1) Buckets: 1024 B ## Using a PostgreSQL Foreign Data Wrapper to Query and Join Redis Data DevFeed: [Using a PostgreSQL Foreign Data Wrapper to Query and Join Redis Data]() Original publisher: [Read original article]() Author: Map Published: 2012-10-18T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [Redis](), [Databases](), [SQL](), [Code]() Tags: [code](), [database](), [development](), [git](), [github](), [postgres](), [psql](), [redis](), [sql]() ### AI overview This tutorial explains how to connect Redis to PostgreSQL using a Foreign Data Wrapper, configure the extension and foreign table, and query or join Redis data with PostgreSQL data. ### Source excerpt Yesterday there was a post which hit Hacker News that talked about using SQL to access Mongo. While this is powerful I think much of the true value was entirely missed within the post. SQL is an expressive language, though people are often okay with accessing Mongo data through its own ORM. The real value is that you could actually query the data from within Postgres then join across your data stores, without having to do some ETL process to move data around. Think... joining sales data from Postgres with user reviews stored in Mongo or searching for visits to a website (retained in redis) against purchases by user in Postgres. The mechanism pointed out was a MongoDB Foreign Data Wrapper. A Foreign Data Wrapper or FDW essentially lets you connect to an external datastore from within a Postgres database. In addition to the Mongo FDW released the other day there's many others. For example Postgres 9.0 and up ships with one called db_link, which lets you query and join across two different Postgres databases. Beyond that there's support for a variety of other data stores including some you may have never expected: Redis Textfile MySQL Oracle ODBC LDAP Twitter More Lets look at actually getting the Redis one running then see what some of the power of it really looks like. First we have to get the code then build it: git clone git://github.com/antirez/hiredis.git cd hiredis make sudo make install Then download the FDW from PGXN PATH=/Applications/Postgres.app/Contents/MacOS/bin/:$PATH USE_PGXS=1 make sudo PATH=/Applications/Postgres.app/Contents/MacOS/bin/:$PATH USE_PGXS=1 make install Now you'll want to connect to your Postgres database, using psql and enable the extension, and finally create your foreign table to your redis server. This is assuming a locally running redis, though you could connect to a remote just as easily: CREATE EXTENSION redis_fdw; CREATE SERVER redis_server FOREIGN DATA WRAPPER redis_fdw OPTIONS (address '127.0.0.1', port '6379'); CREATE FOREIGN TAB