Re: [SQL] function to find difference between in days between two dates
Hello PostgreSQL hasn't any official function for it. If you need it, you can write own function CREATE FUNCTION date_diff(date, date) returns integer as $$ select $1-$2; $$ language sql; Regards Pavel Stehule 2007/6/14, Ashish Karalkar <[EMAIL PROTECTED]>: Hello all, Is there any function to find differences in days between two dates? I am using select abs(current_date - '2007-06-15') to get the desired result. but I think there must be a function and I am missing it, if so, can anybody please point me to that. Thanks in advance With regards Ashish Karalkar ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] function to find difference between in days between two dates
am Thu, dem 14.06.2007, um 11:56:15 +0530 mailte Ashish Karalkar folgendes: > Hello all, > > Is there any function to find differences in days between two dates? Yes, age(). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] setof or array as input parameter to postgresql 8.2 functions
How can delete my suscription to this forums? -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Jyoti Seth Enviado el: jueves, 14 de junio de 2007 8:04 Para: 'Pavel Stehule' CC: [EMAIL PROTECTED]; [email protected] Asunto: Re: [SQL] setof or array as input parameter to postgresql 8.2 functions Thanks a lot. Regards, Jyoti -Original Message- From: Pavel Stehule [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 11:27 AM To: Jyoti Seth Cc: [EMAIL PROTECTED]; [email protected] Subject: Re: [SQL] setof or array as input parameter to postgresql 8.2 functions 2007/6/14, Jyoti Seth <[EMAIL PROTECTED]>: > Thanks for the solution. With this I am able to pass arrays and > multidimensional arrays in postgresql functions. > > One of my problem is still left I want to pass set of values with different > datatypes.For eg: > I want to pass following values to the function: > 1 ajay 1000.12 > 2 rita 2300.24 > 3 leena 1230.78 > 4 jaya 3432.45 > > As the values have different data types I have to create three > different arrays. Is there any way with which I can pass this as a > single setof values. > > You have to wait for 8.3 where arrays on composite types are supported. Currently in one our application we use array of arrays where different types are too, and we cast it to text. Regards Pavel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] function to find difference between in days between two dates
2007/6/14, A. Kretschmer <[EMAIL PROTECTED]>: am Thu, dem 14.06.2007, um 11:56:15 +0530 mailte Ashish Karalkar folgendes: > Hello all, > > Is there any function to find differences in days between two dates? Yes, age(). there is difference date - date --> integer age(date::timestamp, date::timestamp) --> interval regards Pavel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Organization of tables
Hi,
I have a question regarding the organization of a table I want to
create in my database: I have the following setup:
Table Fragments (simplified example):
Primary key = {mass}
Approach (1)
fragment | mass (of fragment) | peptide
---++--
A | x | Peptide1, Peptide2
Q | y | Peptide1
K | z | Peptide 2, Peptide3
The idea here is that a peptide can be composed of many fragments e.g.
Peptide 2 is made up of fragments A and K; Peptide1 is made up of A
and Q and so on.
My idea is to create an index on the mass column and be able to
retrieve all Peptides that contain a certain fragment mass e.g
SELECT peptide
FROM Fragments
WHERE mass = x;
Should give me: Peptide1, Peptide2
The alternative way I have thought of to organize this table is to
have something as follows:
Approach (2)
Primary Key = {fragment, mass, peptide}
fragment | mass (of fragment) | peptide
---++--
A | x | Peptide1
A | x | Peptide2
Q | y | Peptide1
K | z | Peptide 2
K | z | Peptide 3
If I consider 2500 unique fragments then, using approach (1), table
Fragments will hold 2,500 tuples. If I consider the same number of
fragments then table Fragments using approach 2 holds 15,000 tuples.
I have considered using approach (1) whereby I would have less tuples
to search but if I wanted to access the peptides they belong to I
would retrieve the list of corresponding peptides e.g the string
"Peptide1, Peptide2" and process it in my program. However this seems
like a hack around the way a database table should be organised. The
problem increases further when I have to scale up and consider more
unique fragments (>2500).
Any help on how best to structure such data would be mostly appreciated.
- Salman Tahir
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] [SQL] function to find difference between in days between two dates
2007/6/14, Martijn van Oosterhout <[EMAIL PROTECTED]>: On Thu, Jun 14, 2007 at 09:00:12AM +0200, Pavel Stehule wrote: > Hello > > PostgreSQL hasn't any official function for it. If you need it, you > can write own function Not true. Anything that is done by an operator as actually done by a function. So the function exists: # \df date_mi List of functions I know about it, but it's undocumented Pavel ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [GENERAL] [SQL] function to find difference between in days between two dates
Hrm, yet Bruce mentions it in one of his documents. http://momjian.us/main/writings/pgsql/data_processing.pdf It may be undocumented, but it get a lot of hits on google :) why google? look to pgsql/src/backend/utils/adt/date.c :-) Regards Pavel Stehule ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Random Unique Integer
I have a table T1. It contains a field called F1. Is there a way for me to set the table T1 up such that F1 can be populated with a random integer such that F1 is a unique integer? I would rather not create a stored procedure. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu
Re: [SQL] Random Unique Integer
On Thu, Jun 14, 2007 at 08:08:26AM -0500, Campbell, Lance wrote: > I have a table T1. It contains a field called F1. Is there a way for > me to set the table T1 up such that F1 can be populated with a random > integer such that F1 is a unique integer? What problem are you trying to solve? The solution might depend on what you're trying to do. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Organization of tables
On 6/14/07, Salman Tahir <[EMAIL PROTECTED]> wrote:
Any help on how best to structure such data would be mostly appreciated.
See: http://en.wikipedia.org/wiki/Database_normalization
*** Grossly oversimplified example follows ***
CREATE TABLE PEPTIDE(
NAME TEXT PRIMARY KEY
);
CREATE TABLE FRAGMENT(
NAME TEXT PRIMARY KEY
, MASS TEXT
);
CREATE TABLE PEPTIDE_FRAGMENT(
FRAGMENT TEXT NOT NULL REFERENCES FRAGMENT(NAME)
, PEPTIDE TEXT NOT NULL REFERENCES PEPTIDE(NAME)
);
INSERT INTO PEPTIDE
VALUES ('Peptide 1'),('Peptide 2'),('Peptide 3');
INSERT INTO FRAGMENT
VALUES ('A','x'),('Q','y'),('K','z');
INSERT INTO PEPTIDE_FRAGMENT
VALUES
('A','Peptide 1'),('A','Peptide 2'),('Q','Peptide 1')
,('K','Peptide 2'),('K','Peptide 3');
SELECT F.NAME AS FRAGMENT, F.MASS
, (SELECT ARRAY_TO_STRING(ARRAY(
SELECT PEPTIDE FROM PEPTIDE_FRAGMENT
WHERE FRAGMENT = F.NAME ORDER BY NAME
), ',')) AS PEPTIDE
FROM FRAGMENT F;
fragment | mass | peptide
--+--+-
A| x| Peptide 1,Peptide 2
Q| y| Peptide 1
K| z| Peptide 2,Peptide 3
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Random Unique Integer
I have a web application that is used to create web surveys and web forms. Users can create any number of surveys or forms at any time. The primary key on one of my tables defines the ID for any given form or survey. I do NOT want the number sequential. Since I use the number in the web URL as a parameter, I don't want people guessing what survey or form is next. By creating a very large random number the users cannot easily guess another form or survey's ID. This number is not designed for security but to keep people from being nosey and poking around easily into someone else's form or survey. Can a table have a primary key that is randomly generated? Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 8:26 AM To: Campbell, Lance Cc: [email protected] Subject: Re: [SQL] Random Unique Integer On Thu, Jun 14, 2007 at 08:08:26AM -0500, Campbell, Lance wrote: > I have a table T1. It contains a field called F1. Is there a way for > me to set the table T1 up such that F1 can be populated with a random > integer such that F1 is a unique integer? What problem are you trying to solve? The solution might depend on what you're trying to do. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Random Unique Integer
On 14.06.2007, at 22:40, Campbell, Lance wrote:
I have a web application that is used to create web surveys and web
forms. Users can create any number of surveys or forms at any time.
The primary key on one of my tables defines the ID for any given
form or
survey. I do NOT want the number sequential. Since I use the
number in
the web URL as a parameter, I don't want people guessing what
survey or
form is next. By creating a very large random number the users cannot
easily guess another form or survey's ID. This number is not designed
for security but to keep people from being nosey and poking around
easily into someone else's form or survey.
Can a table have a primary key that is randomly generated?
you could do it like this:
test=# create table testsurvey (id_survey serial primary key, survey
text);
NOTICE: CREATE TABLE will create implicit sequence
"testsurvey_id_survey_seq" for serial column "testsurvey.id_survey"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"testsurvey_pkey" for table "testsurvey"
CREATE TABLE
test=# insert into testsurvey (survey) values ('first');
INSERT 0 1
test=# insert into testsurvey (survey) values ('second');
INSERT 0 1
test=# insert into testsurvey (survey) values ('third');
INSERT 0 1
test=# select *, md5(id_survey) from testsurvey;
id_survey | survey | md5
---++--
1 | first | c4ca4238a0b923820dcc509a6f75849b
2 | second | c81e728d9d4c2f636f067f89cc14862c
3 | third | eccbc87e4b5ce2fe28308fd9f2a7baf3
(3 rows)
so you actually show out only the PK id-s md5 hash
test=# create index idx_survey_md5 on testsurvey (md5(id_survey));
CREATE INDEX
test=# select * from testsurvey where md5(id_survey) =
'c81e728d9d4c2f636f067f89cc14862c';
id_survey | survey
---+
2 | second
The hashing algorithm you chose depends on the amount of rows you
expect there.
And also how difficult cracking it needs to be.
In case you have many rows and you think collision checking is
necessary you could
store the calculated hash in a new column. for replication management
purposes
its better to keep the PK as serial / bigserial.
Example: 100K surveys,
hashtext(id) : 4B int
100 000 / 256 ** 4 = 42949 (1:42949 chance that the next number will
lead to a valid survey entry)
keep the hashed pk value in a separate row as you will deplete
1/42949-th of the hash space with 100K rows
peace,
Kristo
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
