PostgreSQL: Get ROW_COUNT of Last Executed Query Like MS SQL @@ROWCOUNT In Microsoft SQL Server, @@ROWCOUNT returns the number of rows affected by the most recent SQL statement. PostgreSQL doesn’t use @@ROWCOUNT, but provides a similar way to get the affected row count using the GET DIAGNOSTICS statement. :contentReference[oaicite:0]{index=0} What is ROW_COUNT in PostgreSQL? In PostgreSQL, the equivalent of MSSQL’s @@ROWCOUNT is the ROW_COUNT value retrieved via GET DIAGNOSTICS . This captures the number of rows impacted by the last INSERT , UPDATE , or DELETE statement executed in a PL/pgSQL block. :contentReference[oaicite:1]{index=1} Example: Using GET DIAGNOSTICS The following example shows how to update records and capture the number of rows affected: DO $$ DECLARE v_RowCountInt INTEGER; BEGIN UPDATE usermst SET emailid = 'abc@gmail.com'; GET DIAGNOSTICS v_RowCountInt = ROW_COUNT; RAISE NOTICE 'Rows affected: %', v_RowCountInt;...

PostgreSQL: Convert Multiple Rows to JSON PostgreSQL supports powerful JSON functions that let you convert regular table rows into JSON format. This is especially useful when building APIs, exporting data, or preparing structured output for web applications. Understanding JSON Functions in PostgreSQL PostgreSQL includes several JSON functions, and one of the most useful for transforming multiple rows into JSON output is row_to_json() . This function turns a single row into a JSON object. :contentReference[oaicite:0]{index=0} Example Table and Sample Data Suppose we create a sample table to demonstrate how multiple rows can be exported as JSON. CREATE TABLE UserMst ( code INTEGER, name VARCHAR(100), status VARCHAR(50) ); INSERT INTO UserMst (code, name, status) VALUES (1001, 'Jacob', 'Active'), (1002, 'William', 'In-Active'), (1003, 'Sophia', 'Active'); Convert Every Row to a JSON Object Use the following SQL que...

PostgreSQL: How to List Tables Using psql and pgAdmin When working with PostgreSQL databases, one of the most common tasks is viewing the list of tables available in a database. PostgreSQL provides multiple ways to list tables, including the psql command-line tool and the pgAdmin graphical interface . This guide explains both methods clearly with examples. List Tables Using psql Command Line The psql tool provides simple meta-commands to explore database objects. 1. Connect to Database psql -U username -d database_name 2. List All Tables \dt This command lists all tables in the current schema. 3. List Tables from a Specific Schema \dt public.* 4. List Tables with Pattern Matching \dt *order* This displays tables containing the word order in their name. List Tables Using SQL Query You can also list tables using SQL queries against system catalogs. SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_ty...

PostgreSQL: How to Restrict User Access PostgreSQL allows fine-grained access control using roles and privileges. Revoke All Access REVOKE ALL ON DATABASE mydb FROM username; Grant Read-Only Access GRANT CONNECT ON DATABASE mydb TO username; GRANT SELECT ON ALL TABLES IN SCHEMA public TO username; Prevent Future Table Access ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM username; This ensures the user can only read data and not modify it.

PostgreSQL: Export Data to Excel The easiest way to export PostgreSQL data to Excel is by using the CSV format. Export Table to CSV COPY employee TO '/tmp/employee.csv' DELIMITER ',' CSV HEADER; Export Query Result COPY ( SELECT * FROM employee WHERE role = 'Developer' ) TO '/tmp/developers.csv' CSV HEADER; The exported CSV file can be opened directly in Microsoft Excel.