Best Useful Postgresql Resources online details

Blogs :

1. https://www.dbrnd.com/tag/postgresql/
2. https://postgreshelp.com/
3. http://bajis-postgres.blogspot.com/
4. http://www.postgresqltutorial.com/


https://www.dbrnd.com/tag/postgresql/page/3/ (post: from last post in this page.)


Good Postgresql Posts:

Developer Posts:

1. Removing the duplicate records in a table. 
https://www.dbrnd.com/2019/09/postgresql-how-to-delete-all-duplicate-rows-except-one/

2. Converting normal user to super user in postgresql.
https://www.dbrnd.com/2019/03/postgresql-script-to-convert-user-to-super-user/

3.Getting all the schemas in a particular database in postgresql.
https://www.dbrnd.com/2018/08/postgresql-how-to-get-the-list-of-all-schemas-of-a-database-in-psql/

4. How to apply "Access Exclusive" lock on a table in postgresql.
https://www.dbrnd.com/2017/01/postgresql-how-to-apply-access-exclusive-lock-mode-on-table-share-lock-update-lock-row-lock/

5. How to recover the forgot password of super user POSTGRESQL. 
https://www.dbrnd.com/2018/04/postgresql-how-to-recover-postgres-user-password/

6. How to write RECURSIVE CTE in POSTGRESQL and example.
http://www.postgresqltutorial.com/postgresql-recursive-query/

7. How GROUP BY ROLLUP works and examples.
http://www.postgresqltutorial.com/postgresql-rollup/

8. DELETE JOIN in POSTGRESQL and examples. 
https://www.dbrnd.com/2018/04/postgresql-delete-join-with-an-example/

9.  UPDATE JOIN in POSTGRESQL and examples.
http://www.postgresqltutorial.com/postgresql-update-join/

10. Getting the server configurations in POSTGRESQL (SHOW ALL;).
https://www.dbrnd.com/2018/04/postgresql-show-all-for-checking-the-configuration-of-server/

11. Stop the new User from creating a new Table in POSTGRESQL.
https://www.dbrnd.com/2018/04/postgresql-stop-the-new-user-from-creating-a-new-table/

12. How to drop all Tables in POSTGRESQL?.
https://www.dbrnd.com/2018/04/postgresql-how-to-drop-all-tables/  

13.How to switch the Database Connection in PSQL?. 
https://www.dbrnd.com/2018/04/postgresql-how-to-switch-the-database-connection/

14. Cross Database Queries using DbLink Extension in POSTGRESQL.
https://www.dbrnd.com/2015/05/postgresql-cross-database-queries-using/

15. Handling the Null values in POSTGRESQL (Like isnull function).
http://www.postgresqltutorial.com/postgresql-coalesce/
http://www.postgresqltutorial.com/postgresql-nullif/


16. Data types in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-data-types/
http://www.postgresqltutorial.com/postgresql-boolean/
http://www.postgresqltutorial.com/postgresql-char-varchar-text/
http://www.postgresqltutorial.com/postgresql-integer/
http://www.postgresqltutorial.com/postgresql-numeric/
http://www.postgresqltutorial.com/postgresql-date/
http://www.postgresqltutorial.com/postgresql-time/
http://www.postgresqltutorial.com/postgresql-timestamp/
http://www.postgresqltutorial.com/postgresql-uuid/


17. How to initiate a transaction in POSTGRESQL and commit or rollback the transaction.
http://www.postgresqltutorial.com/postgresql-transaction/

18. How to use COPY command in POSTGRESQL for importing the data from CSV File.
http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/

19. How to use COPY command in POSTGRESQL for exporting the data to CSV File and  "\COPY" command details.
http://www.postgresqltutorial.com/export-postgresql-table-to-csv-file/


20. Manipulation on databases in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-create-database/
http://www.postgresqltutorial.com/postgresql-alter-database/
http://www.postgresqltutorial.com/postgresql-drop-database/
http://www.postgresqltutorial.com/postgresql-copy-database/
http://www.postgresqltutorial.com/postgresql-database-indexes-table-size/

21. Creating the tables from other existing tables in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-select-into/
http://www.postgresqltutorial.com/postgresql-create-table-as/

22. Creating the Sequence in POSTGRESQL for generating the auto insert values.
http://www.postgresqltutorial.com/postgresql-sequences/

23. Identity column – shows you how to use the identity column.
http://www.postgresqltutorial.com/postgresql-identity-column/

24. Renaming a table in POSTGRESQL.
ALTER TABLE table_name RENAME TO new_table_name;

