[SQL] COPY to table with array columns (Longish)

[Phillip Smith]

Hi All,

 

Hope someone can help me – our main company system
runs on Raining Data PICK/D3 (if anyone familiar with it) which stores records
in it’s “tables” as variable length items. Every item has a
unique Primary Key (per table) then each item can have a variable number of
fields. These fields are delimited by Char 254, then each field can have
sub-values delimited by Char 253, then sub-sub-values delimited by Char 252.

 

Anyway, we are trying to export everything to Postgres for
reporting and querying etc (not to actually run the system…. Yet) and
hasn’t been a problem so far – everything like stock and purchase
orders, sales orders etc can pretty easily be turned in to a flat file with
standard number of columns and consistent data. We truncate every table each
night then import that latest TSV export from D3 using a COPY command.

 

The problem arises with tables like our SYS table which
store generic system data, so one record could have 3 fields, but the next
could have 300. The only way I can work out how to export multi-valued data like
this to Postgres is to use an array column. So the table has 2 columns –
the pkey and a data array.

 

How do I get this imported to the truncated table each
night? At the moment I think my best option is to modify the export for the SYS
table to call PSQL and use standard SQL INSERT statements to directly insert it
instead of exporting to a flat file, then import to Postgres.

 

Thanks all,

-p

 

For those who are interested, or if it might help, here’s
a rough comparison of the database structure of D3:

Windows   = PICK/D3 = Postgres

Drive     = Account = Database

Directory     = File    = Table

File      = Item    = Row

Line in text file = Attribute   = Field

(none)    = Value   = Array
Element (?)

(none)    = Sub
Value   = (none?)

 

Phillip Smith

IT Coordinator

Weatherbeeta P/L

8 Moncrief Rd

Nunawading, VIC, 3131

AUSTRALIA

 

E. [EMAIL PROTECTED]

 





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] COPY to table with array columns (Longish)

[Phillip Smith]

No problem!

 

SYS  'ZKCOST' size = 21

01 2750

This is a simple record in the SYS file.
Primary Key is “ZKCOST” and the Data would be 1 element with a
value of 2750.

 

SYS  'ZPRECMPL' size =
2069 
O

01 928898

02 928899

03 928900

04 928901

05 928902

06 928903

07 928904

08 928907

09 928908

10 928909

11 928910

12 928915

13 928916

14 928917

15 928918

16 928919

17 928920

18 928921

19 928925

20 928926

21 928927



Another SYS record – this is a list
of invoices that are waiting to be confirmed and therefore obviously constantly
change with additions and deletions. So in the SQL this would need to have a
Primary Key of “ZPRECMPL” and 21 elements in the array, each with
an invoice number… (well, there’s actually 293 in there at the
moment, but I don’t think I need to fill up the e-mail with all of them!)

 

The whole sys file is variable length
records like this – they range from 1 to over 17,000 fields per record.

 

Hope this helps,

Thanks,

-p

 

-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Aaron
Bono
Sent: Tuesday, 13 June 2006 12:36 AM
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table
with array columns (Longish)

 

Can
you provide an example?

Thanks,
Aaron



On 6/11/06, Phillip Smith <[EMAIL PROTECTED] >
wrote:







Hi All,

 

Hope someone can help me – our main company
system runs on Raining Data PICK/D3 (if anyone familiar with it) which stores
records in it's "tables" as variable length items. Every item has a
unique Primary Key (per table) then each item can have a variable number of
fields. These fields are delimited by Char 254, then each field can have
sub-values delimited by Char 253, then sub-sub-values delimited by Char 252.

 

Anyway, we are trying to export everything to
Postgres for reporting and querying etc (not to actually run the system….
Yet) and hasn't been a problem so far – everything like stock and
purchase orders, sales orders etc can pretty easily be turned in to a flat file
with standard number of columns and consistent data. We truncate every table
each night then import that latest TSV export from D3 using a COPY command.

 

The problem arises with tables like our SYS table
which store generic system data, so one record could have 3 fields, but the
next could have 300. The only way I can work out how to export multi-valued
data like this to Postgres is to use an array column. So the table has 2
columns – the pkey and a data array.

 

How do I get this imported to the truncated table
each night? At the moment I think my best option is to modify the export for
the SYS table to call PSQL and use standard SQL INSERT statements to directly
insert it instead of exporting to a flat file, then import to Postgres.

 

Thanks all,

-p

 

For those who are interested, or if it might help,
here's a rough comparison of the database structure of D3:

Windows
  =
PICK/D3 = Postgres

Drive  
  =
Account = Database

Directory  
  =
File    = Table

File 
   
= Item   
= Row

Line in text file
= Attribute   = Field

(none) 
  =
Value   = Array
Element (?)

(none) 
  = Sub Value  
= (none?)

 

Phillip Smith

IT Coordinator

Weatherbeeta P/L

8 Moncrief Rd

Nunawading, VIC, 3131

AUSTRALIA

 

E. [EMAIL PROTECTED]


 













***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] COPY to table with array columns (Longish)

[Phillip Smith]

The export from the D3 system is written
in PICK BASIC – similar to ‘normal’ BASIC.

 

This currently exports Attribute (field) 1
to 9 of each SYS record with a Primary Key starting with “Z” (‘]’
is a wildcard in the SSELECT statement)

153 EXECUTE 'SSELECT SYS WITH A0 "Z]"' CAPTURING JUNK

154 LOOP

155    READNEXT SYS.ID THEN

156   READ SYS.REC FROM SYS,
SYS.ID ELSE

157  SYS.ID =
'XX'

158   END

159   *

160   OUTLINE = SYS.ID

161   FOR SYS.SUB = 1 TO 9

162  OUTLINE =
OUTLINE:AM:SYS.REC

163   NEXT SYS.SUB

164   *

165   CONVERT CHAR(252) TO
"" IN OUTLINE

166   CONVERT CHAR(92) TO
"" IN OUTLINE

167   CONVERT CHAR(34) TO
"" IN OUTLINE

168   OUTLINE = OCONV(OUTLINE,
"MCU")

169   N=%FPUTS(OUTLINE:NL,
(CHAR*)STREAM)

170    END ELSE

171   SYS.ID = 'XX'

172    END

173 490 NULL

174 UNTIL SYS.ID = 'XX' DO REPEAT

 

So you’re suggesting creating a
child table for each SYS record? Ie, a table called “ZPRECMPL” etc?

 

Thanks for your input guys,

Cheers,

-p

 

-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Aaron
Bono
Sent: Tuesday,
 13 June 2006 12:58
 PM
To: Tom
  Lane
Cc: [EMAIL PROTECTED];
pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table
with array columns (Longish)

 

I
agree with Tom.  Personally I cannot think of a time I would use an array
column over a child table.  Maybe someone can enlighten me on when an
array column would be a good choice.

What language are you using to do the export if I may ask? 

-Aaron



On 6/12/06, Tom Lane <[EMAIL PROTECTED]> wrote:

"Phillip Smith"
<[EMAIL PROTECTED]>
writes:
> The whole sys file is variable length records like this - they range =
> from 1
> to over 17,000 fields per record. 

17000?  I think you really need to rethink your
schema.  While you could
theoretically drop 17000 elements into a PG array column, you wouldn't
like the performance --- it'd be almost unsearchable for instance. 

I'd think about two tables, one with a single row for each SYS record
from the original, and one with one row for each detail item (the
invoice numbers in this case).  With suitable indexes and a foreign
key 
constraint, this will perform a lot better than an array-based
translation.

And no, in neither case will you be able to import that file without
massaging it first.

regards,
tom lane 



 





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] COPY to table with array columns (Longish)

[Phillip Smith]

Not quite... ZKCOST and ZPRECMPL are two completely different things. They
have no relation except they're both stored in the SYS table in D3.

If we put it in a tree:
SYS
 |
 +- ZKCOST
 | \- 
 |
 +- ZPRECMPL
 | +- 
 | +- 
 | +- 
 | \- 

or table:
SYS
+---+-+-+-+-+
| ZKCOST|  | | | |
| ZPRECMPL  |  |  |  |  |
+---+-+-+-+-+

So other than a variable-element array, the only other way would be to
create a table with a column count equal to or greater than the maximum
amount of values (call that value 'Y') that any sys item holds then if a
particular record (eg, ZKCOST) has less values than Y, fill the rest of the
columns with blanks (as above).

That's what I've done at the moment, but only for 9 columns, so anything
over 9 fields will be truncated past and including field 10:
wbau=# \d sys
Table "public.sys"
 Column | Type | Modifiers
+--+---
 a0 | text | not null
 a1 | text |
 a2 | text |
 a3 | text |
 a4 | text |
 a5 | text |
 a6 | text |
 a7 | text |
 a8 | text |
 a9 | text |
Indexes:
"id" PRIMARY KEY, btree (a0)

a0 = primary key - eg, ZPRECMPL or ZKCOST

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Aaron Bono
Sent: Tuesday, 13 June 2006 2:12 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table with array columns (Longish)

I think two tables should suffice: ZKCOST and ZPRECMPL.

So you would have

ZKCOST
zkcost_id,
zkcost_value

and

ZPRECMPL
zkcost_id,
zprecmpl_id,
zprecmpl_value

where zkcost_id is the primary key for ZKCOST and zkcost_id,
zprecmpl_id together are the primary key for ZPRECMPL and zkcost_id is
a foreign key from ZPRECMPL to ZKCOST.

That will work won't it?

-Aaron

On 6/12/06, Phillip Smith <[EMAIL PROTECTED]> wrote:

> So you're suggesting creating a child table for each SYS record? Ie, a
table called "ZPRECMPL" etc?

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

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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

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

Re: [SQL] COPY to table with array columns (Longish)

[Phillip Smith]

Thanks Aaron - There are currently 8175 records in my SYS file - I might
need to go with this approach but be selective about which items I export so
I don't end up with 8000 tables related to SYS! There's probably a lot of
 in there that doesn't actually need to be exported.

Thanks again,
-p

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Aaron Bono
Sent: Wednesday, 14 June 2006 1:05 AM
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table with array columns (Longish)

So how about creating a sys table too:

SYS
   sys_id

ZKCOST
   sys_id,
   zkcost_id,
   zkcost_value

and

ZPRECMPL
   sys_id,
   zprecmpl_id,
   zprecmpl_value

This gives you the flexibility to expand to as many "columns" for
ZPRECMPL as you want.  The bottom line is, I think it would be much
more efficient storage to determine a way to turn your variable number
of columns into rows of a value table.

For example, I have a web site for role playing games.  Since each
game has different attributes for the characters you play, I need a
flexible way to define the list of attributes and then allow people to
enter the values of those attributes.  Below is a simplified version
of my table structure:

attribute
   attribute_id (PK),
   attribute_name

character
   character_id (PK),
   character_name

character_attribute
   character_attribute_id (PK),
   character_id (FK),
   attribute_id (FK),
   value

It is a little different than your problem but demonstrates how a
variable number of columns (in this case a variable number of
attributes for a character) can be stored with one row representing
each column.

Because I don't understand the context of your problem as well as you
do, you will probably have to determine how to tweak this to meet your
needs.  But I think, from the information you have provided, that this
"pivoted" table approach will work for you.

-Aaron

On 6/13/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
> Not quite... ZKCOST and ZPRECMPL are two completely different things. They
> have no relation except they're both stored in the SYS table in D3.
>
> If we put it in a tree:
> SYS
>  |
>  +- ZKCOST
>  | \- 
>  |
>  +- ZPRECMPL
>  | +- 
>  | +- 
>  | +- 
>  | \- 
>
> or table:
> SYS
> +---+-+-+-+-+
> | ZKCOST|  | | | |
> | ZPRECMPL  |  |  |  |  |
> +---+-+-+-+-+

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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] COPY to table with array columns (Longish)

[Phillip Smith]

No, it was me that didn't understand!! But I do now - quite simple really!

Cheers,
-p

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Aaron Bono
Sent: Wednesday, 14 June 2006 10:41 AM
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table with array columns (Longish)

I guess I still don't understand...

If you take the approach operationsengineer1 and I suggested, you
should only need 3 or 4 tables regardless of the number of SYS file
records.

Good luck with your implementation.

-Aaron

On 6/13/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
> Thanks Aaron - There are currently 8175 records in my SYS file - I might
> need to go with this approach but be selective about which items I export
so
> I don't end up with 8000 tables related to SYS! There's probably a lot of
>  in there that doesn't actually need to be exported.
>
> Thanks again,
> -p

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

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

Re: [SQL] to_char with time

[Phillip Smith]

Try using current_timestamp instead of current_time.
This works for me in a SELECT:

to_char(current_timestamp + '1 MONTH AGO', 'YYMM') as ‘reference’

 

Cheers,

-p

 

-Original Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of chester
c young
Sent: Wednesday,
 14 June 2006 2:10
 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] to_char with time

 

