pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple Question??
15+ messages / 11 participants
[nested] [flat]
* Simple Question??
@ 1999-02-27 22:50 archiver <archiver@db.geocrawler.com>
0 siblings, 0 replies; 15+ messages in thread
From: archiver @ 1999-02-27 22:50 UTC (permalink / raw)
To: pgsql-sql@hub.org
Here's a question that maybe you can answer easily...
If I am using a sequence (seq_a) in table_a
...then "create table_b as select * from table_a"
...does table_b start using that sequence (seq_a), starting at the point
where table_a left off??
I have a 1GB table that I want to optimize by reordering it, and I will want
the new table to use the same sequence to generate the primary key. I will
then drop the original table and rename the table_b to table_a.
Any clues?
Tim Perdue
tim@perdue.net
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Simple Question??
@ 1999-02-28 18:20 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 1999-02-28 18:20 UTC (permalink / raw)
To: pgsql-sql@hub.org
"archiver" <archiver@db.geocrawler.com> writes:
> If I am using a sequence (seq_a) in table_a
> ...then "create table_b as select * from table_a"
> ...does table_b start using that sequence (seq_a), starting at the point
> where table_a left off??
I assume you are wondering about what happens to a column declared as
SERIAL in table_a?
I have not experimented (and really, the most reliable way to check this
sort of thing is to experiment on a small test case). But remember that
what SERIAL really means is INT4 DEFAULT NEXTVAL('some_sequence')
(plus the system goes off and creates the sequence for you). My guess
is that the result of the SELECT will simply show the column type as
INT4, which means that table_b will have a plain INT4 column. There's
no way that I know of for information about column constraints and
default values to propagate through the result of a SELECT.
So if you do it that way, I'd expect table_b to reproduce the current
values of the serial-number column from table_a, but it'd just be a
static snapshot; the column wouldn't act like a serial number anymore.
What you should do instead is to explicitly create table_b using the
properties you want, which in particular would mean saying INT4 DEFAULT
NEXTVAL('table_a's_sequence') rather than just SERIAL for this column.
That way table_b will continue to generate serial numbers from the
same sequence table_a was using. Then transfer the data with something
like INSERT INTO ... SELECT FROM. (Personally I'd probably use a COPY
out followed by COPY back in; I just like having backup copies of info
outside the database when I'm making major rearrangements...)
> I have a 1GB table that I want to optimize by reordering it, and I will want
> the new table to use the same sequence to generate the primary key. I will
> then drop the original table and rename the table_b to table_a.
Just out of curiosity, what makes you think that reordering the table
will "optimize" anything? I have a feeling that what you really need to
be worrying about is creating appropriate indexes, or some such.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: [SQL] Simple Question??
@ 1999-03-01 18:05 Michael Davis <michael.davis@prevuenet.com>
0 siblings, 0 replies; 15+ messages in thread
From: Michael Davis @ 1999-03-01 18:05 UTC (permalink / raw)
To: pgsql-sql@hub.org
I would say yes as long as table_b still references the same sequence as
table_a. Sequences are stored and maintained separately from the tables
they are used in. If you drop and recreate a sequence, the starting number
of that sequence will be 1 even though the database using the sequence has
500 records in it. You can reset the value of the sequence (seq_a) to the
highest value currently stored in the database for the column that seq_a is
being used with. When I ported my Access97 table to Postgres, I had to set
the value of each sequence I used to match the highest value stored in my
tables.
-----Original Message-----
From: archiver [SMTP:archiver@db.geocrawler.com]
Sent: Saturday, February 27, 1999 3:51 PM
To: pgsql-sql@hub.org
Subject: [SQL] Simple Question??
Here's a question that maybe you can answer easily...
If I am using a sequence (seq_a) in table_a
...then "create table_b as select * from table_a"
...does table_b start using that sequence (seq_a), starting at the
point
where table_a left off??
I have a 1GB table that I want to optimize by reordering it, and I
will want
the new table to use the same sequence to generate the primary key.
I will
then drop the original table and rename the table_b to table_a.
Any clues?
Tim Perdue
tim@perdue.net
^ permalink raw reply [nested|flat] 15+ messages in thread
* simple question
@ 2000-01-12 10:10 Thomas Leavitt <thomas.leavitt@clickrebates.com>
0 siblings, 0 replies; 15+ messages in thread
From: Thomas Leavitt @ 2000-01-12 10:10 UTC (permalink / raw)
To: pgsql-sql
I would like to know if function parameters are used to pass output values
back to the function.
e.g.
create function func1(p1 varchar,p2 varchar OUTPUT, p3 varchar OUTPUT)
BEGIN
SELECT p1 = t.foo1, p3 = t.foo2
FROM test_table t
WHERE foo3 = p1
END
DECLARE p2 varchar(50), p3 varchar(50)
CALL func1('abc',@p2 out,@p3 out)
PRINT @p2
PRINT @p3
--
Thomas Leavitt -- thomas.leavitt@clickrebates.com; 408-591-3342
Co-Founder/COO, ClickRebates.Com, Inc. (www.clickrebates.com)
Get Paid What You're Worth!
Sign up for our pre-launch guest list and refer your friends!
Or use our working prototype at www.clickrebates.org to "Surf for a Higher
Cause" (earn money for charity)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Simple Question
@ 2001-04-14 21:55 Lonnie Cumberland <lonnie_cumberland@yahoo.com>
0 siblings, 0 replies; 15+ messages in thread
From: Lonnie Cumberland @ 2001-04-14 21:55 UTC (permalink / raw)
To: pgsql-sql
Hello All,
This may be the wrong mailing list for this question but I hope someone can
answer it for me as it is holding up my development progress.
I have now been able to compile the tutorial/complex.c functions and test them
out with no problems.
I made a copy of the Makefile, Makefile.global, and Makefile.port (Linux) from
the tutorial directory and made a simple modification for the paths. Now the
'c" files compile up just fine.
The problem is that this set up will not work if I make some C++ files. I think
that there is something wrong in one of the above makefiles for the g++
compiler and the needed options, but do not know where to find it.
I even made a copy of the "concat_text(....)" function from the manual and
saved one copy as a "c" file. It compiled just fine. I then renamed that same
file to "cc" for C++ and it compiled ok, but the psql command interperter gave
me some error that it could not find the function even thout it worked the
first time and I moade no other changes.
I also need to link in some other libraries like "libfile.a" that I have for
some of the functions that will be placed in my pgSQL interface but am not sure
hoe to do this as well with the default Makefiles.
Does someone have a simple Makefile that will allow me to compile up my C++
functions?
Cheers,
Lonnie
__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* simple question!
@ 2001-09-27 18:13 Esteban Gutierrez Abarzua <esgutier@sauce.chillan.ubiobio.cl>
0 siblings, 1 reply; 15+ messages in thread
From: Esteban Gutierrez Abarzua @ 2001-09-27 18:13 UTC (permalink / raw)
To: pgsql-sql
hi.
I have a simple question!
is there a command intersect? I mean .... exist a union command, but I
don't know if exist a intersect command.
thanks
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: simple question!
@ 2001-09-27 23:43 Josh Berkus <josh@agliodbs.com>
parent: Esteban Gutierrez Abarzua <esgutier@sauce.chillan.ubiobio.cl>
0 siblings, 0 replies; 15+ messages in thread
From: Josh Berkus @ 2001-09-27 23:43 UTC (permalink / raw)
To: Esteban Gutierrez Abarzua <esgutier@sauce.chillan.ubiobio.cl>; pgsql-sql
Esteban,
> is there a command intersect? I mean .... exist a union command, but
> I
> don't know if exist a intersect command.
> thanks
Yes. There is also EXCEPT as well as INTERSECT and UNION. See the
online docs, in SQL COMMANDS --> SELECT
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 15+ messages in thread
* Simple Question
@ 2005-01-11 21:42 Terry Lee Tucker <terry@esc1.com>
0 siblings, 5 replies; 15+ messages in thread
From: Terry Lee Tucker @ 2005-01-11 21:42 UTC (permalink / raw)
To: pgsql-sql
Hello:
I'm trying to figure out how to convert a floating point value into an
interval of time. I'm calculating the time required to drive from point A to
point B. For the sake of this question, we'll just say it is miles/speed. So:
drv_time = 478 / 45.0;
The value of this is: 10.6222222222222222
Is there a way of converting this value to an interval. It seems that INTERVAL
only works with a quoted literal value.
If I type:
rnd=# select interval '10.8444444444444444 hours';
interval
----------------------------
@ 10 hours 50 mins 40 secs
(1 row)
Anybody have an pointers?
Thanks...
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Simple Question
@ 2005-01-11 22:06 Guy Fraser <guy@incentre.net>
parent: Terry Lee Tucker <terry@esc1.com>
4 siblings, 1 reply; 15+ messages in thread
From: Guy Fraser @ 2005-01-11 22:06 UTC (permalink / raw)
To: Terry Lee Tucker <terry@esc1.com>; +Cc: pgsql-sql
Convert to seconds first (3600 sec/hr) :
select (
'3600'::int4
* '478'::int4
/ '45.0'::float8
)::int4::reltime::interval ;
interval
----------
10:37:20
(1 row)
I don't know if "::int4::reltime::interval" is the best
way to end up with an interval, but its the only way I
could figure out how to do it off the top of my head.
On Tue, 2005-11-01 at 16:42 -0500, Terry Lee Tucker wrote:
> Hello:
>
> I'm trying to figure out how to convert a floating point value into an
> interval of time. I'm calculating the time required to drive from point A to
> point B. For the sake of this question, we'll just say it is miles/speed. So:
>
> drv_time = 478 / 45.0;
>
> The value of this is: 10.6222222222222222
>
> Is there a way of converting this value to an interval. It seems that INTERVAL
> only works with a quoted literal value.
>
> If I type:
> rnd=# select interval '10.8444444444444444 hours';
> interval
> ----------------------------
> @ 10 hours 50 mins 40 secs
> (1 row)
>
> Anybody have an pointers?
>
> Thanks...
>
>
> Work: 1-336-372-6812
> Cell: 1-336-363-4719
> email: terry@esc1.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Guy Fraser
Network Administrator
The Internet Centre
1-888-450-6787
(780)450-6787
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Simple Question
@ 2005-01-11 22:07 Terry Lee Tucker <terry@esc1.com>
parent: Terry Lee Tucker <terry@esc1.com>
4 siblings, 0 replies; 15+ messages in thread
From: Terry Lee Tucker @ 2005-01-11 22:07 UTC (permalink / raw)
To: pgsql-sql
I figured it out. This works:
travel_window INTERVAL;
drv_time FLOAT;
drv_time = miles / 45.0; -- drive time
travel_window = quote_literal(drv_time || '' hours'');
The variable, travel_window becomes: @ 10 hours 50 mins 40 secs, which is
what I wanted.
If anybody knows any other ways, I'd be interested in see that too.
On Tuesday 11 January 2005 04:42 pm, Terry Lee Tucker saith:
> Hello:
>
> I'm trying to figure out how to convert a floating point value into an
> interval of time. I'm calculating the time required to drive from point A
> to point B. For the sake of this question, we'll just say it is
> miles/speed. So:
>
> drv_time = 478 / 45.0;
>
> The value of this is: 10.6222222222222222
>
> Is there a way of converting this value to an interval. It seems that
> INTERVAL only works with a quoted literal value.
>
> If I type:
> rnd=# select interval '10.8444444444444444 hours';
> interval
> ----------------------------
> @ 10 hours 50 mins 40 secs
> (1 row)
>
> Anybody have an pointers?
>
> Thanks...
>
>
> Work: 1-336-372-6812
> Cell: 1-336-363-4719
> email: terry@esc1.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Simple Question
@ 2005-01-11 22:15 Michael Fuhr <mike@fuhr.org>
parent: Terry Lee Tucker <terry@esc1.com>
4 siblings, 1 reply; 15+ messages in thread
From: Michael Fuhr @ 2005-01-11 22:15 UTC (permalink / raw)
To: Terry Lee Tucker <terry@esc1.com>; +Cc: pgsql-sql
On Tue, Jan 11, 2005 at 04:42:21PM -0500, Terry Lee Tucker wrote:
> Subject: [SQL] Simple Question
Please use a more descriptive subject -- think about how somebody
looking at a list of 200 messages, all with subjects like "Simple
Question" or "PostgreSQL Question," would decide to look at yours.
> drv_time = 478 / 45.0;
>
> The value of this is: 10.6222222222222222
>
> Is there a way of converting this value to an interval. It seems that INTERVAL
> only works with a quoted literal value.
You can do arithmetic on intervals:
SELECT 478 / 45.0 * interval'1 hour';
For more information, see "Date/Time Functions and Operators" in
the documentation.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Simple Question
@ 2005-01-11 22:18 Terry Lee Tucker <terry@esc1.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 15+ messages in thread
From: Terry Lee Tucker @ 2005-01-11 22:18 UTC (permalink / raw)
To: pgsql-sql
Thank you for the reply in spite of the subject.
On Tuesday 11 January 2005 05:15 pm, Michael Fuhr saith:
> On Tue, Jan 11, 2005 at 04:42:21PM -0500, Terry Lee Tucker wrote:
> > Subject: [SQL] Simple Question
>
> Please use a more descriptive subject -- think about how somebody
> looking at a list of 200 messages, all with subjects like "Simple
> Question" or "PostgreSQL Question," would decide to look at yours.
I will do this in the future.
>
> > drv_time = 478 / 45.0;
> >
> > The value of this is: 10.6222222222222222
> >
> > Is there a way of converting this value to an interval. It seems that
> > INTERVAL only works with a quoted literal value.
>
> You can do arithmetic on intervals:
>
> SELECT 478 / 45.0 * interval'1 hour';
I like your soultion better than mine. Thanks for the answer.
>
> For more information, see "Date/Time Functions and Operators" in
> the documentation.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Simple Question
@ 2005-01-11 22:21 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Terry Lee Tucker <terry@esc1.com>
4 siblings, 0 replies; 15+ messages in thread
From: Stephan Szabo @ 2005-01-11 22:21 UTC (permalink / raw)
To: Terry Lee Tucker <terry@esc1.com>; +Cc: pgsql-sql
On Tue, 11 Jan 2005, Terry Lee Tucker wrote:
> Hello:
>
> I'm trying to figure out how to convert a floating point value into an
> interval of time. I'm calculating the time required to drive from point A to
> point B. For the sake of this question, we'll just say it is miles/speed. So:
>
> drv_time = 478 / 45.0;
>
> The value of this is: 10.6222222222222222
I think something like
478/45.0 * interval '1 hour'
may do what you want.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Simple Question
@ 2005-01-11 22:21 Terry Lee Tucker <terry@esc1.com>
parent: Guy Fraser <guy@incentre.net>
0 siblings, 0 replies; 15+ messages in thread
From: Terry Lee Tucker @ 2005-01-11 22:21 UTC (permalink / raw)
To: pgsql-sql
Thanks for the reply. My answer was a little different than yours because I
used 488 instead of 478. Well, that three ways so far ;o)
On Tuesday 11 January 2005 05:06 pm, Guy Fraser saith:
> Convert to seconds first (3600 sec/hr) :
>
> select (
> '3600'::int4
> * '478'::int4
> / '45.0'::float8
> )::int4::reltime::interval ;
> interval
> ----------
> 10:37:20
> (1 row)
>
> I don't know if "::int4::reltime::interval" is the best
> way to end up with an interval, but its the only way I
> could figure out how to do it off the top of my head.
>
> On Tue, 2005-11-01 at 16:42 -0500, Terry Lee Tucker wrote:
> > Hello:
> >
> > I'm trying to figure out how to convert a floating point value into an
> > interval of time. I'm calculating the time required to drive from point A
> > to point B. For the sake of this question, we'll just say it is
> > miles/speed. So:
> >
> > drv_time = 478 / 45.0;
> >
> > The value of this is: 10.6222222222222222
> >
> > Is there a way of converting this value to an interval. It seems that
> > INTERVAL only works with a quoted literal value.
> >
> > If I type:
> > rnd=# select interval '10.8444444444444444 hours';
> > interval
> > ----------------------------
> > @ 10 hours 50 mins 40 secs
> > (1 row)
> >
> > Anybody have an pointers?
> >
> > Thanks...
> >
> >
> > Work: 1-336-372-6812
> > Cell: 1-336-363-4719
> > email: terry@esc1.com
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> --
> Guy Fraser
> Network Administrator
> The Internet Centre
> 1-888-450-6787
> (780)450-6787
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Simple Question
@ 2005-01-11 22:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Terry Lee Tucker <terry@esc1.com>
4 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2005-01-11 22:43 UTC (permalink / raw)
To: Terry Lee Tucker <terry@esc1.com>; +Cc: pgsql-sql
Terry Lee Tucker <terry@esc1.com> writes:
> I'm trying to figure out how to convert a floating point value into an
> interval of time.
Use something like
regression=# select (478 / 45.0) * '1 hour'::interval;
?column?
----------
10:37:20
(1 row)
... or whatever other scale factor you have in mind.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2005-01-11 22:43 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-27 22:50 Simple Question?? archiver <archiver@db.geocrawler.com>
1999-02-28 18:20 Re: Simple Question?? Tom Lane <tgl@sss.pgh.pa.us>
1999-03-01 18:05 RE: [SQL] Simple Question?? Michael Davis <michael.davis@prevuenet.com>
2000-01-12 10:10 simple question Thomas Leavitt <thomas.leavitt@clickrebates.com>
2001-04-14 21:55 Simple Question Lonnie Cumberland <lonnie_cumberland@yahoo.com>
2001-09-27 18:13 simple question! Esteban Gutierrez Abarzua <esgutier@sauce.chillan.ubiobio.cl>
2001-09-27 23:43 ` Re: simple question! Josh Berkus <josh@agliodbs.com>
2005-01-11 21:42 Simple Question Terry Lee Tucker <terry@esc1.com>
2005-01-11 22:06 ` Re: Simple Question Guy Fraser <guy@incentre.net>
2005-01-11 22:21 ` Re: Simple Question Terry Lee Tucker <terry@esc1.com>
2005-01-11 22:07 ` Re: Simple Question Terry Lee Tucker <terry@esc1.com>
2005-01-11 22:15 ` Re: Simple Question Michael Fuhr <mike@fuhr.org>
2005-01-11 22:18 ` Re: Simple Question Terry Lee Tucker <terry@esc1.com>
2005-01-11 22:21 ` Re: Simple Question Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-01-11 22:43 ` Re: Simple Question Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox