Re: [SQL] Assigning a timestamp without timezone to a timestamp

[Markus Schaber]

Hi, Chris,
Hi, Andrew,

Chrisj wrote:

> please be patient with me sometimes I am slow but I usually get there.


Andrew Sullivan wrote:
> Sorry, I'm dim, 

> Told you I'm dim.


That's just plain wrong. You guys are using PostgreSQL, and that's the
proof that you're the brightest people on the planet. :-)


HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] i have table

[Markus Schaber]

Hi, Penchalaiah,

Penchalaiah P. wrote:

> now I want to add one more field in this table.. but that field has to
> come next to cda_no.. I mean as a 3^rd field.. If I am adding that field
> it is coming last field …

In SQL, field order in the table is not given by design.

A "SELECT * FROM table" might even give you the columns alphabetically
ordered, or in a different random order each time in a different server
implementation.

If you need the colums in a specific order, use "SELECT foo, bar, baz
FROM table" or create a View.

All relevant SQL constructs (SELECT, INSERT, UPDATE, COPY, etc.) let you
specify the columns explicitly to guarantee a given order.

> may I know how it is possible to that table…

If you _really_ want to do that despite what I wrote above, you have
several possibilities:

- COPY the table to some file, drop the table, recreate the table with
  the desired new column order, and then COPY the table back using an
  explicitly specified, correct row order.

- use CREATE TABLE ... AS SELECT to select the data into a new table,
  drop the old table, rename the new one to the old one.

In both cases, you've to recreate all missing indices, foreing key
constraints etc.


HTH,
Markus


-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] How to overwrite a table [only data]?

[Ezequias Rodrigues da Rocha]

Hi list,

I would like to overwrite a table with problem with his original. But there are some points.

I can't just delete and restore it becouse it has a foreign key and when I try to delete it ask for drop cascade.

As I know the drop cascade delete in both tables the rows related. Is it correct ?


Regards...--  =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-  Atenciosamente (Sincerely)Ezequias Rodrigues da Rocha
 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-A pior das democracias ainda é melhor do que a melhor das ditadurasThe worst of democracies is still better than the better of dictatorships
 http://ezequiasrocha.blogspot.com/

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[christopher wood]

Thanks Markus,

But I can't even take credit for that, my business partner suggested using 
Postgres.


I have been a DB2 DBA most of my professional life (25 years) until 
recently, and a huge proponent of DB2 against the likes of Oracle and 
MS-SQL.


So far I am very impressed with Postgres but there is a lot more in Postgres 
to get one's head around.  I certainly appreciate having people like you and 
Andrew to help me along.


God Bless,
- chris



From: Markus Schaber <[EMAIL PROTECTED]>
To: Andrew Sullivan <[EMAIL PROTECTED]>
CC: chrisj <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org
Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp
Date: Wed, 04 Oct 2006 11:07:40 +0200

Hi, Chris,
Hi, Andrew,

Chrisj wrote:

> please be patient with me sometimes I am slow but I usually get there.


Andrew Sullivan wrote:
> Sorry, I'm dim,

> Told you I'm dim.


That's just plain wrong. You guys are using PostgreSQL, and that's the
proof that you're the brightest people on the planet. :-)


HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] i have table

[Erik Jones]

There is one non-SQL related reason that I like to be able to order 
columns, at least the way they are displayed whenever the table is 
described:  human comprehension.  For example, I like to group all keys 
in a table before data, that includes primary as well as foreign keys.   
So, say I'm building on to an existing application and I need to do an 
ALTER TABLE on an existing table to add a foreign key to an existing 
table.  I'd like that key to be listed with the other keys, but 
presently that's not possible in a simple way and, to be honest, I 
usually just go without as the process you've described below is too 
prone to user (human) error when dealing with live, sensitive data for 
me to want to mess with it.


Markus Schaber wrote:

Hi, Penchalaiah,

Penchalaiah P. wrote:

  

now I want to add one more field in this table.. but that field has to
come next to cda_no.. I mean as a 3^rd field.. If I am adding that field
it is coming last field …



In SQL, field order in the table is not given by design.

A "SELECT * FROM table" might even give you the columns alphabetically
ordered, or in a different random order each time in a different server
implementation.

If you need the colums in a specific order, use "SELECT foo, bar, baz
FROM table" or create a View.

All relevant SQL constructs (SELECT, INSERT, UPDATE, COPY, etc.) let you
specify the columns explicitly to guarantee a given order.

  

may I know how it is possible to that table…



