=Special';
COMMENT
# \dd sched.days;
Schema | Name | Object | Description
+--++-
(0 rows)
You're using the wrong psql command. Try \d+ sched.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase
. What am I
doing wront?
fieldsep applies only to unaligned mode (\a or \pset format unaligned).
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
using non-holdable cursors you'll need
to be in a transaction block.
begin;
select * from getfoo('M');
fetch all in unnamed portal 14;
commit;
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire
expression; see above.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
? The solution might depend
on what you're trying to do.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
in an ordinary Perl script:
eval { do something };
if ($@) { handle the error }
--
Michael Fuhr
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
own spatial reference
system. However, I did a few tests with your parameters and various
datums for the lat/lon and couldn't get the exact transformed values
in your example.
You might get more help on the PROJ.4 and PostGIS mailing lists.
--
Michael Fuhr
---(end
the documentation for the correct way to use extract():
http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
/pgcrypto.
--
Michael Fuhr
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
, as you often don't need the full power of
pgcrypto is md5 suffices for your hashing needs.
You could make a proposal in pgsql-hackers but I think 8.3 is in
feature freeze so don't expect to see it until 8.4, if it's accepted
at all. There's always PgFoundry :-)
--
Michael Fuhr
-DIAGNOSTICS
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan
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
---(end of broadcast
the documentation to learn more about quote_ident() and
quote_literal() and when to use each:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
http://www.postgresql.org/docs/8.2/interactive/functions-string.html#FUNCTIONS-STRING-OTHER
--
Michael Fuhr
(). See String Functions and Operators
in the documentation for more information.
http://www.postgresql.org/docs/8.2/interactive/functions-string.html
--
Michael Fuhr
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project
that originated in Windows. Also, client_encoding can be set
by the client without having to restart the backend.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command
no quote character?
Are there any control characters that won't appear in the data?
--
Michael Fuhr
---(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
cur_objrefcursor;
query_obj text := 'SELECT mrc_xy_position FROM ' || quote_ident(tablename);
BEGIN
OPEN cur_obj FOR EXECUTE query_obj;
[...]
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
if call the function. Neither to use “execute” . Who can solve
it? Thanks!
Are you sure you need to use cursors? Can you not build the query
strings and use EXECUTE or FOR variable IN EXECUTE query LOOP?
--
Michael Fuhr
---(end of broadcast)---
TIP 1
://www.postgresql.org/docs/8.2/interactive/tutorial-transactions.html
PostgreSQL actually treats every SQL statement as being executed
within a transaction. If you do not issue a BEGIN command, then
each individual statement has an implicit BEGIN and (if successful)
COMMIT wrapped around it.
--
Michael
'));
--
Michael Fuhr
---(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
ON foo (lower(t)),
0);
In 8.2.3 the error location is:
LOCATION: RangeVarGetRelid, namespace.c:200
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Thu, Feb 08, 2007 at 11:14:33AM -0300, Alvaro Herrera wrote:
Michael Fuhr wrote:
The error appears to happen for anything that uses SPI. A C function
that executes the following fails with the same error:
SPI_exec(CREATE TABLE foo (t text); CREATE INDEX foo_idx ON foo
(lower(t
applies
to each command separately, but it is not applied to hidden commands
generated by rules.
When read_only is false, SPI_execute increments the command counter
and computes a new snapshot before executing each command in the
string.
Should that documentation be modified?
--
Michael Fuhr
the difficulties you're having. A minimal but complete example
that illustrates the problem might be useful.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
) = '';
SELECT * FROM tmpstk WHERE ean = NULL;
None of the above queries return any rows.
Checking for equality against NULL won't work unless you have
transform_null_equals set, which you shouldn't. Use IS NULL instead:
SELECT * FROM tmpstk WHERE ean IS NULL;
--
Michael Fuhr
definition or rule action; doing so would have to allow for the
possibility of a view column deriving its value from an arbitrarily
complex expression involving multiple tables, subqueries, etc.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you
. Does the
following show a Euro sign or does it show blank?
SELECT convert('\342\202\254', 'utf8', 'win1252');
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index
://www.postgresql.org/docs/8.2/interactive/release-8-2.html
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
each query?
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
2
(1 row)
test= SELECT greatest(2, 1);
greatest
--
2
(1 row)
test= SELECT greatest(6, 3, 1, 10, 9, 5, 2, 7, 8, 4);
greatest
--
10
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have
faster (if two geometries intersect then the distance between them
is 0).
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
, text category_sql) variant.
Unrelated suggestion: prices should probably be numeric instead of
floating-point due to the inexactness of the latter.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
and the macro you're missing.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
in another transaction reference
the same key then the delete will block until the other transaction
commits or rolls back; if the other transaction commits then the
delete will fail. In PL/pgSQL you can trap that failure with an
EXCEPTION clause that catches foreign_key_violation.
--
Michael
On Sat, Oct 14, 2006 at 07:58:06PM -0400, Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
On Sat, Oct 14, 2006 at 03:52:04PM +0200, Markus Schaber wrote:
Create an after delete trigger on the referencing table that checks
whether there still are records with the same key (IF EXISTS
On Sat, Oct 14, 2006 at 08:20:10PM -0400, Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
Unless the inserters got there first. I just tested both ways; if
the insert acquires the lock first then the delete fails, but if the
delete acquires the lock first then the insert fails
');
mcfix
-
John McNeil
(1 row)
You could use
$_[0] =~ s/\b(Ma?c)([a-z])/$1\u$2/g;
to change Mcdonald and Macdonald into McDonald and MacDonald,
respectively. However, since both Macdonald and MacDonald are used,
determining which is correct would be impossible.
--
Michael
?
Is there a reason you're maintaining customeraddress as a materialized
view rather than as a real view?
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
://archives.postgresql.org/pgsql-general/2005-09/msg00047.php
--
Michael Fuhr
---(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
elaborate.
--
Michael Fuhr
---(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
to be followed by most implementations:
--
Michael Fuhr
---(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
/docs/8.1/interactive/plpgsql.html
--
Michael Fuhr
---(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
...
See Date/Time Input, Date/Time Output, and Data Type Formatting
Functions in the documentation:
http://www.postgresql.org/docs/8.1/interactive/datatype-datetime.html
http://www.postgresql.org/docs/8.1/interactive/functions-formatting.html
--
Michael Fuhr
---(end
from reservation R
)
-- and not exits
Does the real query have exits instead of exists? I created
some test tables and fixed that typo and then both queries worked.
--
Michael Fuhr
---(end of broadcast)---
TIP
be END IF.
If you make the indicated changes then the function should be created
successfully. I didn't look closely at the logic, so whether it'll
actually work is another matter ;-)
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked
between databases isn't a good idea
but it looks like you've already had that discussion in the recent
Multiple DB join thread.
http://archives.postgresql.org/pgsql-sql/2006-08/msg00097.php
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget
source code distribution.
If you install PostgreSQL via packages then the contrib modules
might be in a package other than the base installation. If so then
the package will typically have the word contrib in its name.
--
Michael Fuhr
---(end of broadcast
as a static query but it might be worth testing.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
wondering
why the split? 20 million records isn't very big.
And why multiple databases instead of multiple schemas within the
same database? Or even all data in the same schema? Is there a
reason for the segregation?
--
Michael Fuhr
---(end of broadcast
will fail with relation with OID
X does not exist. See the FAQ for the reason and how to avoid
it:
http://www.postgresql.org/docs/faqs.FAQ.html#item4.19
Instead of using a temporary table, consider incorporating that
query directly into the main query/queries.
--
Michael Fuhr
. Try using numitems bigint instead of numitems int.
--
Michael Fuhr
---(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
statement be there?
--
Michael Fuhr
---(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
shown in the FAQ:
http://www.postgresql.org/docs/faqs.FAQ.html#item4.3
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your
://www.postgresql.org/docs/8.1/interactive/libpq-exec.html
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
and
Operators in the Functions and Operators chapter of the
documentation:
http://www.postgresql.org/docs/8.1/interactive/functions-formatting.html
http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html
--
Michael Fuhr
---(end of broadcast
directly instead of using information_schema. If you're
returning the results of a simple query, and if you can make that
query work without SECURITY DEFINER, then you could use a view
instead of a function.
--
Michael Fuhr
---(end of broadcast)---
TIP 3
(as PostgreSQL does with its ~, ~*, !*, and !~* operators).
--
Michael Fuhr
---(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
the version number in the above links if you're using a
version other than 8.1)
--
Michael Fuhr
---(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
stmt (integer) AS SELECT * FROM foo WHERE x = $1;
prepare_time| 2006-06-16 07:07:41.682999-06
parameter_types | {integer}
from_sql| t
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
one of the operands to numeric or one of
the floating-point types is yet another.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
, easily
created in earlier versions) then you could replace the CASE
expression with a cast (inspect_pass::integer). Whether to use the
more explicit CASE or the more concise cast is a matter of style.
--
Michael Fuhr
---(end of broadcast)---
TIP 4
digest()
for making SHA1, MD5, and other digests; hmac() for making Hashed
Message Authentication Codes; and encrypt()/encrypt_iv() and
decrypt()/decrypt_iv() for doing encryption and decryption. Since
8.1 pgcrypto also has functions for doing OpenPGP symmetric and
public-key encryption.
--
Michael
ON clause:
SELECT DISTINCT ON (date_trunc('hour', tstamp))
process, date_trunc('hour', tstamp) AS hour
FROM process
WHERE date_trunc('day', tstamp) = '2005-10-26'
ORDER BY date_trunc('hour', tstamp), tstamp;
--
Michael Fuhr
---(end of broadcast
result is empty. Try an
outer join:
SELECT *
FROM registrars LEFT OUTER JOIN notes USING (note_id)
WHERE regname = 'blah';
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe
the list archives for discussion.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
README.tablefunc.
--
Michael Fuhr
---(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
objects somewhere other than where the database was expecting.
What's the output of pg_config --libdir --version?
su postgres
psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql database
Or, omitting the su, psql -U postgres
--
Michael Fuhr
---(end of broadcast
is
(assuming that pg_config and the postmaster agree). Did the module
and the database come from the same source? If so then the
originator's packaging could use improving.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
a given aid's max weight:
SELECT aid, cat
FROM tablename AS t
JOIN (SELECT aid, max(weight) AS weight
FROM tablename
GROUP BY aid) AS s USING (aid, weight);
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
the entire table, not the
(aid, cat) pair with the max weight for a given aid.
--
Michael Fuhr
---(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
. The queries
could use LIMIT 2 instead of LIMIT 1 and update only the first row
that came back, but then you'd have the same problem with a third
concurrent transaction (and with LIMIT 3 and a fourth transaction,
and so on).
--
Michael Fuhr
---(end of broadcast
identifiers:
http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
? If not then please provide a more concrete
example that shows what you're trying to do.
CREATE VIEW foo AS
SELECT * FROM some_table
WHERE test_for_equality_is_syntactically_ugly;
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your
';
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
)
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
: time zone cst6cdt not recognized
This works only since 8.1. Here's an excerpt from the 8.1 Release
Notes:
* Allow the full use of time zone names in AT TIME ZONE, not just the
short list previously available (Magnus)
--
Michael Fuhr
---(end of broadcast
suggest something
else.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
in or the man pages. Anyone know where I can find such a
list?
The libpq documentation has a list of environment variables, although
it's not complete:
http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html
--
Michael Fuhr
---(end of broadcast
On Thu, Mar 02, 2006 at 01:16:47AM -0500, Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
The libpq documentation has a list of environment variables, although
it's not complete:
http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html
Er, what's not complete about
On Thu, Mar 02, 2006 at 12:33:31AM -0700, Michael Fuhr wrote:
On Thu, Mar 02, 2006 at 01:16:47AM -0500, Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
The libpq documentation has a list of environment variables, although
it's not complete:
http://www.postgresql.org/docs/8.1
inet_client_addr().
http://www.postgresql.org/docs/8.1/interactive/functions-info.html
I forget if earlier versions have a way to get the client's IP
address without resorting to hacks (e.g., writing a function in a
privileged language like plperlu and calling netstat or lsof).
--
Michael Fuhr
,
RIGHTARG = foo
);
SELECT * FROM foo ORDER BY foo USING ;
tree | caption
--+-
root.2 | c
root.2.1 | a
root.2.2 | b
root.4 | f
root.4.2 | c
root.4.1 | k
root.3 | i
root.1 | z
(8 rows)
--
Michael Fuhr
---(end of broadcast
. But as someone else mentioned, do
consider upgrading, if not to 8.1.3 or 8.0.7 then at least to 7.3.14.
Lots of bugs have been fixed in the three years since 7.3.2 was
released, some involving data loss.
--
Michael Fuhr
---(end of broadcast)---
TIP 9
an ancestor's label). That could be
expensive for a large table but it might be worth considering.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
' + x
FROM generate_series(0, date'2006-02-28' - date'2006-02-01') AS g(x);
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
on an ungranted lock?
Do other tables have foreign key references to ncccr10? If so then
you might need indexes on the referring columns.
What version of PostgreSQL are you running?
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0
: no notnull values, invalid stats
ANALYZE
postgis= UPDATE foo SET geom = GeomFromText('POINT(0 0)');
UPDATE 1
postgis= ANALYZE foo;
ANALYZE
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
#PLPGSQL-STATEMENTS-EXECUTING-DYN
--
Michael Fuhr
---(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
NEW.column is the same as
OLD.column.
Why the concern? Are the checks expensive? Do they have side
effects? What do they do?
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
(A)2 and A.a=B.b;
But I want to apply foo() to the tuples generated by the join
operation. How can I do that?
Is this what you're looking for?
select *
from (select * from A, B where A.a = B.b) as s
where foo(s) 2;
--
Michael Fuhr
---(end of broadcast
strings; just use your API's quote/escape (or whatever)
function or its placeholder mechanism (if it has one). If you're
using an interface that doesn't have any of these capabilities, what
is it? Some people might want to avoid it ;-)
--
Michael Fuhr
---(end of broadcast
it not work? Was any error message emitted? What is the view
definition?
I wonder if not work means didn't lock the rows and the cause
is simply not having a surrounding transaction.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you
the Release Notes for a summary of fixes since 8.0.4:
http://www.postgresql.org/docs/8.0/interactive/release.html#RELEASE-8-0-6
http://www.postgresql.org/docs/8.0/interactive/release-8-0-5.html
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget
/pgSQL, presumably because it's one
of the possible RAISE levels. You should also get an error if you
try 'exception', 'warning', etc.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
hacking. Anyone up for it?
Possibly. Would it involve much more than what the main parser's
grammar does with unreserved_keyword and friends? I suppose this
ought to move to pgsql-hackers.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you
and future versions).
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
for an outer join.
http://www.postgresql.org/docs/8.1/interactive/tutorial-join.html
http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
/
If you look around you should be able to find shapefiles with points
or polygons for cities and postal codes.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
1 - 100 of 356 matches
Mail list logo