this does not work:
select to_char(current_time,'HH24:MI')

what am I missing?  is it possible to format a time column in a select?

thanks,
stumped, aka, chester

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





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] listen_addresses = '*' ok, specific address(es) no (.... and a thread hi-jack!)

[Phillip Smith]

Hi Geoff,

Listen_addresses means what local interface to listen to connections - ie,
if you have 2 network interfaces (cards) in the machine that go to 2
different networks - such as one to the internet and one to your LAN, you
could tell Postgres to only listen on the LAN interface for connections so
it won't accept connections from anything on the internet. What you're after
would be better done by a firewall (ipchains / iptables)


I've just installed Postgres 8.1 on RedHat 7.1 and I'm getting the error:
"2006-06-16 14:49:00 NZST @ []LOG:  could not create IPv6 socket: Address
family not supported by protocol"

RedHat 7.1 does not support IPv6, but I don't need it - how can I disable
it? I've set my listen_addresses to:
listen_addresses = '172.23.0.1'

Yes, I do have a local address 172.23.0.1 as per output from ifconfig:
[EMAIL PROTECTED] pgsql]$ ifconfig eth0
eth0  Link encap:Ethernet  HWaddr 00:20:ED:38:EB:F4
  inet addr:172.23.0.1  Bcast:172.23.255.255  Mask:255.255.0.0
  UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
  RX packets:2548578 errors:0 dropped:0 overruns:0 frame:0
  TX packets:2479774 errors:0 dropped:0 overruns:1 carrier:0
  collisions:0 txqueuelen:100
  Interrupt:18 Base address:0xe000 Memory:e0998000-e0998c40



Cheers,
-p


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Geoffrey Knauth
Sent: Friday, 16 June 2006 12:06 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] listen_addresses = '*' ok, specific address(es) no 

Tom,

I omitted the LOG and HINT lines before.

LOG:  could not bind IPv4 socket: Can't assign requested address
HINT:  Is another postmaster already running on port 5432? If not,  
wait a few seconds and retry.
WARNING:  could not create listen socket for "192.168.1.33"
FATAL:  could not create any TCP/IP sockets

This works fine if I use '*' instead of '192.168.1.33'.

Andrew Sullivan wrote:
> Well, do you actually have an interface with that address?

I think I do, in that the machine's wireless interface is set up with  
a 192.168.1.x/24 address and 1.33 is on the same subnet.  Or maybe  
I'm misunderstanding.  I thought the purpose of listen_addresses was  
to allowing incoming connections only from listed addresses.

Geoff

On Jun 15, 2006, at 10:40, Tom Lane wrote:

> Geoffrey Knauth <[EMAIL PROTECTED]> writes:
>> I'm running PostgreSQL 8.1.3.  In my postgresql.conf, the following
>> works:
>> listen_addresses = '*'
>
>> but the following does not:
>> listen_addresses = '192.168.1.33'
>
>> I get an error:
>> WARNING:  could not create listen socket for "192.168.1.33"
>> FATAL:  could not create any TCP/IP sockets
>
> There should be more info than that --- AFAICS all the failure  
> paths in
> that code emit LOG messages.  Perhaps you have log_min_messages set  
> too
> high to allow the info to come out?

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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(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] listen_addresses = '*' ok, specific address(es) no

[Phillip Smith]

quote:
"The right way to limit incoming connections to only come *from*
particular IP addresses is to use pg_hba.conf."

Apologies Geoff - that would be the easier way rather than ipchains /
iptables.

-p



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Tom Lane
Sent: Friday, 16 June 2006 12:47 PM
To: Geoffrey Knauth
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] listen_addresses = '*' ok, specific address(es) no 

Geoffrey Knauth <[EMAIL PROTECTED]> writes:
> Andrew Sullivan wrote:
>> Well, do you actually have an interface with that address?

> I think I do, in that the machine's wireless interface is set up with  
> a 192.168.1.x/24 address and 1.33 is on the same subnet.  Or maybe  
> I'm misunderstanding.  I thought the purpose of listen_addresses was  
> to allowing incoming connections only from listed addresses.

You're misunderstanding then.  What listen_addresses can bind to is IP
addresses of *your own machine*.  For example, if you bind to only
127.0.0.1 then only local loopback connections will work.  Binding to
just one external IP address is only interesting if your machine has
more than one such address; then it prevents connections that're coming
in through one of the other addresses.

The right way to limit incoming connections to only come *from*
particular IP addresses is to use pg_hba.conf.

regards, tom lane

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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

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

Re: [SQL] listen_addresses = '*' ok, specific address(es) no (.... and a thread hi-jack!)

[Phillip Smith]

Dang - Our NZ operations are a lot smaller than ours. They only have the one
Linux server and it's primary role is to run the D3 gear I was talking about
the other day - same in Australia!! I am trying to get the number crunchers
to fork out the $$$ for RH ES4 and a nice new server.

Thanks Tom, I'll put up with the errors for now until I can get them a new
server.
-p


-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Friday, 16 June 2006 1:04 PM
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] listen_addresses = '*' ok, specific address(es) no (
and a thread hi-jack!) 

"Phillip Smith" <[EMAIL PROTECTED]> writes:
> 
> I've just installed Postgres 8.1 on RedHat 7.1

Uh ... *why*?  I wouldn't use RH 7.1 today any more than I'd use PG 7.1 ...

> and I'm getting the error:
> "2006-06-16 14:49:00 NZST @ []LOG:  could not create IPv6 socket: Address
> family not supported by protocol"

It seems you've got libc code that supports IPv6 even though your kernel
does not (else PG would not have been told that an IPv6 address was a
possible translation of "localhost").  PG copes with this but will issue
LOG messages complaining about it.  If you don't like the warnings, fix
your system so it's all on the same page about whether IPv6 is supported.

regards, tom lane


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

[SQL] SELECT Aggregate

[Phillip Smith]

Hi all,

I have two tables which are storing all our sales orders /
invoices as below. sales_order.trans_no and soh_product.soh_num are the common
columns. This is PostgreSQL 8.1.4 (ie, the latest release)

 

We have some issues that I’ve been able to identify
using this SELECT:

SELECT  trans_no,

    customer,

    date_placed,

    date_complete,

    date_printed,

    ord_type,

    ord_status,

    customer_reference,

    salesman,

    parent_order,

    child_order,

    order_number

FROM    sales_orders

WHERE   (trans_no Like
'8%' AND order_number Like '8%')

 OR (trans_no
Like '9%' AND order_number Like '8%')

 OR     (trans_no
Like '8%' AND order_number Like '9%')

 OR     (trans_no
Like '9%' AND order_number Like '9%')

 AND    (warehouse='M')

 AND    (date_placed
> (current_date + ('12 months ago'::interval)))

ORDER BY trans_no DESC

 

But I want to add in a wholesale value of each order –
SUM(soh_product.sell_price) – How would be best to do this? Would it be
easiest to create a function to accept the trans_no then do a SELECT on
soh_product and return that value?

 

Thanks,

-p

 

I’ve tried to do this but Postgres complains about
having to include all the other columns in either an aggregate or the GROUP BY.

SELECT  trans_no,

    customer,

    date_placed,

    date_complete,

    date_printed,

    ord_type,

    ord_status,

    SUM(soh_product.sell_price),

    customer_reference,

    salesman,

    parent_order,

    child_order,

    order_number

FROM    sales_orders,
soh_product

WHERE   (trans_no Like
'8%' AND order_number Like '8%')

 OR (trans_no
Like '9%' AND order_number Like '8%')

 OR     (trans_no
Like '8%' AND order_number Like '9%')

 OR     (trans_no
Like '9%' AND order_number Like '9%')

 AND    (warehouse='M')

 AND    (sales_orders.trans_no
= soh_product.soh_num)

 AND    (date_placed
> (current_date + ('12 months ago'::interval)))

GROUP BY soh_product.soh_num

ORDER BY trans_no DESC

 

CREATE TABLE sales_orders

(

  trans_no varchar(6) NOT NULL,

  customer varchar(6),

  date_placed date,

  date_complete date,

  date_printed date,

  ord_type varchar(1),

  ord_status varchar(1),

  discount float8,

  customer_reference text,

  warehouse varchar(3),

  salesman varchar(3),

  username text,

  ordered_value float8 DEFAULT 0,

  supplied_value float8 DEFAULT 0,

  ordered_qty int8,

  supplied_qty int8 DEFAULT 0,

  frieght float8 DEFAULT 0,

  delivery_instructions text,

  parent_order varchar(6),

  child_order varchar(6),

  apply_to_order varchar(6),

  fo_release date,

  order_number varchar(6),

  orig_fo_number varchar(6),

  CONSTRAINT soh_pkey PRIMARY KEY (trans_no)

)

CREATE TABLE soh_product

(

  soh_num varchar(6) NOT NULL,

  prod_code varchar(6) NOT NULL,

  qty_ordered numeric(8),

  qty_supplied numeric(8),

  cost_price numeric(10,2),

  sell_price numeric(10,2),

  sales_tax numeric(10,2),

  discount numeric(10,2),

  cost_gl varchar(5),

  if_committed varchar(1)

)





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] SELECT Aggregate

[Phillip Smith]

I’ve tried Aaron’s suggestion of
the GROUP BY and I don’t know much about it, but it ran for around 17
hours and still going (it had a dedicated Dual Xeon 3.0GHz box under RHEL4 running
it!)

 

I’ll give Richard’s suggestion
a try and see if that comes up any better. Like I said yesterday, this might just
be too much for Postgres and I’ll need to summarize it in the export from
our live system and add a new column before I import it to the sales_orders
table

 

Cheers,

-p

 

-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Aaron
Bono
Sent: Friday,
 30 June 2006 01:25
To: Richard Broersma Jr
Cc: Phillip Smith;
pgsql-sql@postgresql.org
Subject: Re: [SQL] SELECT
Aggregate

 

I am
not familiar enough with how postgres optimizes the queries but won't this end
up with 

total number of queries run on DB = 1 query + 1 query/row in first query

What would be more efficient on a large database - a query like Richard
submitted (subquery in the select) or one like I submitted (join the two tables
and then do a group by)?  My guess is it depends on the % of records
returned out of the sales_orders table, the smaller the % the better Richard's
query would perform, the higher the % the better the join would run. 

The database I am working with aren't big enough yet to warrant spending a lot
of time researching this but if someone with more experience knows what is best
I would love to hear about it.

Thanks,
Aaron Bono 



On 6/29/06, Richard Broersma Jr <[EMAIL PROTECTED]>
wrote:

>
SELECT  trans_no,
>
customer,
>
date_placed,
>
date_complete,
>
date_printed,
>
ord_type,
>
ord_status,
  select
(

SUM(sell_price)

from soh_product

where sales_orders.trans_no = soh_product.soh_num
  )
as transact_sum, 
>
customer_reference,
>
salesman,
>
parent_order,
>
child_order,
>
order_number
> FROMsales_orders
> WHERE   (trans_no Like '8%' AND
order_number Like '8%') 
>  OR (trans_no
Like '9%' AND order_number Like '8%')
>  OR (trans_no
Like '8%' AND order_number Like '9%')
>  OR (trans_no
Like '9%' AND order_number Like '9%')
>  AND(warehouse='M')

>  AND(date_placed
> (current_date + ('12 months ago'::interval)))
> ORDER BY trans_no DESC







***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] SELECT Aggregate

[Phillip Smith]

Well whatdyaknow?? Being
a Postgres newbie I hadn't even played with indexes yet. They're awesome!!

 

Using Richard's
suggestion of the Sub-Select in the COLUMN list, combined with adding some
indexes, I can now return this in under 5 seconds!

 

I’ve included the
new SELECT query, as well as the definitions of the indexes below for anyone who’s
interested.

 

Thanks guys!

 

QUERY:

SELECT  trans_no,

    customer,

    date_placed,

    date_complete,

    date_printed,

    (SELECT  SUM(sell_price)


FROM   soh_product


WHERE  sales_orders.trans_no = soh_product.soh_num


) AS wholesale,

    ord_type,

    ord_status,

    customer_reference,

    salesman,

    parent_order,

    child_order,

    order_number

FROM    sales_orders

WHERE   (trans_no Like '8%' AND order_number
Like '8%')

 OR     (trans_no Like '9%'
AND order_number Like '8%')

 OR     (trans_no Like '8%'
AND order_number Like '9%')

 OR     (trans_no Like '9%'
AND order_number Like '9%')

 AND    warehouse='M'

 AND    date_placed > (current_date
+ ('12 months ago'::interval))

ORDER BY    trans_no DESC

 

INDEXES:

CREATE INDEX sales_orders_customer

  ON sales_orders

  USING btree

  (customer);

 

CREATE INDEX sales_orders_orderno

  ON sales_orders

  USING btree

  (order_number);

 

CREATE INDEX sales_orders_customer

  ON sales_orders

  USING btree

  (customer);

 

