INSERT and UPDATE of ALLBALLS/INFINITY dates and MOVE COLUMNS

Marco Lazzeri writes:

I need some examples of INSERT and UPDATE of DATE columns using
'ALLBALLS' and 'INFINITY' values. I could not find examples on the net.

These values are only supported by the type timestamp, not by date.

I need, also, to move columns in tables (just like the MOVE BEFORE/MOVE
AFTER MySQL commands). Can you help me?

This is not possible in PostgreSQL. Your best bet is to drop the table
and recreate it. Alternatively, do surgery using ADD COLUMN and DROP
COLUMN.

--
Peter Eisentraut peter_e@gmx.net

On Thu, 6 Nov 2003, Marco Lazzeri wrote:

I need, also, to move columns in tables (just like the MOVE BEFORE/MOVE
AFTER MySQL commands). Can you help me?

I do it with select into:

begin;
select field3, field2, field4, field1 into newtable from oldtable;
drop oldtable;
alter table newtable rename to oldtable;
commit; (or rollback; if something goes wrong).

Il gio, 2003-11-06 alle 16:00, scott.marlowe ha scritto:

I need, also, to move columns in tables (just like the MOVE BEFORE/MOVE
AFTER MySQL commands). Can you help me?

I do it with select into:

begin;
select field3, field2, field4, field1 into newtable from oldtable;
drop oldtable;
alter table newtable rename to oldtable;
commit; (or rollback; if something goes wrong).

Good idea! But you'll lose CONSTRAINTs and DEFAULTs. Isn't it?

Cheers,
Marco

On Thu, Nov 06, 2003 at 05:01:54 -0300,
MaRcElO PeReIrA <gandalf_mp@yahoo.com.br> wrote:

$ select * from products;
prod_id | description
--------+---------------------
1 | S470DXBLM
12 | S470DXABM
33 | RG250DX
--------+---------------------
(3 rows)

and it is ok to me, but not to the users.

Instead of using the MAX aggregate function, or the SELECT with LIMIT
clause, another approach is to use a stored procedure to increment a
sequence counter column you keep in a separate table. I have a database
that has a "master-detail" type relationship between a supplier table and
an employee table (zero or more employees work for one supplier). And I
keep a separately-incremented employee primary key sequence for the
employees of each supplier. To do that I define a column in the supplier
table holding the value of the most-recently issued employee key value,
and increment that inside a stored procedure using a trigger when a new
employee is inserted for a given supplier.

The supplier table is defined in part as

CREATE TABLE supplier
(
supplier_pk int4 NOT NULL,
...
employee_seq int4 NOT NULL DEFAULT 0,
CONSTRAINT supplier_pkey PRIMARY KEY (supplier_pk)
);

The employee table is defined in part as

CREATE TABLE paid.employee
(
supplier_pk int4 NOT NULL,
employee_pk int4 NOT NULL,
...
CONSTRAINT employee_pkey PRIMARY KEY (supplier_pk, employee_pk),
);

The sequencing procedure looks like:

CREATE OR REPLACE FUNCTION employee_seq_next(int4)
RETURNS int4 AS
'
DECLARE
l_supplier_pk ALIAS FOR $1;
BEGIN
UPDATE supplier
SET
employee_seq = (employee_seq + 1)
WHERE (supplier_pk = l_supplier_pk);

RETURN (SELECT employee_seq FROM supplier
WHERE (supplier_pk = l_supplier_pk));
END;'
LANGUAGE 'plpgsql' VOLATILE;

and the trigger procedure which calls the sequencing function looks like

CREATE OR REPLACE FUNCTION employee_bit()
RETURNS trigger AS
'
BEGIN
if new.employee_pk IS NULL THEN
SELECT INTO NEW.employee_pk employee_seq_next(new.supplier_pk);
END IF;
RETURN new;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

I'm told that doing the UPDATE first inside the trigger creates a lock on
the supplier table until the trigger transaction completes, so (I would
suppose, but I'm not expert enough to assert this for sure that) this
would assure you of getting one sequence increment at a time.

This seems like a workable paradigm which I used in other cases as well.

Still end up with holes in the sequence, though, if an employee row is
deleted, for example. Using the MAX function or LIMIT clauses would
protect against that in the cases where the most-recently-added employee
row were deleted.

Something else you can do, is define all your foreign key constraints
with the ON UPDATE CASCADE clause, so that you can manually change your
primary key values to fill in the holes.

~Berend Tober

On Thu, 6 Nov 2003, Marco Lazzeri wrote:

Il gio, 2003-11-06 alle 16:00, scott.marlowe ha scritto:

I need, also, to move columns in tables (just like the MOVE BEFORE/MOVE
AFTER MySQL commands). Can you help me?

I do it with select into:

begin;
select field3, field2, field4, field1 into newtable from oldtable;
drop oldtable;
alter table newtable rename to oldtable;
commit; (or rollback; if something goes wrong).

Good idea! But you'll lose CONSTRAINTs and DEFAULTs. Isn't it?

Correct. Also any views based on the underlying table will no longer
work.

Then again, the order of fields in a table is pretty esoteric, so I'd
expect this to be a one time thing. Me personally, I just live with them
in the order they were created in mostly.

MaRcElO PeReIrA wrote:

Hi guys,

I have been using the following table (short, short,
short version):

CREATE TABLE products (
prod_id SERIAL,
description TEXT
);

BUT, there is lots os users blaming because the holes
in the [prod_id] field (and, of course it as supposed
to be like this, because sequences only increase their
values and never rollback).

So, a real SELECT statement would return:

$ select * from products;
prod_id | description
--------+---------------------
1 | S470DXBLM
12 | S470DXABM
33 | RG250DX
--------+---------------------
(3 rows)

and it is ok to me, but not to the users.

How can I assure a ''sequence WITHOUT holes''?

Whatever you do, continue to use a serial field.

Now, it sounds like the users are just complaining about asthetics or
something, because both you and I know that it will work fine with the
way it is currently set up. But, you want to keep the users happy...

So, set up the table like this:

rec_id | prod_id | description
--------+---------------------
1 | 1 | S470DXBLM
12 | 2 | S470DXABM
33 | 3 | RG250DX
--------+---------------------

rec_id would be your primary key. prod_id would be what you show to the
users. Now, say someone deletes the middle record - you end up with:

rec_id | prod_id | description
--------+---------------------
1 | 1 | S470DXBLM
33 | 3 | RG250DX
--------+---------------------

So, you still have a gap to be filled when the next record is added.
What you will want to do is keep the "prod_id" of the record that was
deleted - store it in a "holding" table of deleted "prod_id" records:

rec_id | prod_id
--------+---------
1 | 2
--------+---------

Then, when you add a record to your main table, select for the lowest
prod_id from the deleted items table, and use that prod_id to insert
into your main table, and delete the record from the deleted items
table. So, your main table would then look something like this:

rec_id | prod_id | description
--------+---------------------
1 | 1 | S470DXBLM
33 | 3 | RG250DX
34 | 2 | XYZ123
--------+---------------------

If the deleted items table is empty when you go to insert, select the
largest prod_id from the main table, and increment it by one before
adding the record.

Now, you only show "prod_id" and "description" to the users, and voila,
no more gaps (at least as far as they are concerned - rec_id will still,
of course, have gaps, as it should).

Hope this helps...

Andrew Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.