25. Temporary tables in POSTGRESQL details.
http://www.postgresqltutorial.com/postgresql-temporary-table/

26. Creating and using Schemas in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-schema/


27. Cast function in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-cast/


28. Creating Block in in POSTGRESQL.
http://www.postgresqltutorial.com/plpgsql-block-structure/

29. Creating Variables details in declaration block in POSTGRESQL.
http://www.postgresqltutorial.com/plpgsql-variables/

30. Declaring Constants in declaration block in POSTGRESQL.
http://www.postgresqltutorial.com/plpgsql-constants/

31. Raise Statement in POSTGRESQL.
http://www.postgresqltutorial.com/plpgsql-errors-messages/

32. Create Function examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-create-function/

33. Function parameters examples in POSTGRESQL.
http://www.postgresqltutorial.com/plpgsql-function-parameters/

34. Function overloading examples in POSTGRESQL.
http://www.postgresqltutorial.com/plpgsql-function-overloading/

35. Function return as table in POSTGRESQL.
http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/

36. If statement examples in POSTGRESQL.
http://www.postgresqltutorial.com/plpgsql-if-else-statements/


37. Case statement examples in POSTGRESQL.
http://www.postgresqltutorial.com/plpgsql-case-statement/

38. Different loops (Loop, While Loop and For Loop) in POSTGRESQL.
http://www.postgresqltutorial.com/plpgsql-loop-statements/

39. Creating Stored Procedures in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-create-procedure/


40. Retrieving data from other databases in POSTGRESQL.
https://venkataramanapostgresqlreference.blogspot.com/2019/10/retrieving-data-from-other-databases-in.html







901. SELECT statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-select/

902. ORDER BY statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-order-by/

903. DISTINCT statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-select-distinct/

904. WHERE statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-where/

905. LIMIT statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-limit/

906. FETCH statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-fetch/

907. IN statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-in/

908. BETWEEN statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-between/

909. LIKE statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-like/

910. IS NULL statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-is-null/

911. ALIAS statments examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-alias/

912. JOINS examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-joins/

913. CROSS JOINS examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-cross-join/

914. NATURAL JOINS examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-natural-join/

915. GROUP BY examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-group-by/

916. HAVING examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-having/

917. UNION ALL AND UNION examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-union/

918. INTERSECT examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-intersect/

919. EXCEPT examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-tutorial/postgresql-except/

920. GROUPING SETS examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-grouping-sets/

921. GROUP BY CUBE examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-cube/

922. GROUP BY ROLLUP examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-rollup/

923. SUB QUERY examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-subquery/

924. INSERT examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-insert/

925. UPDATE examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-update/

926. UPDATE JOIN examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-update-join/

927. UPSERT JOIN examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-upsert/

928. DELETE JOIN examples in POSTGRESQL.
http://www.postgresqltutorial.com/postgresql-delete/


998.Displaying the full length of the string columns (more than 255 columns) in PGADMIN.
https://www.dbrnd.com/2018/04/postgresql-get-a-full-length-column-string-in-pgadmin-query-editor/

999. Getting the current Database name in POSTGRESQL.
https://www.dbrnd.com/2018/04/postgresql-get-the-name-of-current-database/




Admin Posts:

1. While using PSQL getting error like "psql is not recognized as an internal command windows". How to solve this?.
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-1-psql-is-not.html

2. Restarting the POSTGRESQL server. 
https://venkataramanapostgresqlreference.blogspot.com/2019/10/how-to-start-and-stop-postgresql-server.html

3. Process to add existing databases in the server and other server database to PGADMIN.

https://venkataramanapostgresqlreference.blogspot.com/2019/10/psql-process-to-connect-databases-xhq.html


4. Create Database syntax and examples.

http://www.postgresqltutorial.com/postgresql-create-database/


5. Configurations in postgresql server to read the data from other clients.

https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-server-connectivity.html


6. Queries to know the available and installed extensions in POSTGRESQL. Pg_Extension (Installed Extensions), PG_Available_Extensions (Available Extensions)

https://blog.dbi-services.com/listing-the-extensions-available-in-postgresql/  


7. Good post for relation between user, role and privilleges in POSTGRESQL. 

https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/


8. What is Tablespace and it's uses in POSTGRESQL.

http://www.postgresqltutorial.com/postgresql-create-tablespace/


9. PostgreSQL - OID System Column
http://www.sqlines.com/postgresql/oid


10. Postgresql System files and their uses
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-system-files-and-their-uses.html

 

PSQL Posts:

1. While using PSQL getting error like "psql is not recognized as an internal command windows". How to solve this?.
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-1-psql-is-not.html

