Re: [SQL] oid's in views.
Hey, Dado,
> Hi Josh!
> Once you have your usq, how do you get more info about that row?
> How do you know which table it came from?
Well, if you have to go at it from that angle (hey, I have this USQ,
where did it come from) then you're in trouble. However, I never use it
that way. Let me give you an example of USQ use:
Modifications table
TABLE candidates
usq INT4 DEFAULT NEXTVAL ('universal_sq'),
first_name VARCHAR NULL,
etc.
TABLE orders
usq INT4 DEFAULT NEXTVAL ('universal_sq'),
client_usq INT4 NOT NULL REFERENCES clients(usq),
etc.
TABLE mod_data
ref_usq INT4 NOT NULL PRIMARY KEY,
entry_date DATETIME NOT NULL,
entry_user INT4 NOT NULL references users(usq),
mod_date DATETIME NOT NULL,
mod_user INT4 NOT NULL references users(usq)
Thus I effectively have a One-to-One relationship between all of the
tables posessing USQs and the mod_data table. This means I can use one
function to update this timestamp information, regardless of table,
whenever a record is inserted or updated.
When I'm retrieving modification information, I never start with the
mod_data table. To do so would be asking the question, "What records,
in any table, were modified by Josh on Decemebr 12th?" which really
isn't useful and would be very difficult (but possible) to query.
Instead, the question I'm usually asking is, "When and by who was the
current record on the screen modified?" Which means that I am
retrieving a single, unique, row from mod_data (SELECT * FROM mod_data
WHERE ref_usq = 451).
This whole scheme, which has been very convenient for me, would not have
been possible without a good way of insuring USQ uniqueness between
tables, which, thankfully, our core team was foresighted enough to
supply. Unfortunately, that does mean that this solution is not
portable to other RDBMSs, but as PostgreSQL grows in market share,
that's less of a concern.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] GUID in postgres
Dinesh, Please do not cross-post to two different lists. It annoys those of us who are subscribed to both lists, and you are less likely to get help. > Is there any concept of sys_Guid in postgres. > If yes what is that ?? > As i am using GUID in sql and oracle, then what is counterpart of > this in postgres. It is urgent. No. Frankly, I don't know what a sys_GUID is. However, PostgreSQL allows you to create your own functions, operators, and data types, so I'm sure you could make your own GUID, whatever one is. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Lock full database
dear all I want lock full tables,how to write SQL command? thanks -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] can't update 'c:\windows'
thanks. your reply -- ""Josh Berkus"" <[EMAIL PROTECTED]> ¼¶¼g©ó¶l¥ó·s»D :[EMAIL PROTECTED] > Guard, > > First, this kind of beginner question is more appropriate for the NOVICE > list. > > > IF update table set field='c:\windows' > > but > > > > c:\windows -> c:windows > > That's because "\" is the Unix escape character. If I were to want to > save "O'Reilly", for example, I could: > UPDATE table SET name = 'O\'Reilly'; > > Since you want to save an actual backslash, do this: > > UPDATE table SET field = 'C:\\windows'; > > In your interface code, you may which to add a function that doubles > your backslashes. > > -Josh Berkus > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Cross-posting (was Re: [SQL] GUID in postgres)
"Josh Berkus" <[EMAIL PROTECTED]> writes: > Please do not cross-post to two different lists. It annoys those of us > who are subscribed to both lists, and you are less likely to get help. Just FYI, the Postgres mail server has a very nifty feature you can set so that you get only one copy of cross-posted messages. It's a real godsend IMHO. Set your subscription class to "unique" rather than "each" for all lists you are on, and presto. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to find primary keys by querying system tables
Hi. I'm trying to write a select statement that does the following; select all tables in my database; for each table, select all columns, and tell me if the column is a primary key in the table. I've tried this by querying pg_class, pg_index and pg_attribute, but I have a problem when I this is supposed to be "general", ie I do not know the maximum number of columns in a primary key (I can't use subscription of "indkey"). Is there a way of testing for membership in an int2vector-field? For example: select column, "prim.key" from ... where pg_attribute.attnum in pg_index.indkey ?? Thanks! -Jostein ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] GUID in postgres
Hi, Is there any concept of sys_Guid in postgres. If yes what is that ?? As i am using GUID in sql and oracle, then what is counterpart of this in postgres. It is urgent. Thanks in advance Bye Dinesh Parikh
Re: [SQL] dropping foreign key
Can I just set tgenabled to false? Stephan Szabo wrote: > On Tue, 23 Oct 2001, Joseph Shraibman wrote: > > >>I have to drop a froeign key from one of my tables. The problem is that I have >another >>table that has a foreign key on the first one, so I can't do the select to >temp-table >>thing and move it back. >> >>Is there any way I can remove it by mucking with pg's internal tables? >> > > Yeah. All you should need to do is remove the three triggers that > were created for the constraint (1 on the fk table, 2 on the pk table). > You can find them in pg_trigger and you'll need to double quote the > names for the drop trigger statements because they're mixed case. > > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(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: [SQL] dropping foreign key
On Wed, 24 Oct 2001, Joseph Shraibman wrote: > Can I just set tgenabled to false? I'm not sure if it works now (in 7.2 devel) but I'm pretty sure that doesn't work in past versions unfortunately. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] dropping foreign key
On Tue, 23 Oct 2001, Joseph Shraibman wrote: > I have to drop a froeign key from one of my tables. The problem is that I have >another > table that has a foreign key on the first one, so I can't do the select to >temp-table > thing and move it back. > > Is there any way I can remove it by mucking with pg's internal tables? Yeah. All you should need to do is remove the three triggers that were created for the constraint (1 on the fk table, 2 on the pk table). You can find them in pg_trigger and you'll need to double quote the names for the drop trigger statements because they're mixed case. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PL/pgSQL triggers ON INSERT OR UPDATE
From [EMAIL PROTECTED] Tue Oct 23 18:33:27 2001 Content-Disposition: inline From: "Aasmund Midttun Godal" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [SQL] PL/pgSQL triggers ON INSERT OR UPDATE Date: Tue, 23 Oct 2001 16:31:12 GMT X-Mailer: Googley-Moogley First, I may be wrong but I do think they would prefer if you did not cross-post (especially to hackers). Second I think it probably make more sense to make two different triggers here. If you really wanted to do it that way you might want to try executing that part. Regards, Aasmund. Thank you, I should have been *really* tired that night, but it is working now ... No more complaints about missing OLD variable. It works as I wanted. One trigger for both events, checks for TG_OP. Sorry about cross-posting attempt ;) please don't post that mail --san; ---(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: [SQL] Auto Increment
create table mytable ( myfield serial primary key, myotherfield integer); Using the SERIAL data type automatically creates the sequence and default statements for you. -Original Message- From: Mayuresh Kadu [mailto:[EMAIL PROTECTED]] Sent: Monday, October 22, 2001 2:36 AM To: [EMAIL PROTECTED] Subject: [SQL] Auto Increment hi all, could anyone tell me how to make a primary key to AUTO INCREMENT. The document is not exactly very explainatory about it :) Thankx in advance ... Mayuresh ---(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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] GUID in postgres
On Thursday 25 October 2001 03:41, John Hasler wrote: > Josh writes: > > I'm sure you could make your own GUID, whatever one is. > > Globally Unique IDentifier, probably. Just hash a 128 bit random number > with the current date. That gives you no gurantee it will be unique. What we are using is the following: - All tables in need of a global ID _within_ a database inherit a globid table which contains nothing but an ID of type serial. - When we need cross-database unique IDs within the same system, the globid table contains a database identifier as well (like the OID of the pg_database entry for the database). Horst ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] GUID in postgres
Josh writes: > I'm sure you could make your own GUID, whatever one is. Globally Unique IDentifier, probably. Just hash a 128 bit random number with the current date. -- John Hasler [EMAIL PROTECTED] Dancing Horse Hill Elmwood, Wisconsin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
