Re: [SQL] Birthday search.
Here's an example of something that works with some of
the data that I have lying around. Replace exit_dt
with "birthday" (or whatever you have named the field
that holds the birthday information) and replace
ticket_no with name (or whatever) and you should be on
the right track.
SELECT exit_dt, ticket_no FROM tscales
WHERE (
date_part('month', exit_dt) > 4 AND
date_part('month', exit_dt) < 12)
OR (
date_part('month', exit_dt) = 4 AND
date_part('day', exit_dt) >= 15
)
OR (
date_part('month', exit_dt) = 12 AND
date_part('day', exit_dt) <= 20)
ORDER BY exit_dt;
I don't consider myself enough of a SQL guru to know
if this is the "correct" way to do this, but this
query selects some 21000 records out of a 12
record table on my desktop test machine (PII 400 96M
Ram standard IDE hard drive running Linux) without any
indexes on exit_dt in a couple of seconds.
For more information check out the user guide section
on date/time functions. It is quite interesting. The
extract function would have served as well, but it
requires more typing.
Jason
--- Antti Linno <[EMAIL PROTECTED]> wrote:
> I have a table with personal data, including date of
> birth in date format.
> What i need is a select that searches birthdays in
> certain range.
> E.g. I need to find all birthdays starting from
> 15.april until 20th
> december. As you all understand, year doesn't matter
> :P
> Any simple and elegant solutions to it?
>
> Greetings,
> Antti
>
>
>
> ---(end of
> broadcast)---
> TIP 3: 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
__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Sorting and then...
SELECT name FROM test ORDER BY id DESC LIMIT 10; Take care, Jason --- Wei Weng <[EMAIL PROTECTED]> wrote: > Suppose I have a table > > create table test > ( > id integer, > name text > ); > > And I want to get the names of the largest 10 "id"s. > How can I do that in > sql? > > Thanks! > > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 3: 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] system time
Try using: SELECT now() It should do what you want. If not there are a whole pile of date functions. Look them up in the Users Guide. Hope this is helpful. Jason --- Seema Noor <[EMAIL PROTECTED]> wrote: > is there any function from which i can get system > time? > > > Do You Yahoo!? > Get your free @yahoo.co.uk address at > http://mail.yahoo.co.uk > or your free @yahoo.ie address at > http://mail.yahoo.ie > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] batch file
I just put files commands that I want in some file like "foo.sql" and then do: psql mydatabase -f foo.sql Is this what you are looking for? Jason --- Milen <[EMAIL PROTECTED]> wrote: > Hi all!, > > I want to execute several SQL statements one after > another. > Does anyone know if it is possible to create > something like a "batch file" > which contains all SQL commands I want. > > Best Regards: > Milen > > ---(end of > broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: [NOVICE] Knowing new item in table...
--- macky <[EMAIL PROTECTED]> wrote:
> im still in the dark when it comes to postgres.
> is there a way i can
> know if there was a new entry on a table...
That's a pretty common problem.
> scenario...
>
> i have a script that runs every 5 minutes... that
> script checks if there's
> a new entry in that table... and if YES there's a
> new entry ill do some
> processing...
Probably the most straightforward thing to do is to
simply insert a timestamp in each row as it is added
to the database. That way you can do a simple select
to figure out which rows have been added:
SELECT * FROM my_table WHERE insert_time > now() - '5
minutes'::interval
or alternatively
SELECT * FROM my_table WHERE insert_time > '2001-08-03
11:30'
The best part is that creating a column that includes
a timestamp automagically is fairly easy to do.
Simply define your table like this:
CREATE TABLE foo (
insert_time timestamp DEFAULT CURRENT_TIMESTAMP,
nametext
);
Once your table is created you simply insert into
table foo ignoring the insert_time column like so:
INSERT INTO foo (name) VALUES ('Jason');
and your timestamp automagically gets inserted:
processdata=> SELECT * FROM foo;
insert_time| name
+---
2001-08-03 11:32:48-06 | Jason
(1 row)
Pretty neat, huh?
> is there an internal utility that i can use in
> postgres that can tell me
> that this rows a new commers... hehehe
Nope, you have to come up with the logic yourself.
However, PostgreSQL has all kinds of tools that are
really helpful.
> if someone has other ideas on how to deal with this
> speak out...
>
> thanks in advance..
>
> btw..
>my idea is that that table will have an addtional
> column as reference
> lets say column "READ" 1 for yes 0 for NO
>
That would work too, but it would be a lot harder.
For example, you would have to first select all the
rows where READ is 0, do your processing, and then
update all of those rows to 1. You would almost
certainly want to do all of this in a transaction so
that you could roll READ back to 0 if something went
wrong, and you would probably want to lock the table
to boot as you would have to worry about your
processing step taking more than 5 minutes. If it
did, the second transaction would see the last 10
minutes of of inserts as being unread even though the
first transaction was still working on them.
I hope this is helpful,
Jason
__
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] changes to table creation syntax in 7.1.2?
It looks like it works here :(. Do you have an error message? processdata=# select version(); version --- PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.95.4 (1 row) processdata=# CREATE TABLE category processdata-# ( processdata(# uid int4 PRIMARY KEY, processdata(# description text NOT NULL, processdata(# parent int4 NULL REFERENCES category(uid) processdata(# ) processdata-# processdata-# ; NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'category_pkey' for table 'category' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE --- Jayson Callaway <[EMAIL PROTECTED]> wrote: > In postgres 7.0.x I had some working code that > lookes something like: > > CREATE TABLE category > ( > uid int4 PRIMARY KEY, > description text NOT NULL, > parent int4 NULL REFERENCES category(uid) > ) > > After upgrading to postgres 7.1.2 however this > syntax is not accepted > anymore. I receive an error that says it can not > create the reference > because the table category does not exist. > > How do I setup this type of reference structure in > 7.1.2? Did the syntax > change? > > I am running under Linux. > -- > Jayson Callaway > > > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] system maintained keys
Check out the SERIAL type. It does precisely what you
want. An idea as to how this is used would be like
this:
CREATE TABLE foo (
prim_key SERIAL PRIMARY KEY,
bar text
);
I tend to create sequences by hand like this:
CREATE SEQUENCE my_sequence_seq;
And then I create my table with a definition like
this:
CREATE TABLE foo (
prim_key int DEFAULT nextval('my_sequence_seq')
PRIMARY KEY,
bar text,
);
But that's just because I have been using PostgreSQL
long enough that it didn't have the SERIAL type when I
started. The SERIAL type is just syntactic sugar for
what I generally do the long way.
Either way you simply pretend that the column isn't
there when you do inserts (unless you know what you
are doing) like so:
INSERT INTO foo (bar) VALUES ('hello');
INSERT INTO foo (bar) VALUES ('goodbye');
And then when you select you get:
processdata=> SELECT * FROM foo;
prim_key | bar
--+-
1 | hello
2 | goodbye
(2 rows)
I hope that is helpful,
Jason Earl
--- Stefan Lindner <[EMAIL PROTECTED]> wrote:
> Is there any way to get system maintained keys from
> postgres? e.g. to
> have a table with a primary key column (varchar or
> int) and let postgres
> chose the next unique value for this column?
>
>
>
> ---(end of
> broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
__
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] ORDER BY case insensitive?
SELECT * FROM MyTable ORDER BY lower(Name); Should do the trick. Jason Earl --- Bob Swerdlow <[EMAIL PROTECTED]> wrote: > How do I get the rows sorted in a case insensitive > way? > > I have some queries that basically fit the form: > SELECT * FROM MyTable ORDER BY Name; > When I view the results, all of the Name's that > start with an upper case > letter precede all of the Name's that start with a > lower case letter. I > want them all in alphabetic order, regardless of > case. > > I've looked in PostgreSQL Introduction and Concepts > by Bruce Momjian and in > the FAQ at > http://postgresql.overtone.org/docs/faq-english.html > > Thanks for your help. > > -- > Bob Swerdlow > Chief Operating Officer > Transpose, LLC > [EMAIL PROTECTED] > > > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org __ Do You Yahoo!? NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] ORDER BY case insensitive?
My guess is that compared to the task of sorting
millions of names the fact that you have to lowercase
them first is not going to be a particular burden. No
matter what you do you are going to get a table scan
(unless you qualify your select with a where clause).
You can, however, create an index like:
create index MyTable_lower_idx on MyTable
(lower(name));
It won't help with your particular query, but it
certainly would help for queries like:
SELECT * FROM MyTable WHERE lower(name) = 'jason';
It is also possible to create a trigger that would
automatically lowercase information when it is
inserted or updated. Or even less high tech you can
make sure that all of your INSERTS and UPDATES use the
lower command like this:
INSERT into MyTable (name) VALUES (lower('VALUE'));
Jason
--- Bob Swerdlow <[EMAIL PROTECTED]> wrote:
> Thanks for the suggestion, Jason.
>
> A co-worker of mine, however, had this response:
> Yes, but my guess is that that will first
> convert all million (or
> whatever) records to upper case, and then physically
> sort them. It won't be
> able to make use of the index.
>
> To make this efficient, do we need to uppercase all
> of the data before
> adding to the table? (yuk)
>
> - Bob
>
>
> - Original Message -
> From: "Jason Earl" <[EMAIL PROTECTED]>
> To: "Bob Swerdlow" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Thursday, October 04, 2001 3:47 PM
> Subject: Re: [SQL] ORDER BY case insensitive?
>
>
> > SELECT * FROM MyTable ORDER BY lower(Name);
> >
> > Should do the trick.
> >
> > Jason Earl
> >
> > --- Bob Swerdlow <[EMAIL PROTECTED]> wrote:
> > > How do I get the rows sorted in a case
> insensitive
> > > way?
> > >
> > > I have some queries that basically fit the form:
> > > SELECT * FROM MyTable ORDER BY Name;
> > > When I view the results, all of the Name's that
> > > start with an upper case
> > > letter precede all of the Name's that start with
> a
> > > lower case letter. I
> > > want them all in alphabetic order, regardless of
> > > case.
> > >
> > > I've looked in PostgreSQL Introduction and
> Concepts
> > > by Bruce Momjian and in
> > > the FAQ at
> > >
> http://postgresql.overtone.org/docs/faq-english.html
> > >
> > > Thanks for your help.
> > >
> > > --
> > > Bob Swerdlow
> > > Chief Operating Officer
> > > Transpose, LLC
> > > [EMAIL PROTECTED]
> > >
> > >
> > >
> > > ---(end of
> > > broadcast)---
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> >
> >
> > __
> > Do You Yahoo!?
> > NEW from Yahoo! GeoCities - quick and easy web
> site hosting, just
> $8.95/month.
> > http://geocities.yahoo.com/ps/info1
> >
>
__
Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Triggers do not fire
I can vouch for that. I have several tables with 10 to 16 million entries in much the same sort of setup as you are describing (primary key, timestamp, value). PostgreSQL is will quite happily use the timestamp indexes when accessing this table, and it doesn't default to a sequential scan until a considerable number of tuples would be searched. For example: processdata=> explain select count(*) from caseweights1 where dt > '2001-10-11'; NOTICE: QUERY PLAN: Aggregate (cost=255053.37..255053.37 rows=1 width=0) -> Index Scan using caseweights1_dt_idx on caseweights1 (cost=0.00..254827.01 rows=90544 width=0) EXPLAIN processdata=> select count(*) from caseweights1 where dt > '2001-10-11'; count 146773 (1 row) processdata=> select count(*) from caseweights1; count -- 14984087 (1 row) As you can see, even though my table is fairly large PostgreSQL will happily use indexes for queries even when there is a significant number of tuples that are to be accessed. The count command with the index took perhaps a second on my 400MHz 128M ram normal IDE hard drive test server. The count of all the records, on the other hand, triggered a sequential scan that took a long time to complete. In other words, chances are good that PostgreSQL will handle your data without special modification. Jason --- Tom Lane <[EMAIL PROTECTED]> wrote: > Reiner Dassing <[EMAIL PROTECTED]> writes: > > I have a table which has a lot of entries (some x > millions) of the kind > > (id, timestamp, value) > > The access (selects) is concentrated to the timely > last some thousands > > entries. > > To adapt this fact I want to setup a "virtual" > table - test in my > > example - which > > is accessed by the clients but in reality the > entries are separated to > > different small > > tables. These table are dynamically created to > hold the values > > distinguished by years. > > Why bother? Seems like you are just making life > complicated for > yourself. One big table with a suitable index ought > to work fine. > > regards, tom lane > > ---(end of > broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] serial column vs. explicit sequence question
Charlie Toohey <[EMAIL PROTECTED]> writes:
> I'm having a problem and there seems to be 2 solutions. It is simple
> and straighforward, but will take several paragraphs to explain.
>
> I have a schema with a master-detail design. The master table does
> not have an expicit id, so I have a column of type serial.
>
> Lets say I need to insert a row into the master table and N rows
> into the detail table. After inserting a row into master, and before
> detail, I need to read the master table to obtain the value of the
> id for the row just inserted, so I can insert this id as the foreign
> key value for the N rows in the detail table.
>
> This seems like a poor solution because I have to write and then
> read the master table each time. With lot of activity on these
> tables, I don't know how well this will scale. Additionally, the
> only way that I can guarantee that I am getting the id of the most
> recent row inserted into master is to SET TRANSACTION ISOLATION
> LEVEL SERIALIZABLE --- because otherwise, if other processes are
> inserting rows into master/detail concurrently, I may pick up the id
> from an incorrect row (the technique used to get the correct id is
> to include a timestamp column on the insert into master and then
> query for the latest row).
>
> A better solution would seem to use a sequence explicitly, rather
> than a id column of type serial. I would obtain the id value from
> the sequence, and then insert this id into the master table and into
> the detail table. This way, I wouldn't be writing/reading the same
> table constantly -- I would only be writing to it, and, I would
> guarantee that I would be using the correct id in both master and
> detail without have to SET TRANSACTION ISOLATION LEVEL
> SERIALIZEABLE.
>
> Any comments on which solution you would choose, or is there a
> better solution ?
>
> Thanks,
> Charlie
The SERIAL type is a thin veneer over an underlying conglomeration of
a unique index and a sequence, nothing more, nothing less. I still
prefer to use the old syntax that spelled this out explicitly (mostly
because it reminded me that I needed to drop the sequences as well as
the table if I made changes during the development phases of my
project). Instead of using a serial type I have a whole pile of
scripts that contain bits that look like this:
DROP TABLE prod_journal;
DROP SEQUENCE prod_journal_id_seq;
CREATE SEQUENCE prod_journal_id_seq;
CREATE TABLE prod_journal (
id int PRIMARY KEY
DEFAULT nextval('prod_journal_id_seq'),
...
);
The SERIAL type does precisely the same sort of thing. The only
difference is that PostgreSQL thinks up the sequence name for you
(currently PostgreSQL tries to choose a name that looks precisely like
the one I have chosen in this example). The reason that I bring this
up is A) it makes me happy to think that I have been using PostgreSQL
long enough that my PostgreSQL memories predate the SERIAL type, and
B) to point out that there is not really a difference between using
the SERIAL type and using sequences explicitly.
What you *really* need is to get acquainted with the nifty sequence
functions currval and nextval. They hold the secret to sequence
Nirvana. See Chapter 4 Section 11 of the PostgreSQL User's Guide for
the full scoop. The short story is that curval gives the current
value of the sequence (for whichever backend you are connected to) and
nextval will give you the next value of the sequence.
Now let's say that you had two simple tables foo for the master record
and bar for the detail records.
test=# create table foo (id serial primary key, name text);
NOTICE: CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL column
'foo.id'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table
'foo'
CREATE
test=# create table bar (master int references foo, detail text);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
You could then insert into these tables using something like this:
test=# begin;
BEGIN
test=# insert into foo (name) values ('Jason');
INSERT 67423220 1
test=# insert into bar (master, detail) values (currval('foo_id_seq'), 'Does this
work');
INSERT 67423221 1
test=# insert into bar (master, detail) values (currval('foo_id_seq'), 'Apparently
So!');
INSERT 67423222 1
test=# commit;
COMMIT
As long as you hang onto your connection to the back end you don't
even have to wrap this as one transaction. Currval is connection
dependent, and so as long as you have the same connection currval will
give the correct answer, and currval is very very fast.
Hope this was helpful,
Jason
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Home-brewed table syncronization
Michael A Nachbaur <[EMAIL PROTECTED]> writes: > On Wednesday 09 July 2003 02:28 pm, Cliff Wells wrote: >> On Wed, 2003-07-09 at 14:14, Michael A Nachbaur wrote: >> > So, I'm looking at syncronizing 4 tables from one master database to >> > several child databases. I'm thinking of doing the following with >> > DBD::Multiplex: >> > >> > DELETE FROM TableA; >> > INSERT INTO TableA (..) VALUES (...); >> > >> > >> > on all the child databases, but I'm not sure what kind of impact >> > this would have on my servers. My impression is that this would >> > hammer the indexes, and might blow any memory optimization out >> > the window. Only a few records in my dataset will change from >> > time-to-time, but just the process of determining what is >> > different may take more effort than simply rebuilding. >> >> Keep a timestamp associated with each record. Only update the records >> with timestamps later than your last sync. > > I'm dealing with an existing database structure that, though I can > change it, has a lot of impact on the rest of my infrastructure. If > I can find a way of doing this without resorting to timestamps, I'd > much rather do it that way. Would it be possible to add another table (changelog) that contained the primary key of the record that has changed and a timestamp? The changelog table could be maintained via triggers (on update, insert, or delete). Your synchronization software could then be relatively simple. It would simply need to check your changelog table for rows that have changed. Jason ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
