Re: [SQL] SELECT 1st field
When you select from function I think column name is the same as function name. So if function name is func query would be: SELECT func AS id FROM func(5); Sent from my Windows Phone From: Jan Bakuwel Sent: 15/05/2012 08:02 To: [email protected] Subject: [SQL] SELECT 1st field Hi, I've spend some time checking the documentation but haven't been able to find what I'm looking for. I've got a function that returns a set of integers and a view that selects from the function. What I need is the ability to name the column in the view, ie. create function func(i int) returns setof integer as $$ ... ...code ... $$ language plpythonu volatile; create view v as select 1 as "id" from func(5); In other words I'd like to refer to the first (and only) field returned and give that an alias, in this case "id". In some SQL dialects you can use "select 1" to select the first field, "select 2" to select the 2nd field and so on. Any suggestions? regards, Jan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
2012/5/14 Tom Lane > Carlos Mennens writes: > > I'm not understanding why I'm not able to change this column type from > > char to integer? > > > forza=# ALTER TABLE customers > > ALTER COLUMN cust_id TYPE integer; > > ERROR: column "cust_id" cannot be cast to type integer > > Try "ALTER ... cust_id TYPE integer USING cust_id::integer". > > If you don't specify a USING expression, the command requires an > implicit coercion from one type to the other, and there is none from > char(n) to int. You can force it with an explicit coercion, though. > > It strikes me that "cannot be cast" is a poor choice of words here, > since the types *can* be cast if you try. Would it be better if the > message said "cannot be cast implicitly to type foo"? We could also > consider a HINT mentioning use of USING. > Tom, as usual you are right. I find it reasonable to improve the message and to add the hint into an error report in this case. -- // Dmitriy.
Re: [SQL] SELECT 1st field
Jan Bakuwel writes: > What I need is the ability to name the column in the view, ie. > create view v as select 1 as "id" from func(5); I think what you're looking for is the ability to re-alias a column name, for example select id from func(5) as foo(id); regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SELECT 1st field
Both works fine: SELECT generate_series AS id FROM generate_series(1,5); and SELECT id FROM generate_series(1,5) AS foo(id); Technically dont know is there any differenece... Thanks, Misa 2012/5/15 Tom Lane > Jan Bakuwel writes: > > What I need is the ability to name the column in the view, ie. > > > create view v as select 1 as "id" from func(5); > > I think what you're looking for is the ability to re-alias a column name, > for example > >select id from func(5) as foo(id); > >regards, tom lane > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] order by different on mac vs linux
On Mon, May 14, 2012 at 5:00 PM, Tom Lane wrote: > Wes James writes: >> Why is there a different order on the different platforms. > > This is not exactly unusual. You should first check to see if > lc_collate is set differently in the two installations --- but even if > it's the same, there are often platform-specific interpretations of > the sorting rules. (Not to mention that OS X is flat out broken when > it comes to sorting UTF8 data ...) > > If you want consistent cross-platform results, "C" locale will get > that for you, but it's pretty stupid about non-ASCII characters. > > For more info read > http://www.postgresql.org/docs/9.1/static/charset.html > > regards, tom lane Thanks, I'll take a look. -wes -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order by different on mac vs linux
On Mon, May 14, 2012 at 5:00 PM, Tom Lane wrote: > Wes James writes: >> Why is there a different order on the different platforms. > > This is not exactly unusual. You should first check to see if > lc_collate is set differently in the two installations --- but even if > it's the same, there are often platform-specific interpretations of > the sorting rules. (Not to mention that OS X is flat out broken when > it comes to sorting UTF8 data ...) > > If you want consistent cross-platform results, "C" locale will get > that for you, but it's pretty stupid about non-ASCII characters. > > For more info read > http://www.postgresql.org/docs/9.1/static/charset.html > > regards, tom lane I checked both and they both say this: show lc_collate; lc_collate - en_US.UTF-8 (1 row) You mention OS X is broken, but it looks like the linux box is broken in this case. With the current database I have, it looks like everything is sorting correctly on both platforms, except those two records (the two lines from my first email) which I added later for testing characters and escaping, etc. on a web app. -wes -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order by different on mac vs linux
On Mon, May 14, 2012 at 5:00 PM, Tom Lane wrote: > Wes James writes: >> Why is there a different order on the different platforms. > > This is not exactly unusual. You should first check to see if > lc_collate is set differently in the two installations --- but even if > it's the same, there are often platform-specific interpretations of > the sorting rules. (Not to mention that OS X is flat out broken when > it comes to sorting UTF8 data ...) > > If you want consistent cross-platform results, "C" locale will get > that for you, but it's pretty stupid about non-ASCII characters. > > For more info read > http://www.postgresql.org/docs/9.1/static/charset.html > > regards, tom lane I tried using the postgres that comes with ubuntu (sudo apt-get install postgresql). With my app I kept getting invalid password. I went in to the database sudo -u postgres database and did \password and set a password, but I still got invalid password error from the app api trying to make a connection. I then went back to the source installed version and now the output is correct. I'm not sure what changed. hmmm. -wes -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order by different on mac vs linux
On Tue, May 15, 2012 at 10:06 AM, Wes James wrote: > On Mon, May 14, 2012 at 5:00 PM, Tom Lane wrote: >> Wes James writes: >>> Why is there a different order on the different platforms. >> >> This is not exactly unusual. You should first check to see if >> lc_collate is set differently in the two installations --- but even if >> it's the same, there are often platform-specific interpretations of >> the sorting rules. (Not to mention that OS X is flat out broken when >> it comes to sorting UTF8 data ...) >> >> If you want consistent cross-platform results, "C" locale will get >> that for you, but it's pretty stupid about non-ASCII characters. >> >> For more info read >> http://www.postgresql.org/docs/9.1/static/charset.html >> >> regards, tom lane > > I tried using the postgres that comes with ubuntu (sudo apt-get > install postgresql). > > With my app I kept getting invalid password. I went in to the > database sudo -u postgres database and did \password and set a > password, but I still got invalid password error from the app api > trying to make a connection. > > I then went back to the source installed version and now the output is > correct. I'm not sure what changed. hmmm. It's most likely an issue with the settings in your pg_hba.conf file. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order by different on mac vs linux
On Tue, May 15, 2012 at 10:16 AM, Scott Marlowe wrote: > On Tue, May 15, 2012 at 10:06 AM, Wes James wrote: >> On Mon, May 14, 2012 at 5:00 PM, Tom Lane wrote: >>> Wes James writes: Why is there a different order on the different platforms. >>> >>> This is not exactly unusual. You should first check to see if >>> lc_collate is set differently in the two installations --- but even if >>> it's the same, there are often platform-specific interpretations of >>> the sorting rules. (Not to mention that OS X is flat out broken when >>> it comes to sorting UTF8 data ...) >>> >>> If you want consistent cross-platform results, "C" locale will get >>> that for you, but it's pretty stupid about non-ASCII characters. >>> >>> For more info read >>> http://www.postgresql.org/docs/9.1/static/charset.html >>> >>> regards, tom lane >> >> I tried using the postgres that comes with ubuntu (sudo apt-get >> install postgresql). >> >> With my app I kept getting invalid password. I went in to the >> database sudo -u postgres database and did \password and set a >> password, but I still got invalid password error from the app api >> trying to make a connection. >> >> I then went back to the source installed version and now the output is >> correct. I'm not sure what changed. hmmm. > > It's most likely an issue with the settings in your pg_hba.conf file. Ok - if I try the apt-get version, I'll look at that. The apt-get version is a cluster version. Looks like that file is in /etc/postgresql/9.1/dbname I see local all postgres peer local all all peer host all all 127.0.0.1/32 md5 host all all ::1/128 md5 in there. -wes -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SELECT 1st field
Hi Misa, Tom & msi77, On 16/05/12 00:21, Misa Simic wrote: > SELECT id FROM generate_series(1,5) AS foo(id); Thanks for the suggestions - all sorted! cheers, Jan smime.p7s Description: S/MIME Cryptographic Signature
