[SQL] auto_insert
I'm looking function at postrgeSQL the same like "auto_insert " function at mySQL
[SQL] join syntax
Title: One of my colleagues has created a database where he has the same field name in two tables and uses this field to link his tables rather than some arbitrary value. For example, he has used "exp_id" in two tables. When writing his joins he uses a syntax that says something like JOIN ON EXP_ID. Can someone tell me what that syntax should be? I am not very familiar with it since I typically use the syntax where one field is set equal to the other. Personally I prefer not to set databases up this way but cannot seem to convince him of this. And yet I am supposed to now help him with his database and application.. Is there some documentation that would define this type of syntax? How is this handled if you have more than one table in the join? It does not appear that this format would allow for this. Thanks Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
[SQL] duplicate dates
Title: I have one table that has a date/time field in it. I'd like to identify the records in the database where the date/time fields are the same. How can I do this? Do I need to create a view or temp table? Is there a way to run through one table multiple times. Thanks. Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
[SQL] looking for empty fields
Title: We recently upgraded from version 7.2.3 to 7.3.3 and seem to have some code that has broken. We were doing checks in various locations looking for nulls and/or empty fields. To search for empty fields we said something like select count(am_pk) from arraymeasurement where al_fk is null or al_fk=''; Is this not allowed anymore? All my check with the double ticks are failing. I assume there is a difference in postgres between an empty and null field. How can I check for both in 7.3.3? Thanks Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [SQL] looking for empty fields
Title: Viorel Thank you for responding and sorry to waste your time. I just realized my stupid mistake after sending the message! It was an integer field. Jodi Viorel Dragomir wrote: - Original Message - From: JodiKanter To: Postgres SQL List Sent: Wednesday, August 06, 2003 5:07PM Subject: [SQL] looking for emptyfields We recently upgraded from version 7.2.3 to 7.3.3 and seem to have somecode that has broken. We were doing checks in various locations looking fornulls and/or empty fields. To search for empty fields we said something like select count(am_pk) from arraymeasurement where al_fk is null oral_fk=''; It's a string value that you're searching for? If not, don't use ''. Is this not allowed anymore? Allmy check with the double ticks are failing. I assume there is a difference inpostgres between an empty and null field. How can I check for both in7.3.3? Thanks Jodi -- ___ Jodi L Kanter BioInformatics DatabaseAdministrator University of Virginia (434) 924-2846 [EMAIL PROTECTED] -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
[SQL] query assistance
Is there a straight forward way to pull out duplicates in a particular
field given a value in another field?
For example, I have a table that lists users and study names associated
with those users. Each user can have one or more study names. My goal
is to determine if any of these people have duplicate study names.
There are duplicated study names across the system and that is ok. I
just want to see if any users have duplicate study names among their
studies.
My table looks like this:
Table "public.study"
Column | Type |
Modifiers
--+-+--
sty_pk | integer | not null default
nextval('pk_seq'::text)
study_name | character varying(128) | not null
start_date | timestamp without time zone |
sty_comments | text |
created_by | integer |
Indexes: study_pkey primary key btree (sty_pk)
I am concerened with study_name. The created_by field tells me who owns
it.
Thanks
Jodi
--
___
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
[EMAIL PROTECTED]
[SQL] picking max from list
I have a query that produces results similar to this: run# rd_pk group# 0 9209 5 1 9209 8 0 9520 2 1 9520 5 0 9520 etc 0 8652 1 8652 2 8652 0 8895 1 8894 Ultimately I want to know the group number for EACH rd_pk with the highest run number. Can this be done in one query? Or will I need to code with a loop? Thanks Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
[SQL] search and replace
I have a field in one of my tables that has a path to a file listed. I need to move those files and now have to update all those paths. Is there a simply search and replace type option in sql? or do I need to do an update using the entire new path name? If so, I think the easier solution will be to dump the table, use a macro to update it and then restore. Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
[SQL] [Fwd: Majordomo results: unsubscribe]
I am going to be on leave from work for a few months and am trying to remove myself from the admin and sql lists until I return. I received the following message below. Can anyone assist? I am not sure why it's not working. My email address is [EMAIL PROTECTED] and my alias for this account is [EMAIL PROTECTED]. Both may be listed because I frequently get emails in duplicate. Any help would be greatly appreciated. Jodi Kanter Original Message Subject: Majordomo results: unsubscribe Date: Wed, 15 Dec 2004 15:55:43 + From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Jodi Kanter <[EMAIL PROTECTED]> >>>> approve GCW9AF unsubscribe pgsql-general [EMAIL PROTECTED] The unsubscribe command did not succeed. The password is invalid. Some common reasons for this error are: The password was mistyped. The address [EMAIL PROTECTED] has not been registered. (You may be registered under a different e-mail address.) The password is not a valid administrative password for the pgsql-general mailing list. The password was a temporary password, and has expired. See "help password" and "help admin_passwords" for more details. >>>> -- Stopping at signature separator. No more commands will be processed. Valid commands processed: 1 0 succeeded, 0 stalled, and 1 failed. Use the following command: sessioninfo 9a89a68dacc14bc87d2253d7c07230a6189a4e70 to see technical information about this session. -- ___ ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
