Thread: Question about serial vs. int datatypes

Not sure if the lists are currently working - I just joined both this one
and pgsql-novice, and posted a message to that one earlier in the day, but I
haven't received either any replies or even my own message back, so I don't
know if it's working. I know there was some kind of hardware failure with
regard to the lists, but I don't know how severe.

Anyway: I'm converting a FileMaker Pro database into PostgreSQL, and have a
question (actually, tons of questions, but I'll stick to one for now). Each
of the three major database files in FMP (equivalent to tables in
PostgreSQL) has a number field as a primary key which is incremented
serially as new records are added. Nice and simple, and the same thing a lot
of PostgreSQL databases use.

But... when converting an existing database that already has several hundred
records in it, I can't make that field serial in PostgreSQL, can I? Because
I don't want the existing records renumbered - that would break the links
between the different tables. But if I make the id number just a smallint
field, then I'm stuck incrementing it manually after the conversion. Is
there any way around this? Any way to import the existing records with their
id number intact, and then have it switch to serial after that for new
records?

Any advice would be appreciated...


Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org

On 05/06/2003 21:05 Lynna Landstreet wrote:

> Anyway: I'm converting a FileMaker Pro database into PostgreSQL, and have
> a
> question (actually, tons of questions, but I'll stick to one for now).
> Each
> of the three major database files in FMP (equivalent to tables in
> PostgreSQL) has a number field as a primary key which is incremented
> serially as new records are added. Nice and simple, and the same thing a
> lot
> of PostgreSQL databases use.
>
> But... when converting an existing database that already has several
> hundred
> records in it, I can't make that field serial in PostgreSQL, can I?
> Because
> I don't want the existing records renumbered - that would break the links
> between the different tables. But if I make the id number just a smallint
> field, then I'm stuck incrementing it manually after the conversion. Is
> there any way around this? Any way to import the existing records with
> their
> id number intact, and then have it switch to serial after that for new
> records?
>
> Any advice would be appreciated...


Looking at the docs and after a bit of playing around with 7.3.3, I think
this may work:

1) I believe PG _will_ accept the serial numbers you insert so importing
the data with your serial number fields should work.

2) For each serial type you will find that PG has a created a Sequence
object with a name of the form tablename_columnname_seq. After the import,
you will need to set this to the max value of your serial column with
SELECT pg_catalog.setval("sequencename", value, true);
HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Lynna Landstreet wrote:
 >
 > the current structure where the keywords in any given set (i.e. subject,
 > medium, etc.) are all lumped together into one long string for each
record
 > would be considered bad database design

Again, I'm no expert either but I would definitely say that is bad
design. Strangely enough my current job is also converting a FMP DB to
postgres and I had the same issue. Many data clumped into one field.

 > But if I make each keyword into a boolean field, I'm not sure how
 > to display them.

That's not a DB issue. In your case that's a web browser issue and
related to whatever web programming language you will use.

 > Pg usually return the data in the columns, not
 > the column names.

The data returned is associated with a column name so you always know
where your data came from ;)

 > When people ultimately view the database records on the
 > web, I want them to be able to see something like:
 >
 > Medium: black and white photograph
 > Subject: landscape, nature
 > Processes: hand-tinting, photocollage
 >
 > Or something to that effect, but NOT:
 >
 > black and white photograph: yes
 > landscape: yes
 > nature: yes
 > hand-tinting: yes
 > photocollage: yes

The only difference between the two versions you show here is that in
the second one you don't show the column names, but of course you (the
programmer) know what they are since you did the SELECT that fetched the
data ...

 > So I'm thinking that probably each set of keywords (medium, subject,
etc.)
 > probably needs to be a table {...]

Hum, database design. A thorny issue for the newbie. The way you decide
to go will affect many things so plan carefully.

I don't know enough about your data or database design to suggest
anything but from what I can gather of your data you would have an
exhibition/art piece (?) table in it.

Then you would have a table for, say b/w photography and in that table
you would have one entry for each art piece that fell into that
category. And so on for all the other kinds of possible ways to
categorize the art piece.

So the art piece table would no contain any information at all on what
kind of art it is. To find that out you would need to search all the
possible category tables to see if there was a matching entry for that
art piece.

I think someone may suggest that a view would make you life easier if
you did decide to go that route ...

 > but I'm not sure if there's a way to modify a select statement so that it
 > outputs the names of all columns with a yes/true/1 in them, rather than
 > outputting the values themselves.