If you _really_ want to do that despite what I wrote above, you have
several possibilities:

- COPY the table to some file, drop the table, recreate the table with
  the desired new column order, and then COPY the table back using an
  explicitly specified, correct row order.

- use CREATE TABLE ... AS SELECT to select the data into a new table,
  drop the old table, rename the new one to the old one.

In both cases, you've to recreate all missing indices, foreing key
constraints etc.


HTH,
Markus


  



--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] i have table

[Aaron Bono]

On 10/4/06, Erik Jones <[EMAIL PROTECTED]> wrote:
There is one non-SQL related reason that I like to be able to ordercolumns, at least the way they are displayed whenever the table isdescribed:  human comprehension.  For example, I like to group all keysin a table before data, that includes primary as well as foreign keys.
So, say I'm building on to an existing application and I need to do anALTER TABLE on an existing table to add a foreign key to an existingtable.  I'd like that key to be listed with the other keys, butpresently that's not possible in a simple way and, to be honest, I
usually just go without as the process you've described below is tooprone to user (human) error when dealing with live, sensitive data forme to want to mess with it.Ah, but it is possible... if you use views.
I recommend you build views and query off them.  Then you can control the order the columns will appear.==   Aaron Bono   Aranya Software Technologies, Inc.
   http://www.aranya.com   http://codeelixir.com==

Re: [SQL] How to overwrite a table [only data]?

[Aaron Bono]

On 10/4/06, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
Hi list,

I would like to overwrite a table with problem with his original. But there are some points.

I can't just delete and restore it becouse it has a foreign key and when I try to delete it ask for drop cascade.

As I know the drop cascade delete in both tables the rows related. Is it correct ?You can drop the foreign keys and then the table.  Then when you recreate and repopulate the table, you can add the foreign keys back at the very end. 
==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com   
http://codeelixir.com==

Re: [SQL] i have table

[Erik Jones]

Aaron Bono wrote:
On 10/4/06, *Erik Jones* <[EMAIL PROTECTED] > 
wrote:


There is one non-SQL related reason that I like to be able to order
columns, at least the way they are displayed whenever the table is
described:  human comprehension.  For example, I like to group all
keys
in a table before data, that includes primary as well as foreign
keys.
So, say I'm building on to an existing application and I need to do an
ALTER TABLE on an existing table to add a foreign key to an existing
table.  I'd like that key to be listed with the other keys, but
presently that's not possible in a simple way and, to be honest, I
usually just go without as the process you've described below is too
prone to user (human) error when dealing with live, sensitive data for
me to want to mess with it.


Ah, but it is possible... if you use views.

I recommend you build views and query off them.  Then you can control 
the order the columns will appear.
Which would be great if I didn't have (many) thousands of lines of code 
that already use the tables.  Besides, this is no where near a 'make or 
break' thing.  It's just a matter of aesthetic preference.


--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[Markus Schaber]

Hi, Christopher,

christopher wood wrote:

> But I can't even take credit for that, my business partner suggested
> using Postgres.

So I guess he's a smart one, too. :-)

At least as long as he understands that free software does not mean a
TCO of zero dollars, that's the main mistake when businesses try to jump
on the free software train.

> I have been a DB2 DBA most of my professional life (25 years) until
> recently, and a huge proponent of DB2 against the likes of Oracle and
> MS-SQL.

DB2 is not the worst one, AFAICT.

And as a long-time DBA, you know that administration of a real DBMS is
not "install and forget", but lots of fine-tuning and ongoing care.

> So far I am very impressed with Postgres but there is a lot more in
> Postgres to get one's head around.  I certainly appreciate having people
> like you and Andrew to help me along.

PostgreSQL is neither perfect, nor a one-fits-all solution. (Hey, that's
the reason for loads of individuals and companies to develop new
releases and extensions, after all. :-)

But it's a stable DBMS providing most features one would expect and use,
and it has a very supportive community, and commercial supporters and
niche-derivates.

As long as you want to keep learning, and don't hesitate getting
involved, PostgreSQL will offer you a satisfying experience.

Regards,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