CREATE INDEX soh_product_prodcode

  ON soh_product

  USING btree

  (prod_code);

 

CREATE INDEX soh_product_transno

  ON soh_product

  USING btree

  (soh_num);

 

 

-Original Message-
From: Richard Broersma Jr [mailto:[EMAIL PROTECTED] 
Sent: Friday, 30 June 2006 10:51
To: Phillip Smith; pgsql-sql@postgresql.org
Subject: Re: [SQL] SELECT Aggregate

 

> I've tried Aaron's suggestion of the GROUP BY and I don't know
much about

> it, but it ran for around 17 hours and still going (it had a
dedicated Dual

> Xeon 3.0GHz box under RHEL4 running it!)

 

Maybe, this query that you are trying to run is a good candidate for a
"Materialize View".

http://archives.postgresql.org/pgsql-performance/2004-02/msg00279.php

 

Also before you run your query you might want to see the explain plan
is.  Perhap it is using a

sequencial scan in a place where an index can improve query
preformance.

 

 





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] join two tables with sharing some columns between two

[Phillip Smith]

If I understand correctly... I think this should work:

SELECT  table1.id,
table1.person_name,
table2.car_description,
table1.date_arrival,
table1.date_departure
FROMtable1, table2
WHERE   table1.id = table2.id
;

Assuming the date_arrival and date_departure fields are the same on both
tables then it doesn't matter if you pull them from table1 or table2,
otherwise you'll need to change the SELECT appropriately.

If you want it to go in to a new table as well as opposed to just a query,
you could have a play with SELECT INTO...

Cheers,
-p

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of filippo
Sent: Monday, 26 June 2006 23:39
To: pgsql-sql@postgresql.org
Subject: [SQL] join two tables with sharing some columns between two

Hi,

I have two tables like these: (this is an example, the actual tables
have diffferent fields and meanings)

TABLE1
id
person_name
date_arrival
date_departure

TABLE2
id
car_description
date_arrival
date_departure

I'd like to make a query to have such resulting table

RESULTING_TABLE
id
person_name
car_description
date_arrival
date_departure

the id is the primary key for the three tables and it's unique for the
three (id in table1 cannot be in table2, I use the same counter to
generate the id for table1 and table2).


I is possible to create such a query?

Thanks


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[SQL] Select Maths

[Phillip Smith]

Hi again,

 

Same SELECT query as before, different area
of it… I have a function that calculates the recommended purchase order
quantity for a stock item based off various other values and functions:

pqty(stock.code) AS "pqty"

 

This needs to be rounded up / down to the
nearest multiple of the purchase unit quantity for that product –
It’s Friday afternoon and my head has refused to help me work out the
maths all afternoon!

 

Example:

 Pqty
= 60

 Purchase
Unit = 25

Pqty needs to be rounded down to 50.

 

I guess I’m also asking if I should
do this in the Pqty function or in the SELECT query to optimize the result?

 

Thanks all – Enjoy your weekends I
hope!

Cheers,

-p





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

[SQL] Select CASE Concatenation

[Phillip Smith]

Hi All – Smee again!

 

Two questions but they’re unrelated so I’ll make
2 posts to keep it clean!

 

Number one (and I think is the easier one)…

 

I have a SELECT statement, part of which is a “Flags”
column which is a CASE function, but I need to be able to concatenate the
results together. Example: in the below, I need to be show both “@”
and “K” if both of the CASE blocks are true… Possible?

 

 CASE WHEN stkeoq(stock.code)
= -1 THEN '@'

   WHEN
stock.kit_pack = 'Y' THEN 'K'

 END AS "flags",

 

Note: “stkeoq” is a function

 

The actual CASE is going to end up with 7 individual tests
and therefore 7 difference flags that I’ll need to test and concatenate
all the true ones…

 

Thanks,

-p





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Select Maths

[Phillip Smith]

Beautiful – Works a treat. Thanks
Aaron.

 

A follow-on problem now… I have the
below column in the select, but I need to validate the value across all 3 rules
–  I need to assign it to a variable!!

 

Example – my pqty function
calculates a value less than the suppliers minimum order qty (and therefore
fails the first CASE below), I need to set the column to a new value (stock.purchase_unit)
– That’s all OK. But I need to check this new value against the
remaining 2 CASE’s…

 



CASE  WHEN pqty(stock.code)
< stock.purchase_unit THEN stock.purchase_unit

    --^^^--
Check that our suggested purchase qty is greater than then suppliers minimum
order qty

  WHEN
MOD(pqty(stock.code), stock.box_qty) > 0 THEN stock.box_qty * ROUND(CAST(pqty(stock.code)
AS DOUBLE PRECISION) / stock.box_qty)

    --^^^--
Check that our suggested purchase qty is a multiple of the box qty

  WHEN
pqty(stock.code) < (urate(stock.code) * creditors.review_cycle) THEN urate(stock.code)
* creditors.review_cycle

    --^^^--
Check that our suggested purchase qty is greater than our Usage Rate x Creditor
Review Cycle

END AS "pqty",



 

Thanks again for all your help guys,

-p

 

-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Aaron
Bono
Sent: Friday,
 7 July 2006 18:37
To: Phillip Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Select Maths

 

On 7/7/06, Phillip Smith <[EMAIL PROTECTED]>
wrote:











Same SELECT query as before, different area
of it… I have a function that calculates the recommended purchase order
quantity for a stock item based off various other values and functions:

pqty(stock.code) AS "pqty"

 

This needs to be rounded up / down to the
nearest multiple of the purchase unit quantity for that product –
It's Friday afternoon and my head has refused to help me work out the maths all
afternoon!

 

Example:


Pqty = 60


Purchase Unit = 25

Pqty needs to be rounded down to 50.

 

I guess I'm also asking if I should do this
in the Pqty function or in the SELECT query to optimize the result?












select 25 * round(cast(60 as double precision) / 25) 



 







***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Select Maths

[Phillip Smith]

Example:

Funcation pqty(stock.code) calculates a value of 0 for a particular product. This fails the
last CASE that makes sure the pqty() value is greater than
our Usage Rate *
Review Cycle – in this case is
3. But that is less than our Minimum Order Qty (First CASE) and not a multiple
of our Box Qty (Second CASE)

Another example could be
that pqty() calculates less than the Minimum Order Qty (fails first CASE) so
we raise it to the Minimum Order Qty, but that new value could fail either or
both of the second CASE’s.

Minimum Order Qty
= stock.purchase_unit
Box Qty = stock.box_qty

I guess
a better way to word it is that because pqty() returns a calculated
value each time and I can’t take that value and assign it to a variable,
then use that variable. If I was writing VB or similar I’d want something
like:

intPurchaseQty = pqty(stock.code)

CASE  WHEN intPurchaseQty < stock.purchase_unit THEN
intPurchaseQty = stock.purchase_unit

WHEN MOD(intPurchaseQty, stock.box_qty) > 0 THEN intPurchaseQty
= stock.box_qty * ROUND(CAST(intPurchaseQty AS DOUBLE PRECISION) /
stock.box_qty)

WHEN intPurchaseQty < (urate(stock.code) *
creditors.review_cycle) THEN intPurchaseQty = urate(stock.code) * creditors.review_cycle

END

COLUMN = intPurchaseQty AS
"pqty",

I hope
that makes it a lighter shade of mud!!

 

-Original
Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Aaron Bono
Sent: Tuesday,
 11 July 2006 02:36
To: Phillip Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Select Maths






Can you provide example values and show where it is and is not working?  I
am not quite sure what you are trying to do here. 

-Aaron 



 







***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Select Maths

[Phillip Smith]

It ties back to my other post about the “FLAGS”
column – I need to be able to find out if the original pqty() calculation has
needed to be modified.

 

I guess what you’re saying is to have 2
functions – one that calculates the figure I have at the moment, then a second
to return the adjusted figure?

 

Then I can use the first function when I’m
working out what the flags need to be, then the second to give the actual
adjusted figure….

 

-p

 

-Original
Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Aaron Bono
Sent: Tuesday,
 11 July 2006 13:42
To: Phillip Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Select Maths


Why wouldn't you be able to do this in a function?  Pass in stock.code,
stock.purchase_unit, stock.box_qty and creditors.review_cycle .  You can
then use variables in the function, right?

-Aaron 





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

[SQL] Table Join (Maybe?)

[Phillip Smith]

Hi again all,

 

I have two tables:

1. Sales figures by date and
customer.

2. Customer details –
including their Geographic State

 

I need to extract a report from the first table (I can do
that!), and in that report order by their State (I can do that too!), but I
also need a summary of all the customers in each state, below the end of each
state, and have a grand total at the bottom.

 

Eg:

Customer 1  State 1 $100.00

Customer 2  State 1 $100.00

State
1 $200.00

Customer 3  State 2 $100.00

Customer 4  State 2 $100.00

State
2 $200.00

Grand Total $400.00

 

Does anyone have any magic pointers for me? I’ve been
playing with SELECT INTO as 2 queries (the individual customers, then the
summary figures added to the temp table) but I end up with ROWS IN FIRST QUERY *
ROWS IN SECOND QUERY instead of them all sorted together nicely L

 

Thanks all,

-p

 

Table Defs:

CREATE TABLE sales_figures

(

  rep_date date NOT NULL,

  store varchar(6) NOT NULL,

  sales_value numeric DEFAULT 0,

  sales_customers int4 DEFAULT 0,

  CONSTRAINT sales_figures_pkey PRIMARY KEY (rep_date,
store),

  CONSTRAINT sales_figures_store FOREIGN KEY
(store)

  REFERENCES stores
(code) MATCH SIMPLE

  ON UPDATE NO ACTION
ON DELETE NO ACTION

)

 

CREATE TABLE stores

(

  code varchar(2) NOT NULL DEFAULT
''::character varying,

  name varchar(32) NOT NULL DEFAULT
''::character varying,

  bms varchar(1) DEFAULT 'Y'::character
varying,

  state text DEFAULT 'UNKNOWN'::text,

  business_open varchar(1) DEFAULT 'Y'::character
varying,

  CONSTRAINT stores_pkey PRIMARY KEY (code)

)

 

 

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA 

 





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

FW: [SQL] Table Join (Maybe?)

[Phillip Smith]

Thanks all for your suggestions – the
below suggestion works perfectly.

 

A little tweak of the column counts and
group by clauses makes it work like a charm.

 

Richard – this particular extract is
via PHP to a web page so I can’t use those reporting tools, but I’ll
have a look and fiddle for my next project – Thanks!

 

Cheers,

-p

 

