[SQL] Export Access 97 to PostgreSQL
Hello I have a db in MS Access 97 and now i have to import the data in PostgreSQL. I can create the table structure in PostgreSql but in what format can i export the table from Access so Postgresql can read it? Thanks Shavonne
[SQL] Part 1 of several - Converting a varchar to an interval
Hello, I have several large tables, over 100 million records each. One of the fields is callee 'duration'. It is a varchar that contains what is essentially an integer that is the duration of an event in milleseconds. Could someone tell me a simple way to convert a value such as 134987 stored in a varchar into an interval? This will dovetail with my next question. Thanks for all the help both now and previous. Shawn -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Question 2 Interval and timestamptz
Hello! The second part of my question is: Given a value as an interval, see previous posting, is there a simply method to take a given timestamptz value and a given interval value and create the sum or difference of the 2 in timestamptz format? The scenario is that the afore mentioned tables, see previous post, in addition to the duration varchar field, also have a field called event_at_utc which is a timestamptz type. I need to calculate the end time of the event given the interval calculated from the duration (varchar) field. Any all all help is greatly appreciated. Shawn -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Export Access 97 to PostgreSQL
On Wednesday 26 March 2008 11:46:43 Shavonne Marietta Wijesinghe wrote: > Hello > > I have a db in MS Access 97 and now i have to import the data in > PostgreSQL. I can create the table structure in PostgreSql but in what > format can i export the table from Access so Postgresql can read it? > > Thanks > > Shavonne Hello Shavonne, I'm no expert but I have often used either the tab or comma delimited format. It requires that the fields in the PostgreSQL table are in the same order as the original and that the data types are formatted similarly, but it has worked for me. Also a sed and awk'ing of the results can clean up alot of formatting issues. example, one of my fields is in the timestamp with time zone format but MS-SQL doesn't differentiate Time Zones, at least not in the database I'm pulling from. I use this line to add the UTC to the field " sed 's/\t/ UTC\t/1'". Shawn -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Export Access 97 to PostgreSQL
Shavonne Marietta Wijesinghe wrote: Hello I have a db in MS Access 97 and now i have to import the data in PostgreSQL. I can create the table structure in PostgreSql but in what format can i export the table from Access so Postgresql can read it? csv would be the most common and easiest. Basically any structured text file can be used as you have the option of specifying what characters are used to separate fields etc when you import to postgresql. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Part 1 of several - Converting a varchar to an interval
On Wednesday 26 March 2008 16:16:57 you wrote:
> select ('134987'::int/1000.00) * interval ' 1 second' ;
> ?column?
> --
> 00:02:14.987
> (1 row)
Thanks Adrian!
Wow!
Shawn
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
FW: Re: [SQL] Question 2 Interval and timestamptz
-- Forwarded Message: --
From: [EMAIL PROTECTED] (Adrian Klaver)
To: Shawn <[EMAIL PROTECTED]>
Subject: Re: [SQL] Question 2 Interval and timestamptz
Date: Wed, 26 Mar 2008 16:24:39 +
>
> -- Original message --
> From: Shawn <[EMAIL PROTECTED]>
> > Hello!
> >
> > The second part of my question is:
> >
> > Given a value as an interval, see previous posting, is there a simply
> > method
> > to take a given timestamptz value and a given interval value and create the
> > sum or difference of the 2 in timestamptz format?
> >
> > The scenario is that the afore mentioned tables, see previous post, in
> > addition to the duration varchar field, also have a field called
> > event_at_utc
> > which is a timestamptz type. I need to calculate the end time of the event
> > given the interval calculated from the duration (varchar) field.
> >
> > Any all all help is greatly appreciated.
> >
> > Shawn
> >
>
>
select '2008-03-26 09:21:44':: timestamptz + (('134987'::int/1000)* interval '1
second');
?column?
2008-03-26 09:23:58-07
(1 row)
Forgot to Reply All.
--
Adrian Klaver
[EMAIL PROTECTED]
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question 2 Interval and timestamptz
Awesome Adrian!
Thanks! Just what I needed.
On Wednesday 26 March 2008 16:24:34 you wrote:
> -- Original message --
> From: Shawn <[EMAIL PROTECTED]>
>
> > Hello!
> >
> > The second part of my question is:
> >
> > Given a value as an interval, see previous posting, is there a simply
> > method to take a given timestamptz value and a given interval value and
> > create the sum or difference of the 2 in timestamptz format?
> >
> > The scenario is that the afore mentioned tables, see previous post, in
> > addition to the duration varchar field, also have a field called
> > event_at_utc which is a timestamptz type. I need to calculate the end
> > time of the event given the interval calculated from the duration
> > (varchar) field.
> >
> > Any all all help is greatly appreciated.
> >
> > Shawn
>
> select '2008-03-26 09:21:44':: timestamptz + (('134987'::int/1000)*
> interval '1 second'); ?column?
>
> 2008-03-26 09:23:58-07
> (1 row)
>
> --
> Adrian Klaver
> [EMAIL PROTECTED]
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
-- Forwarded Message: --
From: [EMAIL PROTECTED] (Adrian Klaver)
To: Shawn <[EMAIL PROTECTED]>
Subject: Re: [SQL] Part 1 of several - Converting a varchar to an interval
Date: Wed, 26 Mar 2008 16:16:58 +
>
>
>
>
> -- Original message --
> From: Shawn <[EMAIL PROTECTED]>
> > Hello,
> >
> > I have several large tables, over 100 million records each. One of the
> > fields
> > is callee 'duration'. It is a varchar that contains what is essentially an
> > integer that is the duration of an event in milleseconds. Could someone
> > tell
> > me a simple way to convert a value such as 134987 stored in a varchar into
> > an
> > interval?
> >
> > This will dovetail with my next question.
> >
> > Thanks for all the help both now and previous.
> >
> > Shawn
> >
>
select ('134987'::int/1000.00) * interval ' 1 second' ;
?column?
--
00:02:14.987
(1 row)
Forgot to Reply All.
--
Adrian Klaver
[EMAIL PROTECTED]
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
[EMAIL PROTECTED] (Adrian Klaver) writes:
> select ('134987'::int/1000.00) * interval ' 1 second' ;
> ?column?
> --
> 00:02:14.987
> (1 row)
Or even easier:
regression=# select 134987 * interval '1 msec';
?column?
--
00:02:14.987
(1 row)
regards, tom lane
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
-- Original message --
From: Tom Lane <[EMAIL PROTECTED]>
> [EMAIL PROTECTED] (Adrian Klaver) writes:
> > select ('134987'::int/1000.00) * interval ' 1 second' ;
> > ?column?
> > --
> > 00:02:14.987
> > (1 row)
>
> Or even easier:
>
> regression=# select 134987 * interval '1 msec';
>?column?
> --
> 00:02:14.987
> (1 row)
>
>
> regards, tom lane
>
That's the secret, I tried '1 millisecond' . Should have dug further.
--
Adrian Klaver
[EMAIL PROTECTED]
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
-- Original message --
From: Tom Lane <[EMAIL PROTECTED]>
> [EMAIL PROTECTED] (Adrian Klaver) writes:
> > select ('134987'::int/1000.00) * interval ' 1 second' ;
> > ?column?
> > --
> > 00:02:14.987
> > (1 row)
>
> Or even easier:
>
> regression=# select 134987 * interval '1 msec';
>?column?
> --
> 00:02:14.987
> (1 row)
>
>
> regards, tom lane
I just tried this and got the following which is also what I got when using
'millisecond'
Select 134987 * interval '1 msec';
ERROR: invalid input syntax for type interval: "1 msec"
SELECT version();
version
PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3 (Ubuntu
4.0.3-1ubuntu5)
--
Adrian Klaver
[EMAIL PROTECTED]
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
[EMAIL PROTECTED] (Adrian Klaver) writes: > Select 134987 * interval '1 msec'; > ERROR: invalid input syntax for type interval: "1 msec" > SELECT version(); > version > > PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3 > (Ubuntu 4.0.3-1ubuntu5) 8.2.3 is your problem --- this is fixed in 8.2.5 and up: http://archives.postgresql.org/pgsql-committers/2007-05/msg00345.php regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Finding all References to a Primary Key
I'm sorry if this is has been discussed, but I tried to find the answer in the archives and failed, so... How do I find all the rows in other tables that reference a specific row in another table? I'm only trying to find rows that are in tables where there is a Foreign Key referencing the primary key of the table in question. Example: Table People has a primary key of people_id There are say 20 tables that have foreign keys referencing people.people_id How do I find all the rows in all of those 20 tables that reference a particular person in the people table? Thank you in advance, Matthew O'Connor -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Bizarre sort order
Does anyone think this is the correct behavior? adaps_db=# select * from upc_usage order by 1 ; usage -- 53E ABC CYPHER _GENERAL H66 HAWK _JOE RSRA S61 S65 S70 S76 S92 XWING (14 rows) It appears to be ignoring the underscore! Database has LATIN1 encoding and was recently migrated from 8.0 to 8.3. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Bizarre sort order
"Conlon, Joseph F SIK" <[EMAIL PROTECTED]> writes: > Does anyone think this is the correct behavior? If you don't think so, you need to change to C locale. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
-- Original message -- From: Tom Lane <[EMAIL PROTECTED]> > [EMAIL PROTECTED] (Adrian Klaver) writes: > > Select 134987 * interval '1 msec'; > > ERROR: invalid input syntax for type interval: "1 msec" > > > SELECT version(); > > version > > > > > > PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3 > (Ubuntu 4.0.3-1ubuntu5) > > 8.2.3 is your problem --- this is fixed in 8.2.5 and up: > http://archives.postgresql.org/pgsql-committers/2007-05/msg00345.php > > regards, tom lane Note to self: Keep up with bug fixes. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
On Wednesday 26 March 2008 17:14:28 Tom Lane wrote: > Or even easier: > > regression=# select 134987 * interval '1 msec'; > ?column? > -- > 00:02:14.987 > (1 row) > > > regards, tom lane Tom and Adrian, i am trying to incorporate the solution you gave into a function, trying to save some typing. Its keeps throwing a syntax error: edacs=# create or replace function dur_interval_msec(char) returns interval as 'select ($1 * interval '1 msec');' language sql immutable returns null on null input; ERROR: syntax error at or near "1" LINE 2: as 'select ($1 * interval '1 msec');' ^ obviously it doesn't like the extra single quotes around the 1 msec. Any suggestions for a work around? Shawn -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
On Wed, Mar 26, 2008 at 5:45 PM, Shawn <[EMAIL PROTECTED]> wrote: > obviously it doesn't like the extra single quotes around the 1 msec. Any > suggestions for a work around? Use dollar quoting, e.g.: create or replace function dur_interval_msec(int) returns interval as $$ select ($1 * interval '1 msec'); $$ language sql immutable returns null on null input; -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
On Wednesday 26 March 2008 23:12:07 Rodrigo E. De León Plicet wrote: > Use dollar quoting, e.g.: > > create or replace function dur_interval_msec(int) returns interval > as > $$ > select ($1 * interval '1 msec'); > $$ > language sql > immutable > returns null on null input; Perfect! Great! Thank you! There are so many things about SQL I don't know Shawn -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Dropping Functions
Hi Guys, Just a quick question. Is there a known problem with dropping function in 8.2.6? I can make them but they can't be deleted. Shawn -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Dropping Functions
On Wednesday 26 March 2008 5:05 pm, Shawn wrote: > Hi Guys, > > Just a quick question. Is there a known problem with dropping function in > 8.2.6? I can make them but they can't be deleted. > > Shawn They will not deleted if something else depends on them i.e. a trigger. Can you show the error message if any? -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Dropping Functions
On Thursday 27 March 2008 00:13:26 Adrian Klaver wrote: > On Wednesday 26 March 2008 5:05 pm, Shawn wrote: > > Hi Guys, > > > > Just a quick question. Is there a known problem with dropping function > > in 8.2.6? I can make them but they can't be deleted. > > > > Shawn > > They will not deleted if something else depends on them i.e. a trigger. > Can you show the error message if any? > -- > Adrian Klaver > [EMAIL PROTECTED] Thanks Adrian, Sorry to bother you, I found it. I had 2 _ characters in the name and I was trying to delete with only 1. Talk about bonehead Shawn -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
