Posts in 2020
PostgreSQL mini cookbook: Performance tuning, debugging and testing
Last Update: in PostgreSQL
PostgreSQL mini cookbook: Performance Tuning, Debugging and Testing, those tricks back to 2001 and still works today. 😉
Keeping index statistics up to date Performance has been steadily deteriorating as you use your Postgres system. Solution Use the VACUUM ANALYZE command in psql or the vacuumdb command-line tool. vacuumdb is garbage-collect and analyze a PostgreSQL …
PostgreSQL mini cookbook: Dealing with the system tables
Last Update: in PostgreSQL
PostgreSQL mini cookbook: Dealing with the system tables, those tricks back to 2001 and still works today. 😉
Seeing what tables exist You want to check if a table already exists. Solution There is a view called pg_tables or the system table pg_class - you can query either. There is less information in pg_tables but it is easier to understand. List all the …
PostgreSQL mini cookbook: Constraining your data
Last Update: in PostgreSQL
PostgreSQL mini cookbook: Constraining your data, those tricks back to 2001 and still works today. 😉
Making sure a related record exists Records in one table should all be connected to records in another table - you shouldn’t have diary entries for a company that isn’t listed in the companies table. Solution Define a FOREIGN KEY on the …
PostgreSQL mini cookbook: Controlling Access to your data
Last Update: in PostgreSQL
PostgreSQL mini cookbook: Controlling Access to your data, those tricks back to 2001 and still works today. 😉
Restricting users to certain records or certain fields with views The accounts team don’t need to see unfilled orders and the sales team don’t need to see bank details. You want to enforce this. Solution Create one view for each group of …
PostgreSQL mini cookbook: Automating processes
Last Update: in PostgreSQL
PostgreSQL mini cookbook: Automating processes, those tricks back to 2001 and still works today. 😉
Enabling plpgsql You want to write functions and build triggers in Postgres’ scripting language plpgsql. PL/pgSQL is a loadable procedural language for the PostgreSQL database system. The design goals of PL/pgSQL were to create a loadable …
PostgreSQL mini cookbook: advanced query tricks
Last Update: in PostgreSQL
PostgreSQL mini cookbook: advanced query tricks, those tricks back to 2001 and still works today. 😉
Having a dynamic field value without a lookup table You want a “calculated” column returned from a select but joining to a lookup table is either not practical or not desirable. Solution Use the CASE...WHEN...ELSE...END structure or write …
PostgreSQL mini cookbook: aggregate query tricks
Last Update: in PostgreSQL
PostgreSQL mini cookbook: PostgreSQL aggregate query tricks, those tricks back to 2001 and still works today. 😉
Sum a field You want to calculate the total number of payments per customer, staff. Solution Use SUM() and GROUP BY: SELECT a, b, sum(c) FROM t GROUP BY a ,b; Example: # SELECT * FROM payment; payment_id | customer_id | staff_id | rental_id | amount …
PostgreSQL mini cookbook: basic query tricks
Last Update: in PostgreSQL
PostgreSQL mini cookbook: PostgreSQL basic query tricks, those tricks back to 2001 and still works today. 😉
Case insensitive searches You want to check equality or do a regular expression search but ignoring case Solution Either use lower() on the field and the value being compared or use the ILIKE or ˜˜* regular expression operators. Discussion If you …
psql 101
Last Update: in PostgreSQL
psqlis a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file.psql shell command List exist databases $ psql --list List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ------------------------+-------+----------+-------------+-------------+------------------- dvdrental | dbch | UTF8 …