-Original Message-
From: Arulmani V A
[mailto:[EMAIL PROTECTED] 
Sent: Wednesday,
 19 July 2006 20:49
To: Phillip Smith
Subject: Re: [SQL] Table Join
(Maybe?)

 





Hi,

Postgres does not seem to support OLAP functions like ROLLUP, CUBE, etc. I'm
not a Postgres expert, but I think we can try the following alternative
approach :

Sample data for the two tables given by you are created as :

Table : stores
code     name         bms    
state         business_open
1        Customer1   
Y        State1   
    Y
2        Customer2   
Y        State2   
    Y
3        Customer3   
Y        State1   
    Y
4        Customer4   
Y        State2   
    Y

Table : sales_figures
rep_date     store     sales_value
    sales_customers
2006-01-01    1       
1000            0
2006-01-02    1       
1000            0
2006-02-01    2       
200               
0
2006-02-02    2       
200               
0
2006-02-03    2       
500               
0
2006-01-03    1       
100               
0
2006-01-01    3       
300               
0
2006-01-02    3       
200               
0
2006-02-01    4       
700               
0
2006-02-02    4       
400               
0

If I execute the following query :
SELECT a.name, a.state, SUM(b.sales_value) FROM stores a, sales_figures b WHERE
a.code = b.store GROUP BY a.state, a.name
UNION
SELECT NULL, a.state, SUM(b.sales_value) FROM stores a, sales_figures b WHERE
a.code = b.store GROUP BY a.state
 UNION
SELECT 'GRAND TOTAL', NULL, SUM(b.sales_value) FROM stores a, sales_figures b
WHERE a.code = 
b.store ORDER BY 2

I get the following output (same as what you expect?) :

name        state    sum

Customer1    State1    2100
Customer3    State1    500
NULL        State1    2600
Customer2    State2    900
Customer4    State2    1100
NULL        State2    2000
GRAND TOTAL    NULL    4600

Is the above approach OK?

Regards
Arul



- Original Message
----
From: Phillip Smith <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Wednesday, July 19, 2006 1:04:58 PM
Subject: [SQL] Table Join (Maybe?)



Hi again all,

 

I have two tables:

1. Sales figures by date and
customer.

2. Customer details –
including their Geographic State

 

I need to extract a report from the first table (I can do
that!), and in that report order by their State (I can do that too!), but I
also need a summary of all the customers in each state, below the end of each
state, and have a grand total at the bottom.

 

Eg:

Customer 1  State
1 $100.00

Customer 2  State
1 $100.00

State 1 $200.00

Customer 3  State
2 $100.00

Customer 4  State
2 $100.00

State 2 $200.00

Grand
Total
$400.00

 

Does anyone have any magic pointers for me? I’ve been playing
with SELECT INTO as 2 queries (the individual customers, then the summary
figures added to the temp table) but I end up with ROWS IN FIRST QUERY * ROWS
IN SECOND QUERY instead of them all sorted together nicely L

 

Thanks all,

-p

 

Table Defs:

CREATE TABLE sales_figures

(

  rep_date date NOT
NULL,

  store varchar(6) NOT
NULL,

  sales_value numeric
DEFAULT 0,

  sales_customers int4
DEFAULT 0,

  CONSTRAINT
sales_figures_pkey PRIMARY KEY (rep_date, store),

  CONSTRAINT
sales_figures_store FOREIGN KEY (store)

 
REFERENCES stores (code) MATCH SIMPLE

 
ON UPDATE NO ACTION ON DELETE NO ACTION

)

 

CREATE TABLE stores

(

  code varchar(2) NOT
NULL DEFAULT ''::character varying,

  name varchar(32) NOT
NULL DEFAULT ''::character varying,

  bms varchar(1)
DEFAULT 'Y'::character varying,

  state text DEFAULT
'UNKNOWN'::text,

  business_open
varchar(1) DEFAULT 'Y'::character varying,

  CONSTRAINT
stores_pkey PRIMARY KEY (code)

)

 

 

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA 

 



 

***Confidentiality and Privilege
Notice*** 

The
material contained in this message is privileged and confidential to the
addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy or
deliver this message to anyone, and you should destroy it and kindly notify the
sender by reply email. 

Information
in this message that does not relate to the official business of Weatherbeeta
must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta,
its employees, contractors or associates shall not be liable for direct,
indirect or consequential loss arising from transmission of this message or any
attachments 



 

Re: [SQL] Importing data from csv

[Phillip Smith]

I recently did this by parsing the data
through a VB program that appended a “\” in front of any Char(10)
and/or Char(13) characters which tells Postgres to accept the next character as
a literal part of the column value I believe – must do because it worked!
I also quoted the whole column as part of the VB prog…

 

Worked for me but I’m not sure the
exact science behind it so someone else might be able to be of some more
detailed help.

 

Cheers,

-p

 

-Original
Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sumeet
Sent: Friday,
 25 August 2006 00:48
To: pgsql-sql@postgresql.org
Subject: [SQL] Importing data from
csv

 

Hi Folks,

sorry if this is a duplicate post, i've been tryin to find a solution of
importing data into postgres from a csv file. The problem is, I have a database
which consists of columns which contain newline characters (mac and unix). now
when i export these files to a csv format, there are some line breaks (mixed
unix and mac) in the data which breaks the copy procedure. 

I also tried using the script posted in one of the previous posts..

#! /usr/bin/perl
$inquotes = 0;
while (<>){
 # Chop the crlf
 chop ($_);
 chop ($_);

 # this first bit goes through and replaces 
 # all the commas that re not in  quotes with
tildes
 for ($i=0 ; $i < length($_) ; $i++){
 $char=substr($_,$i,1);
 if ($char eq '"' ){

$inquotes = not($inquotes); 
 }else{
 if (
(!$inquotes) && ($char eq ",") ){

substr($_,$i,1)="~";
 }
 }
 }
 # this replaces any quotes
 s/"//g;
 print "$_\n";
}


cat data_file | perl scriptname.pl > outputfile.dat

and when i run the copy command i get messages like data missing for xyz
column.
any possible hints... 

--
Thanks,
Sumeet 





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Importing data from csv

[Phillip Smith]

There you go – it was the quotes
that did it, not the back-slashes. I knew someone else would shed some better
light! J

 

Cheers,

-p

 

-Original Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scot P. Floess
Sent: Friday, 25 August 2006 10:00
To: [EMAIL PROTECTED]
Cc: Phillip Smith;
pgsql-sql@postgresql.org
Subject: Re: [SQL] Importing data
from csv

 

And if its contained with
quotes...its considered a field

Scot P. Floess wrote: 

A newline in CSV parlance
denotes the end of a recordunless that newline is contained with quotes...

Phillip Smith wrote: 

I
recently did this by parsing the data through a VB program that appended a
“\” in front of any Char(10) and/or Char(13) characters which tells
Postgres to accept the next character as a literal part of the column value I
believe – must do because it worked! I also quoted the whole column as
part of the VB prog…

 

Worked
for me but I’m not sure the exact science behind it so someone else might
be able to be of some more detailed help.

 

Cheers,

-p

 

-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
On Behalf Of Sumeet
Sent: Friday, 25 August 2006 00:48
To: pgsql-sql@postgresql.org
Subject: [SQL] Importing data from
csv

 

Hi Folks,

sorry if this is a duplicate post, i've been tryin to find a solution of
importing data into postgres from a csv file. The problem is, I have a database
which consists of columns which contain newline characters (mac and unix). now
when i export these files to a csv format, there are some line breaks (mixed
unix and mac) in the data which breaks the copy procedure. 

I also tried using the script posted in one of the previous posts..

#! /usr/bin/perl
$inquotes = 0;
while (<>){
 # Chop the crlf
 chop ($_);
 chop ($_);

 # this first bit goes through and replaces 
 # all the commas that re not in  quotes with
tildes
 for ($i=0 ; $i < length($_) ; $i++){
 $char=substr($_,$i,1);
 if ($char eq '"' ){

$inquotes = not($inquotes); 
 }else{
 if (
(!$inquotes) && ($char eq ",") ){

substr($_,$i,1)="~";
 }
 }
 }
 # this replaces any quotes
 s/"//g;
 print "$_\n";
}


cat data_file | perl scriptname.pl > outputfile.dat

and when i run the copy command i get messages like data missing for xyz
column.
any possible hints... 

--
Thanks,
Sumeet 

 

***Confidentiality
and Privilege Notice*** 

The material contained in this message is privileged
and confidential to the addressee. If you are not the addressee indicated in
this message or responsible for delivery of the message to such person, you may
not copy or deliver this message to anyone, and you should destroy it and
kindly notify the sender by reply email. 

Information in this message that does not relate to
the official business of Weatherbeeta must be treated as neither given nor
endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or
associates shall not be liable for direct, indirect or consequential loss
arising from transmission of this message or any attachments 





-- Scot P. Floess27 Lake RoyaleLouisburg, NC  27549 252-478-8087 (Home)919-754-4592 (Work) Chief Architect JPlate  http://sourceforge.net/projects/jplateChief Architect JavaPIM http://sourceforge.net/projects/javapim  





-- Scot P. Floess27 Lake RoyaleLouisburg, NC  27549 252-478-8087 (Home)919-754-4592 (Work) Chief Architect JPlate  http://sourceforge.net/projects/jplateChief Architect JavaPIM http://sourceforge.net/projects/javapim



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Question about time

[Phillip Smith]

Try the to_char() function instead of cast()

SELECT to_char(fecha_hora_factura, 'HH:MM:SS') FROM nota_venta

Cheers,
-p

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Judith
Sent: Friday, 17 November 2006 5:14 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Question about time

Hi everyone I'm doing a query like this:

SELECT CAST(fecha_hora_factura as time) FROM nota_venta

and I get:

14:16:52.824395

the field is timestamp type... I just want the HOUR:MINUTE:SECOND

the question is how I drop the millisecond??

Thnx in advanced!!!


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

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


---(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] SQL command join question

[Phillip Smith]

Would this be more appropriate...?
SELECT t1.*
FROM t1
OUTER JOIN t2 ON (t1.a=t2.a AND t1.b=t2.b)
OUTER JOIN t3 ON (t1.c=t3.c);


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Ehab Galal
Sent: Thursday, 30 November 2006 11:42
To: pgsql-sql@postgresql.org
Subject: [SQL] SQL command join question

Hi,

I have three tables t1(a, b, c, d), t2(a, b, c, k), and t3(c, e). I need to 
outer join them as shown below, but only have all tuples from t1 as output. 
But the following syntax does not allow me to do so.

SELECT t1.*
FROM (t1 outer join t2 on (t1.a=t2.a and t1.b=t2.b)) t outer join t3 on 
(t1.c=t3.c);

I'm getting the following error message:
ERROR:  invalid reference to FROM-clause entry for table "t1"
HINT:  There is an entry for table "t1", but it cannot be referenced from 
this part of the query.

I'll be grateful if someone may help me with this.

Thanks,
Ehab

_
Fixing up the home? Live Search can help 
http://imagine-windowslive.com/search/kits/default.aspx?kit=improve&locale=e
n-US&source=hmemailtaglinenov06&FORM=WLMTAG


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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[SQL] INSERT DELETE RETURNING

[Phillip Smith]

Hi All,

 

As per Daniel Caune's posting (with no replies) on October 22nd,
(http://archives.postgresql.org/pgsql-sql/2006-10/msg00195.php), Can I do
this.?

 

INSERT INTO stock_deleted_tmp (

  code,

  description,

  date_deleted

  )

  DELETE FROM ONLY stock_tmp

  WHERE grp = '0001'

  RETURNING code, description, current_timestamp

 

At the moment I'm getting this error:

ERROR:  syntax error at or near "DELETE"

LINE 6: DELETE FROM ONLY stock_tmp

 

Yes I have upgraded to PG 8.2:

horseland=# DELETE FROM ONLY stock_tmp WHERE grp = '0001' RETURNING code,
description, current_timestamp;

  code  | description  |  now

+--+
---

 920687 | DEBRISOL 500ML   | 2006-12-07
09:57:37.434605+11

 460950 | ROMA BASIC BUZZ OFF F/MSK SND/BRN S  | 2006-12-07
09:57:37.434605+11

 460951 | ROMA BASIC BUZZ OFF F/MSK SND/BRN M  | 2006-12-07
09:57:37.434605+11

 460952 | ROMA BASIC BUZZ OFF F/MSK SND/BRN L  | 2006-12-07
09:57:37.434605+11

 460953 | ROMA BASIC BUZZ OFF F/MSK SND/BRN XL | 2006-12-07
09:57:37.434605+11

 402832 | COMB PLASTIC BALL BLK| 2006-12-07
09:57:37.434605+11

(6 rows)

  

DELETE 6

horseland=#

 

Thanks,

-p

 

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA 



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

[SQL] could not find pathkey item to sort

[Phillip Smith]

Hi All,

 

Since upgrading to PG 8.2, I'm getting this error on a nightly (bash) script
that calls psql (it's purpose is to find stock codes that share an EAN
Barcode with another code - it used to work, now doesn't):

ERROR: could not find pathkey item to sort

SQL state: XX000

 

The error is because of the ORDER BY clause of this query:

SELECT  code, description, ean

FROM$TMPTABLE

WHERE   ean <> ''

 ANDean IN (SELECT ean FROM $TMPTABLE GROUP BY ean HAVING count(ean) >
1)

ORDER BY ean

 

The $TMPTABLE is created as per:

CREATE TEMP TABLE $TMPTABLE

(

  code varchar(6),

  description varchar(38),

  grp varchar(4),

  brand text,

  style text,

  supplier varchar(6),

  supplier_code text,

  wholesale numeric(10,2),

  retail numeric(10,2),

  ean varchar(13)

)

WITHOUT OIDS;

-- Import the file from WB Database

COPY $TMPTABLE FROM '$TMPFILE';

 

I've tried creating an index and doing a reindex on the temp table because
the select to no avail.

 

Any help would be great - I've "fixed" it at the moment by commenting the
ORDER BY clause, but it would be nice to have it sorted properly, and also
in case it happens in any other tables.

Thanks,

-p

 

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA
 
E. [EMAIL PROTECTED]

 



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Question about GUI

[Phillip Smith]

Assuming you mean for administration, a couple of the options available out
there are pgAdmin and phpPgAdmin... pgAdmin is a stand-alone program,
available for both *nix and Windows, while phpPgAdmin is a web-based admin
console.

Another option is the PostgreSQL module of WebMin

http://www.pgadmin.org/
http://phppgadmin.sourceforge.net/
http://www.webmin.com/

Personally, I use pgAdmin and have webmin with the Postgres module
installed, although I don't use it. I haven't played with phpPgAdmin.

Cheers,
-p


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Judith
Sent: Wednesday, 10 January 2007 10:45
To: pgsql-sql@postgresql.org
Subject: [SQL] Question about GUI

Hello every body, I just want to know if there is some GUI with 
postgreSQL for Fedore Core 3??

Thanks in advanced!!


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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

[SQL] Differentiate Between Zero-Length String and NULL Column Values

[Phillip Smith]

Hi All,

Small problem with 8.2.1, I have a temp table of basic stock details:
CREATE TEMP TABLE tmpstk
(
  code varchar(6),
  description varchar(38),
  grp varchar(4),
  brand text,
  style text,
  supplier varchar(6),
  supplier_code text,
  wholesale numeric(10,2),
  retail numeric(10,2),
  ean varchar(13)
)
WITHOUT OIDS;

This table is populated using a COPY query – works OK. If I try the
following query:
SELECT * FROM tmpstk
I get what I expect, almost 8000 rows including rows similar to the
following:
"401514","EQUINADE_SHOWSILK_SHAMPOO_5L","3209","EQUINADE","SHAMPOO","EQUEST"
,"401514","0.00","0.00","0.00","0.00","10.00","9329028056594"
"401600","A/DRESS_SHAMPOO_TEA_TREE_OIL_500ML","3208","AUSTRALIAN_DRESSAGE","
SHAMPOO","EQUEST","401600","0.00","0.00","0.00","0.00","10.00",""

The second row (401600) is what I’m interested in for this particular
problem. The problem is when I try and add a WHERE clause:
SELECT * FROM tmpstk WHERE ean = '';
SELECT * FROM tmpstk WHERE TRIM(ean) = '';
SELECT * FROM tmpstk WHERE ean = NULL;
None of the above queries return any rows.

What am I doing wrong? It should return the 401600 row above, along with
several hundred other rows. I have attached the bash script that actually
does this processing.

Help is much appreciated.
Cheers,
-p


Phillip Smith
IT Coordinator
Weatherbeeta P/L
AUSTRALIA
 
E. [EMAIL PROTECTED]



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments



import-wb-stock.sh
Description: Bourne shell script

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

Re: [SQL] COPY FROM query.

[Phillip Smith]

"Having said that, if I switch it to a forward slash it works without 
error... odd, given that weenblows standard is backslash."

Yes, but PostgreSQL uses a back-slash as an escape character, which needs to
be used to escape itself at an application level before the O/S gets to deal
with it :)

-p


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Paul Lambert
Sent: Monday, 12 February 2007 11:09
To: Joe
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY FROM query.

Joe wrote:
> Hi Paul,
> 
> On Mon, 2007-02-12 at 08:19 +0900, Paul Lambert wrote:
>> I'm attempting to copy from a table into a file using a select query 
>> inside the copy.
>>
>> The following is my command:
>>
>> COPY (SELECT DISTINCT ON (dealer_id,appraisal_id) * FROM 
>> appraisals_temp) TO 'c:\autodrs_appraisal_new.txt' WITH DELIMITER AS '^' 
>> CSV HEADER;
>>
>> I get the following returned:
>>
>> WARNING:  nonstandard use of escape in a string literal
>> LINE 1: ...ealer_id,appraisal_id) * FROM appraisals_temp) TO
'C:\autodr...
>>   ^
>> HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
>>
>> ERROR: relative path not allowed for COPY to file
>> SQL state: 42602
>>
>>
>> (The caret character is pointing to the M in FROM)
> 
> I believe that on Windows you need to use double backslashes, i.e., 'c:\
> \autodrs_appraisal_new.txt', although the regular slash may also work,
> i.e., 'c:/autodrs_appraisal_new.txt'.
> 
> Joe
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 
> 

If this is the case, it is strange that the first copy statement works 
as that is also only using a single backslash.

Having said that, if I switch it to a forward slash it works without 
error... odd, given that weenblows standard is backslash.

Thanks for the help though.

-- 
Paul Lambert
Database Administrator
AutoLedgers


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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Regaring posting a query

[Phillip Smith]

Exactly the same way as you posted this message - include your query, and
what you question is and we'll see what we can do.

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of sahaanaa subha
Sent: Wednesday, 14 February 2007 17:45
To: pgsql-sql@postgresql.org
Subject: [SQL] Regaring posting a query

 

Dear sir,

 I would like to know how to post my queres in postgres mailing list and how
to view the answers.

Thanks & Regards

subha.N


 

  _  

I use Kify Mail - http://email.kify.com - Get your Kify Mail Account Today -
Its Free, Fast, Easy, Secure & Spam-Free.



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Retrieving 'Credit' when 'C'

[Phillip Smith]

SELECT  when,

CASE WHEN type = 'C' THEN 'Credit' END AS type

FROMmytable;

 

Assuming your column names are actually "when" and "type" you should just
have to change "mytable" to the correct table name and run in psql or the
SQL Window of pgAdmin or wherever you usually run your SQL queries to get
what you want.

 

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Ezequias Rodrigues da Rocha
Sent: Friday, 16 February 2007 03:45
To: Ezequias Rodrigues da Rocha; pgsql-sql@postgresql.org
Subject: Re: [SQL] Retrieving 'Credit' when 'C'

 

Just a question, where to put it ? I didn't notice yet. 



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

[SQL] DISTINCT ON not working...?

[Phillip Smith]

Hi all,

Strange one - I have a nightly export / import routine that exports from one
database and imports to another. Has been working fine for several months,
but last night it died on a unique constraint.

To cut out all the details, the code that is causing the problem:
SELECT  DISTINCT ON (ean)
code,
CASE WHEN ean IS NULL OR valid_barcode(ean) = false THEN
null ELSE ean END AS ean
FROMTMPTABLE
WHERE   code NOT IN (SELECT code FROM stock_deleted)
 ANDean IS NOT NULL

That is the code that generates the error on the unique constraint against
the ean column.

If I play with that and run this:
SELECT  DISTINCT ON (ean)
CASE WHEN ean IS NULL OR valid_barcode(ean) = false THEN
null ELSE ean END AS ean,
count(*)
FROMTMPTABLE
WHERE   code NOT IN (SELECT code FROM stock_deleted)
 ANDean IS NOT NULL
  GROUP BY ean

I get a several thousand rows returned, all with a count(*) of 1, except one
row:
3246576919422   2

DISTINCT ON should eliminate one of those rows that is making that 2 - as I
said, it's been working fine for several months, and it is still doing it
correctly for approximately 100 other rows that have duplicate ean codes.

Can anyone give me a hand to work out why this one is doubling up?!

Cheers,
~p


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

Re: [SQL] DISTINCT ON not working...?

[Phillip Smith]

Removing the CASE statement all together:
SELECT  DISTINCT ON (ean)
  ean,
  count(*)
FROMTMPTABLE
WHERE   code NOT IN (SELECT code FROM stock_deleted)
 ANDean IS NOT NULL
GROUP BY ean

Still gives me:
3246576919422   2



-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 20 February 2007 15:33
To: Phillip Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] DISTINCT ON not working...? 

Perhaps you've confused yourself by using "ean" as both an input and an
output column name?  I think that the "ean" in the DISTINCT ON clause
will effectively refer to that CASE-expression, whereas the one in the
WHERE clause is just referring to the underlying column (and thus making
the IS NULL test in the CASE rather pointless).

regards, tom lane


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

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

Re: [SQL] DISTINCT ON not working...?

[Phillip Smith]

This is a temporary table (with no indexes) that gets created in the same
transaction block as the SELECT gets run, but I tried creating an index on
the ean column anyway with no luck:

CREATE INDEX ean_idx ON TMPTABLE USING btree (ean);
SELECT  DISTINCT ON (ean)
ean,
count(*)
FROMTMPTABLE
WHERE   code NOT IN (SELECT code FROM stock_deleted)
 ANDean IS NOT NULL
GROUP BY ean;

Still returns:
3246576919422   2


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Marcin Stêpnicki
Sent: Tuesday, 20 February 2007 23:34
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] DISTINCT ON not working...?

Wild guess - have you tried reindexing this table? I haven't seen
corrupted indexes since 7.1, though - it usually means subtle hardware
problems.


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

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

Re: [SQL] DISTINCT ON not working... RESOLVED

[Phillip Smith]

This has been resolved -- although I still think it may be a bug in
Postgres.

I'm confused as hell, it's Friday, and it's hot though... So I'll have to
think about it over the weekend and let you know if I can make sense of it.

Thanks all for your suggestions.

Cheers,
~p


-Original Message-
From: Phillip Smith [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 20 February 2007 09:25
To: 'pgsql-sql@postgresql.org'
Subject: DISTINCT ON not working...?

Hi all,

Strange one - I have a nightly export / import routine that exports from one
database and imports to another. Has been working fine for several months,
but last night it died on a unique constraint.

To cut out all the details, the code that is causing the problem:
SELECT  DISTINCT ON (ean)
code,
CASE WHEN ean IS NULL OR valid_barcode(ean) = false THEN
null ELSE ean END AS ean
FROMTMPTABLE
WHERE   code NOT IN (SELECT code FROM stock_deleted)
 ANDean IS NOT NULL

That is the code that generates the error on the unique constraint against
the ean column.

If I play with that and run this:
SELECT  DISTINCT ON (ean)
CASE WHEN ean IS NULL OR valid_barcode(ean) = false THEN
null ELSE ean END AS ean,
count(*)
FROMTMPTABLE
WHERE   code NOT IN (SELECT code FROM stock_deleted)
 ANDean IS NOT NULL
  GROUP BY ean

I get a several thousand rows returned, all with a count(*) of 1, except one
row:
3246576919422   2

DISTINCT ON should eliminate one of those rows that is making that 2 - as I
said, it's been working fine for several months, and it is still doing it
correctly for approximately 100 other rows that have duplicate ean codes.

Can anyone give me a hand to work out why this one is doubling up?!

Cheers,
~p


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Insert based in a select

[Phillip Smith]

INSERT INTO second_table (master_id) SELECT id FROM master_table ORDER
BY id;

Was it important to you that the id and master id in the second table
match up? ie, 1 and 10, 2 and 20, 3 and 30 - not 1 and 30, 2 and 10, 3
and 20 etc...

~p

On Sat, 2007-02-24 at 21:06 -0300, Ezequias Rodrigues da Rocha wrote:

> Hi list,
> 
> Could someone help me with this problem
> 
> I have a table like this
> 
> Master table
> 
> id number
> 10 9898398398
> 20 9938378390
> 30 9873636736
> 
> and I want to make an insert into a table with this structure:
> 
> Secondary table
> 
> id(sequence), master_id
> 110
> 220
> 330
> 
> It is possible to make this insert based in all records of my master table ?
> 
> Any help would be very welcomed.
> 
> Best Regards
> Ezequias
> 
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
> 
> http://www.postgresql.org/about/donate


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Insert based in a select

[Phillip Smith]

(Please reply to the list when replying)

 

The error is correct - you are telling PG to insert one row (the literal
values you've passed to INSERT), but the sub-query is returning multiple
rows which won't fit in a single row.

 

I see 2 options, but someone else I'm sure will have a more elegant way to
do it:

1) insert into base.ingresso (select id from base.cartao order by id)

2) insert into base.ingresso values (nextval(' base.ingresso_id'), 4, now(),
12.34, 12.34, 1, 1678, (select id from base.cartao where id not in (SELECT
id from base.ingresso) order by id limit 1), 2, 25, 99)

 

Number 1 will create one row in base.ingresso for each row in base.cartao.
You will then need to use UPDATE to adjust the values in each of those rows
to the other values you want.

 

Number 2 would need to be run over and over again - not a practical option I
imagine.

 

~p

 

-Original Message-
From: Ezequias Rodrigues da Rocha [mailto:[EMAIL PROTECTED] 
Sent: Monday, 26 February 2007 00:43
To: [EMAIL PROTECTED]
Subject: Re: [SQL] Insert based in a select

 

Phillip,

Thank you for the information but the master (id) is only a field of my
secondary table.

My sql statement is like this:

insert into base.ingresso values (nextval(' base.ingresso_id'), 4, now(),
12.34, 12.34, 1, 1678, (select id from base.cartao order by id), 2, 25, 99)

And I am getting the following error return message:

ERROR: more than one row returned by a subquery used as an expression 
SQL state: 21000

Could you give me another help with this ?

Ezequias



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Insert based in a select

[Phillip Smith]

Apologies – the first statement should have included the column name to
insert to:

INSERT INTO base.ingresso (id_column) (SELECT id FROM base.cartao ORDER BY
id)

 

That will insert one row in ingresso for each row in cartao – only changing
the id column. All the other columns in each row will be populated with the
default values. That is why you will need to do an UPDATE afterwards.
Something like:

UPDATE base.ingresso

SET col1 = ‘value1’, sol2 = ‘value2’

WHERE id IN (SELECT id FROM base.cartao);

 

Hope this makes sense,

~p

 

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Ezequias Rodrigues da Rocha
Sent: Monday, 26 February 2007 10:58
To: Phillip Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Insert based in a select

 

The second statement works but only 1 row was affected. It do not apply to
me.

The first statement i didn't understand. You are adding only the first field
on my base.ingresso table ?

Regards
Ezequias

2007/2/25, Phillip Smith <[EMAIL PROTECTED]>:

(Please reply to the list when replying)

 

The error is correct – you are telling PG to insert one row (the literal
values you've passed to INSERT), but the sub-query is returning multiple
rows which won't fit in a single row.

 

I see 2 options, but someone else I'm sure will have a more elegant way to
do it:

1) insert into base.ingresso (select id from base.cartao order by id)

2) insert into base.ingresso values (nextval(' base.ingresso_id'), 4, now(),
12.34, 12.34, 1, 1678, (select id from base.cartao where id not in (SELECT
id from base.ingresso) order by id limit 1), 2, 25, 99)

 