2. Increasing the cmd window size for PSQL.
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-2-increasing-cmd.html

3. PSQL process to connect to a database and exist from the database commands.
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-3-connecting.html

4. PSQL command to find list of databases in POSTGRESQL server.
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-4-findings-dbs-in.html

5. PSQL Commands to create a new database and switching between the databases. 
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-5-creating-db-in.html

6.  PSQL Commands to creating a table, inserting data and selecting the data from the tables. 
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-6-creating.html

7. PSQL commands to take db backup and restoring the db backup. 
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-7-taking-db-backup.html

7.1. PSQL commands to take compressed db backups.
https://www.dbrnd.com/2017/12/postgresql-how-to-take-compressed-backup-of-database-pg_dump/

7.2. Details of PG_Dump and PG_DumpAll Commands for backup POSTGRESQL DBs. 
http://www.postgresqltutorial.com/postgresql-backup-database/


8. PSQL Command to find list of tables in a databaase. 
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-8-getting-tables.html

9. PSQL command/process to execute a script file (i.e. .SQL file). 
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-9-executing-script.html

10. PSQL command to take table backup with data and without data.
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tutorial-10-taking-table.html

11. Useful PSQL command in POSTGRESQL.
http://www.postgresqltutorial.com/psql-commands/






Check these Postgresql Posts Again:

https://www.dbrnd.com/2019/07/postgresql-example-of-serializable-isolation-level-concurrency-control-of-transaction/ 

 2.Read the different lock modes on tables and understand them.
https://www.dbrnd.com/2017/01/postgresql-how-to-apply-access-exclusive-lock-mode-on-table-share-lock-update-lock-row-lock/

3.  












Postgresql Tools

1. Postgresql tools details like (Pg Agent, )
https://venkataramanapostgresqlreference.blogspot.com/2019/10/postgresql-tools.html

2. 3rd Party tools which will be used to connect to POSTGRESQL. 

DBeaver

DBeaver is free and open source universal database tool for developers and database administrators.

Supports all popular databases: MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, etc.


https://severalnines.com/database-blog/top-gui-tools-postgresql

















Query for Removing Duplicate Records in POSTGRESQL.
   
DELETE FROM tbl_RemoveDuplicate
WHERE ID IN
(SELECT ID
FROM (SELECT id,
         ROW_NUMBER() OVER (partition BY Name ORDER BY ID) AS RowNumber
     FROM tbl_RemoveDuplicate) AS T
WHERE T.RowNumber > 1);

DELETE FROM TBL_REMOVEDUPLICATES
WHERE ID IN (
    SELECT ID FROM
        ( SELECT ID, ROW_NUMBER(PARTITION BY NAME ORDER BY ID) AS ROWNUMBER FROM TBL_REMOVEDUPLICATES )
    WHERE ROWNUMBER >1
); 




Query for creating a table with autoincrement (identity), columns default value in POSTGRESQL.

CREATE TABLE customers (
   customer_id serial PRIMARY KEY,
   name VARCHAR UNIQUE,
   email VARCHAR NOT NULL,
   active bool NOT NULL DEFAULT TRUE
);     




Delete Join Query examples in POSTGRESQL. 

DELETE FROM link
USING link_tmp
WHERE
    link.id = link_tmp.id;    



  
Update Join Query examples in POSTGRESQL. 

UPDATE link_tmp
SET rel = link.rel,
 description = link.description,
 last_update = link.last_update
FROM
   link
WHERE
   link_tmp.id = link.id;




Update Join Query with result as updated records examples in POSTGRESQL. 

UPDATE link
SET description = 'Learn PostgreSQL fast and easy',
 rel = 'follow'
WHERE
   ID = 1
RETURNING id,
   description,
   rel; 
 




Update Query to set default values of column examples in POSTGRESQL. 

UPDATE link
SET last_update = DEFAULT
WHERE
   last_update IS NULL;   




Insert Query with default values columns examples in POSTGRESQL. 

INSERT INTO link (url, name, last_update)
VALUES
   ('https://www.tumblr.com/','Tumblr',DEFAULT); 
 
 




Alter Query to set default values to column examples in POSTGRESQL. 

ALTER TABLE link ADD COLUMN last_update DATE;

ALTER TABLE link ALTER COLUMN last_update
SET DEFAULT CURRENT_DATE;





Create table Query for Creating table like other table examples in POSTGRESQL. 

CREATE TABLE link_tmp (LIKE link);
 

Comments

Popular posts from this blog

How to connect postgresql database through ODBC driver in excel

Postgresql System files and their uses