Re: [SQL] Retrieving tuple data on insert
you can retrieve it using the currentVal function on the
sequence used to generate that id.
"sebmil" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hello,
>
> I have a table with two columns, created with : CREATE TABLE test ( id
> serial primary key, name text ) ;
>
> To populate the table i use :
> INSERT INTO test(name) values('test1'); so the "id" is automatically set
> by PostgreSQL.
>
> Now the problem, i would like to retrieve the value of "id" that
> PostgreSQL assigns to this tuple, but i can't make a SELECT on the name i
> just inserted because it's not UNIQUE, so SELECT may return multiple
> results.
>
> Is there a way to retrieve the value of "id" from the insertion ?
>
> I was thinking of something with Oids, like getting the Oid of the INSERT
> statement then use it to SELECT the tuple, but i didn't find what to do
> with an Oid in PostgreSQL documentation.
>
> Also, it would be better if it was possible in only one operation (not
> INSERT then SELECT).
>
> Thanks in advance.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Order By Question
> This seems like the answer must be pretty easy, but I can't think of it: > > In the following statement: > > select field1 from my_table where field2 in (3, 1, 2); > > How can I modify this statement so that the record are returned in the > order of first those records having field2 = 3, then field2 = 1, then > field2 = 2. > As it stands, I am getting them returned in the order of the value of > field1. > One way is to have a priority table where each value is mapped to its associated priority and then you do a join against this table and order by the priority value instead. So you might have a table like follows: priorityvalue 1 3 2 1 3 2 -- Prasanth Kumar [EMAIL PROTECTED]
Re: [SQL] sql programming
"Michael Wagner" <[EMAIL PROTECTED]> writes: > We need to export an SQL database to Excel. Is this within your scope and = > what might your cost be? > > Please respond to Dan Beach Why not just save it to text CSV format and Excel can then easily import it. -- Prasanth Kumar [EMAIL PROTECTED]
Re: [SQL] question on SELECT
Howard Hiew <[EMAIL PROTECTED]> writes: > Hi, > I would like to know what is the sql statement that list all the tables > name. > > For example in Oracle, > 'SELECT TABLE_NAME from ALL_TABLES where owner="Oracle" '; > > What is the statement for Postgres? > Thank you > > Best Regards, > Howard > CIM/MASTEC > Tel:(65)8605283 You can do '\dt' to list all tables. There is also a system table 'pg_tables' which you can use if you like to do a select instead. Do SELECT tablename FROM pg_tables where tableowner='postgres'; -- Prasanth Kumar [EMAIL PROTECTED]
[SQL] Select by priority
I need some suggestions on how to construct a particular select that I need. I have a table of addresses where the primary key is the persons_id and a address_type field. The address_type field is a character which specifies whether the address is for the home, work, or mailing. A person can have multiple addresses though only one of a type. What I want to do is select a list of address for each distinct individual but wish to select based of priority that if the mailing address exists, I get that one only. If there is no mailing address, I want the home address and failing that, get the work address. Is it within the realm of sql to be able to do that? -- Prasanth Kumar [EMAIL PROTECTED]
Re: [SQL] Select by priority
"omid omoomi" <[EMAIL PROTECTED]> writes: > Hi, > How about ordering by Address_type ? only needs to have 1, 2, 3 as > different address types in your desired order. like : > > select person_id , address where address in not null order by address_type > limit 1 ; > > sure you can change the limit if you wish more addresses. > hope that helps > Omid Omoomi > > >From: [EMAIL PROTECTED] (Prasanth A. Kumar) > >To: [EMAIL PROTECTED] > >Subject: [SQL] Select by priority > >Date: 15 Jul 2000 10:11:43 -0700 > > > >I need some suggestions on how to construct a particular select that I > >need. I have a table of addresses where the primary key is the > >persons_id and a address_type field. The address_type field is a > >character which specifies whether the address is for the home, work, > >or mailing. A person can have multiple addresses though only one of a > >type. What I want to do is select a list of address for each distinct > >individual but wish to select based of priority that if the mailing > >address exists, I get that one only. If there is no mailing address, I > >want the home address and failing that, get the work address. Is it > >within the realm of sql to be able to do that? I think the order thing will work though I have no choice of using numbering for the address_type as I am working off a pre-existing database. They are using a mnemonic char type. I am essentially batch downloading and processing this and other database tables for query and presentations using web pages. BTW, does the 'limit' feature exist in Oracle? The main database I am extracting data from is Oracle... -- Prasanth Kumar [EMAIL PROTECTED]
Re: [SQL] Select by priority
"Gary J. Farmer" <[EMAIL PROTECTED]> writes: > >> I think the order thing will work though I have no choice of using > >> numbering for the address_type as I am working off a pre-existing > >> database. They are using a mnemonic char type. I am essentially batch > >> downloading and processing this and other database tables for query > >> and presentations using web pages. BTW, does the 'limit' feature exist > >> in Oracle? The main database I am extracting data from is Oracle... > > I do not know whether you can add a table associating "priority" with > "address_type". If you can, you might try something like the following > example, using the address_priority table: > Appears to work with either Oracle or Postgres (though I changed VARCHAR > to VARCHAR2 for Oracle). > > Gary Farmer That looks like a workable solution. I'll try it out. Thanks... -- Prasanth Kumar [EMAIL PROTECTED]