Number 1 will create one row in base.ingresso for each row in base.cartao.
You will then need to use UPDATE to adjust the values in each of those rows
to the other values you want.

 

Number 2 would need to be run over and over again – not a practical option I
imagine…

 

~p

 

-Original Message-
From: Ezequias Rodrigues da Rocha [mailto:[EMAIL PROTECTED] 
Sent: Monday, 26 February 2007 00:43
To: [EMAIL PROTECTED]
Subject: Re: [SQL] Insert based in a select

 

Phillip,

Thank you for the information but the master (id) is only a field of my
secondary table.

My sql statement is like this:

insert into base.ingresso values (nextval(' base.ingresso_id'), 4, now(),
12.34, 12.34, 1, 1678, (select id from base.cartao order by id), 2, 25, 99)

And I am getting the following error return message:

ERROR: more than one row returned by a subquery used as an expression 
SQL state: 21000

Could you give me another help with this ?

Ezequias

 

***Confidentiality and Privilege Notice*** 

The material contained in this message is privileged and confidential to the
addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy or
deliver this message to anyone, and you should destroy it and kindly notify
the sender by reply email. 

Information in this message that does not relate to the official business of
Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments 




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



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

[Re: [SQL] PRIMARY KEY]

[Phillip Smith]

If you actually need to know the value of N_GEN in your ASP application,
you will need to query the database first and select the NEXTVAL from
the sequence that the "serial" data type will create, then use that
returned value in your insert - ie, DON'T exclude it from the insert,
otherwise it will default to NEXTVAL again and return a different value.

The only catch with this is that you most likely won't end up with
contiguous values in this column - ie, if a user cancels after you seect
nextval, but before you insert - the value of the sequence has already
increased, and will be increased again before returning a value when you
next select nextval

Cheers,
~p

On Wed, 2007-03-07 at 12:57 +0100, M.P.Dankoor wrote:

> Hello,
> 
> Is it possible to redesign your table as follows:
> 
> create table Mod48_00_2007 (
> IDtext,
> N_GEN serial not null,
> FORMSTORE text,
> COD_NOTATIO   text,
> PA_COGNOMEtext,
> constraint pk_Mod48_00_2007 primary key (N_GEN)
> );
> 
> Your insert simply becomes:
> INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO, PA_COGNOME)
> VALUES ('192168217200737122012', '', '00128', 'DE MARTINIS')
> 
> Do note that you do not refer to the N_GEN column, it will use the
> next value, please refer to
> http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
> for more information.
> 
> Mario
> 
> Shavonne Marietta Wijesinghe wrote: 
> 
> > Hello 
> >   
> > I have created a table 
> > CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY,
> > FORMSTORE text, COD_NOTAIO text, PA_COGNOME text); 
> >   
> > And i insert the rows via a form in ASP. When the form loads i have
> > a functin that goes and gets the value of the field N_GEN adds 1 to
> > it and shows it to the user. 
> > The problem is when i have 2 users working at the same time. 
> >   
> > For example the last value in my field N_GEN is 2 
> > When both the users A and B loads the form (ASP page) it sees N_GEN
> > = 3 :) 
> >   
> > So they fill in the form and user A clicks on the button OK and the
> > record has been inserted with N_GEN = 3. But when the user B clicks
> > on the button the record is not inserted because it has the same key
> > "3" 
> >   
> > INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO,
> > PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE
> > MARTINIS') 
> >   
> > Is there any way i can do this automatically? i mean maybe i have to
> > use someother property instead of "Primary Key" ?? 
> >   
> > Thanks 
> >   
> > Shavonne Wijesinghe
> > 
> 
> 

Re: [SQL] [Re: PRIMARY KEY]

[Phillip Smith]

Of course - my bad... That's the main reason I upgraded to 8.2! 

On Fri, 2007-03-09 at 09:03 -0600, Bruno Wolff III wrote:

> On Wed, Mar 07, 2007 at 23:20:12 +1100,
>   Phillip Smith <[EMAIL PROTECTED]> wrote:
> > If you actually need to know the value of N_GEN in your ASP application,
> > you will need to query the database first and select the NEXTVAL from
> > the sequence that the "serial" data type will create, then use that
> > returned value in your insert - ie, DON'T exclude it from the insert,
> > otherwise it will default to NEXTVAL again and return a different value.
> 
> In 8.2, you can also use the RETURNING clause to get those values.


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Rollback

[Phillip Smith]

You can not rollback a committed transaction.

Time to restore some backups - what kind of backup are you using?

~p

On Sat, 2007-03-17 at 16:19 +0530, Hetal Patel wrote:

> Hello Friends
> New to postgresql
> I m in trouble
> I fired a query a "DELETE" query from a PGADMIN III postgresql tool
> Which have deleted some rows which shud ve not been deleted
> How do i get it rollback
> I mean i want all the rows back
> Please help me
> Its urgent
> Thanks in advance for Help
> 
> 
> Hetal Patel
> System Dept.
> Banasdairy
> 


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Help with sub query

[Phillip Smith]

Add something like this to your query:

 

ORDER BY price_time LIMIT 1

 

Cheers,

~p

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of roopa perumalraja
Sent: Monday, 19 March 2007 13:56
To: pgsql-sql@postgresql.org
Subject: [SQL] Help with sub query

 

Hi all,

 

I want the last value from the group of rows. The table 'index_prices' is

 

index_code price_time price

 

XYZ09:45:00   7.5

XYZ09:46:00   7.4

XYZ09:59:00   7.2

XYZ10:00:00   7.3

XYZ10:01:00   7.6

XYZ10:02:00   7.3

.

.

.

 

The other table 'times' is for time interval

 

snapshot_time

 

10:00:00

10:30:00

11:00:00

 

I want the last value of the price between the interval of time. So I do 

 

select tm.index_code, tm.price_date, t.snapshot_time, last(tm.price)
from index_prices_200703 tm, times t where tm.index_code = 'XYZ' and
tm.price_time >= (t.snapshot_time - '30 minute' :: interval)::time and
tm.price_time < t.snapshot_time  
group by t.snapshot_time, tm.price_date, tm.index_code order by
tm.index_code, tm.price_date, t.snapshot_time; 

 

The last() doesn't work. Can this be done using sub query?

 

Thanks in advance

  

  _  

Food
  fight? Enjoy some healthy debate
in the Yahoo!
  Answers Food & Drink Q&A.



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Issue with copying data from a text file.

[Phillip Smith]

Can you not export the source file with escape characters? ie, 

^17\" Alloy Wheels^

~p

On Tue, 2007-03-20 at 11:25 +0900, Paul Lambert wrote:

> I have a procedure in place that copies data from a caret delimited text 
> file into a table storing some information.
> 
> One of the fields in the table contains an item description which may 
> contain item dimensions such as - 17" alloy wheels
> 
> The problem I am getting when I do my load is I believe due to the 
> presence of the double quotation marks giving the copy the impression 
> that it is to include the information following as a single text string 
> until it gets to the next set of double quotes. As a result, I get the 
> following:
> 
> AutoDRS=#   COPY deal_lines_temp_load FROM 
> 'c:/temp/autodrs_deal_lines.txt'
> WITH DELIMITER AS '^' CSV HEADER;
> ERROR:  value too long for type character varying(30)
> CONTEXT:  COPY deal_lines_temp_load, line 87, column order_desc: "17 5 
> spoke alloy wheels.^1291.18^117.38^983.69^1291.18^^C^^
> 
> The column as you can see is defined as a 30 character field, the load 
> contains in this column ^17" 5 spoke alloy wheels.^
> 
> I note an option in the COPY command to specify the quote character, 
> defaulting to double quote. The problem being a single quote will also 
> be used in the data, as will other characters. Is there any way to get a 
> copy to have no quote character? I.e. read the file and put whatever is 
> between the caret characters straight into the appropriate field exactly 
> as is.
> 
> TIA,
> Paul.
> 

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, Vic, 3131
AUSTRALIA

P. +613 9845 0600
F. +613 9845 0655
E. [EMAIL PROTECTED]


smime.p7s
Description: S/MIME cryptographic signature

Re: [SQL] Issue with copying data from a text file.

[Phillip Smith]

Maybe use char 254 or 253 or something similar... Anything that isn't
going to be found in the file.

Have you tried using a string as a delimiter? QUOTE
'THIS.STRING.ISNT.IN.THE.TEXT.FILE'

~p

On Tue, 2007-03-20 at 11:59 +0900, Paul Lambert wrote:

> Andrej Ricnik-Bay wrote:
> > On 3/20/07, Paul Lambert <[EMAIL PROTECTED]> wrote:
> > 
> >> The source file comes from extracts on our main application which sits
> >> inside an in-house pretending-to-be-a-dbms file system. The content of
> >> these extracts would be difficult to change - the extract program would
> >> need to parse the data looking for quotes and preceed them with the
> >> necessary escape character.
> >>
> >> Not being a proper database dump it's not a simple matter of flicking a
> >> switch to get it to include the escape character. The way the extracts
> >> are written would require a few dozen lines of code to each extract, and
> >> theres about 40ish extracts.
> >>
> >> Plus I don't maintain that side of our code, and those that do can be a
> >> bit lazy and I'd likely be waiting months to get it done - if they even
> >> decide to do it.
> > Pipe it through sed and replace the Carets with TABS?
> > sed 's/^/\t/g' c:/temp/autodrs_deal_lines.txt > 
> > c:/temp/autodrs_deal_lines.tab
> > 
> > Then use copy like so:
> > \copy table from 'c:/temp/autodrs_deal_lines.tab' delimiter E'\t' null ''
> > 
> > 
> > Cheers,
> > Andrej
> > 
> > 
> 
> The data contains tabs... don't ask why... I don't have a clue :P
> 
> I'll do something along the lines of sed... but not with sed, I'll use 
> the command line interpreter on the OpenVMS systems where the extracts 
> run. I just thought there might have been a quicker way to switch it off 
> in the copy command, i.e. specifying "quote none" as one of the 
> parameters to the command. I guess not...
> 
> Thanks for the pointers.
> 
> P.
> 

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, Vic, 3131
AUSTRALIA

P. +613 9845 0600
F. +613 9845 0655
E. [EMAIL PROTECTED]


smime.p7s
Description: S/MIME cryptographic signature

Re: [SQL] Foreign Unique Constraint

[Phillip Smith]

Perhaps this...? It would work, but depending how many rows are in the
table, it could become incredibly slow.

ALTER TABLE table1 ADD CHECK (extension NOT IN (SELECT extension FROM
table2));

And the converse for table2:
ALTER TABLE table2 ADD CHECK (extension NOT IN (SELECT extension FROM
table1));

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Jon Horsman
Sent: Tuesday, 27 March 2007 23:22
To: pgsql-sql@postgresql.org
Subject: [SQL] Foreign Unique Constraint

I was wondering if someone could help point me in the right direction
w.r.t. foreign unique constraints.  I'm working on a legacy database
and have a new requirement and am not sure how to do it.

I have something like this

create table table1 (
id SERIAL PRIMARY KEY
extension UNIQUE,

)

create table table2 (
id SERIAL PRIMARY KEY
extension UNIQUE,

)

Basically table 1 and table 2 both have the concept of an extension
that must be unique but the rest of the info in the tables are
different.  I need to ensure that if i add an entry to table 1 with
extension 1000 that it will fail if there is already an entry in
table2 with the same extension.

Essentially i need to do something like the following but i get errors
saying this can't be done.

alter table table1 add check (extension <> table2.extension);

It should be noted i am using pg version 7.4.13 and this can't change.

Thanks for any help you can offer.

Jon.

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

   http://archives.postgresql.org


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Foreign Unique Constraint

[Phillip Smith]

Is that an 8.2 thing? I'm sure I've done it before, probably in 8.1

Maybe it was a trigger I did it in - I can't remember what I had for
breakfast, let alone a slow sub-query I did months ago :P

Cheers,
~p

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Peter Eisentraut
Sent: Wednesday, 28 March 2007 19:08
To: pgsql-sql@postgresql.org
Cc: Phillip Smith; 'Jon Horsman'
Subject: Re: [SQL] Foreign Unique Constraint

Am Mittwoch, 28. März 2007 00:17 schrieb Phillip Smith:
> Perhaps this...? It would work, but depending how many rows are in the
> table, it could become incredibly slow.
>
> ALTER TABLE table1 ADD CHECK (extension NOT IN (SELECT extension FROM
> table2));

