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_i d_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 11 2512
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_i d_seq'::text),
pos_id integer default currval ('public.zeit_i d_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_i d_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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
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_i d_seq'::text), pos_id integer default currval ('public.zeit_i d_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
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
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_i d_seq'::text), pos_id integer default currval ('public.zeit_i d_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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
> 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_i d_seq'::text), pos_id integer default currval ('public.zeit_i d_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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
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_i d_seq'::text), pos_id integer default currval ('public.zeit_i d_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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
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_i d_seq'::text), pos_id integer default currval ('public.zeit_i d_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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Frank Natoli |
last post by:
Have two tables "abc" and "xyz", where "xyz" is a superset, column-wise, of
"abc".
Is there any simple way to inject all the rows of "abc" into "xyz"?
Tried "insert into xyz select * from abc" but got a complaint of "column
count doesn't match value count at row 1", which of course is true.
Any way to tell SQL to simply set defaults for the missing columns?
|
by: akej via SQLMonster.com |
last post by:
Hi, i have table with 15 columns
CREATE TABLE . (
PRIMARY KEY ,
NULL ,
NULL ,
NULL ,
NULL ,
(50) NULL ,
NULL
|
by: robert |
last post by:
been ruminating on the question (mostly in a 390/v7 context) of
whether, and if so when, a row update becomes an insert/delete.
i assume that there is a threshold on the number of columns of
the table, or perhaps bytes, being updated where the engine just
decides, screw it, i'll just make a new one.
surfed this group and google, but couldn't find anything.
the context: we have some java folk who like to parametize/
|
by: ven |
last post by:
hello i`m makin a asp.net service with database connection where i have an
insert with decimal value, when i run my function i get this error :
There are fewer columns in the INSERT statement than values specified in the
VALUES clause. The number of values in the VALUES clause must match the
number of columns specified in the INSERT statement
this is my insert :
Dim queryString2 = "INSERT INTO
|
by: Mad Scientist Jr |
last post by:
can someone explain how to simply populate a grid in .net ? the way i
understand it, there is no more msflexgrid, and instead is this new
control that has to be tied to a dataset, and it is a real pain to
work with if you just want to throw some values in a grid and edit
them with a textbox. i don't want to persist anything in a database, i
just need a fast cheap and easy grid in memory that i can work with!
i had functions in vb6 that...
| |
by: Geoffrey KRETZ |
last post by:
Hello,
I'm wondering if the following behaviour is the correct one for
PostGreSQL (7.4 on UNIX).
I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a launching
the following request :
INSERT INTO temp_tab VALUES (1,2,3)
|
by: cavassinif |
last post by:
I need to dynamic select a column in which insert a vale based on a
parameter value, I have this code, but it throws an incorrect syntax
error.
How do I dinamically select a column to insert based on a parameter?
Create PROCEDURE dbo.UpdateDetalleOT (
@eotId int,
)
|
by: Kevin Lawrence |
last post by:
Hi all
I want to do "INSERT INTO Table (Blob) Values('blobdataasstring')".
...rather than using the parameter driven method, is it possible? And if so
what encoder do I use to convert the bytes to string format?
Thanks
Kev
|
by: rn5a |
last post by:
During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am
using MS-Access 2000 database table for this app. Note that the
datatype of all the fields mentioned above are Text. Apart from the
above columns, there's another column in the DB table named
'RegDateTime' whose datatype is Date/Time which is...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |