[GENERAL] Date / interval question
Hi List; I'm populating a time dimension. I need to get the number of days since the start of the fiscal year and also the number of months since the start of the fiscal year based on the current 'date' being processed. Example: my current process date is 01/01/2007 start date of fiscal year is 09/01/2006 I can get the number of days since the start of the fiscal year like this: # select date '01/01/2007' - date '09/01/2006' as interval; interval -- 122 (1 row) However I'm stumped [er how to get the number of months from 09/01/2007 thru 01/01/2007 Thoughts ? Thanks in advance... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgcrypto and dblink
[EMAIL PROTECTED] ("Roberts, Jon") writes:
> I am moving from Windows to Solaris and I need pgcrypto and dblink.
> Where are these? I don't see anything in the configure that suggests it
> is even an option.
They are part of the set of "contrib" functions.
You head to directory "contrib", and, if those are the only ones you
need, head assortedly to:
a) contrib/pgcrypto, and run "make install" to install that, then
b) contrib/dblink, and (surprise!) run "make install" to install it.
That compiles anything that needs to be compiled, and stows the object
code in the installation's "lib" area, and stows scripts to activate
the respective services in the installation's "share" area.
So step c) and d) would be...
c) Load, into whichever databases you want to use these functions in,
the script
share/contrib/pgcrypto.sql
d) Load, into whichever databases you want to use these functions in,
the script
share/contrib/dblink.sql
--
(reverse (concatenate 'string "ofni.secnanifxunil" "@" "enworbbc"))
http://cbbrowne.com/info/languages.html
Monday is an awful way to spend one seventh of your life. -- Unknown
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Way to shutdown/freeze/stop an individual database without taking postmaster down?
[EMAIL PROTECTED] ("W S") writes:
> I was asked this question, and I wasn't sure if it is possible:
>
>
> do you know of a way to stop just one database (not delete/drop) on
> our PostgreSQL 8.1 server?
>
>
> And, while I know how to shut down postmaster, and/or put in rules to
> pg_hba.conf to limit access to a certain database, is there any way to
> freeze or stop just one database and not others? I'm attempting to
> RTFM it, but so far I've had no luck.
You could put in rules to pg_hba.conf to shut users out of a
particular database, and then run "pg_ctl reload" to signal the
postmaster to start applying the new rules.
That will prevent any new connections from coming into the database in
question.
But does not get rid of existing connections. You could kill the
backends associated with the existing connections... You can query
pg_catalog.pg_stat_activity to find the relevant list.
--
let name="cbbrowne" and tld="linuxdatabases.info" in String.concat "@"
[name;tld];;
http://cbbrowne.com/info/advocacy.html
Whatever is contradictory or paradoxical is called the back of God.
His face, where all exists in perfect harmony, cannot be seen by man.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can´t connect but listen address and pg_hba configured correctly
On Thu, Apr 10, 2008 at 3:00 PM, Marcelo de Moraes Serpa <[EMAIL PROTECTED]> wrote: > Thank you for the replies, > > Actually I did not change the port number. For some bizarre reason, the > pgsql 8.1 debian package comes with port 5433 pre-configured and this was > exactly was causing the problem, of course, I wasn't noting that the port > was different, but they are so similar (that's what happens when you have a > stressful day of work..). I changed it to 5432 and everything went fine. > > Do you see how little changes can make a **big** difference. I wonder what > was going through the head of the person who altered the port number for > this release... Actually, what debian and ubuntu do is allow you to have > 1 version of pgsql installed at a time, and each version gets the next available port. So, it's likely your machine once had another version like 8.0 on it, and then had 8.1 added. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can´t conne ct but listen address and pg_hba configured correctly
On Thu, Apr 10, 2008 at 06:00:30PM -0300, Marcelo de Moraes Serpa wrote: > Thank you for the replies, > > Actually I did not change the port number. For some bizarre reason, the > pgsql 8.1 debian package comes with port 5433 pre-configured and this was > exactly was causing the problem, of course, I wasn't noting that the port > was different, but they are so similar (that's what happens when you have a > stressful day of work..). I changed it to 5432 and everything went fine. Debian allows parallel installs. In all likelyhood you had 7.4 still installed using port 5432 and then you installed 8.1 which got assigned the next available port. It would have mentioned this during installation and also pg_lsclusters would have told you this. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Can´t connect but listen address and pg_hba configured correctly
Thank you for the replies, Actually I did not change the port number. For some bizarre reason, the pgsql 8.1 debian package comes with port 5433 pre-configured and this was exactly was causing the problem, of course, I wasn't noting that the port was different, but they are so similar (that's what happens when you have a stressful day of work..). I changed it to 5432 and everything went fine. Do you see how little changes can make a **big** difference. I wonder what was going through the head of the person who altered the port number for this release... Thanks, Marcelo. On Thu, Apr 10, 2008 at 5:50 PM, Kyle Wilcox <[EMAIL PROTECTED]> wrote: > Did you purposely change the default port? Are you specifying the change > in pgadmin? If the pg_hba.conf file is the problem, pgadmin will tell you > with a message like: > > FATAL: no pg_hba.conf entry for host "IPADDRESS", user "USER", database > "DATABASE", SSL ON/OFF > > Are you getting a similar message or is the connection timing out? > > > Marcelo de Moraes Serpa wrote: > > > I can´t connect to my postgresql8.1 server running on Debian. The > > pgadmin > > client says it can't connect. I already edited the pg_hba.conf and > > postgresql.conf (listen_addresses = '*' and port) but the problem > > remains, > > pg_admin is running on a XP machine without Firewalls enabled, Debian is > > also not running any kind of firewall. I should also note the I just > > upgraded to 8.1, some minutes ago I was running 7.4 and connections > > **were > > working fine**. > > > > pg_hba.conf: > > > > http://www.pastebin.ca/980122 > > > > postgresql.conf (connection settings section): > > > > http://www.pastebin.ca/980147 > > > > PostgreSQL 8.1 Debain Etch package. > > > > Any hints greatly appreciated! > > > > Marcelo. > > > > > -- > > Kyle Wilcox > NOAA Chesapeake Bay Office > 410 Severn Avenue > Suite 107A > Annapolis, MD 21403 > office: (410) 295-3151 > [EMAIL PROTECTED] > > A: It takes over twice as long to understand the conversation. > Q: What's wrong with top-posting? > A: Top-posting. > Q: What's the worst thing about plain text email discussions? >
Re: [GENERAL] pgcrypto and dblink
> > I am moving from Windows to Solaris and I need pgcrypto and dblink. > > Where are these? I don't see anything in the configure that suggests > it > > is even an option. > > They're not handled by 'configure'. They are in the 'contrib' > directory in the source tree, and you install them by first installing > PG itself, then go into the module directory, e.g. 'contrib/pgcrypto', > and running 'make'. > Thanks so much! Wouldn't it make sense to add a section to this page that describes the contrib process? http://www.postgresql.org/docs/8.3/static/install-post.html I had thought all of the installation options were set using configure. A post installation step is fine but I think it needs to be documented as such. Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can´t connect but listen addr ess and pg_hba configured correctly
Did you purposely change the default port? Are you specifying the change in pgadmin? If the pg_hba.conf file is the problem, pgadmin will tell you with a message like: FATAL: no pg_hba.conf entry for host "IPADDRESS", user "USER", database "DATABASE", SSL ON/OFF Are you getting a similar message or is the connection timing out? Marcelo de Moraes Serpa wrote: I can´t connect to my postgresql8.1 server running on Debian. The pgadmin client says it can't connect. I already edited the pg_hba.conf and postgresql.conf (listen_addresses = '*' and port) but the problem remains, pg_admin is running on a XP machine without Firewalls enabled, Debian is also not running any kind of firewall. I should also note the I just upgraded to 8.1, some minutes ago I was running 7.4 and connections **were working fine**. pg_hba.conf: http://www.pastebin.ca/980122 postgresql.conf (connection settings section): http://www.pastebin.ca/980147 PostgreSQL 8.1 Debain Etch package. Any hints greatly appreciated! Marcelo. -- Kyle Wilcox NOAA Chesapeake Bay Office 410 Severn Avenue Suite 107A Annapolis, MD 21403 office: (410) 295-3151 [EMAIL PROTECTED] A: It takes over twice as long to understand the conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the worst thing about plain text email discussions? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can´t connect but listen address and pg_hba configured correctly
On Thu, Apr 10, 2008 at 1:26 PM, Marcelo de Moraes Serpa < [EMAIL PROTECTED]> wrote: > I can´t connect to my postgresql8.1 server running on Debian. The pgadmin > client says it can't connect. I already edited the pg_hba.conf and > postgresql.conf (listen_addresses = '*' and port) but the problem remains, > pg_admin is running on a XP machine without Firewalls enabled, Debian is > also not running any kind of firewall. I should also note the I just > upgraded to 8.1, some minutes ago I was running 7.4 and connections **were > working fine**. > > pg_hba.conf: > > http://www.pastebin.ca/980122 > > postgresql.conf (connection settings section): > > http://www.pastebin.ca/980147 > > PostgreSQL 8.1 Debain Etch package. > > Any hints greatly appreciated! Your postgresql.conf has port set to 5433 (PostgreSQL default port is 5432). Did you change the port in pgadmin to connect to 5433? Joey
Re: [GENERAL] how to use transaction isolation
On 4/10/08, Gong <[EMAIL PROTECTED]> wrote:
>
>
> In the java code below, I set the transaction isolation to serialization.
>
> public class IsolationTest {
>
> private static String select = "select * from tmp where url = 'aaa'";
>
> public static void main(String[] args) throws Exception{
> //ConncetionFactory is a factory class for managing connection
> Connection con = ConnectionFactory.getConnection();
>
>
> con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
> con.setAutoCommit(false);
>
> Statement smt = con.createStatement();
> ResultSet r1 = smt.executeQuery(select);
> System.out.println(r1.next()); //(1)
> con.commit(); //(2)
>
> ResultSet r2 = smt.executeQuery(select);
> System.out.println(r2.next()); //(3)
> con.commit();
> smt.close();
>
> ConnectionFactory.closeConnection();
> }
> }
>
> I set a break point at (2), then I run this code in debug mode. When it
> suspended at (2), line(1) print "false". Then, I execute an insert statement
> in pgadmin: insert into tmp values('aaa'), after that I continued to run the
> code, and line(3) print "true". I have set the transaction isolation to
> serialization, didn't the two select statements print the same result?
Your commit at (2) ends the transaction, and the second select runs in
a new one.
jan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgcrypto and dblink
On Thu, Apr 10, 2008 at 3:46 PM, Roberts, Jon <[EMAIL PROTECTED]> wrote: > I am moving from Windows to Solaris and I need pgcrypto and dblink. > Where are these? I don't see anything in the configure that suggests it > is even an option. They're not handled by 'configure'. They are in the 'contrib' directory in the source tree, and you install them by first installing PG itself, then go into the module directory, e.g. 'contrib/pgcrypto', and running 'make'. -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgcrypto and dblink
Roberts, Jon escribió: I am moving from Windows to Solaris and I need pgcrypto and dblink. Where are these? I don't see anything in the configure that suggests it is even an option. Jon They are contribs, you have to install them after pgsql smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] Can´t connect but listen address and pg_hba configured correctly
I can´t connect to my postgresql8.1 server running on Debian. The pgadmin client says it can't connect. I already edited the pg_hba.conf and postgresql.conf (listen_addresses = '*' and port) but the problem remains, pg_admin is running on a XP machine without Firewalls enabled, Debian is also not running any kind of firewall. I should also note the I just upgraded to 8.1, some minutes ago I was running 7.4 and connections **were working fine**. pg_hba.conf: http://www.pastebin.ca/980122 postgresql.conf (connection settings section): http://www.pastebin.ca/980147 PostgreSQL 8.1 Debain Etch package. Any hints greatly appreciated! Marcelo.
[GENERAL] pgcrypto and dblink
I am moving from Windows to Solaris and I need pgcrypto and dblink. Where are these? I don't see anything in the configure that suggests it is even an option. Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Way to shutdown/freeze/stop an individual database without taking postmaster down?
On Apr 10, 2008, at 2:37 PM, Craig Ringer wrote: I haven't checked whether pg_hba.conf rules are reloaded, but `pg_ctl reload' can re-read some settings without a postmaster restart. So you might be able to disallow access in pg_hba then reload. I'd be curious to know if that works and if/how it affects existing connections - though I can always test it myself. Yes, a pg_ctl reload will reload pg_hba.conf and, iirc, it does not kill any existing connections as the values in that file are checked at time of connection, not time of config load. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to use transaction isolation
In the java code below, I set the transaction isolation to serialization.
public class IsolationTest {
private static String select = "select * from tmp where url = 'aaa'";
public static void main(String[] args) throws Exception{
//ConncetionFactory is a factory class for managing connection
Connection con = ConnectionFactory.getConnection();
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
con.setAutoCommit(false);
Statement smt = con.createStatement();
ResultSet r1 = smt.executeQuery(select);
System.out.println(r1.next()); //(1)
con.commit(); //(2)
ResultSet r2 = smt.executeQuery(select);
System.out.println(r2.next()); //(3)
con.commit();
smt.close();
ConnectionFactory.closeConnection();
}
}
I set a break point at (2), then I run this code in debug mode. When it
suspended at (2), line(1) print "false". Then, I execute an insert statement in
pgadmin: insert into tmp values('aaa'), after that I continued to run the code,
and line(3) print "true". I have set the transaction isolation to
serialization, didn't the two select statements print the same result?
btw: postgresql version is 8.2, jdbc version is postgresql-8.2-506.jdbc3.jar,
and jdk version is 1.5
Re: [GENERAL] percentile rank query
On Thu, Apr 10, 2008 at 05:20:21PM +0100, William Temperley wrote: > SELECT count(*) AS frequency, score, > SELECT count(uid) FROM scoretable st2 WHERE st2.score <= > st1.score) - count(*)) + (count(*)/2))::float/(select > count(*) from scoretable)) > > FROM scoretable st1 > GROUP BY score > ORDER BY score > > I think that's a percentile rank now. I'm not quite sure how this is calculated but I think you may want to be converting to a non-integral type earlier (i.e. as you're dividing by two, not after). I also find all the subselects a bit difficult to follow so have moved them around: SELECT x.frequency, x.score, (x.rank + x.frequency / 2.0) / y.total AS pr FROM ( SELECT count(*) AS frequency, score, (SELECT count(*) FROM scoretable t WHERE t.score < s.score) AS rank FROM scoretable s GROUP BY score) x, (SELECT count(*) AS total FROM scoretable) y ORDER BY score; Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Way to shutdown/freeze/stop an individual database without taking postmaster down?
W S wrote: > Greetings, > > I was asked this question, and I wasn't sure if it is possible: > > > do you know of a way to stop just one database (not delete/drop) on > our PostgreSQL 8.1 server? One possible way: You can update its pg_database record, setting datallowconn to 'f' to disable new connections to the DB, then boot off all existing users of it by killing their backends. A discussion about this appeared here recently, and a search of the archives for `datallowconn' should find it. > And, while I know how to shut down postmaster, and/or put in rules to > pg_hba.conf to limit access to a certain database, is there any way to > freeze or stop just one database and not others? I'm attempting to > RTFM it, but so far I've had no luck. I haven't checked whether pg_hba.conf rules are reloaded, but `pg_ctl reload' can re-read some settings without a postmaster restart. So you might be able to disallow access in pg_hba then reload. I'd be curious to know if that works and if/how it affects existing connections - though I can always test it myself. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Way to shutdown/freeze/stop an individual database without taking postmaster down?
What do you hope to get out of this that you wouldn't get out of locking out access? On Thu, 10 Apr 2008, W S wrote: Greetings, I was asked this question, and I wasn't sure if it is possible: do you know of a way to stop just one database (not delete/drop) on our PostgreSQL 8.1 server? And, while I know how to shut down postmaster, and/or put in rules to pg_hba.conf to limit access to a certain database, is there any way to freeze or stop just one database and not others? I'm attempting to RTFM it, but so far I've had no luck. Thanks, Will -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to use postgre sql from inside process
On Thu, Apr 10, 2008 at 11:25 AM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Thu, Apr 10, 2008 at 5:45 AM, CMOS <[EMAIL PROTECTED]> wrote: > > hi, > > i would like to get services of postgresql from inside the process (to > > use it as a library and linking to it), i.e not having a separate > > postgresql process and communicating with it. Is this possible? > > No. > > > > if possible what are the impact on licensing. > > None. It's BSD. If you wanna try and hack up something like that go > ahead. Generally you're better off with SQLLite. Yes, it's theoretically possible, but making PG into a library would be a LOT of work. Don't even think about it. Use SQLLite if you need that kind of functionality. -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Way to shutdown/freeze/stop an individual database without taking postmaster down?
Greetings, I was asked this question, and I wasn't sure if it is possible: do you know of a way to stop just one database (not delete/drop) on our PostgreSQL 8.1 server? And, while I know how to shut down postmaster, and/or put in rules to pg_hba.conf to limit access to a certain database, is there any way to freeze or stop just one database and not others? I'm attempting to RTFM it, but so far I've had no luck. Thanks, Will -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ODBCng vs psqlODBC
Hello, they're the 2 available ODBC drivers for PG, which one is better to use. If they're equals, what are the circumstances to use one over the other. Thanks Bruno Lavoie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] percentile rank query
On Thu, Apr 10, 2008 at 4:36 PM, Osvaldo Rosario Kussama <[EMAIL PROTECTED]> wrote: > > Try: > > SELECT count(*) AS frequency, score, > count((SELECT * FROM scoretable st2 WHERE st2.score <= st1.score)) AS > runningtotal > FROM scoretable st1 > GROUP BY score > ORDER BY score > > Osvaldo > Thankyou Osvaldo- that worked! Final version: SELECT count(*) AS frequency, score, SELECT count(uid) FROM scoretable st2 WHERE st2.score <= st1.score) - count(*)) + (count(*)/2))::float/(select count(*) from scoretable)) FROM scoretable st1 GROUP BY score ORDER BY score I think that's a percentile rank now. Cheers Will -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] select statement fails
"Andrus" <[EMAIL PROTECTED]> writes: > 1. SQL assumes that CHARACTER(n) column is always padded with spaces in > right. > So casting to text should preserve spaces. No, it should not. In CHAR(n), trailing spaces are semantically insignificant; 'foo' and 'foo ' are considered equal. In TEXT they are just as significant as any other character, and those strings are definitely not equal. So 'foo ' as CHAR(4) and 'foo ' as TEXT do not actually mean the same thing at all, and similarly ' ' means two different things as CHAR(1) and as TEXT, even though they look the same. The SQL spec's definition of CHAR(n) behavior is really pretty broken in my opinion; you're almost always better off using varchar. In this particular case, where you think that a space has semantic significance, CHAR(n) is simply wrong. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] select statement fails
On Apr 10, 2008, at 3:13 AM, Andrus wrote: Albe, select * from test where tc=' '::text; Because the arguments to the operator "=" are of different type, implicit type conversion takes place. "character(1)" will by converted to "text", during this conversion trailing blanks will be ignored, as befits the "character(n)" type. Thank you. 1. SQL assumes that CHARACTER(n) column is always padded with spaces in right. That is only for storage and display. So casting to text should preserve spaces. Why PostgreSQL cast to text violates SQL ? It doesn't and it is right there in the manual: "Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type character, and they will be removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values." http://www.postgresql.org/docs/current/interactive/datatype-character.html 2. create table test ( tc char(1) ); create index tc on test(tc); select * from test where tc='x'::text; I'm afraid that if test table has large number of rows, PostgreSQL is not capable to use index for this query doe to the cast to text. Is it so ? You have two options: 1. Just us text for the column's data type. 2. Create an index on the column cast as text: CREATE INDEX test_tc_txt_idx ON test (tc::text); Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] select statement fails
On Thu, Apr 10, 2008 at 11:13:33AM +0300, Andrus wrote: > 1. SQL assumes that CHARACTER(n) column is always padded with spaces in > right. > So casting to text should preserve spaces. > Why PostgreSQL cast to text violates SQL ? It says it is padded with spaces, but it also says that these spaces are insignificant and should be ignored in certain contexts. This area of the spec is poorly worked out, see http://archives.postgresql.org/pgsql-sql/2004-02/msg00229.php for some examples of where the behaviour you want doesn't work. > 2. > > create table test ( tc char(1) ); > create index tc on test(tc); > select * from test where tc='x'::text; > > I'm afraid that if test table has large number of rows, PostgreSQL is not > capable to use index for this query doe to the cast to text. > Is it so ? PostgreSQL does have the concept of cross-type index operators, so the above may work in recent versions. On the other hand, you could just drop the cast and it will always work. Seems odd you add a cast explicitly to a type different from the column you are comparing to. It's just asking for trouble. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Proper Installation of Postgres and Postgis on 10.5 Intel
Stefan Schwarzer wrote: I tried now for weeks to get postgres & postgis going on my machine, in vain... Lots of frustration has been built up, lots of energy went into it... But nothing goes... I mean, postgres is running, postgis is installed, but it constantly craches, doesn't accept shp2pgsql imports etc... What error messages have you got? What ./configure options did you use? What crashes - the client process running the command? or the whole postgres server? Do you have any core dumps? Or crash logs? Does shp2pgsql give any messages? Have you looked in console.log? Is the shp file known to import ok on other installations? -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] percentile rank query
William Temperley escreveu: Hi all I'm trying to calculate the percentile rank for a record based on a 'score' column, e.g. a column of integers such as: 23,77,88,23,23,23,12,12,12,13,13,13 without using a stored procedure. So, select count(*) as frequency, score from scoretable group by score order by score Yields: frequency score 3 12 3 13 4 23 1 77 1 88 However I'd like this result set: frequency score runningtotal 3 123 3 136 4 2310 1 7711 1 88 12 Where the running total is the previous frequency added to the current frequency. Score order is significant. So I can then do ((runningtotal-frequency)+(frequency/2))/(select count(*) from scoretable) to give me the percentile rank for each score. Is this possible in one query? I just can't figure out how to get the running total in a result set. Try: SELECT count(*) AS frequency, score, count((SELECT * FROM scoretable st2 WHERE st2.score <= st1.score)) AS runningtotal FROM scoretable st1 GROUP BY score ORDER BY score Osvaldo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to use postgre sql from inside process
On Thu, Apr 10, 2008 at 5:45 AM, CMOS <[EMAIL PROTECTED]> wrote: > hi, > i would like to get services of postgresql from inside the process (to > use it as a library and linking to it), i.e not having a separate > postgresql process and communicating with it. Is this possible? No. > if possible what are the impact on licensing. None. It's BSD. If you wanna try and hack up something like that go ahead. Generally you're better off with SQLLite. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Triggers
On Wednesday 09 April 2008 14:56, Greg Sabino Mullane wrote: > > I see the following in the documentation for pg_trigger related > > to tgenabled: "Controls in which session_replication_role modes the > > trigger fires. O = trigger fires in "origin" and "local" modes, > > D = trigger is disabled, R = trigger fires in "replica" mode, A = > > trigger fires always." > > > > My question is: When tgenabled is set to "D", how does that setting > > interact with session_replication_role and, is there a way to use > > tgenabled with a setting of "D" to prevent a particular trigger > > from firing. Using ALTER TABLE to disable the trigger won't work > > because the whole table is locked during the transaction and I only > > want the disabled trigger to apply to the current transaction in the > > current session. > > If you simply want to ignore all triggers, just use a 'replica' role. > When done, switch it back to 'origin' (or your default, which should > be origin). > > If you want to fire only a single trigger, set it to 'always' mode and > switch to 'replica'. If you want to fire all triggers *except* a > certain trigger, set that trigger to replica mode and leave the > session_replication_mode unchanged (default/origin). > > You should be using ALTER TABLE and not worry about changing tgenabled > yourself, in case it wasn't obvious. You should be able to make permanent > changes and then just use session_replication_role to control how it acts > in a particular transaction. Greg, Thanks for your help on this. I'll try to work out something along these lines. I'm inclined to update one of the system tables to accomplish this because that's the way we did it in version 7.4.x. In that case, we were setting reltriggers to 0 in pg_class to turn off all the triggers on a given table, and, in fact, I was doing that at Tom's suggestion for solving the problem in a post to the list long, long, ago, and far, far, away. Again, thanks for taking the time to help :o] > > Here's a quick example: > > SET client_min_messages = 'ERROR'; > DROP SCHEMA IF EXISTS triggertest CASCADE; > SET client_min_messages = 'NOTICE'; > > CREATE SCHEMA triggertest; > > SET SEARCH_PATH = triggertest; > > CREATE TABLE foo(a int); > > INSERT INTO foo VALUES (1); > > CREATE FUNCTION trig1() > RETURNS TRIGGER > LANGUAGE plpgsql > AS $_$ > BEGIN > RAISE NOTICE 'I am trigger one'; > RETURN NULL; > END; > $_$; > > CREATE FUNCTION trig2() > RETURNS TRIGGER > LANGUAGE plpgsql > AS $_$ > BEGIN > RAISE NOTICE 'I am trigger two'; > RETURN NULL; > END; > $_$; > > CREATE FUNCTION trig3() > RETURNS TRIGGER > LANGUAGE plpgsql > AS $_$ > BEGIN > RAISE NOTICE 'I am trigger three'; > RETURN NULL; > END; > $_$; > > CREATE TRIGGER t1 AFTER UPDATE on foo > FOR EACH ROW EXECUTE PROCEDURE trig1(); > > CREATE TRIGGER t2 AFTER UPDATE on foo > FOR EACH ROW EXECUTE PROCEDURE trig2(); > > CREATE TRIGGER t3 AFTER UPDATE on foo > FOR EACH ROW EXECUTE PROCEDURE trig3(); > > UPDATE foo SET a=a; -- all three fire > > ALTER TABLE foo ENABLE ALWAYS TRIGGER t1; > > ALTER TABLE foo ENABLE REPLICA TRIGGER t2; > > UPDATE foo SET a=a; -- two does not fire > > SET session_replication_role TO 'replica'; > > UPDATE foo SET a=a; -- three does not fire > > SET session_replication_role TO DEFAULT; > > UPDATE foo SET a=a; -- two does not fire > > The output of the above yields: > > CREATE TRIGGER > psql:trig.example:53: NOTICE: I am trigger one > psql:trig.example:53: NOTICE: I am trigger two > psql:trig.example:53: NOTICE: I am trigger three > UPDATE 1 > ALTER TABLE > ALTER TABLE > psql:trig.example:59: NOTICE: I am trigger one > psql:trig.example:59: NOTICE: I am trigger three > UPDATE 1 > SET > psql:trig.example:63: NOTICE: I am trigger one > psql:trig.example:63: NOTICE: I am trigger two > UPDATE 1 > SET > psql:trig.example:67: NOTICE: I am trigger one > psql:trig.example:67: NOTICE: I am trigger three > UPDATE 1 > > > -- > Greg Sabino Mullane [EMAIL PROTECTED] > PGP Key: 0x14964AC8 200804091452 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] slow pgsql tables - need to vacuum?
On Apr 7, 11:14 am, [EMAIL PROTECTED] (Alan Hodgson) wrote: > On Monday 07 April 2008, Dan99 <[EMAIL PROTECTED]> wrote: > > > Does TRUNCATE TABLE keep all necessary table > > information such as indexes, constraints, triggers, rules, and > > privileges? > > Yes. It does require an exclusive lock on the table very briefly, though, > which DELETE does not. > > > Currently a mass DELETE is being used to remove the data. > > And that's why the table is bloating. Especially if you aren't VACUUMing it > before loading the new data. > > > Since VACUUM has never been done on the tables before, should a VACUUM > > FULL be done first? If so, approximately how long does a VACUUM FULL > > take on a database with 25 tables each having anywhere form 1,000 to > > 50,000 rows? > > Honestly, you'd be better off dumping and reloading the database. With that > little data, it would be pretty quick. Although, VACUUM is pretty fast on > tables with no indexes. > > > The reason I ask is because this is a live website, and > > any down time is very inconvenient. Also, would it be sufficient > > (after the first VACUUM FULL) to simply use a VACUUM ANALYZE after the > > tables are repopulated (ie. every night)? > > If you do a TRUNCATE and then a fresh load, a simple ANALYZE is sufficient. > > You really should create some indexes though. Right now your queries are > looping through the whole table for every SELECT. The only reason you're > not dying is your tables are small enough to completely fit in memory, and > presumably your query load is fairly low. > > -- > Alan > > -- > Sent via pgsql-general mailing list ([EMAIL PROTECTED]) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general A new website and hence a new database is planed for the near future, so It is good that I am learning all this now. How do indexes work and what columns should I put them on in a given table? Technically, what is the difference between a VACUUM and VACUUM FULL? I know I can probably get all this information from the docs, but I hope you guys can bear with me just a little bit longer :) Thanks, Daniel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] select statement fails
Albe, >> select * from test where tc=' '::text; >Because the arguments to the operator "=" are of different type, >implicit type conversion takes place. >"character(1)" will by converted to "text", during this conversion >trailing blanks will be ignored, as befits the "character(n)" type. Thank you. 1. SQL assumes that CHARACTER(n) column is always padded with spaces in right. So casting to text should preserve spaces. Why PostgreSQL cast to text violates SQL ? 2. create table test ( tc char(1) ); create index tc on test(tc); select * from test where tc='x'::text; I'm afraid that if test table has large number of rows, PostgreSQL is not capable to use index for this query doe to the cast to text. Is it so ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to use postgre sql from inside process
hi, i would like to get services of postgresql from inside the process (to use it as a library and linking to it), i.e not having a separate postgresql process and communicating with it. Is this possible? if possible what are the impact on licensing. thank you -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pl/java on Solaris
Volkan YAZICI <[EMAIL PROTECTED]> writes: > On Thu, 10 Apr 2008, "Roberts, Jon" <[EMAIL PROTECTED]> writes: >> I don't see a pljava file in my share directory like I do on Windows. >> Is pl/java not included when compiling from source? > PL/java[1] is a separate PL project, not builtin to PostgreSQL. The Windows installer actually aggregates quite a few projects besides core PG. If you want to build from source you'll need to collect a number of tarballs, depending on what features you were using. The installer documentation can probably tell you where they all came from. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] begin transaction locks out other connections
On Thu, Apr 10, 2008 at 7:18 PM, Ivano Luberti <[EMAIL PROTECTED]> wrote: > Anyway I am a little surprised by this thing cause I thought that in a case > like this the habgs should happen only at commit/rollback time. > I think that's because Postgres does not have deferred constraint checks. They are checked at the execution time, instead of commit time. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Proper Installation of Postgres and Postgis on 10.5 Intel
I tried now for weeks to get postgres & postgis going on my machine, in vain... Lots of frustration has been built up, lots of energy went into it... But nothing goes... I mean, postgres is running, postgis is installed, but it constantly craches, doesn't accept shp2pgsql imports etc... So, can anybody give me installation instructions on how to install postgres & postgis on Leopard (with a MacPro)? Either by hand- compiling or with MacPorts? Or some other stuff? Strange enough, even the kyngchaos libs didn't work, even after having completely re- installed my machine... Thanks thousand times for any such advice! Stef -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] visibility rule in a EXECUTE with multi sql
laser <[EMAIL PROTECTED]> writes: > when I do a: > execute 'set search_path to bar; create table foo(f1 int);insert into > table foo blah..blah;' > in plpgsql, I found that the insert statement always report that "can't > found table foo" or something > like that. Well, yeah. The whole string is parsed, then executed, so you are trying to parse the insert before foo exists. Break it into multiple EXECUTEs. Or maybe you want "create table as select ...". regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] percentile rank query
Hi all I'm trying to calculate the percentile rank for a record based on a 'score' column, e.g. a column of integers such as: 23,77,88,23,23,23,12,12,12,13,13,13 without using a stored procedure. So, select count(*) as frequency, score from scoretable group by score order by score Yields: frequency score 3 12 3 13 4 23 1 77 1 88 However I'd like this result set: frequency score runningtotal 3 123 3 136 4 2310 1 7711 1 88 12 Where the running total is the previous frequency added to the current frequency. Score order is significant. So I can then do ((runningtotal-frequency)+(frequency/2))/(select count(*) from scoretable) to give me the percentile rank for each score. Is this possible in one query? I just can't figure out how to get the running total in a result set. Thanks very much, Will Temperley -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] begin transaction locks out other connections
Richard, I was convinced that it was not necessary to provide detailed SQL to not introduce any noise in the information I was giving to the list. Anyway you proved right at last, since trying to sample some sql to make others able to reproduce the problem we were also able to identify the cause of the problem. If two insert statements ST1 and ST2, to which are attached triggers that create a postgreSQL schema with the same name, are executed in two concurrent transaction T1 and T2 started from two different connections C1 and C2, then ST2 must wait until T1 has ended (rolled back or commited) before being executed. By consequence the client executing ST2 hangs until T1 has ended. The fact that both statement try to create a schema with the same name make the second one to hang. Now after that we have found in a PostgreSQL book we have that when in the scenario above the 2 statements try to insert a row in a table with the same primary key the second statement hangs. So in some way this behavior is documented so it is not a bug. Anyway I am a little surprised by this thing cause I thought that in a case like this the habgs should happen only at commit/rollback time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pl/java on Solaris
On Thu, 10 Apr 2008, "Roberts, Jon" <[EMAIL PROTECTED]> writes: > I am migrating an 8.3 database from Windows to Solaris. We are using > pl/java and I went through the installation process for this on Windows. > > I'm building Solaris from the source and when running ./configure, I > don't see a switch to include pl/java. Java is in my path too so it > should allow me to install it (if it follows the same pattern as > Windows). > > I don't see a pljava file in my share directory like I do on Windows. > Is pl/java not included when compiling from source? PL/java[1] is a separate PL project, not builtin to PostgreSQL. I don't know about official Windows installation binaries, they would be bringing it by default. [1] http://pljava.projects.postgresql.org/ Regards. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pl/java on Solaris
Roberts, Jon napsal(a): I don't see a pljava file in my share directory like I do on Windows. Is pl/java not included when compiling from source? pl/java is not part of core like pl/pgPerl... You need to download it separately from http://pgfoundry.org/projects/pljava/ Zdenek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pl/java on Solaris
On Thu, Apr 10, 2008 at 5:31 AM, Roberts, Jon <[EMAIL PROTECTED]> wrote: > I am migrating an 8.3 database from Windows to Solaris. We are using > pl/java and I went through the installation process for this on Windows. > > I'm building Solaris from the source and when running ./configure, I > don't see a switch to include pl/java. Java is in my path too so it > should allow me to install it (if it follows the same pattern as > Windows). > > I don't see a pljava file in my share directory like I do on Windows. > Is pl/java not included when compiling from source? here is the user doc from cvs. I might help. http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/pljava/org.postgresql.pljava/docs/userguide.html?rev=1.15&content-type=text/plain -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pl/java on Solaris
I am migrating an 8.3 database from Windows to Solaris. We are using pl/java and I went through the installation process for this on Windows. I'm building Solaris from the source and when running ./configure, I don't see a switch to include pl/java. Java is in my path too so it should allow me to install it (if it follows the same pattern as Windows). I don't see a pljava file in my share directory like I do on Windows. Is pl/java not included when compiling from source? Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] howto set a variable in transaction context
thanks, it works A. Kretschmer schrieb: am Thu, dem 10.04.2008, um 13:32:04 +0200 mailte Thomas Markus folgendes: hi list, how can i set a variable, which content is only visible in current transaction? i know the way to create a temp table with create temp table ... on commit drop but i dont like that way. something like | set_config(setting_name, new_value, is_local) is it possible? Yes, you can use this: http://www.postgresql.org/docs/current/interactive/runtime-config-custom.html How to use? http://groups.google.de/group/pgsql.general/browse_thread/thread/f914569b73d17258/5c0cb606d0ce698d?lnk=st&q=#5c0cb606d0ce698d HTH, Andreas begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany email;internet:[EMAIL PROTECTED] tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Write in file from postgres
Kevin Martins wrote: > Hello everybody, > First off all I am new in postgres but allready got some questions. It's > possible to wirte in a file from postgres? First off, if you write to a mailing list you should first learn how to write an e-mail. Really. Core PostgreSQL does not have such a function; you'd have to write it yourself in C. There is, however, a function "pg_catalog.pg_file_write(text, text, bool)" in the "adminpack" contrib module that you could (ab)use for your purposes. This function may only be used by a superuser, and it would not be a good idea to have it otherwise. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] howto set a variable in transaction context
am Thu, dem 10.04.2008, um 13:32:04 +0200 mailte Thomas Markus folgendes: > hi list, > > how can i set a variable, which content is only visible in current > transaction? > i know the way to create a temp table with > > create temp table ... on commit drop > > but i dont like that way. something like > | > set_config(setting_name, new_value, is_local) > > is it possible? Yes, you can use this: http://www.postgresql.org/docs/current/interactive/runtime-config-custom.html How to use? http://groups.google.de/group/pgsql.general/browse_thread/thread/f914569b73d17258/5c0cb606d0ce698d?lnk=st&q=#5c0cb606d0ce698d HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] howto set a variable in transaction context
hi list, how can i set a variable, which content is only visible in current transaction? i know the way to create a temp table with create temp table ... on commit drop but i dont like that way. something like | set_config(setting_name, new_value, is_local) is it possible? regards thomas | begin:vcard fn:Thomas Markus n:Markus;Thomas org:proventis GmbH adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany email;internet:[EMAIL PROTECTED] tel;work:+49 30 29 36 399 22 x-mozilla-html:FALSE url:http://www.proventis.net version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dump/Transfer Sequence Problems
I am using Navicat to transfer data from one database to another. But it soon gives me an error message like the following: I think you'll probably have to ask the navicat people. If you want to use pg_dump to transfer data from 8.1 to 8.2 though, use the version of pg_dump that ships with 8.2. Ok, tried that Not yet mentioned is the fact that I am trying to import postgis tables (tables with geographic parameter). But I get this: pg_restore: restoring data for table "admin01" pg_restore: restoring data for table "boundaries_national" pg_restore: [archiver (db)] error returned by PQputCopyData: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. pg_restore: *** aborted because of error The log (where can I change the parameters?) says this: ERROR: syntax error at or near "pg_restore" at character 1 STATEMENT: pg_restore -U ss_admin -d geodataportal -v /Users/ schwarzer/Temp/pg_dump.gridca.2008-04-10.gis.c.sql There again, when I look into the dump file, it seems that it doesn't include any statement to create the sequence... Thanks for any hints, Stef -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] include PostgreSQL utilities into 3rd party program package
Michael Raven wrote: Hello everybody. I've got some kind of PostgreSQL copyright related question. Our company is developing an commercial product for PostgreSQL administration. We want to include some utilities (i.e. pg_dump.exe, pg_restore.exe etc) from PostgreSQL for Windows installation package to installation package of our product and use these utilities within our program. Can we make it freely? Whether there are any special conditions for this purpose? What legal aspects of it exist? Best regards, Michael Raven. Postgresql code is released under the BSD style license, you are free to use it as you wish for commercial or non-commercial purposes. This applies to all source code and generated object files as distributed with the main postgresql releases (including the windows binary release) Some extras that you may add from somewhere like pgfoundry may have more restrictive license and each must be checked individually. See the following for full license which is also included in the source files - http://www.postgresql.org/about/licence -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] visibility rule in a EXECUTE with multi sql
hi all, when I do a: execute 'set search_path to bar; create table foo(f1 int);insert into table foo blah..blah;' in plpgsql, I found that the insert statement always report that "can't found table foo" or something like that. I guess it's visibility rule in PostgreSQL, but I can't found clear docs, can someone give me some hint about that? thanks and best regards laser -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] connecting VB to postgreSQL
In any case, it's obvious in your screenshot that you left the "server" and "location" fields blank in the connection setup form. This is most likely the cause of your problem. *doesnt work either..OLE-DB error persists. thanks for the readmefile. * On Wed, Apr 9, 2008 at 8:04 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > Abhishek MANDHANA wrote: > > > Did 30 seconds search worked ? for me its didnt ? > > > > Please just dont test the connection, the applet shows Connection > > successful > > , Are you able to create it and see it under Server Explorer? > > > > > > I mostly use Linux. I'm on Vista right now, but I have never used Visual > Basic and have none of the tools for it. I have Visual Studio for C++ > installed, but not for Visual Basic .NET. So I can't test it, and I'm afraid > I'm not going to install VB.net to test it. As such, I'm working from > information available on the 'net, such as the PostgreSQL OLE DB provider > documentation (which, by the way, is hard to find - I landed up digging it > out of ViewCVS). You, however, will have a copy of the file that came with > your copy of the OLE DB provider. > > This is the README file. I strongly suggest having a look: > > http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/oledb/oledb/README?rev=1.7&content-type=text/x-cvsweb-markup > > In any case, it's obvious in your screenshot that you left the "server" > and "location" fields blank in the connection setup form. This is most > likely the cause of your problem. Try putting "localhost" in the "server" > box, and the name of your database in the "location" box. If you have not > created a database yet, you will need to do so. See the PostgreSQL > documentation on how to do that. > > As for the error message, it basically means "oops, it didn't work and I > don't know why". The PostgreSQL OLE DB provider README explains how to get > more detailed error information. > > -- > Craig Ringer >
