- Storage: BOOLEAN requires 1 byte, CHAR(1) at least 5;
- Speed: BOOLEAN data is passed by value, not reference;
- Flexibility: BOOLEAN accepts a range of equivalent values, e.g. '1','t', 'yes' or TRUE to represent a true value (refer to the documentation for a full description: );
- Portability: The BOOLEAN datatype is defined in SQL99 and should be portable across other modern DBM systems.
The number of cast permutations in PostgreSQL has been minimized to reduce unpredictable behaviour. This means you may have to define some casts very explicitly, in this case SELECT bar::TEXT::INT4 FROM foo (or SELECT cast(bar AS TEXT)::INT4 FROM foo or SELECT cast(cast(bar AS TEXT) AS INT4) FROM foo).
Beginning with PostgreSQL 7.4, the slash command\dC in psql will show a list of all possible cast permutations.
Note that it is not possible to cast BOOLEAN values to another datatype. As a "workaround" it is possible to do something along these lines:
SELECT CASE WHEN value IS TRUE THEN 1 ELSE 0 END AS value FROM my_table
You have a table with at least two rows of completely identical behaviour and wish to delete a certain number without deleting all rows, e.g.
test=# SELECT * FROM mytable; id | foo | bar ----+----------------+----- 1 | Hello World | 22 1 | Hello World | 22 1 | Hello World | 22 1 | Hello World | 33 1 | Other text | 44 (5 rows)
A intuitive solution would be to use LIMIT, e.g. DELETE FROM mytable WHERE id = 1 LIMIT 2, but this syntax is not valid.
The oid column provides a useful if well-hidden unique identifier for each row. Use a subquery to select the required number of rows for deletion:
DELETE FROM mytable WHERE oid IN (SELECT oid FROM mytable WHERE id=1 LIMIT 2)
Note that tables may be created without oids. Also, oids are four byte unsigned integers and are cannot be assumed to be unique, although in the practice they usually will be.
When transferring data from a BYTEA column there does not seem to be a permissible combination of casts, e.g.:
test=# UPDATE mytable SET text_col = byte_col; ERROR: column "text_col" is of type text but expression is of type bytea You will need to rewrite or cast the expression test=# UPDATE mytable SET text_col = byte_col::text; ERROR: Cannot cast type bytea to text
Use DECODE(), e.g.
UPDATE mytable SET text_col = DECODE(byte_col, 'escape')
test=# BEGIN; BEGIN test=# SELECT now(); now ------------------------------- 2003-05-14 22:29:04.074993+02 (1 row) test=# -- (wait several seconds) test=# SELECT now(); now ------------------------------- 2003-05-14 22:29:04.074993+02 (1 row)
The value returned by NOW() (and the equivalent CURRENT_TIMESTAMP) is set at the start of a transaction and does not change. To retrieve the current (wall-clock) time use TIMEOFDAY().
A reason for this behaviour is that it enables user defined functions to be defined as STABLE.
In MySQL it is possible to create tables like this:
CREATE TABLE serial_exmpl ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, abc TEXT )
and have a unique value inserted into the AUTO_INCREMENT column by either omitting the column from any INSERT statements:
INSERT INTO serial_exmpl (abc) VALUES('When is a NULL not a NULL?')
or by inserting a NULL value:
INSERT INTO serial_exmpl VALUES(NULL, 'When it is in a MySQL database!')
This produces the following result:
myprogramlisting> SELECT * FROM serial_exmpl; +----+---------------------------------+ | id | abc | +----+---------------------------------+ | 1 | When is a NULL not a NULL? | | 2 | When it is in a MySQL database! | +----+---------------------------------+ 2 rows in set (0.02 sec)
In PostgreSQL the equivalent table might be:
CREATE TABLE serial_exmpl ( id SERIAL PRIMARY KEY, abc TEXT )
Replace the NULL with DEFAULT and PostgreSQL will insert the next sequence value for you:
INSERT INTO serial_exmpl VALUES(DEFAULT, 'Remember: NULL is the absence of a value')
If for whatever reason it is not possible to convert the MySQL statements to use DEFAULT, it is possible to define a trigger on the affected table(s) like this:
CREATE OR REPLACE FUNCTION null_to_default() RETURNS TRIGGER AS ' BEGIN IF NEW.id IS NULL THEN SELECT INTO NEW.id nextval(''public.serial_exmpl_id_seq''::text); END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER serial_exmpl_trg BEFORE INSERT ON serial_exmpl FOR EACH ROW EXECUTE PROCEDURE null_to_default();
This ensures that insertion of a NULL value will result in automatic conversion to the next sequence value.
The relevant standards such as SQL92 and SQL99 are not as readily available as one might hope. In particular SQL99 only seems to be available via online purchase.
The following links provide further information: