[SQL] reinitialise serial counter

[Indraneel Majumdar]

Hi,

I have a table

create table test (id serial, name text);

after inserting values and doing delete from test if I try to insert
values again, the id starts from where it left off previously. How can I
get the serial counter to restart from 1 ? is it anything to do with
setval(). How to use the setval?

thanks,
Indraneel

/.
# Indraneel Majumdar  ¡  E-mail: [EMAIL PROTECTED]  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076  #
`/

RE: [SQL] reinitialise serial counter

[Edmar Wiggers]

> after inserting values and doing delete from test if I try to insert
> values again, the id starts from where it left off previously. How can I
> get the serial counter to restart from 1 ? is it anything to do with
> setval(). How to use the setval?

The serial value is implemented using a sequence object (see the docs). Once
a sequence returns a number it will never return it again (that's what
sequences are for!).

A serial field is not a good approach if you always want to start numbering
at 1.

Yours sincerely,

Edmar Wiggers
BRASMAP Information Systems
+55 48 9960 2752

[SQL] Trouble Converting Dates

[Kate Collins]

Hi

I am running postgres 7.02 on Linux and I am having trouble converting a

character string to a date using the "to_date" function.

For example:

select to_char( to_date( '001112', 'YYMMDD'), 'MMDD');
 to_char
--
 1112

In other words it is defaulting to the year 0 (actually year 1 BC, since

there is no year 0) instead of 2000.

Now I run the equivalent select statement on Oracle, and I get:

select to_char( to_date( '001112', 'YYMMDD'), 'MMDD') from dual;

TO_CHAR(TO_DATE('001112','YYMMDD'),'MMDD')
---

20001112

Which is what I expect.

Is "YY" suppose to default to the current century or is this an
Oracle'ism?

I looked in my Postgres documents for a table describing the Date
conversion strings, and could not find one.  Is there a reference some
one can direct me to?

BTW, on postgres, when I try:

 select to_char( to_date( '20001112', 'MMDD'), 'MMDD');

I get the error:  "ERROR:  Unable to convert timestamp to date"

Thanks,
Kate


--
=
Katherine (Kate) L. Collins
Senior Software Engineer/Meteorologist
Weather Services International (WSI Corporation)
4 Federal Street
Billerica, MA 01821
EMAIL:  [EMAIL PROTECTED]
PHONE:  (978) 670-5110
FAX:(978) 670-5100
http://www.intellicast.com

[SQL] Cascading a pseudo-delete?

[Brian Powell]

Greetings, for our database, we keep everything--deleting nothing; however,
to the client side, it should appear deleted.  We do this via a status_cd
for each record.  If the status_cd is for 'deleted' then that item is not
shown to the client-side.

Anyhow, my question is that we also use foreign keys, and I would like to
delete the one record and have the deletion cascade to all records using
that foreign key.  This is no problem, until I try to no longer delete, but,
change the flag.  So, my question is, how can a create a rule which
activates on delete and simply marks the record deleted then cascade that
deletion to all of the objects who are constrained to its key?

Thanks,

--Brian

[SQL] Display of RI triggers

[Larry Rosenman]

Is there a NICE way to display Referential integrity triggers? 

I know about select * from pg_trigger;, but that output is, err,
ugly...

(7.0.2, but I suspect it's the same in 7.1devel).

-- 
Larry Rosenman  http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

[SQL] how to index a numeric(12,2) column?

[Forest Wilkinson]

I'd like to create an index on a column whose type is NUMERIC(12,2).
There appears to be no default operator class for the numeric type.  What
class should I use instead?  My guess is that something like this might
work:

CREATE INDEX foo_idx on foo (bar int8_ops);

Will that work properly?  Will it do me any good?  Is there a better way?

Re: [SQL] how to index a numeric(12,2) column?

[Tom Lane]

Forest Wilkinson <[EMAIL PROTECTED]> writes:
> I'd like to create an index on a column whose type is NUMERIC(12,2).
> There appears to be no default operator class for the numeric type.

Uh, what version are you using?  Works fine for me in 7.0.2.

regards, tom lane