Subqueries are not allowed in check constraints.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[SQL] SELECT INSTEAD

[Phillip Smith]

Hi all,

 

I'm trying to create a view of the query below, but I'm being barked at
about "rules on SELECT must have action INSTEAD SELECT". I don't have any
rules in my database, and I don't know how this query is trying to create
one. The query does work if I just run it interactively. Any help would be
much appreciated.

 

SELECT DISTINCT ON (m1.id)

UPPER(m1.id) AS id,

UPPER(m1.first_name) AS first_name,

UPPER(m1.last_name) AS last_name,

UPPER(m1.company) AS company,

UPPER(m1.address1) AS address1,

UPPER(m1.address2) AS address2,

UPPER(m1.suburb) AS suburb,

UPPER(m1.state) AS state

INTO TEMP TABLE mail_duplicates

FROMmaillist as m1

INNER JOIN maillist as m2 ON

(   m1.id <> m2.id

AND  UPPER(m1.first_name) = UPPER(m2.first_name)

AND  UPPER(m1.last_name) = UPPER(m2.last_name)

AND  UPPER(m1.suburb) = UPPER(m2.suburb)

)

WHERE  TRIM(m1.first_name) <> ''

 AND TRIM(m1.last_name) <> ''

;

SELECT d.id AS "ID",

INITCAP(d.first_name) AS first_name,

INITCAP(d.last_name) AS last_name,

INITCAP(d.company) AS company,

INITCAP(d.address1) AS address1,

INITCAP(d.address2) AS address2,

d.suburb AS suburb,

d.state AS state

FROMmail_duplicates AS d

ORDER BY d.last_name, d.first_name, d.suburb, d.id

;



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] SELECT INSTEAD

[Phillip Smith]

Thanks Michael - The 2 queries (sorry, should have said transaction) are a
simple way to identify duplicates in our mailing list. If there are
triplicates, the first query will return 6 rows into the temp table, 1 for
each of the 2 duplicates), but I only need to show the 3 triplicates once
each, not twice each. The DISTINCT ON does that, and that's basically the
whole reason I need to put it in to a temp table and re-select from that,
otherwise I have to sort by the id column (ORDER BY must match DISTINCT ON
expression). Sorting by the ID column doesn't "group" the duplicate /
triplicate rows together for review - that's why I need the ORDER BY.

I guess I could remove the ORDER BY, which eliminates my need to use the
temp table and 2 queries, then do the ordering when I select the view...?

Cheers,
~p

-Original Message-
From: Michael Fuhr [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 29 March 2007 16:49
To: Phillip Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] SELECT INSTEAD

On Thu, Mar 29, 2007 at 03:35:52PM +1000, Phillip Smith wrote:
> I'm trying to create a view of the query below, but I'm being barked at
> about "rules on SELECT must have action INSTEAD SELECT". I don't have any
> rules in my database, and I don't know how this query is trying to create
> one.

http://www.postgresql.org/docs/8.2/interactive/rules-views.html

"Views in PostgreSQL are implemented using the rule system."

> The query does work if I just run it interactively. Any help would be
> much appreciated.

You showed two queries, not one.  The error occurs because you're
trying to create a view that creates a temporary table; you'll need
to write the two queries as a single query that doesn't use a
temporary table.  Also, are you sure you need DISTINCT ON (m1.id)?
What do you intend for that to do?  Is id unique (PRIMARY KEY or
UNIQUE)?  And unless ORDER BY is necessary to determine the result
set (as with DISTINCT ON) then consider leaving it out of the view
definition -- if the outermost query (the query that selects from
the view) needs a certain order than that's the proper place for
ORDER BY.

-- 
Michael Fuhr


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

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

Re: [SQL] setting up a mirroring or replication database

[Phillip Smith]

This may best be asked in the admin group - please don't cross-post. Let's
keep all further discussion in the admin group.

 

A good starting place for you would be the PostgreSQL manual, specifically
the chapter called "High Availability":

http://www.postgresql.org/docs/current/interactive/high-availability.html

. also Pgpool:

http://pgfoundry.org/projects/pgpool/

. and Slony:

http://slony.info/

 

Cheers,

~p

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Karthikeyan Sundaram
Sent: Friday, 13 April 2007 10:39
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: [SQL] setting up a mirroring or replication database

 

Hi Team,
 
We are using Postgres 8.1.0 and in the plans to migrate to 8.2.3.   Ours
is a OLTP application.  Publishers, advertisers and consumers use our system
world wide.  Right now it's not a very big database. But we are expanding
our operations to Europe and US where we are expecting a moster growth.
 
I want to setup a mirroring database or replications database so that if
one database crash, the load balancing database should take care or it.  Not
only that, if I am upgrading one database, the other should take the load. 
 
I have never set this kind of replication or mirroring the database
before.  What are the steps and procedures to do this kind of setup.
 
   Please advise.
 
Regards
skarthi

 

  _  

Live Search Maps - find all the local information you need, right when you
need it. Find   it!



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] slowness when subselect uses DISTINCT

[Phillip Smith]

May I suggest you post an EXPLAIN ANALYZE to the group for the query you're
having problems with...?


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Stuart McGraw
Sent: Thursday, 19 April 2007 04:17
To: pgsql-sql@postgresql.org
Subject: [SQL] slowness when subselect uses DISTINCT

I have several times now run into what seems
like similar performance problems with some 
of my postgresql queries.

I have a view that runs reasonably quicky.

I use this view in a subselect in another 
query and that query too runs reasonably
quicky.

The view returns some unwanted duplicate 
rows so I modify it using either DISTINCT 
or GROUP BY to eliminate them.
View still runs reasonably quickly.

I use the modified view as a subselect as
above, but now the query runs 2-3 orders
of magnitude more slowly than before.

Before I go through the effort of putting 
together a specific and concise test case, 
has anyone seen this general pattern and 
have an explanation or advice?  (PG-8.2.3)


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

Re: [SQL] slowness when subselect uses DISTINCT

[Phillip Smith]

Let's start with one, and whatever is causing it in one, is perhaps in
theory, causing the rest :)

Cheers,
~p

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Stuart McGraw
Sent: Friday, 20 April 2007 00:20
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] slowness when subselect uses DISTINCT


Phillip Smith wrote:
> May I suggest you post an EXPLAIN ANALYZE to the group for the query
you're
> having problems with...?

I will do that but it has happened to me enough that it seems to be
a general pattern, not something specific to one of my queries,
so I thought some communal knowledge may exist.  But apparently
not.

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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

   http://archives.postgresql.org

[SQL] Large journal as psql table. Good idea? Triggering.

[Phillip Smith]

If it's going to be too big for a database, then it's going to be worse
using flat-files on a disk :)

I'd suggest putting it in a database, and have 2 tables:
1) "New" messages to be sent
2) Archive messages

That way the polling machine only has to wait for the database to scan the
"New" table, then move them in to the Archive table once it has sent them.

Depending how many records you're actually looking at, one table with an
index would probably be fine :)

~p

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Bryce Nesbitt
Sent: Tuesday, 24 April 2007 09:12

I'm considering using a postgres table for something that could be done
with a flat file.  Is this a good idea?

I have events on a machine "A", which need to be sent by an SMS/Cell
Phone modem that's on a totally different machine "B".  Potentially this
is a job for a flat file FIFO.

But I'm thinking that maybe it's a job for a database table.  Each new
row would be written with a status (10="new").  And that the modem
process would poll for new rows.  Problem is there will be lots of rows,
but only a trivial few will be "new".  The huge index file and the
polling seem like a drag on the database, unless there is a way to optimize.


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] hi

[Phillip Smith]

Try pgAdmin - Google will find it for you.

~p

On Wed, 2007-04-25 at 10:10 +0530, saji varghese wrote:

> 
> Hi,
> Can i get GUI tool for PostgreSQLin LINUX.Pls let me know from wher i
> candownload the same.
> 




***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Selecting rows with "static" ordering

[Phillip Smith]

The best I can think of off the top of my head would still be multiple SQL,
but at least it would be in one transaction block:

BEGIN;
SELECT '1' AS ordering, t1.* INTO TEMP TABLE work_table FROM t1 WHERE t1.id
= '3';
SELECT '2' AS ordering, t1.* INTO TEMP TABLE work_table FROM t1 WHERE t1.id
= '2';
SELECT '3' AS ordering, t1.* INTO TEMP TABLE work_table FROM t1 WHERE t1.id
= '5';
SELECT '4' AS ordering, t1.* INTO TEMP TABLE work_table FROM t1 WHERE t1.id
= '1';
SELECT '5' AS ordering, t1.* INTO TEMP TABLE work_table FROM t1 WHERE t1.id
= '4';
SELECT t1.* FROM work_table ORDER BY ordering;
COMMIT;

Something to that effect...? It's at least makes it only 1 connection from
the client to the database.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Steve Midgley
Sent: Friday, 27 April 2007 10:01
To: pgsql-sql@postgresql.org
Subject: [SQL] Selecting rows with "static" ordering

Hello,

I have a strange problem (or one that I've never had before anyway). I 
am searching for a list of "id's" for a given table (these id values 
are generated at run-time and held statically in an application-local 
variable).

 From that application, I want to retrieve all those rows, and I want 
them in the order they are currently stored in that variable. So take 
for example this foreign application variable:

   ids = "3,2,5,1,4"

The application then executes this sql:

   select * from table where id in (3,2,5,1,4)

As-is, of course, the above query will return the 5 records in a 
semi-random (i.e. unpredictable/unreliable) order. And I don't want to 
just "order by id" - I want to "order by id(3,2,5,1,4)" (if you see 
what I mean)

Is there a "neat trick" that anyone knows for pulling this off in a 
single query? Basically right now I'm issuing 5 queries to the backend 
to ensure ordering but this horribly inefficient.

Any input or advice would be appreciated,

Steve



---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] syntax error in "createdb"

[Phillip Smith]

Where are you trying to execute these commands? They should be run at
the command line as they are 'standalone' programs, not SQL commands to
be run in an SQL session.

On Mon, 2007-05-07 at 10:01 +0100, kannan kk wrote:

> hello
> 
> when i try to execute createdb,psql,pg_dump,pg_restore and similar
> postgre commands , i get 
> syntax at or near 'createdb' and similar to other statements.
> 
> plz help me wht would be the problem. 
> 
> 
> 
> __
> Office firewalls, cyber cafes, college labs, don't allow you to
> download CHAT? Here's a solution! 


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] Query RE using COPY

[Phillip Smith]

Can you modify the 'extract' and make the extra column "\n" which is the
null escape?

That would be the only other option.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Paul Lambert
Sent: Tuesday, 8 May 2007 12:44
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Query RE using COPY

Jonah H. Harris wrote:
> COPY mytbl (col1, col2, col3, col4, col6) FROM 'myfile.txt' WITH
> DELIMITER AS '^' QUOTE '\f' CSV HEADER;
> 
> 

I would rather not do it this way as I use the same load script at all 
customer sites where the extracts and requirements may vary. I.e. one 
customer may not use the location field mentioned above, but another might.

What I would prefer to do, if possible, is set that column in the 
database to 'not required' so that if I get an extract that doesn't have 
that column in the file, the copy doesn't care.

As it stands now I generally have to drop all the unneeded columns from 
  numerous tables, perform my load and then re-add them back again.

When we're talking about 40 or more tables in each database some tables 
with several hundred columns... your suggestion would be a bit 
cumbersome - particularly if it is only one or two columns in each table 
that that the client doesn't need.

-- 
Paul Lambert
Database Administrator
AutoLedgers


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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

Re: [SQL] Passing input to a view?

[Phillip Smith]

Remove the WHERE clause that specifies the date so the view includes all
dates, then apply the WHERE clause when selecting the view.

SELECT  *
FROMinvoiced_repairs
WHERE   invoiced_repairs.received_date BETWEEN '1 Jan 2007' AND '10
May 2007';


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Paul Lambert
Sent: Thursday, 10 May 2007 16:04
To: pgsql-sql@postgresql.org
Subject: [SQL] Passing input to a view?

Is it possible to define a view to use input parameters rather than a 
hard-wired value in a where clause?

I.e. I have the following view:

CREATE OR REPLACE VIEW invoiced_repairs AS
SELECT ro_header.ro_number, ro_header.received_date, vehicles.vehicle_id,
vehicles.model, vehicles.engine, vehicles.transmission, 
ro_header.odometer_reading,
ro_header.service_description,
(
  CASE WHEN (vehicles.month_of_manufacture <> '' AND 
vehicles.year_of_manufacture <> '')
 THEN
(vehicles.month_of_manufacture || '/' ||
vehicles.year_of_manufacture)
 ELSE
''
  END
 ) AS date_of_manufacture,
