INSERT and UPDATE of ALLBALLS/INFINITY dates and MOVE COLUMNS

[Marco Lazzeri]

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

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

Thank you!
Have a nice day,
--
Marco Lazzeri


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Peter Eisentraut]

Re: INSERT and UPDATE of ALLBALLS/INFINITY dates and MOVE

Marco Lazzeri writes:
[color=blue]
> I need some examples of INSERT and UPDATE of DATE columns using
> 'ALLBALLS' and 'INFINITY' values. I could not find examples on the net.[/color]

These values are only supported by the type timestamp, not by date.
[color=blue]
> I need, also, to move columns in tables (just like the MOVE BEFORE/MOVE
> AFTER MySQL commands). Can you help me?[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[scott.marlowe]

Re: INSERT and UPDATE of ALLBALLS/INFINITY dates and MOVE

On Thu, 6 Nov 2003, Marco Lazzeri wrote:
[color=blue]
> I need, also, to move columns in tables (just like the MOVE BEFORE/MOVE
> AFTER MySQL commands). Can you help me?[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Marco Lazzeri]

Re: INSERT and UPDATE of ALLBALLS/INFINITY dates and

Il gio, 2003-11-06 alle 16:00, scott.marlowe ha scritto:[color=blue][color=green]
> > I need, also, to move columns in tables (just like the MOVE BEFORE/MOVE
> > AFTER MySQL commands). Can you help me?[/color]
>
> 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).[/color]

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

Cheers,
Marco


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[scott.marlowe]

Re: INSERT and UPDATE of ALLBALLS/INFINITY dates and

On Thu, 6 Nov 2003, Marco Lazzeri wrote:
[color=blue]
> Il gio, 2003-11-06 alle 16:00, scott.marlowe ha scritto:[color=green][color=darkred]
> > > I need, also, to move columns in tables (just like the MOVE BEFORE/MOVE
> > > AFTER MySQL commands). Can you help me?[/color]
> >
> > 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).[/color]
>
> Good idea! But you'll lose CONSTRAINTs and DEFAULTs. Isn't it?[/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[btober@seaworthysys.com]

Re: Sequences without blank holes

[color=blue]
> On Thu, Nov 06, 2003 at 05:01:54 -0300,
> MaRcElO PeReIrA <gandalf_mp@yah oo.com.br> wrote:[color=green]
>>
>> $ 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.[/color]
>[/color]

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_ne xt(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_ne xt(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




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html