> 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 w
> 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 p
> 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 t
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
> 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 mi
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;
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 SQ
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.
> 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
-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('1
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
-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
> --+
>
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
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
= 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&
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,
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:
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:
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 simil
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'
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 ma
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 th
[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 o
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 whe
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-
o:[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
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 w
D]
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
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)
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
y 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
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
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
> W
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 A
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
] [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
(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 elegan
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 Roc
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
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(*)
FROM
[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
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
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
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.
"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
600) is what Im 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 re
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.pgadmi
y, 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 p
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
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:
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: [SQ
: [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
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
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
x27;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
AUSTRAL
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?
-
y = 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
n 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]&g
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 tog
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 multipl
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
sno
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
: 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
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
cific 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:
&
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 T
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 connection
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: Wednesda
[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, Phi
bably 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 com
t_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?
--
d 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]>
w
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 ru
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
74 matches
Mail list logo
