How to start PostgreSQL server in Windows

how to restart PostgreSQL server in windows and how to setup PostgreSQL server and how to run PostgreSQL server windows and how to check postgres server is running
Dr.MohitBansal Profile Pic
Dr.MohitBansal,Canada,Teacher
Published Date:26-10-2017
Your Website URL(Optional)
Comment
What Is a PostgreSQL Server? If you think that a PostgreSQL server is just a storage system, and the only way to communicate with it is by executing SQL statements, you are limiting yourself tremendously. That is using just a tiny part of the database's features. A PostgreSQL server is a powerful framework that can be used for all kinds of data processing, and even some non-data server tasks. It is a server platform that allows you to easily mix and match functions and libraries from several popular languages. Consider this complicated, multi-language sequence of work: 1. Call a string parsing function in Perl. 2. Convert the string to XSLT and process the result using JavaScript. 3. Ask for a secure stamp from an external time-stamping service such as www.guardtime.com, using their SDK for C. 4. Write a Python function to digitally sign the result. This can be implemented as a series of simple function calls using several of the available server programming languages. The developer needing to accomplish all this work can just call a single PostgreSQL function without having to be aware of how the data is being passed between languages and libraries: SELECT convert_to_xslt_and_sign(raw_data_string);What Is a PostgreSQL Server? In this book, we will discuss several facets of PostgreSQL server programming. PostgreSQL has all of the native server-side programming features available in most larger database systems such as triggers, automated actions invoked automatically each time data is changed. But it has uniquely deep abilities to override the built-in behavior down to very basic operators. Examples of this customization include the following. Writing User-defined functions (UDF) in C for carrying out complex computations: • Add complicated constraints to make sure that data in the server meets guidelines. • Create triggers in many languages to make related changes to other tables, log the actions, or forbid the action to happen if it does not meet certain criteria. • Define new data types and operators in the database. • Use the geography types defined in the PostGIS package. • Add your own index access methods for either existing or new data types, making some queries much more efc fi ient. What sort of things can you do with these features? There are limitless possibilities, such as the ones listed as follows: • Write data extractor functions to get just the interesting parts from structured data, such as XML or JSON, without needing to ship the whole, possibly huge, document to the client application. • Process events asynchronously, like sending mail without slowing down the main application. You could create a mail queue for changes to user info, populated by a trigger. A separate mail-sending process can consume this data whenever it's notied b fi y an application process. The rest of this chapter is presented as a series of descriptions of common data management tasks showing how they can be solved in a robust and elegant way via server programming. The samples in this chapter are all tested to work, but they come with minimal commentary. They are here just to show you various things server programming can accomplish. The techniques described will be explained thoroughly in later chapters. 8 Chapter 1 Why program in the server? Developers program their code in a number of different languages and it could be designed to run just about anywhere. When writing an application, some people follow the philosophy that as much of the logic as possible for the application, should be pushed to the client. We see this in the explosion of applications leveraging JavaScript inside browsers. Others like to push the logic into the middle tier with an application server handling the business rules. These are all valid ways to design an application, so why would you want to program in the database server? Let's start with a simple example. Many applications include a list of customers who have a balance in their account. We'll use this sample schema and data: CREATE TABLE accounts(owner text, balance numeric); INSERT INTO accounts VALUES ('Bob',100); INSERT INTO accounts VALUES ('Mary',200); Downloading the example code You can download the example code files for all Packt books you have purchased from your account athttp://www.packtpub.com. If you purchased this book elsewhere, you can visithttp://www.packtpub. com/support and register to have the files e-mailed directly to you. When using a database, the most common way to interact with it is to use SQL queries. If you want to move 14 dollars from Bob's account to Mary's account, with simple SQL it would look like this: UPDATE accounts SET balance = balance - 14.00 WHERE owner = 'Bob'; UPDATE accounts SET balance = balance + 14.00 WHERE owner = 'Mary'; But you have to also make sure that Bob actually has enough money (or credit) on his account. It's also important that if anything fails then none of the transactions happen. In an application program, the preceding code snippet becomes: BEGIN; SELECT amount FROM accounts WHERE owner = 'Bob' FOR UPDATE; now in the application check that the amount is actually bigger than 14 UPDATE accounts SET amount = amount - 14.00 WHERE owner = 'Bob'; UPDATE accounts SET amount = amount + 14.00 WHERE owner = 'Mary'; COMMIT; 9 What Is a PostgreSQL Server? But did Mary actually have an account? If she did not, the lastUPDATE will succeed by updating zero rows. If any of the checks fail, you should do aROLLBACK instead ofCOMMIT. Once you have done all this for all the clients that transfer money, a new requirement will invariably arrive. Perhaps, the minimum amount that can be transferred is now5.00. You will need to revisit all your code in all your clients again. So what can you do to make all of this more manageable, more secure, and more robust? This is where server programming, executing code on the database server itself, can help. You can move the computations, checks, and data manipulations entirely into a User-den fi ed function (UDF) on the server. This does not just ensure that you have only one copy of operation logic to manage, but also makes things faster by not needing several round-trips between client and server. If required, you can also make sure that only as much information as needed is given out of the database. For example, there is no business for most client applications to know how much money Bob has on his account. Mostly, they only need to know if there is enough money to make the transfer, or more to the point, if the transaction succeeded. Using PL/pgSQL for integrity checks PostgreSQL includes its own programming language named PL/pgSQL that is aimed to integrate easily with SQL commands. PL stands for programming language, and this is just one of the many languages available for writing server code. pgSQL is shorthand for PostgreSQL. Unlike basic SQL, PL/pgSQL includes procedural elements, like the ability to use if/then/else statements and loops. You can easily execute SQL statements, or even loop over the result of a SQL statement in the language. The integrity checks needed for the application can be done in a PL/pgSQL function which takes three arguments: names of the payer and recipient, and the amount to pay. This sample also returns the status of the payment: CREATE OR REPLACE FUNCTION transfer( i_payer text, i_recipient text, i_amount numeric(15,2)) RETURNS text AS DECLARE payer_bal numeric; BEGIN SELECT balance INTO payer_bal FROM accounts 10 Chapter 1 WHERE owner = i_payer FOR UPDATE; IF NOT FOUND THEN RETURN 'Payer account not found'; END IF; IF payer_bal i_amount THEN RETURN 'Not enough funds'; END IF; UPDATE accounts SET balance = balance + i_amount WHERE owner = i_recipient; IF NOT FOUND THEN RETURN 'Recipient does not exist'; END IF; UPDATE accounts SET balance = balance - i_amount WHERE owner = i_payer; RETURN 'OK'; END; LANGUAGE plpgsql; Here are a few examples of using this function, assuming you haven't executed the previously proposedUPDATE statements yet: postgres= SELECT FROM accounts; owner balance -+- Bob 100 Mary 200 (2 rows) postgres= SELECT FROM transfer('Bob','Mary',14.00); transfer OK (1 row) postgres= SELECT FROM accounts; owner balance -+- Mary 214.00 Bob 86.00 (2 rows) 11 What Is a PostgreSQL Server? Your application would need to check the return code and decide how to handle these errors. As long as it was written to reject any unexpected value, you could extend this function to do more checking, such as minimum transferrable amount, and be sure it would be prevented. There are three errors this can return: postgres= SELECT FROM transfer('Fred','Mary',14.00); transfer - Payer account not found (1 row) postgres= SELECT FROM transfer('Bob','Fred',14.00); transfer Recipient does not exist (1 row) postgres= SELECT FROM transfer('Bob','Mary',500.00); transfer Not enough funds (1 row) For these checks to always work, you would need to make all transfer operations go through the function, rather than manually changing the values with SQL statements. About this book's code examples The sample output shown here has been created with PostgreSQL'spsql utility, usually running on a Linux system. Most of the code will work the same way if you are using a GUI utility likepgAdmin3 to access the server instead. When you see lines like this: postgres= SELECT 1; Thepostgres= part is the prompt shown by thepsql command. Examples in this book have been tested using PostgreSQL 9.2. They will probably work on PostgreSQL version 8.3 and later. There have not been many major changes to how server programming happens in the last few versions of PostgreSQL. The syntax has become stricter over time to reduce the possibility of mistakes in server programming code. Due to the nature of those changes, most code from newer versions will still run on the older ones, unless it uses very new features. However, the older code can easily fail to run due to one of the newly-enforced restrictions. 12 Chapter 1 Switching to the expanded display When using thepsql utility to execute a query, PostgreSQL normally outputs the result using vertically aligned columns: psql -c "SELECT 1 AS test" test 1 (1 row) psql psql (9.2.1) Type "help" for help. postgres= SELECT 1 AS test; test 1 (1 row) You can tell when you're seeing a regular output because it will end up showing the number of rows. This type of output is hard to fit into the text of a book like this. It's easier to print the output from what the program calls the expanded display, which breaks each column into a separate line. You can switch to expanded using either the-x command-line switch, or by sending\x to thepsql program. Here is an example of using each: psql -x -c "SELECT 1 AS test" - RECORD 1 test 1 psql psql (9.2.1) Type "help" for help. postgres= \x Expanded display is on. postgres= SELECT 1 AS test; - RECORD 1 test 1 13 What Is a PostgreSQL Server? Notice how the expanded output doesn't show the row count, and it numbers each output row. To save space, not all of the examples in the book will show the expanded output being turned on. You can normally tell which type you're seeing by differences like this, whether you're seeing rows orRECORD. The expanded mode will be normally preferred when the output of the query is too wide to fit into the available width of the book. Moving beyond simple functions Server programming can mean a few different things. Server programming is not just writing server functions. There are many other things you can do in the server which can be considered programming. Data comparisons using operators For more complex tasks you can define your own types, operators, and casts from one type to another, letting you actually compare apples and oranges. As shown in the next example, you can define the type, fruit_qty, for fruit-with-quantity and then teach PostgreSQL to compare apples and oranges, say to make one orange to be worth 1.5 apples and convert apples to oranges: postgres= CREATE TYPE FRUIT_QTY as (name text, qty int); postgres= SELECT '("APPLE", 3)'::FRUIT_QTY; fruit_quantity (APPLE,3) (1 row) CREATE FUNCTION fruit_qty_larger_than(left_fruit FRUIT_QTY, right_fruit FRUIT_QTY) RETURNS BOOL AS BEGIN IF (left_fruit.name = 'APPLE' AND right_fruit.name = 'ORANGE') THEN RETURN left_fruit.qty (1.5 right_fruit.qty); END IF; IF (left_fruit.name = 'ORANGE' AND right_fruit.name = 'APPLE' ) THEN 14 Chapter 1 RETURN (1.5 left_fruit.qty) right_fruit.qty; END IF; RETURN left_fruit.qty right_fruit.qty; END; LANGUAGE plpgsql; postgres= SELECT fruit_qty_larger_than('("APPLE", 3)'::FRUIT_ QTY,'("ORANGE", 2)'::FRUIT_QTY); fruit_qty_larger_than - f (1 row) postgres= SELECT fruit_qty_larger_than('("APPLE", 4)'::FRUIT_ QTY,'("ORANGE", 2)'::FRUIT_QTY); fruit_qty_larger_than - t (1 row) CREATE OPERATOR ( leftarg = FRUIT_QTY, rightarg = FRUIT_QTY, procedure = fruit_qty_larger_than, commutator = ); postgres= SELECT '("ORANGE", 2)'::FRUIT_QTY '("APPLE", 2)'::FRUIT_ QTY; ?column? t (1 row) postgres= SELECT '("ORANGE", 2)'::FRUIT_QTY '("APPLE", 3)'::FRUIT_ QTY; ?column? f (1 row) 15 www.allitebooks.comWhat Is a PostgreSQL Server? Managing related data with triggers Server programming can also mean setting up automated actions (triggers), so that some operations in the database cause some other things to happen as well. For example, you can set up a process where making an offer on some items is automatically reserved to them in the stock table. So let's create a fruit stock table: CREATE TABLE fruits_in_stock ( name text PRIMARY KEY, in_stock integer NOT NULL, reserved integer NOT NULL DEFAULT 0, CHECK (in_stock between 0 and 1000 ), CHECK (reserved = in_stock) ); TheCHECK constraints make sure that some basic rules are followed: you can't have more than1000 fruits in stock (they'll probably go bad), you can't have negative stock, and you can't reserve more than what you have. CREATE TABLE fruit_offer ( offer_id serial PRIMARY KEY, recipient_name text, offer_date timestamp default current_timestamp, fruit_name text REFERENCES fruits_in_stock, offered_amount integer ); Theoffer table has an ID for the offer (so you can distinguish between offers later), recipient, date, offered fruit name, and offered amount. For automating the reservation management, you first need a TRIGGER function, which implements the management logic: CREATE OR REPLACE FUNCTION reserve_stock_on_offer () RETURNS trigger AS BEGIN IF TG_OP = 'INSERT' THEN UPDATE fruits_in_stock SET reserved = reserved + NEW.offered_amount WHERE name = NEW.fruit_name; ELSIF TG_OP = 'UPDATE' THEN UPDATE fruits_in_stock SET reserved = reserved - OLD.offered_amount + NEW.offered_amount 16 Chapter 1 WHERE name = NEW.fruit_name; ELSIF TG_OP = 'DELETE' THEN UPDATE fruits_in_stock SET reserved = reserved - OLD.offered_amount WHERE name = OLD.fruit_name; END IF; RETURN NEW; END; LANGUAGE plpgsql; You have to tell PostgreSQL to call this function each and every time the offer row is changed: CREATE TRIGGER manage_reserve_stock_on_offer_change AFTER INSERT OR UPDATE OR DELETE ON fruit_offer FOR EACH ROW EXECUTE PROCEDURE reserve_stock_on_offer(); After this we are ready to test the functionality. First, we will add some fruit to our stock: INSERT INTO fruits_in_stock(name,in_stock) Then, we check that stock (this is using the expanded display): postgres= \x Expanded display is on. postgres= SELECT FROM fruits_in_stock; - RECORD 1 name APPLE in_stock 500 reserved 0 - RECORD 2 name ORANGE in_stock 500 reserved 0 Next, let's make an offer of100 apples to Bob: postgres= INSERT INTO fruit_offer(recipient_name,fruit_name,offered_ amount) VALUES('Bob','APPLE',100); INSERT 0 1 postgres= SELECT FROM fruit_offer; - RECORD 1 +- offer_id 1 recipient_name Bob offer_date 2013-01-25 15:21:15.281579 fruit_name APPLE 17 What Is a PostgreSQL Server? offered_amount 100 postgres= SELECT FROM fruits_in_stock; - RECORD 1 name ORANGE in_stock 500 reserved 0 - RECORD 2 name APPLE in_stock 500 reserved 100 On checking the stock we see that indeed 100 apples are reserved: postgres= SELECT FROM fruits_in_stock; - RECORD 1 name ORANGE in_stock 500 reserved 0 - RECORD 2 name APPLE in_stock 500 reserved 100 If we change the offered amount, the reservation follows: postgres= UPDATE fruit_offer SET offered_amount = 115 WHERE offer_id = 1; UPDATE 1 postgres= SELECT FROM fruits_in_stock; - RECORD 1 name ORANGE in_stock 500 reserved 0 - RECORD 2 name APPLE in_stock 500 reserved 115 We also get some extra benefits. First, because of the constraint on the stock table, you can't sell the reserved apples: postgres= UPDATE fruits_in_stock SET in_stock = 100 WHERE name = 'APPLE'; ERROR: new row for relation "fruits_in_stock" violates check constraint "fruits_in_stock_check" DETAIL: Failing row contains (APPLE, 100, 115). 18 Chapter 1 More interestingly, you also can't reserve more than you have, even though the constraints are on another table: postgres= UPDATE fruit_offer SET offered_amount = 1100 WHERE offer_id = 1; ERROR: new row for relation "fruits_in_stock" violates check constraint "fruits_in_stock_check" DETAIL: Failing row contains (APPLE, 500, 1100). CONTEXT: SQL statement "UPDATE fruits_in_stock SET reserved = reserved - OLD.offered_amount + NEW.offered_amount WHERE name = NEW.fruit_name" PL/pgSQL function reserve_stock_on_offer() line 8 at SQL statement When you finally delete the offer, the reservation is released: postgres= DELETE FROM fruit_offer WHERE offer_id = 1; DELETE 1 postgres= SELECT FROM fruits_in_stock; - RECORD 1 name ORANGE in_stock 500 reserved 0 - RECORD 2 name APPLE in_stock 500 reserved 0 In a real system, you probably would archive the old offer before deleting it. Auditing changes If you need to know who did what to the data and when it was done, one way to do that is to log every action that is performed on an important table. There are at least two equally valid ways of doing the auditing: • Use auditing triggers • Allow tables to be accessed only through functions, and do the auditing inside these functions Here, we will take a look at minimal examples of both the approaches. 19 What Is a PostgreSQL Server? First, let's create the tables: CREATE TABLE salaries( emp_name text PRIMARY KEY, salary integer NOT NULL ); CREATE TABLE salary_change_log( changed_by text DEFAULT CURRENT_USER, changed_at timestamp DEFAULT CURRENT_TIMESTAMP, salary_op text, emp_name text, old_salary integer, new_salary integer ); REVOKE ALL ON salary_change_log FROM PUBLIC; GRANT ALL ON salary_change_log TO managers; You don't generally want your users to be able to change audit logs, so grant only the managers the right to access these. If you plan to let users access the salary table directly, you should put a trigger on it for auditing: CREATE OR REPLACE FUNCTION log_salary_change () RETURNS trigger AS BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO salary_change_log(salary_op,emp_name,new_salary) VALUES (TG_OP,NEW.emp_name,NEW.salary); ELSIF TG_OP = 'UPDATE' THEN INSERT INTO salary_change_ log(salary_op,emp_name,old_salary,new_salary) VALUES (TG_OP,NEW.emp_name,OLD.salary,NEW.salary); ELSIF TG_OP = 'DELETE' THEN INSERT INTO salary_change_log(salary_op,emp_name,old_salary) VALUES (TG_OP,NEW.emp_name,OLD.salary); END IF; RETURN NEW; END; LANGUAGE plpgsql SECURITY DEFINER; CREATE TRIGGER audit_salary_change AFTER INSERT OR UPDATE OR DELETE ON salaries FOR EACH ROW EXECUTE PROCEDURE log_salary_change (); 20 Chapter 1 Now, let's test out some salary management: postgres= INSERT INTO salaries values('Bob',1000); INSERT 0 1 postgres= UPDATE salaries set salary = 1100 where emp_name = 'Bob'; UPDATE 1 postgres= INSERT INTO salaries values('Mary',1000); INSERT 0 1 postgres= UPDATE salaries set salary = salary + 200; UPDATE 2 postgres= SELECT FROM salaries; - RECORD 1 emp_name Bob salary 1300 - RECORD 2 emp_name Mary salary 1200 Each one of those changes is saved into the salary change log table for auditing purposes: postgres= SELECT FROM salary_change_log; - RECORD 1 changed_by frank changed_at 2012-01-25 15:44:43.311299 salary_op INSERT emp_name Bob old_salary new_salary 1000 - RECORD 2 changed_by frank changed_at 2012-01-25 15:44:43.313405 salary_op UPDATE emp_name Bob old_salary 1000 new_salary 1100 - RECORD 3 changed_by frank changed_at 2012-01-25 15:44:43.314208 salary_op INSERT emp_name Mary old_salary new_salary 1000 - RECORD 4 changed_by frank 21 What Is a PostgreSQL Server? changed_at 2012-01-25 15:44:43.314903 salary_op UPDATE emp_name Bob old_salary 1100 new_salary 1300 - RECORD 5 changed_by frank changed_at 2012-01-25 15:44:43.314903 salary_op UPDATE emp_name Mary old_salary 1000new_salary 1200 On the other hand, you may not want anybody to have direct access to the salary table, in which case you can perform the following: REVOKE ALL ON salaries FROM PUBLIC; Also, give users access to only two functions: the first is for any user looking at salaries and the other is for changing salaries, which is available only to managers. The functions themselves will have all the access to underlying tables because they are declared asSECURITY DEFINER, which means they run with the privileges of the user who created them. The salary lookup function will look like the following: CREATE OR REPLACE FUNCTION get_salary(text) RETURNS integer AS if you look at other people's salaries, it gets logged INSERT INTO salary_change_log(salary_op,emp_name,new_salary) SELECT 'SELECT',emp_name,salary FROM salaries WHERE upper(emp_name) = upper(1) AND upper(emp_name) = upper(CURRENT_USER); – don't log select of own salary return the requested salary SELECT salary FROM salaries WHERE upper(emp_name) = upper(1); LANGUAGE SQL SECURITY DEFINER; Notice that we implemented a "soft security" approach, where you can look up for other people's salaries, but you have to do it responsibly, that is, only when you need to as your manager will know that you have checked. 22 Chapter 1 Theset_salary() function abstracts away the need to check if the user exists; if the user does not, it is created. Setting someone's salary to0 will remove him from the salary table. Thus, the interface is much simplified and the client application of these functions needs to know and do less: CREATE OR REPLACE FUNCTION set_salary(i_emp_name text, i_salary int) RETURNS TEXT AS DECLARE old_salary integer; BEGIN SELECT salary INTO old_salary FROM salaries WHERE upper(emp_name) = upper(i_emp_name); IF NOT FOUND THEN INSERT INTO salaries VALUES(i_emp_name, i_salary); INSERT INTO salary_change_log(salary_op,emp_name,new_salary) VALUES ('INSERT',i_emp_name,i_salary); RETURN 'INSERTED USER ' i_emp_name; ELSIF i_salary 0 THEN UPDATE salaries SET salary = i_salary WHERE upper(emp_name) = upper(i_emp_name); INSERT INTO salary_change_log (salary_op,emp_name,old_salary,new_salary) VALUES ('UPDATE',i_emp_name,old_salary,i_salary); RETURN 'UPDATED USER ' i_emp_name; ELSE salary set to 0 DELETE FROM salaries WHERE upper(emp_name) = upper(i_emp_ name); INSERT INTO salary_change_log(salary_op,emp_name,old_salary) VALUES ('DELETE',i_emp_name,old_salary); RETURN 'DELETED USER ' i_emp_name; END IF; END; LANGUAGE plpgsql SECURITY DEFINER; Now, drop theaudit trigger (or the changes will be logged twice) and test the new functionality: postgres= DROP TRIGGER audit_salary_change ON salaries; DROP TRIGGER postgres= postgres= SELECT set_salary('Fred',750); - RECORD 1 23 What Is a PostgreSQL Server? set_salary INSERTED USER Fred postgres= SELECT set_salary('frank',100); - RECORD 1 - set_salary INSERTED USER frank postgres= SELECT FROM salaries ; - RECORD 1 - emp_name Bob salary 1300 - RECORD 2 - emp_name Mary salary 1200 - RECORD 3 - emp_name Fred salary 750 - RECORD 4 - emp_name frank salary 100 postgres= SELECT set_salary('mary',0); - RECORD 1 - set_salary DELETED USER mary postgres= SELECT FROM salaries ; - RECORD 1 - emp_name Bob salary 1300 - RECORD 2 - emp_name Fred salary 750 - RECORD 3 - emp_name frank salary 100 postgres= SELECT FROM salary_change_log ; ... - RECORD 6 changed_by gsmith changed_at 2013-01-25 15:57:49.057592 salary_op INSERT emp_name Fred old_salary new_salary 750 24 Chapter 1 - RECORD 7 changed_by gsmith changed_at 2013-01-25 15:57:49.062456 salary_op INSERT emp_name frank old_salary new_salary 100 - RECORD 8 changed_by gsmith changed_at 2013-01-25 15:57:49.064337 salary_op DELETE emp_name mary old_salary 1200 new_salary Data cleaning We notice that employee names don't have consistent cases. It would be easy to enforce consistency by adding a constraint: CHECK (emp_name = upper(emp_name)) However, it is even better to just make sure that it is stored as uppercase, and the simplest way to do it is by usingtrigger: CREATE OR REPLACE FUNCTION uppercase_name () RETURNS trigger AS BEGIN NEW.emp_name = upper(NEW.emp_name); RETURN NEW; END; LANGUAGE plpgsql; CREATE TRIGGER uppercase_emp_name BEFORE INSERT OR UPDATE OR DELETE ON salaries FOR EACH ROW EXECUTE PROCEDURE uppercase_name (); The nextset_salary() call for a new employee will now insertemp_name in uppercase: postgres= SELECT set_salary('arnold',80); - RECORD 1 - set_salary INSERTED USER arnold 25 www.allitebooks.comWhat Is a PostgreSQL Server? As the uppercasing happened inside a trigger, the function response still shows a lowercase name, but in the database it is uppercase: postgres= SELECT FROM salaries ; - RECORD 1 - emp_name Bob salary 1300 - RECORD 2 - emp_name Fred salary 750 - RECORD 3 - emp_name frank salary 100 - RECORD 4 - emp_name ARNOLD salary 80 After fixing the existing mixed-case emp_names, we can make sure that allemp_names will be in uppercase in the future by adding a constraint: postgres= update salaries set emp_name = upper(emp_name) where not emp_name = upper(emp_name); UPDATE 3 postgres= alter table salaries add constraint emp_name_must_be_ uppercasepostgres- CHECK (emp_name = upper(emp_name)); ALTER TABLE If this behavior is needed in more places, it would make sense to define a new type – sayu_text, which is always stored as uppercase. You will learn more about this approach in the chapter about defining user types. Custom sort orders The last example in this chapter is about using functions for different ways of sorting. Say we are given a task of sorting words by their vowels only, and in addition to that, make the last vowel the most significant one when sorting. While this task may seem really complicated at first, it is easy to solve with functions: CREATE OR REPLACE FUNCTION reversed_vowels(word text) RETURNS text AS vowels = c for c in word.lower() if c in 'aeiou' vowels.reverse() return ''.join(vowels) 26

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.