1 - why two databases? Couldn't you have just created two separate SCHEMAS?
2 - if you insist on two separate databases:
http://www.postgresql.org/docs/9.2/static/dblink.html
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On
Behalf Of Kaleeswaran Velu
Sent:
you are selecting from a set returning function in
the target list rather than the from clause. It should be more like:
SELECT * FROM crosstab(text, text) AS ( col1_name col1_type [, colN_name
colN_type]* )
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL
Should match to the data type of the filtered value, so CHAR,VARCHAR,All DATE
TYPES should be quoted. INTEGER, BIGINT, SMALLINT should not
-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On
Behalf Of Carlos Mennens
Sent: Thursday,
Check out DB Solo ...
http://www.dbsolo.com/
Does both DDL compare as well as data compare.
From: pgsql-sql-ow...@postgresql.org [pgsql-sql-ow...@postgresql.org] On Behalf
Of manuel antonio ochoa [manuel8aalf...@gmail.com]
Sent: Wednesday, February 23, 2011
You may want to try dblink.
http://www.postgresql.org/docs/current/static/dblink.html
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On
Behalf Of Shaun McCloud
Sent: Friday, December 03, 2010 10:51 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Union Question
-08
(10 rows)
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, 24x7 Support
signature.asc
Description: OpenPGP digital signature
|| ' RESTART WITH ' ||
startval;
EXECUTE sql;
RAISE NOTICE '%', sql;
END IF;
END LOOP;
RETURN 'OK';
END;
$$ LANGUAGE plpgsql STRICT;
select adjust_seqs('public');
8--
HTH,
Joe
--
Joe Conway
This is discussed in this Wiki:
http://wiki.postgresql.org/wiki/Grouping_Sets
-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On
Behalf Of Hiltibidal, Rob
Sent: Wednesday, June 02, 2010 12:06 PM
To: Oliveiros; Wes James;
I am trying to create a update trigger on a table that basically will only fire
when a specific column is updated. I am using version 8.4.3.
My plan of attack was to always fire on any row update, and pass in the OLD and
NEW column that I want to check.
CREATE TRIGGER check_lockout
AFTER
Nevermind all, I figured it out
Thanks Dmitriy ...
From: Dmitriy Igrishin [mailto:dmit...@gmail.com]
Sent: Thursday, May 06, 2010 3:25 PM
To: Plugge, Joe R.
Subject: Re: [SQL] Column Specific Update Trigger Routine
Hey Plugge,
You dont need to pass OLD.* or NEW.* to the trigger function
and ownerid = OLD.ownerid;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$;
Thanks !!
From: Justin Graf [mailto:jus...@magwerks.com]
Sent: Thursday, May 06, 2010 3:59 PM
To: Plugge, Joe R.; pgsql-sql@postgresql.org
Subject
Johnf,
I would think that the _p, _test, _r etc are local variables within the
procedure/function and this is the way that the value (from the select) gets
assigned to that local variable.
-Original Message-
From: pgsql-sql-ow...@postgresql.org
Mark,
Change your query to this:
SELECT id, count(*) FROM mytable GROUP BY id HAVING count(*) 2;
-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On
Behalf Of Mark Fenbers
Sent: Thursday, March 18, 2010 10:07 AM
To:
You may want to try Bucardo ... By performance, are you referring to latency?
If so, bandwidth between sites typically is the factor with latency in any
replication solution.
http://bucardo.org/
-Original Message-
From: pgsql-sql-ow...@postgresql.org
:= mystart + INTERVAL '1 minute';
mystop := mystop + INTERVAL '1 minute';
END LOOP;
END;
$$ LANGUAGE 'plpgsql' STABLE;
[cid:image002.jpg@01CA596B.59B9EC10]Joe R. Plugge
Database Administrator, West Interactive Corporation
11650 Miracle Hills Drive, Omaha NE 68154
402-716-0349
To: Plugge, Joe R.
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Function Syntax Help
2009/10/30 Plugge, Joe R. jrplu...@west.commailto:jrplu...@west.com
I am trying to create a function that will grind through a cdr table and
populate another table. I am trying to load the function and am getting
:= newstop + INTERVAL '1 minute';
END LOOP;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;
From: epai...@googlemail.com [mailto:epai...@googlemail.com] On Behalf Of Brian
Modra
Sent: Friday, October 30, 2009 2:46 PM
To: Plugge, Joe R.
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Function Syntax Help
was applied to
both invoices A and B, you need to group the invoices so that you can
compare total invoiced against total paid.
Joe
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
in the database that is
using en_US collation, but what I would really prefer is the reverse.
BTW, where are those operators documented? Neither Google nor Yahoo nor
postgresql.org search return anything.
Joe
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your
shared with others at our hosting provider. Is there some way to
override the cluster setting, or plans to allow for database-specific
collation orders?
Joe
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql
which is
using the en_US locale, the double quotes are disregarded for ordering
purposes, e.g.,
Medical
Meet
Message
Joe
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
, you
can check sqlca.sqlcode for 100 (NOT FOUND) or a SQLSTATE of 02000.
Joe
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
://www.devx.com/opensource/Article/29071/0/page/3.
Joe
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
the PEP 249
(http://www.python.org/dev/peps/pep-0249/). The execute() and
executemany() Cursor object methods are precisely to prepare and execute
database operations.
Joe
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
;
will result in:
col1 | col2 | col3
--+--+---
1 | 789 | third record
2 | 456 | second record
3 | 789 | third record
So, it is a join ... of a table with a virtual copy of itself.
Joe
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes
would fail in
subsequent releases.
Joe
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
are not digits; they're something or other
in Arabic, apparently.
Precisely. 1777 through 1780 decimal equate to code points U+06F1
through U+06F4, which correspond to the Arabic numerals 1 through 4.
Joe
---(end of broadcast)---
TIP 6: explain
specific. The HTML/Perl script ought to
convert to Western numerals.
Joe
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
/postgresql/timezone.
Joe
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
in PL/Perl and/or PL/Python.
Joe
---(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
those of us who've
taken even a mild interest in TML that it's really needed or is a better
solution than what exists today.
Joe
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
Dmitry,
On Thu, 2007-04-26 at 11:33 +0300, Dmitry Turin wrote:
Joe, i speak not about you, but about statistics.
Do you actually have statistics of how many people in the general
population have learned SQL? And furthermore, how many of those people
didn't already know or didn't want to bother
. Needs are produced also, as goods and capital goods.
Karl Marks
For example, look at yourself.
We are on diametrically opposed sides of that argument, but it's
off-topic, so I'll leave it alone.
Joe
---(end of broadcast)---
TIP 9: In versions
to PostgreSQL that 80% of the users will use, but it will not
be because you or I or even the PG Global Dev Group leaders say it's
better or it's necessary, but because thousands of users may agree
and converge on those choices.
Joe
---(end of broadcast
://philip.greenspun.com/sql/data-modeling.html), and how it can
answer the simple and complex queries in the next two chapters (or as I
said, pick a real-life example of your own) and then maybe we'll have a
more fruitful conversation.
Joe
---(end of broadcast
and PHP/Perl/etc. will be relegated to
the dustbin of programming languages. [Sorry, couldn't resist :-) ]
Joe
* see http://en.wikipedia.org/wiki/Geico
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
. It appears TML is primarily for
joins.
Lastly, Dmitry, I think you'll be better off discussing this in
comp.databases.theory.
Joe
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index
isn't free. Therefore, you do it the same way
any other row gets expired.
Just curious: is there a way to defeat MVCC? i.e., if you can lock the
database exclusively, there won't be any readers or writers?
Joe
---(end of broadcast)---
TIP 4: Have
.
As suggested by the error, you should use a cast, e.g.,
insert into test_a values 9::bit(3);
This will result in binary '001' being inserted because you need 4 bits
to represent decimal 9.
Joe
---(end of broadcast)---
TIP 1: if posting/reading through
://www.postgresql.org/docs/8.2/static/functions-bitstring.html
Joe
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
and you're trying to cast it to bit, which is what the ERROR was telling
you can't do.
Joe
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
/pgSQL function audio_format_func near line
15
The constant is no_data. See
http://www.postgresql.org/docs/8.2/static/errcodes-appendix.html
Joe
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
.* from tabel1 as tb1, table2 as tb2
where tb2.id = 2
and tb1.fk_tb2ID = tb2.id;
Joe
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
of
what you're looking for since they have to measure themselves against
the competition.
And remember to take any numbers with a large grain of salt, YMMV, etc.
Joe
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
\d commands (see Patterns), so multiple tables can
also be selected by writing wildcard characters in the pattern.
But note that this is a new feature in 8.2. In 8.1 and earlier,
multiple -t switches will only get you the last one specified.
Joe
---(end of broadcast
to see how that compares performance-wise.
Joe
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
,
story.title, person.firtname, person.lastname, etc.
What is the most elegant way to build a single query to match search
words with multiple columns?
You may want to take a look at contrib/tsearch2.
Joe
---(end of broadcast)---
TIP 9
) by 1000.
Joe
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
work,
i.e., 'c:/autodrs_appraisal_new.txt'.
Joe
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
,
41509,
41510,
41511,
41512,
41513,
41514,
41515,
41516,
41517,
41518,
41519,
41520,
41521,
41522,
41523,
41524,
41525,
41526,
41527,
41528,
Remove the extra comma.
)
end;
Remove 'end'.
Commit;
Joe
---(end of broadcast
AND x 5?
Can't it reduce that to FALSE?
Joe
---(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
but it would be to a boolean logic
analyzer.
As to whether these query instances represent few or are typical is
arguable, and will depend on the type of application, level of knowledge
among users, and what kind of interfaces are used to enter or generate
the queries.
Joe
---(end
at the setting of redirect_stderr, and the various logging
settings just above it?
Joe
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
a query that will subtract atime of row 1 from row 2, row2
from row3 and so on...
Can anyone please help?
How about something like this
select x.id, x.atime, x.atime - y.atime as diff
from yourtable x, yourtable y
where x.id + 1 = y.id;
Joe
---(end of broadcast
numbers with a large grain of salt, YMMV, etc.
Joe
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
?
Sorry for having gone off-topic. I was just hoping something like \t
could be passed, too.
Try:
psql -A -t -U $DBUSER -d $DB -c $QUERY -F $'\t'
see:
man bash
HTH,
Joe
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
is how I drop the millisecond??
Take a look at date_trunc() under Date/Time Functions and Operators.
Joe
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about
want to cast the field, not the constant, e.g.,
testdb= select * from t2 where date(tm) = '2006-9-6';
tm
---
2006-09-06 00:00:01-04
2006-09-06 23:59:59.99-04
(2 rows)
Joe
---(end of broadcast
always have to hunt this down when I need it.
You mean something like \du at the psql prompt?
Joe
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
can
either use UPPERCASE (as I believe Oracle and DB2 do) or lowercase (as
PG does) when it displays those identifiers.
Joe
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan
just the one single column. Ick.
I didn't realize before that you can also drop all columns, leaving a
table without *any* columns. Is that a SQL92 feature?
Joe
---(end of broadcast)---
TIP 4: Have you searched our list archives
. Expression Evaluation Rules of the manual:
The order of evaluation of subexpressions is not defined. In particular,
the inputs of an operator or function are not necessarily evaluated
left-to-right or in any other fixed order.
There's more examples there too.
Joe
---(end
declared numRegistros.
Joe
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Aaron Bono wrote:
Are there any web based management tools for PostgreSQL (like Mysql PHP
Admin except for PostgreSQL)? I thought I saw a post sometime back
about one but don't remember the name.
Yes, that's phpPgAdmin (http://phppgadmin.com).
Joe
---(end
in two locations:
that's a matter of safety. Slony does it automatically, as long as
the daemon is running. No need to control it.
But with file-based log shipping (see
http://linuxfinances.info/info/logshipping.html) one could write a Java
app to control when the updates are applied.
Joe
-
Uh ... how do you arrive at that conclusion? I haven't done the math,
but by eyeball an average of four-something days doesn't look out of
line for those values.
It seems he's calculating (7 + 9 + 23 + 4 + 3) / 5 ...
Joe
---(end of broadcast
-06-07 22:24:00
2006-06-09 22:21:00
2006-05-31 23:21:00
2006-06-04 22:47:00
2006-06-03 06:05:00
(5 rows)
test= select avg(t - '2006-5-31 0:0'::timestamp) from x2;
avg
-
5 days 09:47:36
(1 row)
Joe
---(end of broadcast
in essence the query is used
to materialize a view, i.e., it's part of an INSERT / SELECT into a
table which is then joined back to the other tables to construct a web
page as well as an RSS feed.
Joe
---(end of broadcast)---
TIP 9: In versions below 8.0
changes would be
somewhat insulated by the layered views.
Best regards,
Joe
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
they're actually new.
However, although creating these views may simplify the subqueries it
doesn't seem there is a way to avoid the eight-way UNION, or is there?
TIA
Joe
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore
representation of the columns). An
interesting side effect was discovering data inconsistencies in the
MySQL database since as part of the conversion I implemented foreign key
constraints under PostgreSQL (which were missing in the former).
Joe
---(end of broadcast
from t1 group by g;
g |a1
---+---
1 | 0.923076923076923
0 | 0
(2 rows)
HTH,
Joe
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command
(band_id, album_id)
)
This of course precludes the same band being listed twice in a given
album. If you do need that info, then you're really asking for tracks.
Joe
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore
or items columns (or subcolumns by type).
Thanks for any suggestions and comments.
Joe
---(end of broadcast)---
TIP 6: explain analyze is your friend
]={}', just as Joe shows ... or at least it would be except
for an overenthusiastic error check:
regression=# select '[1:0]={}' :: int[];
ERROR: upper bound cannot be less than lower bound
I think this should be a legal boundary case. In general, it should be
possible to form zero-size arrays
Bruce Momjian wrote:
Joe Conway wrote:
Any thoughts on how this should be handled for an empty 1D array?
No one responed to this email, so I will try. Is this the one
dimmentional array you were talking about?
test= select array_dims('{}'::integer[]);
array_dims
for an empty 1D array?
The point Markus is complaining about seems like it should
be easily fixable.
Well, easily is a relative term. My Postgres hacking neurons have
gotten kind of rusty lately -- but then maybe that was your underlying
point ;-)
Joe
Index: src/backend/executor/nodeSubplan.c
SET f1 = encrypt(decrypt(f1, 'oldkey', 'aes'), 'newkey', 'aes');
Joe
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get
README.dblink.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
) as t(f1);
?column?
2004-12-01
2004-12-02
2004-12-03
2004-12-04
2004-12-05
[...]
2004-12-20
(20 rows)
HTH,
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister
---
{1}
{1,1}
{1,1,1}
{2}
{2,2}
{2,2,2}
(6 rows)
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
-
PostgreSQL 8.0.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)
(1 row)
Look for a fix soon, at a cvs repository near you
Joe
---(end of broadcast)---
TIP 5
.
Interesting idea -- thanks!
Joe
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
to go. crosstab just wraps the procedural code
in an SRF for you.
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
that go to the list might get lost among the
hundreds of posts that go into my postgres inbox every day. I think
many other people do something similar.
Recipients that prefer not to get separate copies can indicate that desire
by including an appropiate mail-followup-to header.
Also true.
Joe
Josh Berkus wrote:
[2] Wrong datatype for second argument in call to in_array
SQL: SELECT sf_event_decendants(66645,111)
Are you sure this message isn't coming from some PHP middleware, e.g.
peardb or something. See:
http://us2.php.net/manual/en/function.in-array.php
Joe
|1 | A | A
2 | two |2 | Z | Z
3 | three |1 | A | A
(3 rows)
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
roughly behave like individual databases do in MSSQL. See:
http://www.postgresql.org/docs/7.4/static/ddl-schemas.html
If you really need cross-database queries, see contrib/dblink (which
would also work across servers), but it isn't as flexible as using schemas.
HTH,
Joe
)
That is the hashed version that will do what you're looking for.
HTH,
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
-
{1,2,3}
(1 row)
Second line:
if ($r=~/^-([0-9]?)([A-z_]+)/)
{
my $locid = $1;
my $table = $2;
Not sure about this one. Hopefully someone else can chime in. Maybe a
little less efficient, but it seems like it would be easy enough to
parse when true.
HTH,
Joe
---(end
, attempting to
construct an array with a NULL element results in NULL, not an empty array.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
-- there
are some non-trivial technical difficulties to be solved. Unfortunately
that won't change in the upcoming 7.5 either :( -- perhaps for 7.6.
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send
-- there
are some non-trivial technical difficulties to be solved. Unfortunately
that won't change in the upcoming 7.5 either :( -- perhaps for 7.6.
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send
/static/arrays.html
http://www.postgresql.org/docs/7.4/static/functions-array.html
http://www.postgresql.org/docs/7.4/static/functions-comparisons.html
http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
HTH,
Joe
---(end of broadcast
date_trunc('month', current_date + '1 month'::interval);
date_trunc
-
2004-03-01 00:00:00
(1 row)
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister
to this:
regression=# select NULL || 'blah';
?column?
--
(1 row)
HTH,
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
varchar[]
results := parseString(''abc,def,ghi'','','');
tmpv := results[1];
RAISE NOTICE '' tmpv = % '',tmpv;-- tmpv will be null.
END;
How is parseString() defined? What Postgres version?
Joe
---(end of broadcast)---
TIP 9: the planner
Bruce Momjian wrote:
Martin Marques wrote:
Also, for some reason I can't get to sqlstandards.org. Is there any other
place where I can get the SQL200X docs?
Not sure.
See:
http://www.wiscorp.com/sql/sql_2003_standard.zip
Joe
---(end of broadcast
it, I think it is related to (or at least similar to)
the work Tom is currently doing to allow composite types as table
attributes.
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
wind up with a NULL result. It is
similar to:
regression=# select (NULL || 'abc') is null;
?column?
--
t
(1 row)
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
1 - 100 of 205 matches
Mail list logo
