If you find that your template1 has unnecessary objects in it, for example, obsolete functions, then you should clean it out. Only objects that you want to have in any new databases that you create should be in template1
The most conservative and sane solution is to look at objects that are not in the pg_catalog schema and explicitly drop any unwanted objects.
A slightly less conservative approach is to change the template designation of template1, drop it and recreate it from template0. You could do this if your template1 is beyond repair.
=# UPDATE pg_database SET datistemplate = false WHERE datname = 'template1'; =# DROP DATABASE template1; =# CREATE DATABASE template1 WITH template template0; =# UPDATE pg_database SET datistemplate = true WHERE datname = 'template1';
It was suggested, perhaps in jest, that you could also delete the data directory and do an initdb however this would make you lose all of your existing data so it is NOT recommended.
When you copy data using the copy command you can designate how you are specifying NULL in the file by using:
copy .... with NULL 'xxx'where xxx is your NULL string.
The default value of the null designation is '\N' (no quotes). If you use '' as the null designation, it will assume that empty fields are to be null. This may not be correct if your data is supposed to contain empty strings.
The question in hand was about empty fields targeted for timestamp columns. In this case, it was correct to use '' because it did not interfere with other data.
In the normal course of events, sorting by a nullable column will result in the columns containing nulls to sort after all of the other rows. If you want the nulls first, you could sort in descending order:
select col from mytable order by col desc;However, this simply reverses the entire sort order. You may not want that.
To sort in normal order, but with nulls first use:
select coll from mytable order by col IS NULL DESC, COL
This example illustrates simply the feature in PostgreSQL where you may use an expression as part of the order by clause.
The following is a trigger was created to validate the tag_number column for uniqueness on update. However, when columns other than tag_number are updated, they are rejected. When, say, column item_name is updated, both the OLD and the NEW records will have the same value for tag_number and therefore the exception will be raised.
CREATE OR REPLACE FUNCTION validate_tag_number() RETURNS OPAQUE AS ' BEGIN IF new.tag_number=old.tag_number THEN RAISE EXCEPTION ''Number is already present ''; END IF; RETURN NEW; END; 'LANGUAGE 'plpgsql'; CREATE TRIGGER validate_tag_number BEFORE UPDATE ON tag_vendor FOR EACH ROW EXECUTE PROCEDURE validate_tag_number();
What you really want to do to ensure uniqueness is to define the column with a unique index by declaring it to be UNIQUE.
The UPDATE statement can take a FROM clause. This enables easy update of one table from the values of any selection. The FROM clause can be a table or a subquery. The following query can be simplified into one query:
UPDATE product SET price_min = (SELECT min (price) FROM subproduct WHERE subproduct.product = $ID), price_max = (SELECT max (price) FROM subproduct WHERE subproduct.product = $ID) WHERE id = $ID
The following query is is equivalent to the query above.
UPDATE product SET price_min = ss.min_price, price_max = ss.max_price FROM ( SELECT min( price) as min_price, max( price) as max_price FROM subproduct WHERE subproduct.product = $ID ) as ss WHERE id = $ID;
You may not use an alias from the target list in your where clause because the where clause is evaluated before the target list. First the rows are qualified, then the rows are fetched.
-- Incorrect SELECT id, sum(hours) AS totalhours FROM mytable WHERE totalhours > 50 GROUP BY id;
You may then try to put the aggregate in the WHERE clause, however, aggregates are not allowed in the WHERE clause. Qualification for aggregation should be specified in the HAVING clause. WHERE filters rows before aggregation and HAVING filters rows after aggregation. The correct query would be either one of the following:
SELECT id, sum(hours) AS totalhours FROM mytable GROUP BY id HAVING sum(hours) > 50; SELECT * FROM (SELECT id, sum(hours) AS totalhours FROM mytable GROUP BY id) ss WHERE totalhours > 50;To read more about aggregates, see:
Henrik reported occasional total system lock-ups on a production PostgreSQL 7.3 on Red Hat Linux production server. Apparently the machine would have a kernel panic or similar during nightly maintenence which includes VACUUM FULL and REINDEXing.
Respondents were universal in diagnosing the report as a hardware or operating system problem. Several people pointed out that while PostgreSQL could crash itself, it could not crash the host OS unless its activity was exposing an existing hardware or kernel problem.
The most popular diagnosis was bad RAM; seven participants mentioned similar incidents with a variety of systems all related to bad or incompatible RAM. Two people mentioned having seen RAM fail after a significant period of use ... one after 2 years. Other suggested diagnoses included:
Other participants chimed in with a variety of ways to test a new system, which included:
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009