---(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

Re: [SQL] i have table

[Aaron Bono]

On 10/4/06, Erik Jones <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:> On 10/4/06, *Erik Jones* <[EMAIL PROTECTED] [EMAIL PROTECTED]>>> wrote:>> There is one non-SQL related reason that I like to be able to order
> columns, at least the way they are displayed whenever the table is> described:  human comprehension.  For example, I like to group all> keys> in a table before data, that includes primary as well as foreign
> keys.> So, say I'm building on to an existing application and I need to do an> ALTER TABLE on an existing table to add a foreign key to an existing> table.  I'd like that key to be listed with the other keys, but
> presently that's not possible in a simple way and, to be honest, I> usually just go without as the process you've described below is too> prone to user (human) error when dealing with live, sensitive data for
> me to want to mess with it.>>> Ah, but it is possible... if you use views.>> I recommend you build views and query off them.  Then you can control> the order the columns will appear.
Which would be great if I didn't have (many) thousands of lines of codethat already use the tables.  Besides, this is no where near a 'make orbreak' thing.  It's just a matter of aesthetic preference.
 So do it as needed and convert your application slowly.I just name my views as table_name_vw so all you have to do is modify your queries to hit the _vw instead of just the table.  That shouldn't take much time to refactor.
==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com   
http://codeelixir.com==

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[christopher wood]

Hi Markus,

what is AFAICT ?

In the commercial space, I believe DB2 is one of the best



From: Markus Schaber <[EMAIL PROTECTED]>
Reply-To: PostgreSQL SQL List 
To: pgsql-sql@postgresql.org
CC: christopher wood <[EMAIL PROTECTED]>
Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp
Date: Wed, 04 Oct 2006 17:44:49 +0200

Hi, Christopher,

christopher wood wrote:

> But I can't even take credit for that, my business partner suggested
> using Postgres.

So I guess he's a smart one, too. :-)

At least as long as he understands that free software does not mean a
TCO of zero dollars, that's the main mistake when businesses try to jump
on the free software train.

> I have been a DB2 DBA most of my professional life (25 years) until
> recently, and a huge proponent of DB2 against the likes of Oracle and
> MS-SQL.

DB2 is not the worst one, AFAICT.

And as a long-time DBA, you know that administration of a real DBMS is
not "install and forget", but lots of fine-tuning and ongoing care.

> So far I am very impressed with Postgres but there is a lot more in
> Postgres to get one's head around.  I certainly appreciate having people
> like you and Andrew to help me along.

PostgreSQL is neither perfect, nor a one-fits-all solution. (Hey, that's
the reason for loads of individuals and companies to develop new
releases and extensions, after all. :-)

But it's a stable DBMS providing most features one would expect and use,
and it has a very supportive community, and commercial supporters and
niche-derivates.

As long as you want to keep learning, and don't hesitate getting
involved, PostgreSQL will offer you a satisfying experience.

Regards,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Update 3 columns w/ 1 function calc 3 values?

[Markus Schaber]

Hi, paallen,

[EMAIL PROTECTED] wrote:

> I am moving some of my old MS Access functions to
> plpgsql.
> 
> My function was made to return coordinates, X,Y,Z
> for a point along a curved line. 

Depending on the actual kind of data, you might want to look into the
PostgeSQL geometry types, or PostGIS for your geometric data.

> What should I do to speed this up.  What is the
> best way to update 3 columns from one function
> call.  In MSACCESS I once rig the function to
> cycle through the records with a curser but that
> was slow and would crap out due to the shear number.

It might work to mark the function as "stable", and have it return a
compound type in a subselect with an "AS foo" aliasname. Then you should
be able to address the compound's elements via foo.x, foo.y and foo.z.

Or use PostGIS, it's capable of processing 3D points.

HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

---(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

Re: [SQL] i have table

[Daryl Richter]

On 10/4/06 12:20 PM, "Aaron Bono" <[EMAIL PROTECTED]> wrote:

> On 10/4/06, Erik Jones <[EMAIL PROTECTED]> wrote:
>> 
>> Aaron Bono wrote:
>>> On 10/4/06, *Erik Jones* <[EMAIL PROTECTED] >
>>> wrote:
>>> 
>>> There is one non-SQL related reason that I like to be able to order
>>> columns, at least the way they are displayed whenever the table is
>>> described:  human comprehension.  For example, I like to group all
>>> keys
>>> in a table before data, that includes primary as well as foreign
>>> keys.
>>> So, say I'm building on to an existing application and I need to do
>> an
>>> ALTER TABLE on an existing table to add a foreign key to an existing
>>> table.  I'd like that key to be listed with the other keys, but
>>> presently that's not possible in a simple way and, to be honest, I
>>> usually just go without as the process you've described below is too
>>> prone to user (human) error when dealing with live, sensitive data
>> for
>>> me to want to mess with it.
>>> 
>>> 
>>> Ah, but it is possible... if you use views.
>>> 
>>> I recommend you build views and query off them.  Then you can control
>>> the order the columns will appear.
>> Which would be great if I didn't have (many) thousands of lines of code
>> that already use the tables.  Besides, this is no where near a 'make or
>> break' thing.  It's just a matter of aesthetic preference.

Ah, but it *is* a "make or break thing."  I have seen more than one
application crash because some developer didn't understand that columns in a
relation (table) have no defined order.

This (along with its sister axiom that rows have no defined order) is one of
the most commonly misunderstood aspects of relational databases.

> 
> 
> 
> So do it as needed and convert your application slowly.
> 
> I just name my views as table_name_vw so all you have to do is modify your
> queries to hit the _vw instead of just the table.  That shouldn't take much
> time to refactor.
> 
> ==
>Aaron Bono
>Aranya Software Technologies, Inc.
>http://www.aranya.com
>http://codeelixir.com
> ==

--
Daryl
http://itsallsemantics.com





---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[Markus Schaber]

Hi, Christopher,

christopher wood wrote:

> what is AFAICT ?

"As Far As I Can Tell".

It's explained in the "Jargon File":
http://www.catb.org/jargon/html/A/AFAIK.html


HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] How to overwrite a table [only data]?

[Markus Schaber]

Hi, Ezequias,

Ezequias Rodrigues da Rocha wrote:

> I would like to overwrite a table with problem with his original. But
>  there are some points.
> 
> I can't just delete and restore it becouse it has a foreign key and
> when I try to delete it ask for drop cascade.

As long as the delete and restore is in the same transaction, SET
CONSTRAINTS ALL DEFERRED might help.

See the docs for more info.

> As I know the drop cascade delete in both tables the rows related. Is
> it correct ?

Yes, cascade will delete all referencing rows in the otehr table to keep
the constraints intact, keeping only those rows that have NULL in the
foreign key. I think that's not exactly what you want.

HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

---(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

Re: [SQL] i have table

[Aaron Bono]

On 10/4/06, Daryl Richter <[EMAIL PROTECTED]> wrote:
On 10/4/06 12:20 PM, "Aaron Bono" <[EMAIL PROTECTED]> wrote:> On 10/4/06, Erik Jones <[EMAIL PROTECTED]> wrote:
 Aaron Bono wrote:>>> On 10/4/06, *Erik Jones* <[EMAIL PROTECTED] [EMAIL PROTECTED]> wrote:
>> There is one non-SQL related reason that I like to be able to order>>> columns, at least the way they are displayed whenever the table is>>> described:  human comprehension.  For example, I like to group all
>>> keys>>> in a table before data, that includes primary as well as foreign>>> keys.>>> So, say I'm building on to an existing application and I need to do
>> an>>> ALTER TABLE on an existing table to add a foreign key to an existing>>> table.  I'd like that key to be listed with the other keys, but>>> presently that's not possible in a simple way and, to be honest, I
>>> usually just go without as the process you've described below is too>>> prone to user (human) error when dealing with live, sensitive data>> for>>> me to want to mess with it.
> Ah, but it is possible... if you use views.>> I recommend you build views and query off them.  Then you can control>>> the order the columns will appear.
>> Which would be great if I didn't have (many) thousands of lines of code>> that already use the tables.  Besides, this is no where near a 'make or>> break' thing.  It's just a matter of aesthetic preference.
Ah, but it *is* a "make or break thing."  I have seen more than oneapplication crash because some developer didn't understand that columns in arelation (table) have no defined order.This (along with its sister axiom that rows have no defined order) is one of
the most commonly misunderstood aspects of relational databases. So do it as needed and convert your application slowly.>> I just name my views as table_name_vw so all you have to do is modify your
> queries to hit the _vw instead of just the table.  That shouldn't take much> time to refactor. I wasn't condoning using select * in your application - if an application is riddled with that, it is time to start fixing the problem because it WILL bite you eventually.  I never let my team put select * in any queries that end up in the application code - bad stuff that!
Of course sometimes, especially when you are doing quick throw away queries, select * is nice and controlling the order is handy.  It is also beneficial when using a generic database tool which will almost definitely do a select *.
==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com   
http://codeelixir.com==

Re: [SQL] formatting intervals with to_char

[Graham Davis]

I haven't heard any replies from this, so in the meantime I've found a 
hacky way to get the output I desire.  I'm basically calculating the 
hours on the fly and piecing together a formatted string with 
concatenations like this:


SELECT
(((EXTRACT(day from time_idle)*24)+EXTRACT(hour from time_idle)) || ':' ||
   EXTRACT(minute from time_idle) || ':' || EXTRACT(second from 
time_idle))::interval AS myinterval

FROM
( SELECT ('2006-09-15T23:59:00'::timestamp - '2006-09-01 
09:30:41'::timestamp) AS time_idle) FROM_TABLE;


If anyone knows a better/proper way to get this result, let me know.  
Thanks,


Graham.



Graham Davis wrote:


Hi,

I'm trying to format the output of a time interval so that it displays 
as HH:MM:SS no matter how many days it spans.  So for instance, an 
interval of 2 days 4 hours and 0 minutes would look something like 
"52:00:00".  The documentation for to_char states that:


"|to_char(interval)| formats HH and HH12 as hours in a single day, 
while HH24 can output hours exceeding a single day, e.g. >24."


However I can not get it to work with time intervals that span more 
than 1 day.  For instance, the following query returns this time 
interval:


Query:
select ('2006-09-15T23:59:00'::timestamp - '2006-09-01 
09:30:41'::timestamp);


Result:
14 days 14:28:19

But when I run to_char on this with HH24, it doesn't take into effect 
the number of days:


Query:
select to_char(('2006-09-15T23:59:00'::timestamp - '2006-09-01 
09:30:41'::timestamp), 'HH24:MI:SS');


Result:
14:28:19

It just gives me the offset of hours, min, seconds on that 14th day.  
The result I'm looking for is:  350:28:19


What am I doing wrong, or how can I get this desired output?  Thanks,




--
Graham Davis
Refractions Research Inc.
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[Hector Villarreal]

Hi 
I am also interested in this type of setup. However, in the example
below
I am a little confused as to why the table entry is 1, -3 
And the subsequent select statement . I would appreciate an explanation
on the select statement. I do not understand the syntax. 
Thanks in advance 
Hector Villarreal
SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
09:00'||"timezone" as timestamp from storetz where id = 1) as a;
   timestamp

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan
Sent: Tuesday, October 03, 2006 7:52 AM
To: chrisj
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp

On Tue, Oct 03, 2006 at 07:26:52AM -0700, chrisj wrote:
> location, but they are the directive to all store locations saying:
"In the
> context of the timezone your store is located in,  these are the hours
you
> should be open.

Ah.  Well, then, right, it _does_ have to be timezone free.  That's
actually the only case I'd use that.  Sorry, I'm dim, and didn't
understand properly what you were doing.  (I read the "relative to
the store's own time zone" to refer to the corporate office.  No, I
don't know why, either.  Told you I'm dim.)

Anyway, here's something that worked for me (expanding this into your
case ought not to be too tricky):

testing=# SELECT * from storetz ;
 id | timezone 
+--
  1 | -03
(1 row)

testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
09:00'||"timezone" as timestamp from storetz where id = 1) as a;
   timestamp

 2006-10-03 12:00:00+00
(1 row)

A
-- 
Andrew Sullivan  | [EMAIL PROTECTED]
When my information changes, I alter my conclusions.  What do you do
sir?
--attr. John Maynard Keynes

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] i have table

[Scott Marlowe]

On Wed, 2006-10-04 at 13:02, Aaron Bono wrote:
> On 10/4/06, Daryl Richter <[EMAIL PROTECTED]> wrote:
> On 10/4/06 12:20 PM, "Aaron Bono" <[EMAIL PROTECTED]>
> wrote:

> >
> > So do it as needed and convert your application slowly.
> >
> > I just name my views as table_name_vw so all you have to do
> is modify your 
> > queries to hit the _vw instead of just the table.  That
> shouldn't take much
> > time to refactor.
> 
>  
> I wasn't condoning using select * in your application - if an
> application is riddled with that, it is time to start fixing the
> problem because it WILL bite you eventually.  I never let my team put
> select * in any queries that end up in the application code - bad
> stuff that! 
> 
> Of course sometimes, especially when you are doing quick throw away
> queries, select * is nice and controlling the order is handy.  It is
> also beneficial when using a generic database tool which will almost
> definitely do a select *. 

Actually, the one time I've written an application with select * in it
was when I wrote some simple, generic app that used select * to root out
the layout of the table and make a simple edit screen for any generic
table in postgresql.  It used select * from table limit 1 to get the
layout, and using libpq was able to find the type of each field and
thereby produce a proper update / insert query.

But any REAL application should never do that, I agree.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] i have table

[operationsengineer1]

--- "Penchalaiah P." <[EMAIL PROTECTED]> wrote:

> 
> Hi ...
> 
> I have one table with 12 fields..
> 
> 
> 
> CREATE TABLE addition_alteration_memo
> 
> (
> 
>   addition_alteration_memo int8 NOT NULL DEFAULT
>
nextval('addition_alteration_memo_addition_alteration_memo_seq'::regclas
> s),
> 
>   cda_no varchar(7) NOT NULL,
> 
>   week numeric,
> 
>   sheet_no numeric,
> 
>   serial_no numeric,
> 
>   date date,
> 
>   dr_no varchar,
> 
>   amount numeric,
> 
>   memo_no varchar,
> 
>   memo_date date,
> 
>   no_instalments numeric,
> 
>   instalment_rate numeric)
> 
> 
> 
> now I want to add one more field in this table.. but
> that field has to
> come next to cda_no.. I mean as a 3rd field.. If I
> am adding that field
> it is coming last field ...
> 
> may I know how it is possible to that table...
> 
> 
> 
> Thanks  &  Regards
> 
> Penchal reddy | Software Engineer  

Penchal,

yes, you can do this, but it isn't necessary beyond
making you feel more comfortable when you look at your
column names in something like pgadmin3.

i don't have my code in front of me, but what you do
is export your table definitions into sql text.  there
is a statement you can put at the beginning of the
table that deletes it if it already exists.  

update the text file with the columns in order and
then import the sql table definitions.

your original table is deleted (if it exists) and the
new table is generated in the order you want it.

when i get some time on the computer where i've done
this, i will post an example.

oe1

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] formatting intervals with to_char

[Aaron Bono]

On 10/4/06, Graham Davis <[EMAIL PROTECTED]> wrote:
I haven't heard any replies from this, so in the meantime I've found ahacky way to get the output I desire.  I'm basically calculating thehours on the fly and piecing together a formatted string withconcatenations like this:
SELECT(((EXTRACT(day from time_idle)*24)+EXTRACT(hour from time_idle)) || ':' ||EXTRACT(minute from time_idle) || ':' || EXTRACT(second fromtime_idle))::interval AS myintervalFROM( SELECT ('2006-09-15T23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp) AS time_idle) FROM_TABLE;If anyone knows a better/proper way to get this result, let me know. That would be my approach though you could also replace:EXTRACT(minute from time_idle) || ':' || EXTRACT(second from time_idle)
with:to_char(time_idle, ':MM:SS')If you really want to clean up your SQL you could create a function that takes any timestamp and then make the function IMMUTABLE to tell postgresql it doesn't need to rederive the results every time.
==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com   
http://codeelixir.com==

[SQL] psql hangs during load of pg_dumpall file

[James Baxendale]

Hi,

 

I am using version 8.1.3 on Windows XP professional (I have
also tested this under 8.1.4). I have created a pg_dumpall file from my
database after loading a couple of megabytes of data (approximately 2 Mbytes)
into a schema containing roughly 30 tables and 20 sequences. 

 

When I try to recreate my database from the pg_dumpall
output file the psql session I am using hangs forever. The command I am using
to load the pg_dumpall file is:

 

"C:\Program Files\PostgreSQL\8.1\bin\psql.exe" -h
localhost -p  -U postgres -f pg_dumpall_ouput_file.sql

 

After about 5 to 10 seconds the scripted commands in
pg_dumpall_ouput_file.sql hangs forever without producing any error messages.
The interesting and annoying from the point of view of trying to debug what is
going on is that after I kill the psql process and retry the script will hang
at different points in the process of running the commands in the
pg_dumpall_ouput_file.sql file. At one point after trying to load this file
about 20 times with various uninstall and re-install attempts of PostgreSQL in
between to ensure that the installation was not somehow corrupt (both with
8.1.3 and 8.1.4) I eventually got the data loaded.

 

Has anyone ever seen behavior like this before and does anyone
have any suggestions of how to track down what is causing the load process to
hang. BTW, I am pretty sure nothing else is causing deadlock on any of the
tables in question as I can repeat this behavior after a reboot of my machine
followed by the issuance of the command above without running any other
applications.

 

I can still connect to the database using pgAdmin III after
the script of commands hangs so the database seems to still be
‘alive’ after the psql process hangs.

 

Any ideas of how to proceed would be greatly appreciated.

 

Thanks,

 

James