sql-info.de
DeutschEnglish

2. SQL

2.1. What are the advantages of datatype BOOLEAN over e.g. CHAR(1)?

  • 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.

2.2. Why don't casts such as SELECT bar::INT4 FROM foo work when bar is defined as VARCHAR?

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

2.3. How can I delete some but not all identical rows?

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.

References:
http://www.postgresql.org/docs/current/static/datatype-oid.html

2.4. How can I transfer text in a BYTEA column to a TEXT or VARCHAR column?

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

etc.

Use DECODE(), e.g.

UPDATE mytable SET text_col = DECODE(byte_col, 'escape')

2.5. Why do seperate invocations of NOW() return the same timestamp?

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.

2.6. How can I emulate MySQL's use of NULL to obtain the next value of an AUTO_INCREMENT pseudo-sequence?

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.

2.7. Where can I obtain the SQL standards?

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: