{"id":1628,"date":"2024-02-21T00:00:00","date_gmt":"2024-02-21T05:00:00","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=1628"},"modified":"2024-02-21T07:48:29","modified_gmt":"2024-02-21T12:48:29","slug":"postgresql-cheat-sheet","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/postgresql-cheat-sheet\/","title":{"rendered":"PostgreSQL Cheat Sheet"},"content":{"rendered":"\n

PostgreSQL is a powerful open-source relational database management system (RDBMS) known for its extensibility and advanced features. This cheat sheet presents key commands and queries to interact effectively with PostgreSQL databases.<\/p>\n\n\n\n

Connecting to PostgreSQL:<\/strong><\/h4>\n\n\n\n
Command<\/th>Description<\/th><\/tr><\/thead>
psql -U [username] -d [database]<\/code><\/td>Connect to PostgreSQL server with username and database.<\/td><\/tr>
\\l<\/code><\/td>List all databases.<\/td><\/tr>
\\c [database_name]<\/code><\/td>Connect to a specific database.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Managing Tables:<\/strong><\/h4>\n\n\n\n
Command<\/th>Description<\/th><\/tr><\/thead>
CREATE TABLE [table_name] ([column_definitions]);<\/code><\/td>Create a new table with specified columns.<\/td><\/tr>
\\dt<\/code><\/td>List all tables in the current database.<\/td><\/tr>
\\d [table_name]<\/code><\/td>Display the structure of a table.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Inserting Data:<\/strong><\/h4>\n\n\n\n
Command<\/th>Description<\/th><\/tr><\/thead>
INSERT INTO [table_name] VALUES (value1, value2, ...);<\/code><\/td>Insert data into a table.<\/td><\/tr>
INSERT INTO [table_name] (column1, column2, ...) VALUES (value1, value2, ...);<\/code><\/td>Insert data with specified columns.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Querying Data:<\/strong><\/h4>\n\n\n\n
Command<\/th>Description<\/th><\/tr><\/thead>
SELECT * FROM [table_name];<\/code><\/td>Retrieve all columns from a table.<\/td><\/tr>
SELECT column1, column2 FROM [table_name] WHERE condition;<\/code><\/td>Retrieve specific columns with conditions.<\/td><\/tr>
UPDATE [table_name] SET column1 = value1 WHERE condition;<\/code><\/td>Update data in a table.<\/td><\/tr>
DELETE FROM [table_name] WHERE condition;<\/code><\/td>Delete data from a table.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Filtering and Sorting:<\/strong><\/h4>\n\n\n\n
Command<\/th>Description<\/th><\/tr><\/thead>
SELECT * FROM [table_name] WHERE condition ORDER BY column_name ASC\/DESC;<\/code><\/td>Filter and sort query results.<\/td><\/tr>
LIMIT [number];<\/code><\/td>Limit the number of rows returned.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Aggregation Functions:<\/strong><\/h4>\n\n\n\n
Command<\/th>Description<\/th><\/tr><\/thead>
SELECT COUNT(column_name) FROM [table_name];<\/code><\/td>Count the number of rows in a table.<\/td><\/tr>
SELECT AVG(column_name) FROM [table_name];<\/code><\/td>Calculate the average value of a column.<\/td><\/tr>
SELECT MAX(column_name) FROM [table_name];<\/code><\/td>Retrieve the maximum value in a column.<\/td><\/tr>
SELECT MIN(column_name) FROM [table_name];<\/code><\/td>Retrieve the minimum value in a column.<\/td><\/tr>
SELECT SUM(column_name) FROM [table_name];<\/code><\/td>Calculate the sum of values in a column.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Conclusion<\/h3>\n\n\n\n

This PostgreSQL cheat sheet serves as a handy reference for both beginners and experienced users, offering essential commands for managing databases, tables, and data. PostgreSQL’s versatility and rich feature set make it a popular choice for various applications. Customize these commands according to your specific PostgreSQL setup and database requirements.<\/p>\n","protected":false},"excerpt":{"rendered":"

PostgreSQL is a powerful open-source relational database management system (RDBMS) known for its extensibility and advanced features. This cheat sheet presents key commands and queries to interact effectively with PostgreSQL databases. Connecting to PostgreSQL: Command Description psql -U [username] -d [database] Connect to PostgreSQL server with username and database. \\l List all databases. \\c [database_name] […]<\/p>\n","protected":false},"author":1,"featured_media":1780,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,265],"tags":[14,59],"class_list":["post-1628","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","category-reference","tag-database","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1628","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=1628"}],"version-history":[{"count":1,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1628\/revisions"}],"predecessor-version":[{"id":1629,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1628\/revisions\/1629"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media\/1780"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=1628"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=1628"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=1628"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}