l in the manuals, you return results one at a time.
For some examples, see http://techdocs.postgresql.org/ and look for the "Set
Returning Functions" item.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
option in
the ODBC manager that should disconnect them after a certain time.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
posted. Is this possibly a feature of a
> higher PostgreSQL version than 7.3.2?
No - look carefully at Joe's response. He's calling it like:
SELECT * FROM my_function();
You treat the function like a table.
--
Richard Huxton
Archonet Ltd
---(end
ing
> Postgres 7.2.2.
Expected, because you're acting on a timestamp. When you start looking at time
handling across timezones and daylight saving systems across the world it
does get a bit complicated.
--
Richard Huxton
Archonet Ltd
---(end of broad
th quotes, you can refer to
them as upper/lower case. Personally, I create them all lower-case anyway and
use caps for SQL keywords.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
d: no display name and no $DISPLAY
> environment v
> ariable
It looks like it's complaining about not running under X. Are you trying to
start it from the console?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don&#
> there known limits in joining tables?
I'm not aware of any particular limits on joins. There's even a genetic
optimiser that kicks in when joins go above a certain number of tables
(default of 12 but configurable in 7.4 iirc?).
--
Richard Huxton
Archonet Ltd
Perl/PHP/Java, whatever) supply tools
for this.
Your particular issue with \n is just down to PG's standard string parsing -
not really related.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
On Monday 22 December 2003 09:37, Denis wrote:
> Hi Richard..
>
> If your users are required to fire only SELECT and no DML, you can do the
> following:
>
> BEGIN;
> execute the statements given by user
> ROLLBACK;
>
> This will not affect your SELECT and also if any
don't think pgaccess is installed as standard on any version of RedHat.
Downloads are from: http://www.pgaccess.org/
You might also want to look at pgadmin (v3) -
http://pgadmin.postgresql.org/pgadmin3/index.php
There are also other options: phpPgAdmin (web-based), pg-manager?
--
Richard
rdetail
> where orderdetail.orderno=orderheader.orderno) ;
Looks fine to me. In general, you can do something of the form:
UPDATE ta SET a2 = (SELECT expr(b2) FROM tb WHERE b1=a1);
NOTE - make sure you understand what happens when there is no b1=a1 and what
that will mean to your particular setup.
--
ide the CREATE TABLE statement.
AFAIK there are two strands to this:
1. The INDEX keyword inside a CREATE TABLE statement is non-standard
2. PRIMARY KEY/UNIQUE are not indexes - they just happen to create one.
In theory, you can enforce uniqueness without the use of an index. In practice
of
');
UPDATE 1
richardh=# SELECT * FROM foo
richardh-# ;
_aaa| _bbb
+---
1234567890 | 12345
(1 row)
Since your error seems to be complaining about a space, I'd guess you've got
other than numeric values in _aaa.
-- Richard Huxton
---(end of broadcast)---
TIP 8: explain analyze is your friend
ce chapter of the manuals - you'll want to escape any commas with a
backslash:
\,
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
On Friday 09 January 2004 02:13, [EMAIL PROTECTED] wrote:
> Hi,
>
Just realised this question is also posted under COPY command. Ignore me.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list ar
le that does
> already exist?
Something like:
INSERT INTO table_a (a,b,c)
SELECT d,e,f FROM table_b WHERE f=1;
You could replace any of the columns in the SELECT by an expression if you
wanted to.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)
imary key has meaning to the users, and the users will tend
to get it wrong.
The second is probably the more persuasive - the first can definitely have
costs as well as benefits.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP
s - this has been covered recently. I
seem to recall there is an add-on that does just this.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
pen source database-like product interprets
the same construction differently. But it does do what you want in
postgres.
Richard
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
to look for examples:
1. the contrib/tablefunc folder has an example of this sort of thing
2. search the mailing list articles for CONNECT BY (oracle's name for this
sort of thing) or "tree"
--
Richard Huxton
Archonet Ltd
---(end of broadcast)-
both B and A for
> Update and Delete cascade. If I delete from A it deletes all from B. If
> I update A what happens in B?
The column should be changed there too. For example, if we have
B.user_id => A.user_id and change A.user_id from 100 to 200 then the same
should happen in B too.
you want is probably ECPG - see the "client interfaces"
section of the documentation.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend
he url below.
http://techdocs.postgresql.org/
Feel free to come back and ask some more if you have any problems.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
ming you've read the manuals, two good places to look for further
info are:
http://techdocs.postgresql.org/
http://www.varlena.com/GeneralBits/
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend
ess.
Can you give example outputs? It's difficult to decide otherwise.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
On Thursday 05 February 2004 08:28, Raman wrote:
> Hi Richard,
> Follwing are the Results that I get
> WHEN I run "between" query like
>
> ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
> (start_time::time - send_before_time::time)
>
On Thursday 05 February 2004 14:59, Raman Garg wrote:
> Hi Richard,
>
> What I am having is
>
> CREATE TABLE "customer_events" (
> "event_id" numeric (10) NOT NULL,
> "customer_id" numeric (10) NOT NULL,
> "event_name&quo
osition.
In this precise example, could you not:
1. Check index for value
2. If found, seq-scan
Of course that's only going to be a sensible thing to do if you're expecting
one of two results:
1. Value not there
2. Lengthy seq-scan if it is there
--
Richa
On Monday 16 February 2004 12:10, Kumar wrote:
>
> test=# select date_trunc('week',current_date + ('5 month')::INTERVAL);
> ERROR: TIMESTAMP units 'week' not supported
Try EXTRACT(week FROM ...)
--
Richard Huxton
Archonet Ltd
-
on to PostgreSQL, you've found the right list
for SQL questions. We also have a PHP list and a novice list if you're just
starting.
Good luck
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: if posting/reading through U
is referring to a temporary table that is created, destroyed an
re-created (because it's oid will change).
The solution is to use the EXECUTE command to build a dynamic query. Check the
archives for plenty of other people doing this.
--
Richard Huxton
Archonet Ltd
--
concatenation.
This is easy to do, but the order your ABC get processed in is undefined.
You can find info on both in the archives, probably with examples. Also -
check techdocs.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
On Tuesday 17 February 2004 23:33, Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
> > 2. Write a custom aggregate function (like sum()) to do the
> > concatenation. This is easy to do, but the order your ABC get processed
> > in is undefined.
>
> Actu
always "n" characters in length. If you assign
less than "n" characters, it is right-padded with spaces. In all other
respects it behaves as any other text type of length "n" with right-trailing
spaces.
[rant off - ah, feel better for that :-]
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
bloat but I have to recreate the encode/decode in my app,
> so far as I see.
Less bloat than you might expect - large values are TOASTed and compressed.
I'm guessing a lot of your redundancy will be eliminated.
Having said that, bytea's purpose in life is to store your binary data.
--
est is grab some suitably representative images, base64 encode
them and see how much it takes to store 100,000 copies of them.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index s
es. Also you might
want to look at the excellent set-returning function article on
http://techdocs.postgresql.org/
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.po
f your variables match a column in your select (and you seem OK
here).
2. None of the fields in your expression are null (e.g. dat.vormittag_a)
I think it's #2 - concat NULL to any string and you get NULL back.
--
Richard Huxton
Archonet Ltd
---(end of broadca
d-mm-yy, or yy-mm-dd. Throw an error if a
> month or day field is found to be out of range.
I think what happened is that it will now *only* accept the format specified
by your datestyle setting. Previously, as you say, it would have a guess at
almost anything (and sometimes get it wro
t use any of the geometry related types since we've got text here not
numbers. Nothing in the archives seems quite right (AFAICT).
Any smart ideas? I'm happy to trade time when updating the blocks table
against lookup speed.
--
Richard Huxton
Archonet Ltd
-
On Wednesday 25 February 2004 19:18, Richard Huxton wrote:
> Large table representing non-overlapping blocks:
>
> blocks(id int4, min varchar, max varchar)
>
> SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max;
>
> The estimator gets the wrong plan because
On Wednesday 25 February 2004 20:56, Joe Conway wrote:
> Richard Huxton wrote:
> > That's not quite the same though, because it means I need to split
> > ABCAA..ABDBB into ABCAA..ABCZZ and ABDAA..ABDZZ but it's close enough
> > unless someone is feeling clever this
On Wednesday 25 February 2004 21:32, Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
> > Large table representing non-overlapping blocks:
> > blocks(id int4, min varchar, max varchar)
> >
> > SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AN
e possible in some cases, but I'm not
sure how difficult it is to do in all cases.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
;ORDER BY pr_min DESC LIMIT 1)
Aha! I was trying something like that, but couldn't get it quite right and it
was getting too late for me to see clearly.
Thanks Tom, I'll have a play with this later today.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
he replication options available -
you may be able to adapt contrib/dbmirror, or perhaps erserver/rservimp on
gborg.postgresql.org
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregist
onal functions/operators iirc).
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
olumn.
...
(
SELECT x.field2
FROM tableB AS x
WHERE x.field1 = tableB.field1 - 1
)
...
HTH
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
tion.
Yes - a wrapper function is the standard solution. I don't think you need to
do this in 7.4 though.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
su
r(10));
> insert into someOthertest (value) values ('100');
> insert into test (value) values ('10');
> insert into test (value) values ('20');
>
> select currval() should returns 2 (the last value of the test table
> sequence)
Not really - what
g will convert back/fore quite neatly. Alternatively, you might prefer
'base64' - on average a less compact format I'd guess.
I don't know whether the ECPG interface lets you pass "raw" binary back and
fore or not, but hopefully someone else will.
--
Richard H
the data without getting the above error. (Basically it
is looking up different addresses depending on the codes
stored in mhdespatch table). Can I do this with a view, or
do I have to create several views and link them together.
Thanks
Richard
Postgresql 7.4.1,SuSE 8.1
Query Begins ==>
CREATE
ee a $2 constraint so why am i getting the error msg?
Firstly, $2 will be the automatically created name for this new constraint.
Secondly || doesn't mean OR - it joins strings.
Try something like (untested):
ALTER TABLE genus ADD CONSTRAINT valid_gender CHECK (gender IN
('masculine'
At 02:17 10/03/2004, you wrote:
On Tue, 9 Mar 2004, Richard Grosse wrote:
> Trying to convert the query below to a view. The problem is
> despite it working as a query when trying to save it as a
> view the database returns the error that the field tablealias.cmpname
> is duplicate
ease if it is not possible please say.
You're best doing this in the application really.
However, if you only want to work with text, search the mailing list archives
using keywords like:
text concat aggregate
and you should see an example solution where you build your own aggregate
.x
Might be worth a quick look in the -bugs list archives and see if anything
looks like your problem.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
of your problems are down to quoting issues and misleading error
statements.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
ke there you were using transact-sql (if I remember its name
correctly) rather than sql.
The syntax is different (plpgsql bares a startling resemblance to the Oracle
approach), the purpose of each is the same.
--
Richard Huxton
Archonet Ltd
-
if there is any way to do this in one of the later SQL standards?
The CREATE INDEX thing is a bit of a hack, and I certainly wouldn't have
thought of it either.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
rs,
> irrespective of the values of c1 ranging from 100 to 10 million
You could write such a coding so long as you don't mind having ten-thousand
characters in your character-set. Of course, character does not equal byte
like this, but that's true in the various unicode syste
lts I think I should be getting. Is there
> any kind of debug setting, or if not that, a way to output text (i.e.
> printf) from plpgsql?
RAISE NOTICE ''var1 = %, var2 = %'', var1, var2;
Note - you need this format, you can't mix and match expressions etc.
--
R
dump, a specific table/query?
Do you need to provide values for this update/insert, or is it fixed (updating
timestamps sort of thing).
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desir
the procedural language pl/sh - I think that is what you
need.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
E a.other_acc = OLD.acc;
Your second example just ignored the OLD.acc altogether in the join, so of
course you got an unconstraind join of 213 x 213.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
why this
won't work in your case?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
n (other than as NEW.col1, NEW.col2, NEW.col3
etc).
You can however use TG_NAME or TG_RELNAME to see what trigger/table called
you. I find that's helpful.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
the
question please do assist.
Martin - you'll need to explain exactly what you want. Can you show what
outputs you would like given the above data?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: you can get off all lis
| 5
SELECT
d.divisions_name,
s.pd_geo,
COUNT(s.pd_geo)
FROM
ser_divisions d,
ser s
WHERE
d.divisions_id = s.ser_divisions
GROUP BY
d.divisions_name, s.pd_geo
ORDER BY
d.divisions_name, s.pd_geo
;
It's called a join, and any good SQL book should cover it.
--
Richard Huxton
Please ignore - testing a previously misconfigured email client
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
that in Postgres SQL Server.
You could use unicode (e.g. UTF-8) encoding. I don't see what it gains you
though. What do you hope to achieve by storing numerical data as characters?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)--
This is called a join. Any book on databases/SQL should discuss this sort of
stuff.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
bytea,'UTF-8')
> ERROR: No such encoding as 'UTF-8'
I think you're using the encode() function wrongly. The second parameter is
supposed to be something like "base64" or "hex". I'm not sure it makes sense
to try and cast an integer
it will get back compared to how many it
actually gets back.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend
for PostgreSQL.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
nt OIDs that table will use 4
bytes less for every row, and so be a bit faster.
I believe in the next version, the default setting will be not to have
OIDs and you will have to specify "WITH OIDS" if you do want them.
HTH
--
Richard Huxton
Archonet Ltd
---(end
to do?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
also deleted the a table row.
You'll need to show the table definitions (with foreign keys etc) and
also trigger definitions (use "\d table_a" in psql or "pg_dump
--schema-only -t tablename")
--
Richard Huxton
Archonet Ltd
---
the
source folder. Haven't used them myself, so can't say more. Worth
checking the mailing list archives though, since there has been
discussion of this.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't for
ut only for those columns that
have active='y'. For the columns that have active='f' I don't care if num is
unique or not. I'm asking this because num will be doubled some times.
Non-standard but elegant:
CREATE UNIQUE INDEX my_partially_unique_index ON rekl
archives (this list and general would be a good
start) for plenty of discussion on these.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unreg
me serial message_id
column that should act as a clue.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
t any of my PL/SQL functions (or yours) are so brilliant
> that they need trade secret protection.
Some of mine are so ugly, I wish they were hidden away mind you ;-)
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you
ur current connection.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
datatype inside your function. Clearly
PostgreSQL needs to know what type is being returned while parsing the
function, so you can't have a "dynamic type" (if such an idea has any
meaning at all).
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
this. I'd guess you
dropped/recreated the function after defining the trigger, yes?
The solution in older versions is to put the CREATE FUNCTION and CREATE
TRIGGER in the same file so that you drop/recreate both together.
HTH
--
Richard Huxton
Archonet Ltd
---(end of
views? At least for simple views of the kind 'CREATE VIEW v AS SELECT a,b,c FROM t'?
If you're running 7.4 you can look in the information schema, in
view_column_usage - that will tell you which table-columns a view uses.
--
Richard Huxton
Archonet Ltd
---
t myself on occasion. The '==' operator
doesn't exist in plpgsql, you should use '=' when comparing and ':=' for
assignment.
HTH
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
ght well interfere (though
I admit I haven't looked in detail)
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
ey-- other join clauses...
AND t.contDate = (
SELECT max(contDate) FROM ccontinue
)
-- no need for GROUP BY / HAVING
ORDER BY c.citkey
Richard
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
unt_id
or whatever else you like, to the inner select.
Richard
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
h a clever idea involving a
join against a set-returning function, but I'm not sure about
guaranteeing the order of the join vs the returned set (and it's getting
late here). Any ideas people?
Maybe one of those is some use
--
Richard Huxton
Archonet Ltd
---
igh key to t1).
So there's no connection between column "code" in any of the tables? I'm
confused as to the correlation between t0 and t1. I'm also not clear
what t2.code is supposed to be.
Could you give a short (5 rows each) example of the contents of the
tables and th
nd usually given is to do two updates:
UPDATE story SET id = -id;
UPDATE story SET id = -id + 1500;
The real solution would be to check unique constraints at the end of
statement, but I assume this is a tricky change or it would have been
done by now.
--
Richard Huxton
Arch
about it's type-matching, which is why you need to
have two entries for the function.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend
te field1/2/3 then this
won't work.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
bid. However, writing a perl script or plsql
function to do this for you shouldn't be difficult.
Screams out plpgsql to me - it's good at automating what is basically
cut & paste of values.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
co,clo1,nl,l1,m1;
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
as
IF NOT((...)=false)
is a good one. Handling of NULLs causes a great deal of pain to
inexperienced and experienced developers alike. You might be interested
in the archives at http://www.dbdebunk.com/ which IIRC contains some
articles arguing against nulls at all in a relational system.
--
Ri
data to an already partially normalised database.
How can newtable contain data if you don't have any keys for it?
Perhaps a fuller example, with the schemas of the tables in question
would help.
--
Richard Huxton
Archonet Ltd
---(end of broa
lls.
The key point of argument, and where the problem is with your (13 <
NULL)::BOOL point is this:
IT IS NOT MY PROBLEM !!! it is an EXAMPLE WHY WE CAN NOT PROHIBIT NULLS !!!
Umm - who is suggesting prohibiting nulls? I've re-read the entire
thread and can't find any such suggesti
301 - 400 of 1221 matches
Mail list logo
