Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
The best way would be to declare a DEFAULT value for each bit(n) table
column. You can do that after table creation with with ALTER TABLE.

Alternatively, you could use a trigger.
Here is an example for a general purpose trigger function (for 8.2+):

CREATE OR REPLACE FUNCTION bittrig() RETURNS trigger
LANGUAGE plpgsql VOLATILE STRICT AS
$$DECLARE
attname name;
attlen integer;
stmt text;
first boolean := TRUE;
BEGIN
stmt := 'UPDATE ' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ' SET ';
FOR attname, attlen IN
(SELECT c.attname, c.atttypmod
FROM pg_catalog.pg_attribute c
JOIN pg_catalog.pg_class t ON (t.oid = c.attrelid)
JOIN pg_catalog.pg_type m ON (c.atttypid = m.oid)
JOIN pg_catalog.pg_namespace s ON (t.relnamespace = s.oid)
WHERE t.relname = TG_TABLE_NAME AND m.typname = 'bit'
AND s.nspname = TG_TABLE_SCHEMA) LOOP
IF (first) THEN first := FALSE; ELSE stmt := stmt || ','; END IF;
stmt := stmt || ' ' || attname ||
' = B''' || repeat('0', attlen) || '''';
END LOOP;
stmt := stmt || ' WHERE ctid = ''' || NEW.ctid || '''';
EXECUTE stmt;
RETURN NEW;
END;$$;

You use it like that:

CREATE TABLE bittest (id integer PRIMARY KEY, b1 bit(10),
t varchar(10), b2 bit(27));

CREATE TRIGGER bittrig AFTER INSERT ON bittest FOR EACH ROW
EXECUTE PROCEDURE bittrig();

INSERT INTO bittest (id, t) VALUES (42, 'text');

SELECT * FROM bittest;
id | b1 | t | b2
----+------------+------+-----------------------------
42 | 0000000000 | text | 000000000000000000000000000
(1 row)

There is a certain performance penalty with this approach, because after
each INSERT there will be an UPDATE statement.

Besides, there is a slim chance that the system catalogs may change in a
future release and you might have to adapt the function.

Yours,
Laurenz Albe

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
The best way would be to declare a DEFAULT value for each bit(n) table
column. You can do that after table creation with with ALTER TABLE.

Alternatively, you could use a trigger.
Here is an example for a general purpose trigger function (for 8.2+):

CREATE OR REPLACE FUNCTION bittrig() RETURNS trigger
LANGUAGE plpgsql VOLATILE STRICT AS
$$DECLARE
attname name;
attlen integer;
stmt text;
first boolean := TRUE;
BEGIN
stmt := 'UPDATE ' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ' SET ';
FOR attname, attlen IN
(SELECT c.attname, c.atttypmod
FROM pg_catalog.pg_attribute c
JOIN pg_catalog.pg_class t ON (t.oid = c.attrelid)
JOIN pg_catalog.pg_type m ON (c.atttypid = m.oid)
JOIN pg_catalog.pg_namespace s ON (t.relnamespace = s.oid)
WHERE t.relname = TG_TABLE_NAME AND m.typname = 'bit'
AND s.nspname = TG_TABLE_SCHEMA) LOOP
IF (first) THEN first := FALSE; ELSE stmt := stmt || ','; END IF;
stmt := stmt || ' ' || attname ||
' = B''' || repeat('0', attlen) || '''';
END LOOP;
stmt := stmt || ' WHERE ctid = ''' || NEW.ctid || '''';
EXECUTE stmt;
RETURN NEW;
END;$$;

You use it like that:

CREATE TABLE bittest (id integer PRIMARY KEY, b1 bit(10),
t varchar(10), b2 bit(27));

CREATE TRIGGER bittrig AFTER INSERT ON bittest FOR EACH ROW
EXECUTE PROCEDURE bittrig();

INSERT INTO bittest (id, t) VALUES (42, 'text');

SELECT * FROM bittest;
id | b1 | t | b2
----+------------+------+-----------------------------
42 | 0000000000 | text | 000000000000000000000000000
(1 row)

There is a certain performance penalty with this approach, because after
each INSERT there will be an UPDATE statement.

Besides, there is a slim chance that the system catalogs may change in a
future release and you might have to adapt the function.

Yours,
Laurenz Albe

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
The best way would be to declare a DEFAULT value for each bit(n) table
column. You can do that after table creation with with ALTER TABLE.

Alternatively, you could use a trigger.
Here is an example for a general purpose trigger function (for 8.2+):

CREATE OR REPLACE FUNCTION bittrig() RETURNS trigger
LANGUAGE plpgsql VOLATILE STRICT AS
$$DECLARE
attname name;
attlen integer;
stmt text;
first boolean := TRUE;
BEGIN
stmt := 'UPDATE ' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ' SET ';
FOR attname, attlen IN
(SELECT c.attname, c.atttypmod
FROM pg_catalog.pg_attribute c
JOIN pg_catalog.pg_class t ON (t.oid = c.attrelid)
JOIN pg_catalog.pg_type m ON (c.atttypid = m.oid)
JOIN pg_catalog.pg_namespace s ON (t.relnamespace = s.oid)
WHERE t.relname = TG_TABLE_NAME AND m.typname = 'bit'
AND s.nspname = TG_TABLE_SCHEMA) LOOP
IF (first) THEN first := FALSE; ELSE stmt := stmt || ','; END IF;
stmt := stmt || ' ' || attname ||
' = B''' || repeat('0', attlen) || '''';
END LOOP;
stmt := stmt || ' WHERE ctid = ''' || NEW.ctid || '''';
EXECUTE stmt;
RETURN NEW;
END;$$;

You use it like that:

CREATE TABLE bittest (id integer PRIMARY KEY, b1 bit(10),
t varchar(10), b2 bit(27));

CREATE TRIGGER bittrig AFTER INSERT ON bittest FOR EACH ROW
EXECUTE PROCEDURE bittrig();

INSERT INTO bittest (id, t) VALUES (42, 'text');

SELECT * FROM bittest;
id | b1 | t | b2
----+------------+------+-----------------------------
42 | 0000000000 | text | 000000000000000000000000000
(1 row)

There is a certain performance penalty with this approach, because after
each INSERT there will be an UPDATE statement.

Besides, there is a slim chance that the system catalogs may change in a
future release and you might have to adapt the function.

Yours,
Laurenz Albe