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.

PostgreSQL: Script to List Functions You can list all user-defined and system functions in PostgreSQL using system catalogs. List Functions in Current Database SELECT routine_name FROM information_schema.routines WHERE routine_type = 'FUNCTION' AND routine_schema = 'public'; List Functions with Definition SELECT proname, pg_get_functiondef(p.oid) FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = 'public'; This is helpful when auditing or documenting database logic.

PostgreSQL: Script to Drop Foreign Keys Sometimes you may need to drop foreign key constraints while restructuring tables. PostgreSQL allows you to do this easily. Drop a Specific Foreign Key ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey; Drop All Foreign Keys in a Schema SELECT 'ALTER TABLE ' || tc.table_name || ' DROP CONSTRAINT ' || tc.constraint_name || ';' FROM information_schema.table_constraints tc WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public'; Run the generated statements to remove all foreign keys.

PostgreSQL: Convert Multiple Rows to JSON PostgreSQL provides powerful JSON functions that allow you to convert query results into JSON format. This is especially useful for APIs and reporting. Using json_agg() SELECT json_agg(t) FROM ( SELECT id, name, role FROM employee ) t; This query converts multiple rows into a single JSON array. Output Example [ {"id":1,"name":"Alice","role":"Developer"}, {"id":2,"name":"Bob","role":"Manager"} ] The json_agg() function aggregates rows into JSON arrays efficiently.

PostgreSQL: List Tables using PSQL When working in the PostgreSQL interactive terminal ( psql ), you can list tables quickly using built-in commands. List All Tables \dt This shows all tables in the current database schema. You can also filter by pattern: \dt public.* Use: \l — show all databases \c dbname — connect to database \dt — list tables These commands are handy shortcuts in the PostgreSQL CLI. :contentReference[oaicite:3]{index=3}

PostgreSQL: How to Create Index and Examples An index in PostgreSQL speeds up data retrieval operations on a table by creating a data structure that allows quick lookup. Indexes are especially useful on columns that are frequently used in WHERE filters or JOIN conditions. Create a Simple Index CREATE INDEX idx_column ON table_name (column_name); This creates a basic index on column_name in table_name . Example CREATE INDEX idx_employee_name ON employee (name); With this index in place, queries filtering by name will run faster. You can also create compound indexes: CREATE INDEX idx_emp_name_role ON employee (name, role); Indexes improve lookup performance but can slow down inserts or updates slightly because the index must also be maintained. Adjust based on your workload.

PostgreSQL: Insert Query Example The INSERT command in PostgreSQL is used to add records to a table. It is part of the Data Manipulation Language (DML) and allows you to insert one or many rows with defined values. :contentReference[oaicite:1]{index=1} Basic Syntax INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3); If you are inserting values into all the columns of a table in order, you can skip naming the columns: INSERT INTO table_name VALUES (value1, value2, value3); Example — Insert Data CREATE TABLE employee ( id BIGINT PRIMARY KEY, name VARCHAR(100), role VARCHAR(100) ); INSERT INTO employee VALUES (1, 'Alice', 'Developer'), (2, 'Bob', 'Manager'); By grouping rows inside the VALUES clause, you can insert multiple rows in one statement. :contentReference[oaicite:2]{index=2}

PostgreSQL: How to Create User in PostgreSQL In PostgreSQL, a user is essentially a role with login privileges. Creating a user allows you to manage access and permissions for your database. Step-by-Step: Create a PostgreSQL User Here’s how to create a new user in PostgreSQL: -- Connect to PostgreSQL CLI (psql) CREATE USER new_username WITH PASSWORD 'secure_password'; -- To grant login privileges ALTER USER new_username WITH LOGIN; You can also grant privileges to the new user as needed: GRANT ALL PRIVILEGES ON DATABASE your_database TO new_username; What This Does The CREATE USER statement defines a new login role. ALTER USER … WITH LOGIN ensures the account can connect to databases. GRANT assigns permissions on a specific database. PostgreSQL considers users and roles as part of its access control system. You can tailor permissions for security and functionality. :contentReference[oaicite:0]{index=0}