vehicles.identification_number, vehicles.engine_number from
ro_header
INNER JOIN vehicles USING (vehicle_address)
WHERE ro_header.received_date between '1-jan-2007' AND '10-May-2007';
ALTER TABLE invoiced_repairs OWNER TO postgres;

Instead of having it set to 1-Jan-2007 and 10-May-2007 for the dates, 
can I specify that those values should be passed to the view? Or would I 
need to change this to an SQL language function?

I can't find anything in the documentation that answers this, but maybe 
I'm not looking hard enough.

Thanks in advance.

-- 
Paul Lambert
Database Administrator
AutoLedgers


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

Re: [SQL] ASK about SQL

[Phillip Smith]

Do you mean you want it to order it in the same order as you've listed them
in the IN condition?


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of ali nas
Sent: Thursday, 31 May 2007 17:57
To: pgsql-sql@postgresql.org
Subject: [SQL] ASK about SQL

Dear all,

HELP me
PLZ.
...

I'm used this SQL command : 

"" SELECT ean FROM icitem WHERE ean in
(4902715720005,4909411012151,4909411010164,4902715688718,4902715731605,49094
11008864,4909411010188,4909411010126,4909411010140,490941101
2052,4902715720104,4909411010089,4909411010201,0,0) ; ;


The FILE content :   but the result is :
ean
 
---
 
4902715720005 4902715720005
4909411012151 4902715720104
4909411010164 4902715688718
4902715688718 4902715731605
4902715731605 4909411008864
4909411008864 4909411010089
4909411010188 4909411010126
4909411010126 4909411010140
4909411010140 4909411010164
4909411012052 4909411010188
4902715720104 4909411010201
4909411010089 4909411012052
4909411010201 4909411012151


that is different RESULT with the input !!! what must I do if i dont want
POSTGRES not ORDER my SELECT data 

THX's


  
 
Kunjungi halaman depan Yahoo! Indonesia yang baru! 
http://id.yahoo.com/

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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

Re: [SQL] ASK about SQL

[Phillip Smith]

Yes, you can order it the same as how you've listed in the IN condition, but
you need to use the ORDER BY clause... Essentially you just need to pass the
same list to both IN and ORDER BY but with some slightly different
formatting... 

SELECT  ean
FROMicitem
WHERE ean IN (4902715720005, 4909411012151, 4909411010164, 4902715688718,
4902715731605, 4909411008864, 4909411010188, 4909411010126, 4909411010140,
4909411012052, 4902715720104, 4909411010089, 4909411010201, 0)
ORDER BYCASE WHEN ean = 4902715720005 THEN '1'
WHEN ean = 4909411012151 THEN '2'
WHEN ean = 4909411010164 THEN '3'
WHEN ean = 4902715688718 THEN '4'
WHEN ean = 4902715731605 THEN '5'
WHEN ean = 4909411008864 THEN '6'
WHEN ean = 4909411010188 THEN '7'
WHEN ean = 4909411010126 THEN '8'
WHEN ean = 4909411010140 THEN '9'
WHEN ean = 4909411012052 THEN '10'
WHEN ean = 4902715720104 THEN '11'
WHEN ean = 4909411010089 THEN '12'
WHEN ean = 4909411010201 THEN '13'
    WHEN ean = 0 THEN '14'
;


-Original Message-
From: ali nas [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 31 May 2007 18:57
To: Phillip Smith
Subject: Hal: [SQL] ASK about SQL

YES, I don't care with with ORDER BY clause. beacuse i just want what the
data INPUT as like that the data OUTPUT ,so INPUT listed = OUTPUT listed.
so there isn't way in POSTGRES  to do like this ??

- Pesan Asli 
Dari: Phillip Smith <[EMAIL PROTECTED]>
Kepada: ali nas <[EMAIL PROTECTED]>
Terkirim: Kamis, 31 Mei, 2007 3:44:12
Topik: RE: [SQL] ASK about SQL


You will need to explicitly tell Postgres what order you want it in - unless
you tell PG the order you want with an ORDER BY clause, it assumes you don't
care.

I can't recall 100% the exact syntax for the ORDER BY in your case, but
someone else more with it than me at the moment will be able to... Hang
tight! :)


-Original Message-
From: ali nas [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 31 May 2007 18:31
To: Phillip Smith
Subject: Hal: [SQL] ASK about SQL

THX's for answered my question.

YES, i want POSTGRES just do SELECT in the IN condition. add clue, POSTGRES
will show the SELECT data which POSTGRES find first. I dont wanna like that.
So what will I do ??


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by
Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments


  
 
Kunjungi halaman depan Yahoo! Indonesia yang baru! 
http://id.yahoo.com/


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

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

Re: [SQL] Join question

[Phillip Smith]

Whoops, I forgot the JOIN conditions! Fixed below

-Original Message-
From: Phillip Smith [mailto:[EMAIL PROTECTED] 
Sent: Friday, 27 July 2007 11:47
To: 'pgsql-sql@postgresql.org'
Subject: RE: [SQL] Join question


This might give you a starting point if I understand you correctly...

SELECT h.invoice_number,
 h.customer,
 l.item,
 l.amount
FROMlines AS l
JOINheaders AS h ON l.invoice_number = h.invoice_number
UNION
SELECT h.invoice_number,
 h.customer,
 s.item,
 s.amount
FROMsundries AS s
JOINheaders AS h ON s.invoice_number = h.invoice_number
ORDER BY invoice_number, item


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.

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

Re: [SQL] Join question

[Phillip Smith]

This might give you a starting point if I understand you correctly...

SELECT h.invoice_number,
 h.customer,
 l.item,
 l.amount
FROMlines AS l
JOINheaders AS h
UNION
SELECT h.invoice_number,
 h.customer,
 s.item,
 s.amount
FROMsundries AS s
JOINheaders AS h
ORDER BY invoice_number, item


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] populate value of column

[Phillip Smith]

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of novice
Sent: Wednesday, 8 August 2007 15:31
To: pgsql-sql@postgresql.org
Subject: [SQL] populate value of column

> How can I generate the following result?
>
> meter_id | bay
> --+
>  1001 | 01
>  1001 | 02
>  1001 | 03
>  1001 | 04
>  1012 | 01
>  1012 | 02
>  1012 | 03
>  1012 | 04
>  1012 | 05
>  1012 | 06

I even tested this one too ;)
SELECT  meter_id,
LPAD(GENERATE_SERIES(1,area_no),2,'0')
FROMmeter;

Works on 8.2.4 - you didn't say what version you were using.


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.

---(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] populate value of column

[Phillip Smith]

I believe they are standard PostgreSQL functions – not sure what version
they appeared in though.

I learnt about them by RTM

Cheers,
~p

-Original Message-
From: Ronald Rojas [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 14 August 2007 18:00
To: Phillip Smith
Subject: Re: [SQL] populate value of column

Hi philipp,

How do i get those built-in function like LPAD, GENERATE_SERIES in
postgresql? is there a query where I can list down those?

Thanks in advance.

Ronald



On Wed, 2007-08-08 at 15:37 +1000, Phillip Smith wrote: 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of novice
Sent: Wednesday, 8 August 2007 15:31
To: pgsql-sql@postgresql.org
Subject: [SQL] populate value of column

> How can I generate the following result?
>
> meter_id | bay
> --+
>  1001 | 01
>  1001 | 02
>  1001 | 03
>  1001 | 04
>  1012 | 01
>  1012 | 02
>  1012 | 03
>  1012 | 04
>  1012 | 05
>  1012 | 06

I even tested this one too ;)
SELECT  meter_id,
LPAD(GENERATE_SERIES(1,area_no),2,'0')
FROMmeter;

Works on 8.2.4 - you didn't say what version you were using.


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by
Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.

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


==
Ronald Rojas
Systems Administrator
Linux Registered  User #427229

==

MSDOS didn't get as bad as it is overnight -- it took over ten years
of careful development.
-- [EMAIL PROTECTED]





THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

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

Re: [SQL] to_date function

[Phillip Smith]

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of anru chen
Sent: Friday, 7 September 2007 15:23
To: pgsql-sql@postgresql.org
Subject: [SQL] to_date function

> Hi all:
>
> i am use postgres 8.2 on windows XP, following select statement
>
> "select to_date('10 August 2007','DD Month ');"
> return me 0007-08-10,
>
> if i do "select to_date('10 September 2007','DD Month ');"
> result is correct 2007-09-10

Try using TO_CHAR instead of TO_DATE if it's just for display purposes (I
assume it is)

If it's for comparison purposes, then just cast to a date either by CAST('10
August 2007' AS date) or '10 August 2007'::DATE

~p


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Quick question re foreign keys.

[Phillip Smith]

> My question therefore is, is it possible to create a foreign key that is
> conditional, i.e. only enforce the foreign key where the value in that
> table is not null.

My understanding from reading previous threads on this topic is the answer
is no, however you could make your own pseudo-foreign key using triggers to
do the same job, but only when your column is not null.

Not sure of the performance impact of doing this though - someone else may
be able to advise pros and cons in more detail.

Cheers,
~p


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] [ODBC] string function

[Phillip Smith]

Hi,

 

I don't believe the LEFT function exists (at least by default).

 

Try this instead:

select SUBSTR(kd_aln,LENGTH(kd_aln) - 1, 2) as code from airlin

 

Also, please don't cross-post. This isn't an ODBC question. You're more
likely to get useful answers posting once to the correct list.

 

Cheers,

~p

 



THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

Re: [SQL] Support for SQL TOP clause?

[Phillip Smith]

SELECT *

FROM   Individual

LIMIT 3

 

I asked the reverse question moving from PG to MSSQL :-(

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Chinyi Woo
Sent: Thursday, 10 January 2008 14:14
To: pgsql-sql@postgresql.org
Subject: [SQL] Support for SQL TOP clause?

 

Hello, everyone

Does Postgresql support query like SELECT TOP 3 * FROM Individual  ? If I
use ORDER BY, I have to write non-sql code to get the first row in the
result set, which I try to avoid.

 

Thanks

Chinyi



THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

Re: [SQL] JOIN a table twice for different values in the same query

[Phillip Smith]

Try something like this where we alias the joined tables:

SELECT departure_date,
 j1.code AS departure_code,
 j2.code AS arrival_code
FROM jsjourneys
LEFT OUTER JOIN jsports AS j1 ON jsjourneys.departure_port = j1.id
LEFT OUTER JOIN jsports AS j2 ON jsjourneys.arrival_port = j2.id;

As a side note - all the IATA codes are unique for each airport - wouldn't
it be better to use these as the Primary Key and Foreign Keys? Then you
wouldn't have to even join the tables unless you wanted the port names (not
just the code)

Cheers,
~p


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] Extract interdependent info from one table

[Phillip Smith]

>  docnum | alias1 | alias2  | subclass_alias
> ++-+
>  653219 |   3587 | Redraft | Reply
> (1 row)
> 
> What I'd really like is to BOTH Post AND reply, with the alias2 for both.
> Hope this was as clear as mud? :)

Absolutely clear as mud :P

I think this might be what you're after

SELECT
docnum,
alias1,
alias2,
subclass_alias
FROM
docmaster
WHERE
subclass_alias = 'Inquiry'
AND alias1IN
(
SELECT
alias1
FROM
docmaster
WHERE
subclass_alias = 'Reply'
AND
(
alias2 = 'Pending'
 OR alias2 = 'Redraft'
)
)


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

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

Re: [SQL] Proposed archival read only trigger on rows - prevent history modification

[Phillip Smith]

On Tuesday 12 February 2008 03:25, Bryce Nesbitt wrote:
> Yes, the view approach has some advantages.  But it still leaves the 
> underlying tables naked to modification. And since the most likely 
> error is... well... me (or another admin) at the SQL prompt, we want 
> underlying tables protected also.

Couldn't you make a row trigger BEFORE UPDATE?

CREATE OR REPLACE FUNCTION readonly_handler()
  RETURNS "trigger" AS
$BODY$
BEGIN
IF OLD.read_only = TRUE THEN
RETURN OLD;
ELSE
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] pg_dump using SQL

[Phillip Smith]

> Is there any way to do what pg_dump does?, I mean, get the structure of a
table in a database (ex: CREATE TABLE ...)

Turn Query Logging on in postgresql.conf then see what queries are executed
by pgAdmin or similar when you run it?


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Asking GO on SQL SERVER

[Phillip Smith]

> Hem... I think postgresql do not like that.

You may need to add a "COMMIT;" in there somewhere...?


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Working with dates before 4713 BC

[Phillip Smith]

> Do you really need microsecond, or even day, resolution in your dates? I
wonder if it'd not be good enough to store the year as an integer.

Maybe 2 columns:
date_col type date = For dates post 4713BC. Set to NULL if ealier than
4713BC.
year_col type integer = Store the year from date_col

Then write your queries appropriately to look at year_col if date_col IS
NULL?


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql