Horton

Thread

  1. easy way to insert same value into 2 columns

    Sebastian Böck <sebastianboeck@freenet.de> — 2003-10-16T09:40:59Z

    Hello,

whats the most simple way to insert the same value into 2 colums of 
the same table?
My table is looking like this:

  Spalte |   Typ   |     Attribute
--------+---------+----------------------
  id     | integer | default nextval ('public.zeit_id_seq'::text)
  pos_id | integer | default xxx

for the xxx i want the same value than id be inserted automatically

Regards

Sebastian

  2. Re: easy way to insert same value into 2 columns

    Csaba Nagy <nagy@ecircle-ag.com> — 2003-10-16T10:03:09Z

    For your specific question I don't know the answer.

For this particular case you could use:

create table your_table (
  id integer default nextval ('public.zeit_id_seq'::text),
  pos_id integer default currval ('public.zeit_id_seq'::text),
  ...
);

That would work fine as long as you use inserts which don't specify id
if pos_id is not specified (otherwise the currval will throw you an
error cause it cannot be called without nextval being called).

HTH,
Csaba.

On Thu, 2003-10-16 at 11:40, Sebastian Boeck wrote:
> Hello,
> 
> whats the most simple way to insert the same value into 2 colums of 
> the same table?
> My table is looking like this:
> 
>   Spalte |   Typ   |     Attribute
> --------+---------+----------------------
>   id     | integer | default nextval ('public.zeit_id_seq'::text)
>   pos_id | integer | default xxx
> 
> for the xxx i want the same value than id be inserted automatically
> 
> Regards
> 
> Sebastian
> 
> 
> ---------------------------(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

  3. Re: easy way to insert same value into 2 columns

    Sebastian Böck <sebastianboeck@freenet.de> — 2003-10-16T10:11:51Z

    Csaba Nagy wrote:
> For your specific question I don't know the answer.
> 
> For this particular case you could use:
> 
> create table your_table (
>   id integer default nextval ('public.zeit_id_seq'::text),
>   pos_id integer default currval ('public.zeit_id_seq'::text),
>   ...
> );
> 
> That would work fine as long as you use inserts which don't specify id
> if pos_id is not specified (otherwise the currval will throw you an
> error cause it cannot be called without nextval being called).
> 
> HTH,
> Csaba.

Thanks a lot, but is it save to use?

Do i always get the same value, even if an other insert is changing 
the sequence >public.zeit_id_seq< just at the same time?

Regards

Sebastian

  4. Re: easy way to insert same value into 2 columns

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-10-16T10:50:08Z

    Sebastian Boeck wrote:
> Thanks a lot, but is it save to use?
> 
> Do i always get the same value, even if an other insert is changing the 
> sequence >public.zeit_id_seq< just at the same time?

You can write a before insert trigger where you retrieve sequence value once and 
set for two fields. That way it will ensure that both the fields would get same 
value.

  HTH

  Shridhar

  5. Re: easy way to insert same value into 2 columns

    Richard Huxton <dev@archonet.com> — 2003-10-16T10:51:19Z

    On Thursday 16 October 2003 11:11, Sebastian Boeck wrote:
> Csaba Nagy wrote:
> > For your specific question I don't know the answer.
> >
> > For this particular case you could use:
> >
> > create table your_table (
> >   id integer default nextval ('public.zeit_id_seq'::text),
> >   pos_id integer default currval ('public.zeit_id_seq'::text),
> >   ...
> > );
> >
> > That would work fine as long as you use inserts which don't specify id
> > if pos_id is not specified (otherwise the currval will throw you an
> > error cause it cannot be called without nextval being called).
> >
> > HTH,
> > Csaba.
>
> Thanks a lot, but is it save to use?

Not really.

> Do i always get the same value, even if an other insert is changing
> the sequence >public.zeit_id_seq< just at the same time?

Other processes can't interfere - the whole point of sequences is that they 
are safe for this sort of thing.

Where you will have problems is that if one of the developers decides it's 
more efficient to process fields backwards (zzz...pos_id, id) rather than 
forwards (id, pos_id, ...zzz) then it will break.

Use a trigger here. If nothing else so you can stop people like me doing:

INSERT INTO your_table (id,pos_id) VALUES (-1,DEFAULT);

-- 
  Richard Huxton
  Archonet Ltd

  6. Re: easy way to insert same value into 2 columns

    Jacob Vennervald <jvennervald@proventum.net> — 2003-10-16T10:53:40Z

    > Thanks a lot, but is it save to use?
> 
> Do i always get the same value, even if an other insert is changing 
> the sequence >public.zeit_id_seq< just at the same time?
Yes. currval() gives you the last id of the sequence in your session.
So if others log in and insert more rows it wont affect your session.

Can I ask why you would wanna do that anyway?

Jacob


On Thu, 2003-10-16 at 12:11, Sebastian Boeck wrote:
> Csaba Nagy wrote:
> > For your specific question I don't know the answer.
> > 
> > For this particular case you could use:
> > 
> > create table your_table (
> >   id integer default nextval ('public.zeit_id_seq'::text),
> >   pos_id integer default currval ('public.zeit_id_seq'::text),
> >   ...
> > );
> > 
> > That would work fine as long as you use inserts which don't specify id
> > if pos_id is not specified (otherwise the currval will throw you an
> > error cause it cannot be called without nextval being called).
> > 
> > HTH,
> > Csaba.
> 
> Thanks a lot, but is it save to use?
> 
> Do i always get the same value, even if an other insert is changing 
> the sequence >public.zeit_id_seq< just at the same time?
> 
> Regards
> 
> Sebastian
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 
-- 
Venlig hilsen / Best regards,
Jacob Vennervald
System Developer
Proventum Solutions ApS
Tuborg Boulevard 12
2900 Hellerup
Denmark
Phone: 	+45 36 94 41 66
Mobile:	+45 61 68 58 51

  7. Re: easy way to insert same value into 2 columns

    Sebastian Böck <sebastianboeck@freenet.de> — 2003-10-16T11:03:57Z

    Jacob Vennervald wrote:
>>Thanks a lot, but is it save to use?
>>
>>Do i always get the same value, even if an other insert is changing 
>>the sequence >public.zeit_id_seq< just at the same time?
> 
> Yes. currval() gives you the last id of the sequence in your session.
> So if others log in and insert more rows it wont affect your session.
> 
> Can I ask why you would wanna do that anyway?
> 
> Jacob

Sure!

I have several tables with an inheritance hierachy.
The first one (zeit) has a Serialfield called id.
All other tables inherit from this one, so that i have
an absolutely unique id over all tables. But if i want
to reference the id of a table in the middle of the
hierarchy-chain, it won't work, because it's not the
tables own index. Therefore i need an duplicated value.
On this column i can create another index that can be
used as an foreign key by other tables.

Are there any other ways to accomplish this?

Regards

Sebastian

  8. Re: easy way to insert same value into 2 columns

    Csaba Nagy <nagy@ecircle-ag.com> — 2003-10-16T11:21:38Z

    I can't 100% say it's safe to use, but no, other inserts will not
influence the value, because currval will return the last sequence value
used by the current session (i.e. the current connection). And of course
you only have concurrent inserts using different connection, right ?

HTH,
Csaba.


On Thu, 2003-10-16 at 12:11, Sebastian Boeck wrote:
> Csaba Nagy wrote:
> > For your specific question I don't know the answer.
> > 
> > For this particular case you could use:
> > 
> > create table your_table (
> >   id integer default nextval ('public.zeit_id_seq'::text),
> >   pos_id integer default currval ('public.zeit_id_seq'::text),
> >   ...
> > );
> > 
> > That would work fine as long as you use inserts which don't specify id
> > if pos_id is not specified (otherwise the currval will throw you an
> > error cause it cannot be called without nextval being called).
> > 
> > HTH,
> > Csaba.
> 
> Thanks a lot, but is it save to use?
> 
> Do i always get the same value, even if an other insert is changing 
> the sequence >public.zeit_id_seq< just at the same time?
> 
> Regards
> 
> Sebastian
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

  9. Re: easy way to insert same value into 2 columns

    Sebastian Böck <sebastianboeck@freenet.de> — 2003-10-16T11:53:27Z

    Csaba Nagy wrote:
> I can't 100% say it's safe to use, but no, other inserts will not
> influence the value, because currval will return the last sequence value
> used by the current session (i.e. the current connection). And of course
> you only have concurrent inserts using different connection, right ?
> 
> HTH,
> Csaba.

Yes, that' right.

Thanks

Sebastian

  10. Re: easy way to insert same value into 2 columns

    Sebastian Böck <sebastianboeck@freenet.de> — 2003-10-16T11:58:21Z

    Richard Huxton wrote:
> On Thursday 16 October 2003 11:11, Sebastian Boeck wrote:
> 
>>Csaba Nagy wrote:
>>
>>>For your specific question I don't know the answer.
>>>
>>>For this particular case you could use:
>>>
>>>create table your_table (
>>>  id integer default nextval ('public.zeit_id_seq'::text),
>>>  pos_id integer default currval ('public.zeit_id_seq'::text),
>>>  ...
>>>);
>>>
>>>That would work fine as long as you use inserts which don't specify id
>>>if pos_id is not specified (otherwise the currval will throw you an
>>>error cause it cannot be called without nextval being called).
>>>
>>>HTH,
>>>Csaba.
>>
>>Thanks a lot, but is it save to use?
> 
> 
> Not really.
> 
> 
>>Do i always get the same value, even if an other insert is changing
>>the sequence >public.zeit_id_seq< just at the same time?
> 
> 
> Other processes can't interfere - the whole point of sequences is that they 
> are safe for this sort of thing.
> 
> Where you will have problems is that if one of the developers decides it's 
> more efficient to process fields backwards (zzz...pos_id, id) rather than 
> forwards (id, pos_id, ...zzz) then it will break.
> 
> Use a trigger here. If nothing else so you can stop people like me doing:
> 
> INSERT INTO your_table (id,pos_id) VALUES (-1,DEFAULT);
> 

Is it possible to create the function called by a trigger entirely
in SQL, or do i have to use another language?

Regards

Sebastian

  11. Re: easy way to insert same value into 2 columns

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-10-16T12:07:25Z

    Sebastian Boeck wrote:
> Is it possible to create the function called by a trigger entirely
> in SQL, or do i have to use another language?

You can write trigger/functions in many languages. For this particular need 
pl/pgsql would be more than good enough.

  Shridhar

  12. Re: easy way to insert same value into 2 columns

    Bruno Wolff III <bruno@wolff.to> — 2003-10-16T17:20:50Z

    On Thu, Oct 16, 2003 at 13:53:27 +0200,
  Sebastian Boeck <sebastianboeck@freenet.de> wrote:
> Csaba Nagy wrote:
> >I can't 100% say it's safe to use, but no, other inserts will not
> >influence the value, because currval will return the last sequence value
> >used by the current session (i.e. the current connection). And of course
> >you only have concurrent inserts using different connection, right ?
> >
> >HTH,
> >Csaba.
> 
> Yes, that' right.
> 
> Thanks

As mentioned earlier, the problem is with concurrent inserts, but rather
with which default expression gets calculated first. Today you are likely
to get predictable behavior out of this, but if you do an alter table
or perhaps upgrade to a new version of postgres, your code may break.