[GENERAL] Link tables from different databases
Hello, Suppose I have two tables db1.table1 and db2.table2. Is there anyway I can actually have db2.table2 a link to db1.table1 ? What I meant is similar to a symlink in Unix filesystem (db2.table2 is a symlink to db1.table1). I want it so that every operation on db1.table1 (ie. INSERT, UPDATE, DELETE) also happens to db2.table2, but similarly I want it that application can do those operations on db2.table2 but actually it does it on db1.table1 behind the scene. Is there anything like this in postgresql ? I am using version 7.4 by the way. Thanks in advance for any help. RDB -- Reuben D. Budiardja -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database Link between 2 PostgreSQL db
Hello, Oracle has a concept of Database link, IE you can access data from a table in different database, even if the database is in different host, using something like SELECT * FROM [EMAIL PROTECTED]; Is there something similar in PostgreSQL? I would like to know if I can access my data on a remote PostgreSQL server from a local PostgreSQL database. Thank you in advance for any respond. RDB -- Reuben D. Budiardja Dept. Physics and Astronomy University of Tennesse, Knoxville, TN -BEGIN GEEK CODE BLOCK- Version: 3.12 GIT/M/MU/P/S d-(++) s: a-- C++(+++) UL P-- L+++ E- W+++ N+ o? K- w--- !O M- V? !PS !PE Y PGP- t+ 5 X R- tv+ b+ DI D(+) G e++ h+(*) r++ y- --END GEEK CODE BLOCK-- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Help with sorting (ie. ORDER BY expression)
Hello, I am running postgres-7.3. I have a query like this: SELECT question_id, question_text FROM quiz_table WHERE question_id IN (2,10,3,6,4,5); But I want the output to be sorted in the way I give the question_id, something like: SELECT question_id, question_text FROM quiz_table WHERE question_id IN (2,10,3,6,4,5) ORDER BY question_id (2,10,3,6,4,5) Is there any way I can do that, so that the output of the query is question_id, text 2 ... 10 3 6 4 5 Thanks for any help. RDB -- Reuben D. Budiardja Dept. Physics and Astronomy University of Tennesse, Knoxville, TN ---(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 through to the mailing list cleanly
Re: [GENERAL] Fatal error: Call to undefined function: pg_connect()
On Tuesday 05 August 2003 03:31 am, Jean-Christian Imbeault wrote: shreedhar wrote: Is it necessary to uninstall previous version of PHP in my system. No，but a new recompile will probably over-write the version you have now. Which is the better compatible version for Postgre 7.3.2. Most recent should be the best I would think. Can you give any link/info for recompiling PHP www.php.net ? If you have never compiled PHP before I would suggest reading up on compilation before-hand and even asking the php help list. Recompiling PHP also means you need to recompile Apache (if that is the web server you are using). No, you don't need to recompile Apache to recompile PHP, just restart the webserver. My guess would be when you update the new postgresql library override the old one, so PHP needs to be recompile to use the new library. My suggestion, easiest thing to do would be to write a PHP script with just ? phpinfo(); ? and run the script (or browse it through the web). You will see all your old configure options there. Use that to configure your new PHP (make sure you have the option --with-pgsql), do "make", 'make install', and you should be good to go. RDB Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /"\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] How to encrypt data in Postgresql
On Thursday 24 July 2003 02:59 pm, Franco Bruno Borghesi wrote:
You must install pgcrypto (its in your contrib/pgcrypto directory).
Then, the functions crypt and gen_salt will become available.
As an example, to insert a new user (peter) with an encrypted password
(1234) you can do:
INSERT INTO myUsers(name, pass) VALUES ('peter', crypt('1234',
gen_salt('md5'));
To verify that anypassword is OK:
SELECT (anypassword=crypt(anypassword, pass)) WHERE name='peter';
I think if you encrypt MD5 before storing it into the table, then there is no
way to retrieve the corresponding clear text right? since MD5 is one-way
encryption..
RDB
The package includes many other functions, listed in README.pgcrypto.
On Thu, 2003-07-24 at 15:18, Terence Chang wrote:
Hi there:
I know this might be an easy answer, but I was unable to figure out
the solution.
I would like to encrypt a password field in the table. I could not
figure out how phpPGAdmin did.
Should I use PHP's MD5 to encrypt the password? Is there a function in
PostgreSQL that can encrypt the data with MD5?
I would like to encrypt the data in PostgreSQL, so other program can
use the same function. Can anyone give me some hints? What key word
should I search in the document?
Thansk!
--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN
-
/\ ASCII Ribbon Campaign against HTML
\ / email and proprietary format
X attachments.
/ \
-
Have you been used by Microsoft today?
Choose your life. Choose freedom.
Choose LINUX.
-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Can I turn the case sensitive off
On Friday 25 July 2003 02:37 am, Thomas Kellerer wrote: Terence Chang schrieb: I am still getting the error. would this matter with 7.3.3 on windows with cygwin? From my experience I'd never user quotes at any place (neither during creation of the table nor in the SELECT, UPDATE statements). All DBMS I know behave like Postgres. So if you never quote your object names, then you won't have problems. FWIW: I ran into this problem before. I used to develop using Oracle, where column name fold to UPPER case. So in my habits, I created table using pgaccess and type them in UPPER case for both column name and table name Then I could not access from psql. After banging my head to the wall for couple days, I then realize I have to use the double quotes. So somehow pgaccess write the create table statements using doble quotes. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] New Poll @ Codewalkers
On Tuesday 22 July 2003 02:57 pm, Dennis Gearon wrote: Let's be nice and only vote once, also. I don't know if they use cookies to prevent that, but Seems that they log IP address (oh yeah.. I tested the cookie thing...:) ). 28% for postgresql now. Second after MySQL. RDB Gavin M. Roy wrote: Sorry if this is a repost, but codewalkers have a poll up for php developers for their database of choice. I'm not affiliated with the site in any way, I just want to see PgSQL at more than 8% :( It's right on the homepage. http://codewalkers.com/ Gavin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] postgresql.org is unreliable
Hi Does anyone else find the site postgresql.org kinda unreliable? Many times it's stalled for a while. And it's not just today, but very often. It's really frustrating especially when I regularly use it to look up documentation. Is there any mirror to the site ? Thanks. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Duplicate key insert question
On Tuesday 01 July 2003 08:45 pm, Jean-Christian Imbeault wrote: Reuben D. Budiardja wrote: Hi, not sure if this is answering your question, but I just asked similar questions here. I asked about using INSERT WHERE NOT EXISTS (which you can do in PostgreSQL). Here is what you can do: INSERT INTO mytable SELECT 'value1', 'value2' WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) http://marc.theaimsgroup.com/?l=postgresql-generalw=2r=1s=WHERE+NOT+EX ISTSq=b Thanks for the link! I read the thread and it looks like even the above solution is not perfect because of a possible race condition where two inserts trying to insert a row with a pk not in the table will both get think it is ok to do so, try it and then both will fail? No, onlu *one* of them will fail, but yes, the other will then generate error. So it really is a trade off. Another way would be to lock the table, as other has suggested. But then there is disadvantages to that also. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /"\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Duplicate key insert question
On Tuesday 01 July 2003 09:25 pm, Jean-Christian Imbeault wrote: Reuben D. Budiardja wrote: No, onlu *one* of them will fail, but yes, the other will then generate error. So it really is a trade off. Another way would be to lock the table, as other has suggested. But then there is disadvantages to that also. Really? I just got a post form Alvaro Herrera saying; "The solution is not correct in that there _is_ a race condition." Maybe I misunderstood, but "not correct" doesn't sound good :) If you want to avoid the race condition as well, then use the locking mechanism for transaction. Combine it with the previous INSERT ... SELECT ... WHERE NOT EXISTS, it should give you what you want. I suspect it's slower though. Eg: BEGIN WORK; INSERT INTO mytable SELECT 'value1', 'value2' WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) COMMIT WORK; This should solve the Race Condition, since other transaction have to wait. But if the PK already exists, this will quit without error. RDB ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] postgresql.org is unreliable
On Tuesday 01 July 2003 08:47 pm, The Hermit Hacker wrote: On Tue, 1 Jul 2003, Reuben D. Budiardja wrote: On Tuesday 01 July 2003 06:54 pm, The Hermit Hacker wrote: On Tue, 1 Jul 2003, Reuben D. Budiardja wrote: On Tuesday 01 July 2003 06:27 pm, The Hermit Hacker wrote: On Tue, 1 Jul 2003, Reuben D. Budiardja wrote: Hi Does anyone else find the site postgresql.org kinda unreliable? snip What OS are you running on? I take it, from using links, that its Unix based? I am on Redhat Linux 7.3. I just upgrade to the last stable mozilla, and it seems fine, at least for now with initial try. Still don't know what's wrong. Thanks. RDB ---(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 through to the mailing list cleanly
Re: [GENERAL] Duplicate key insert question
On Tuesday 01 July 2003 11:08 pm, Jean-Christian Imbeault wrote: Reuben D. Budiardja wrote: INSERT INTO mytable SELECT 'value1', 'value2' WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) Thank you to everyone who helped out on my question. I am trying to implement the above solution but I'm having problems getting this to work when I want to insert more than one value: TAL=# create table b (a text primary key, b text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'b_pkey' for table 'b' CREATE TABLE TAL=# insert into b select 'a'; INSERT 335311 1 TAL=# insert into b select 'b', select 'b'; ERROR: parser: parse error at or near "select" at character 27 I don't see what you're trying to do. Why do you have two select ? RDB ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] INSERT WHERE NOT EXISTS
Hi, I am developing application with PHP as the front end, PGSQL as the backend. I am trying to figure out what's the best way to do this. I want to check if an entry already exists in the table. If it does, then I will do UPDATE tablename otherwise, I will do INSER INTO tablename... What's the best way to do that? I can of course check first, and then put the login in PHP code, eg: // check if entry already exists SELECT COUNT(*) FROM tablename WHERE [cond] .. if($count 0) UPDATE else INSERT but this will double the hit to the database server, because for every operation I need to do SELECT COUNT(*) first. The data itself is not a lot, and the condition is not complex, but the hitting frequency is a lot. I vaguely remember in Oracle, there is something like this: INSERT INTO mytable SELECT 'value1', 'value2' FROM dummy_table WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) This query will do INSERT, if there is not an entry already in the TABLE mytable that match the condition mycondition. Otherwise, the INSERT just fails and return 0 (without returning error), so I can check on that and do update instead. This is especially useful in my case because about most of the time the INSERT will succeed, and thus will reduce the hit frequency to the DB server from PHP by probably a factor of 1.5 or so. Is there anything like that with PostgreSQL? I looked the docs and googled but haven't found anything. Anyhelp is greatly appreciated. Thanks. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] INSERT WHERE NOT EXISTS
On Wednesday 25 June 2003 03:26 pm, Ian Barwick wrote: snip I vaguely remember in Oracle, there is something like this: INSERT INTO mytable SELECT 'value1', 'value2' FROM dummy_table WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) This query will do INSERT, if there is not an entry already in the TABLE mytable that match the condition mycondition. Otherwise, the INSERT just fails and return 0 (without returning error), so I can check on that and do update instead. This kind of query should work; just leave out the FROM dummy_table bit. (in Oracle it would be FROM dual). Hi, this seems to work. Thanks. Don't know why I didn't just try it. And yes, in Oracle it's SELECT .. FROM dual. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Error with word 'desc'
Hi, Seems that everytime I use the word 'desc' I got an error. For example, this query: SELECT code, desc FROM or_code_table WHERE tr = 'FORMAT' gave me ERROR: parser: parse error at or near desc When I created the table I got the same error too. I got around that by creating the table using pgaccess instead of using psql console. Is 'desc' a reserved key word of some kind ? Even if yes (like in mysql or oracle), the parser should understand the context in which it's mentioned. I don't remember I got that kind of error with mysql / oracle. Here is the table: lightcone=# \d or_code_table; Table or_code_table Column | Type | Modifiers --+--+--- code | character varying(2) | not null tr | character varying(10)| not null desc | character varying(100) | mod_user | character varying(15)| mod_time | timestamp with time zone | default now() Primary key: or_code_table_pkey Any help is greatly appreciated. Thanks. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Foreign Key can't refer to one of 2 primary keys
Hi all, Suppose I have a table with more than one primary key. If I create another table and I want one of the column of that second table REFERENCE to one of the primary key of the first table, how do I do that? eg CREATE TABLE test ( col1 VARCHAR(20), col2 VARCHAR(20), col3 VARCHAR(20), PRIMARY KEY (col1,col2,col3) ); CREATE TABLE myforeign ( mycol1 VARCHAR(20) REFERENCES test, mycol2 VARCHAR(25), PRIMARY KEY(mycol1) ); This gave me ERROR: number of key attributes in referenced table must be equal to foreign key Illegal FOREIGN KEY definition references test I don't se any obvious reason why I cannot do that. Any help? Thanks. -- Reuben D. Budiardja ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
