Hello
Now perhaps this is a bit dumb, but...
I just populated a new table via \copy. After that, I realize that perhaps is a good thing to have a row identifier in it, so I try
clapidus=> alter table tickets add column rid serial;
NOTICE: ALTER TABLE will create implicit sequence "tickets_rid_se q" for SERIAL column "tickets.ri d"
ERROR: adding columns with defaults is not implemented
So my next guess is to define a test table from scratch, this time with theserial field in place. Next I try the \copy:
clapidus=> create table test(rid serial, col_a text);
NOTICE: CREATE TABLE will create implicit sequence "test_rid_s eq" for SERIAL column "test.rid"
CREATE TABLE
clapidus=> \d test
Table "test"
Attribute | Type | Modifier
-----------+---------+-------------------------------------------------------
rid | integer | not null default nextval('public .test_rid_seq': :text)
col_a | text |
clapidus=> \copy test from stdin
23 a record
45 another record
\.
clapidus=> select * from test ;
rid | col_a
-----+------------
23 | a record
45 | another record
(2 rows)
Now the first character from stdin is a tab, in a try to let the sequence come into action:
clapidus=> \copy test from stdin
still another record
\.
ERROR: invalid input syntax for integer: ""
PQendcopy: resetting connection
Grrr. Third attempt:
clapidus=> \copy test from stdin with null as 'NULL'
NULL still another one
\.
ERROR: null value for attribute "rid" violates NOT NULL constraint
PQendcopy: resetting connection
So? Is there a way to add the sequence to an existing table?
Or, alternatively, is there a way to issue a \copy command while letting the sequence fill in the serial field?
thanks in advance
cl. 3 17007
Do it in three steps...
CREATE SEQUENCE tickets_rid_seq ;
ALTER TABLE tickets ADD COLUMN rid INTEGER;
ALTER TABLE tickets ALTER COLUMN rid SET DEFAULT nextval('ticket s_rid_seq')
""Claudio Lapidus"" <cl******@hotma il.com> wrote in message
news:BA******** *************** **@hotmail.com. ..
Hello
Now perhaps this is a bit dumb, but...
I just populated a new table via \copy. After that, I realize that perhaps
is a good thing to have a row identifier in it, so I try
clapidus=> alter table tickets add column rid serial;
NOTICE: ALTER TABLE will create implicit sequence "tickets_rid_se q" for
SERIAL column "tickets.ri d"
ERROR: adding columns with defaults is not implemented
So my next guess is to define a test table from scratch, this time with the
serial field in place. Next I try the \copy:
clapidus=> create table test(rid serial, col_a text);
NOTICE: CREATE TABLE will create implicit sequence "test_rid_s eq" for
SERIAL column "test.rid"
CREATE TABLE
clapidus=> \d test
Table "test"
Attribute | Type | Modifier
-----------+---------+------------------------------------------------------
-
rid | integer | not null default nextval('public .test_rid_seq': :text)
col_a | text |
clapidus=> \copy test from stdin
23 a record
45 another record
\.
clapidus=> select * from test ;
rid | col_a
-----+------------
23 | a record
45 | another record
(2 rows)
Now the first character from stdin is a tab, in a try to let the sequence
come into action:
clapidus=> \copy test from stdin
still another record
\.
ERROR: invalid input syntax for integer: ""
PQendcopy: resetting connection
Grrr. Third attempt:
clapidus=> \copy test from stdin with null as 'NULL'
NULL still another one
\.
ERROR: null value for attribute "rid" violates NOT NULL constraint
PQendcopy: resetting connection
So? Is there a way to add the sequence to an existing table?
Or, alternatively, is there a way to issue a \copy command while letting the
sequence fill in the serial field?
thanks in advance
cl.
"Claudio Lapidus" <cl******@hotma il.com> writes: So? Is there a way to add the sequence to an existing table?
Sure. You have to break the SERIAL down to its component parts though.
Something like
CREATE SEQUENCE seq;
ALTER TABLE tab ADD COLUMN ser INTEGER;
UPDATE tab SET ser = nextval('seq'); -- this will take awhile
ALTER TABLE tab ALTER COLUMN ser SET DEFAULT nextval('seq');
ALTER TABLE tab ALTER COLUMN ser SET NOT NULL;
-- possibly also add a UNIQUE constraint
We haven't yet got round to supporting ADD COLUMN ... DEFAULT because
according to the SQL spec that implies doing the UPDATE to fill the
column values immediately, and that's just a chunk of code no one's
written yet.
Or, alternatively, is there a way to issue a \copy command while letting th= e sequence fill in the serial field?
Yes, you have to list in the COPY command just the columns that are
actually being supplied in the input data. Columns not mentioned (like
the serial column) get filled from their defaults. I think this is
new in 7.3 ... it's pretty recent anyway.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Mon, 1 Sep 2003, Tom Lane wrote: "Claudio Lapidus" <cl******@hotma il.com> writes: So? Is there a way to add the sequence to an existing table?
Sure. You have to break the SERIAL down to its component parts though. Something like
CREATE SEQUENCE seq; ALTER TABLE tab ADD COLUMN ser INTEGER; UPDATE tab SET ser = nextval('seq'); -- this will take awhile ALTER TABLE tab ALTER COLUMN ser SET DEFAULT nextval('seq'); ALTER TABLE tab ALTER COLUMN ser SET NOT NULL; -- possibly also add a UNIQUE constraint
For folks just starting out, you can also do it this way:
=>begin;
=>create table a (info text, date date);
CREATE TABLE
=> insert into a values ('abc','2003-04-03');
INSERT 1127459 1
=> create table b (info text, date date, id serial);
NOTICE: CREATE TABLE will create implicit sequence "b_id_seq" for SERIAL
column "b.id"
CREATE TABLE
=> insert into b (select * from a);
INSERT 1127468 1
=> select * from b;
info | date | id
------+------------+----
abc | 2003-04-03 | 1
=>drop table a;
DROP TABLE
=> alter table b rename to a;
ALTER TABLE
=>commit;
---------------------------(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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: t_pet422 |
last post by:
Hi,
I've got a table in PostgreSql 7.3.2 that has a serial column as a
primary key. I want to insert data into the table (inserting values
for everything EXCEPT the serial column) and at the same time get the
value that the serial column just got incremented to. I'm using PHP
for my API, and pg_query() doesn't return anything on an insert.
pg_insert() just returns a bool.
I've done similar things in MS SQL server with stored...
|
by: RC |
last post by:
I have an Access 2002 database with many tables and forms (but just to
keep things simple, let's say the DB has one Table "Table1" and one
Form "Form1").
I have managed to cobble together so much complex code to add records
and edit records I need to step back and ask you all what is the
proper way to do this. The database is DAO. The table has Columns
SerialNumber, CPU, HardDrive and Model. I am trying to use a Form to
fill out the...
|
by: Aaron Ackerman |
last post by:
I have a bound combobox the appears on a cell within the column of my bound
grid when the user clicks on a cell n(In my vb.net WinForm app).
I am trying to allow the adding of an item to that bound combo by allowing
the user to dynamically type in the new value directly in the combo box
whcih in turn updates the lookup table and then when they have finished
leaves that particular cell on the grid with the correct value.
Seems...
|
by: Prabu Subroto |
last post by:
Dear my friends...
I am using postgres 7.4 and SuSE 9.1.
I want to use auto_increment as on MySQL. I look up
the documentation on www.postgres.com and I found
"serial" .
But I don't know how to create auto_increment.
here is my try:
|
by: Robby Russell |
last post by:
I am trying to track down a method of determining what a sequence name
is for a SERIAL is in postgresql.
For example,
CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);
\d foo
Table "public.foo"
Column | Type | Modifiers
| |
by: tai.cabrera |
last post by:
What I would like to do is input a range of serial numbers in a form
and have that range populate in the table without me having to put them
in one at a time manually. The numbers do not exist yet, either. I'm
not sure how else to explain it.
The tables I use have the following Fields:
Table 1
(PK) Serial Number
Date Sold
Sold By
|
by: george.e.sullivan |
last post by:
Hello all,
I have three tables Employess, Equipment, and Software.
I have tried without success to figure out how to show a single
Equipment Serial Number show all the software installed on that
machine.
For example: if the Equipment Table had Serial Numbers: 123 and 456 in
it. And the Software Table had Widget Version 4, Fishing Version 2,
and Vacation Version 10 in it.. How would I show in a Form:
|
by: access345 |
last post by:
I am trying to streamline my forms by just having one form that does two functions. My form is called frmInformation my textbox is called txtSerialNumber. On the form I have that Data entry function set to No. The primary function of the form is to check for current information. Instead of having another form just for entering new serial numbers. I want this form to do that function as well. So I need some code to check to see if the serial...
|
by: TBass |
last post by:
After successfully creating a connection to an Access Database file on
my computer, I try to add a new row. Everything seems to go fine, but
when I open the file in Access, the row isn't there. I get no errors
or exceptions while running. In the code below, you can see I go back
and check if the data is in the table after I've added it, and it is.
Still, it's never stored permanently. Obviousy, I'm missing some
steps. Can anyone point me...
|
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: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |