Re: [SQL] How to trim values?
On Thu, 28 Dec 2000, Oliver Elphick wrote: > [EMAIL PROTECTED] wrote: > >Hi, > > > >I'm trying to figure out how to take a value like 3.68009074974387 > >(that is calculated from values in my database) and have PostgreSQL > >hand me 3.68. Any suggestions would be appreciated. > > cast it to numeric(x,2) > > (where x is the total number of digits, and 2 is two decimal places). > > template1=# select 3.68009074974387::numeric(3,2); > ?column? > -- > 3.68 > (1 row) > > or use round(value,2) > > > template1=# select round(3.68009074974387, 2); > round > --- > 3.68 > (1 row) or test=# select to_char(3.68009074974387, '99.99'); to_char - 3.68 (1 row) Karel
Re: [SQL] Date/Time problem -(((
> Is there any Date/Time function in PGSQL? I want as shown in this > example delete entries older than 300 secounds. > > I postet to the list because I have found no solution in the > tutorials and search-engine (maybe searched with wrong words?) http://www.postgresql.org/users-lounge/docs/7.0/postgres/functions2876.htm -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 14.00-18.00Email: [EMAIL PROTECTED] 2000 FrederiksbergLørdag 11.00-17.00 Web: www.suse.dk
[SQL] References to SERIAL
Hi, If i create a "internal pk" buy defining on a table a field SERIAL. How do I reference this field in the other table to set the field possible value ? create table book ( /* This is an internal primary key for the book description */ book_pk serial, // End of Book def ); create table books_authors ( ??? // I want to reference the book pk & the author pk to be able to make the X-ref ? ); tx, THomas -- Sat Dec 30 15:23:42 CET 2000 Thomas SMETSe-mail : [EMAIL PROTECTED] Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94. 1030 Bruxelles === Quote of the Day = "In the long run, every program becomes rococo, and then rubble." -- Alan Perlis = End of Quote ===
[SQL] Changing owner of tables, functions, etc.
Saluton, is there a way to "donate" databases, tables, functions etc. to some other postgres user? The reason for this is that it would add some safety with respect to preventing one from accidentally DROPping some important data, as one would have to su to the other user first. Thanks in advance, Albert. -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
[SQL] PL/pgSQL: returning array?
Saluton,
I am trying to write my first plpgsql-functions; I do not seem to
be able to find a way of returning an array; here is what I tried:
,
| CREATE FUNCTION "concatallinstances2" (int4 ) RETURNS text[2] AS '
| declare
| reslt text;
| separator text;
| instance record;
| first text;
| begin
| reslt := ;
| separator := ;
| for instance in select cat from t where id = $1 order by cat LOOP
| if first is null then
| first := instance.cat;
| end if;
| reslt := reslt || separator || instance.cat;
| separator := '', '';
| end loop;
| return {first,reslt};
| end;
| ' LANGUAGE 'plpgsql';
`
I also tried `... RETURNS text[] ...' and `... RETURNS text_ ...' (I
think I have read somewhere that the array type is given by basetype
plus underscore) - none of these seem to work.
I am using:
,
| cgitest=> select version();
| version
|
| PostgreSQL 7.0.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
| (1 row)
`
I hope you can help me; thanks in advance -
Albert.
--
--
Albert Reiner <[EMAIL PROTECTED]>
Deutsch * English * Esperanto * Latine
--
[SQL] configure: what was used?
Saluton, I have to work with a PostgreSQL-installation on a site where I am not the root (but I am supposed to act as the Postgres-superuser). The system administrator installed a bunch of 7.0.2-RPMs from the SuSE-site. How can I find out how that was configured? I am interested mainly in locale and multibyte settings. Thanks in advance, Albert (who always compiles from source and does not like nor trust YaST at all). -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
Re: [SQL] References to SERIAL
On Sat, 30 Dec 2000, Thomas SMETS wrote: > If i create a "internal pk" buy defining on a table a field SERIAL. > How do I reference this field in the other table to set the field > possible value ? > > > create table book ( > /* This is an internal primary key for the book description */ > book_pk serial, > // End of Book def > ); > > create table books_authors ( > ??? // I want to reference the book pk & the author pk to be able to > make the X-ref ? > ); You mean as a foreign key? You would do something like create table books_authors ( book integer references book(book_pk) on delete no action, author integer references author(author_pk) on delete no action, ... ); This forces integrity between the tables so the only allowable values in the books_authors table are those values in the referenced fields (foreign keys). You will probably want to look up the documentation on contraints and foreign keys (I believe they are under the CREATE TABLE documentation). -- Brett http://www.chapelperilous.net/~bmccoy/ --- Did you hear that there's a group of South American Indians that worship the number zero? Is nothing sacred?
Re: [SQL] References to SERIAL
"Brett W. McCoy" wrote:
>On Sat, 30 Dec 2000, Thomas SMETS wrote:
>
>> If i create a "internal pk" buy defining on a table a field SERIAL.
>> How do I reference this field in the other table to set the field
>> possible value ?
...
>
>You mean as a foreign key? You would do something like
>
>create table books_authors (
> book integer references book(book_pk)
> on delete no action,
...
If you need to know which value was used for the SERIAL field, there are
two ways:
1. Use currval('book_book_pk_seq') to get the last value used in this
session.
bray=# insert into junk (name) values ('Fred');
INSERT 1780993 1
bray=# select currval('junk_id_seq');
currval
-
1
(1 row)
2. Use the OID which is returned by a successful INSERT statement to look
up the newly-created row from the table:
bray=# insert into junk (name) values ('Fred');
INSERT 1780993 1
bray=# select * from junk where oid = 1780993 ;
id | name
+--
1 | Fred
(1 row)
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Give to him that asketh thee, and from him that would
borrow of thee turn not away."
Matthew 5:42
Re: [SQL] References to SERIAL
Yeap, Tx Brett. The syntaxe you gave means much more to me than the one I got from the Book "PostresSQL : Introduction & Concept" from Bruce Momjian seems a bit short while the PostgreSQL user guide from Thomas LOCKHART has the full theorical description. I'll probably stick more with the second one in the future. Thomas, > > You mean as a foreign key? You would do something like > > create table books_authors ( > book integer references book(book_pk) > on delete no action, > author integer references author(author_pk) > on delete no action, > ... > ); > > This forces integrity between the tables so the only allowable values in > the books_authors table are those values in the referenced fields (foreign > keys). > > You will probably want to look up the documentation on contraints and > foreign keys (I believe they are under the CREATE TABLE documentation). > -- Sat Dec 30 15:23:42 CET 2000 Thomas SMETSe-mail : [EMAIL PROTECTED] Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94. 1030 Bruxelles === Quote of the Day = "In the long run, every program becomes rococo, and then rubble." -- Alan Perlis = End of Quote ===
Re: [SQL] Looking for comments
1. General comment: you use lettercase to divide words in table and fieldnames; this will be lost as soon as you create the tables, because PostgreSQL folds everything to lower case. Use _ to separate words instead. OK it should be done everywhere where I did some changes, at least. ISBN's have a checkdigit; it would be sensible to provide a function to be used in a CHECK constraint to ensure that the ISBN is valid. OK I'll start searching for it but haven't implemented yet What if you have more than 1 copy of the same title? You should have another table for physical copies, with a foreign key reference to book. I think I solved it in a particular way. I believe it keep things more understandable for me this way. Of course, some titles have multiple ISBNs, depending on the type of binding (e.g. Good News Bible in several different formats). Perhaps you need yet another table to link ISBNs to titles. Each issue of many serials has a volume and issue number; you really don't want a separate definition in book for each issue, do you? Author: many books have multiple authors; also editors. You probably need fields for place and year of publication Solved ? Type: this seems to refer to attributes of serial publications; these have ISSN numbers (rather than ISBN) and the ramifications of checking serial issues are far more complex than you have allowed for. I took a very simple approache for now. The idea is like access bits on Unix sytems. If member ids cannot be negative, you need a CHECK constraint to check the id range. The sequence will not override a direct setting. Yeap & nop Library member Id's are to me any thing that suites the Librarian You define person_ref twice; presumably the first occurrence should be `id'. Yeap You say that one member can reference multiple persons, but you cannot achieve that by referencing a single person in this table. A single field can hold only a single reference. You need a member_person table: CREATE TABLE member_person ( member INTEGER CONSTRAINT member_fkey REFERENCES member (id) ON UPDATE CASCADE ON DELETE NO ACTION, person INTEGER UNIQUE CONSTRAINT person_fkey REFERENCES person (id) ON UPDATE CASCADE ON DELETE NO ACTION, PRIMARY KEY (member, person) ); which will hold all persons related to the member. If you have a person who is primarily responsible, his id goes in the person_ref field. Why make LastLending NOT NULL? If you have a new member there is no last lending and the field would naturally be null. The CHECK constraint on CreatedOn is invalid; a date field cannot ever have a value of '' (it is not held as a string). The NOT NULL constraint is all you need; though you could add a date range check (CreatedOn > '1 Jan 2001' and CreatedOn <= CURRENT_DATE) What do you tink of my usage of an intermediate table ? 4.CountryCodes Why not add a name field and preload this table with the ISO country definitions. (Some of the country codes are not at all obvious, so you need the names.) I expect the Post Office would prefer to have names, too. The PRIMARY KEY constraint makes UNIQUE NOT NULL unnecessary. There is no sense in having a DEFAULT on a primary key field. The default belongs in the address table Simply because of multilinguisme. The countries' name are not anymore a person's name, They have to be translated. (it could also be the case for towns but only stupid Belgian keep double naming for their towns... who cares, thus @ the moment 'bout that ). I also look for a mean to use ResourceBundles so basic users can introduce new countries via the modifications of simple text files. 5.ZipCodes I don't understand the purpose of this table. Presumably you need a PRIMARY KEY (country_code, zip_codes) constraint Well because these are also redunddant & you could imagine that a Library has all the post-code for its resident in a table to avoid mistakes. Site should be update soon. At th emoment the site is still in Beta ... mail is thus safer . Thomas,