If you go with my design you don't knew to output anything. If there is
an entry for that art piece in a particular category table then it's of
that category.

 > BTW, I'll be using PHP to create the front end
 > of this for the web site, if that makes a difference.

I'm using PHP too. So far so good ... I did have to dump out all the FMP
data into one big text file and create a custom PHP script to parse the
data and insert it into PG tables and make the necessary relationships
though.

In my case I did have the same serial issue you mentioned in your last
question but since the primary keys in FMP weren't used for anything
else but keys into tables I didn't need to actually keep the same key
id. I just found all the data in the text file that matched on that key,
parsed it, inserted it into PG, let PG assign a new serial and used the
assigned serial to create the relationships in all the other tables.

HTH,

--

Jean-Christian Imbeault

Lynna Landstreet wrote:
> But... when converting an existing database that already has several hundred
> records in it, I can't make that field serial in PostgreSQL, can I? Because
> I don't want the existing records renumbered - that would break the links
> between the different tables. But if I make the id number just a smallint
> field, then I'm stuck incrementing it manually after the conversion. Is
> there any way around this? Any way to import the existing records with their
> id number intact, and then have it switch to serial after that for new
> records?

You can create the field as serial. A serial data type makes the column
*default* to the next value of a sequence, but if you provide a value
(as you would during the import), the default is not used. But you will
need to set the sequence after the import so that it starts with a
number higher than any you imported. See:

http://www.us.postgresql.org/postgresql-7.3.3/functions-sequence.html

For example:

regression=# create table s1(id serial primary key, f2 text);
NOTICE:  CREATE TABLE will create implicit sequence 's1_id_seq' for
SERIAL column 's1.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 's1_pkey'
for table 's1'
CREATE TABLE
regression=# \d s1
                            Table "public.s1"
  Column |  Type   |                     Modifiers
--------+---------+----------------------------------------------------
  id     | integer | not null default nextval('public.s1_id_seq'::text)
  f2     | text    |
Indexes:
     "s1_pkey" PRIMARY KEY btree (id)

INSERT INTO s1(id,f2) VALUES (1,'hello');
INSERT INTO s1(id,f2) VALUES (2,'world');
SELECT setval('s1_id_seq',42);
INSERT INTO s1(f2) VALUES ('abc');
regression=# SELECT * FROM s1;
  id |  f2
----+-------
   1 | hello
   2 | world
  43 | abc
(3 rows)

HTH,

Joe

> But... when converting an existing database that already has several hundred
> records in it, I can't make that field serial in PostgreSQL, can I?

I guess you haven't actually tried this yet, but if you do an insert
with an explicit value for a column of type serial, it inserts that value.
If you leave that column off the list of columns in the insert statement,
it uses the nextval of the implicit sequence, which is the default value
of the column.  If you use NULL, you will get an error.  You can also
explicitly select the sequence value.

Here's a sample table, test1.  Note the two modifiers for 'keyval'.

 Column  |         Type     |                         Modifiers

---------+------------------+----------------------------------------------
 keyval  | integer          | not null
                              default nextval('public.test1_keyval_seq'::text)
 dataval | character varying(30)

INSERT into test1 values ('15','TEST');
INSERT into test1 (dataval) values ('FISH');
INSERT into test1 values (null,'MOUSE');
INSERT into test1 values (nextval('test1_keyval_seq'),'CAT');

select * from test1;
 keyval | dataval
--------+---------
     15 | TEST
      1 | FISH
      2 | CAT
(3 rows)
--
Mike Nolan

On Thu, Jun 05, 2003 at 04:05:53PM -0400, Lynna Landstreet wrote:
> But... when converting an existing database that already has several hundred
> records in it, I can't make that field serial in PostgreSQL, can I? Because
> I don't want the existing records renumbered - that would break the links
> between the different tables. But if I make the id number just a smallint
> field, then I'm stuck incrementing it manually after the conversion. Is
> there any way around this? Any way to import the existing records with their
> id number intact, and then have it switch to serial after that for new
> records?

Make a new table with a serial type field and then do a \d on that table. You will see that the default value for the
fieldis the next value from the relevant (autocreated) sequence. If you specify a value for the "SERIAL" type field,
thefield will be filled with that rather than a value from the sequence.  

Note that if you do this you need to reset the value of the current sequence. Use something like setval(sequencename)
tomax(your_serial_field). 

Rory

Thanks to everyone who replied to this question, as well as my other one
about special characters. I'm now busily reading up on sequences and
localization. :-)


Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org