[SQL] Querying multiple database
Hello everyone, Please can you point me to the contrib/dblink package links. What am actually looking for is query multiple database located on different machines. Is there any documentation for same which tells the metadata steps? Existing archive link if exists please can you point me same. Regards - Dev
Re: [SQL] Querying multiple database
Am looking for pointers related to cross-database queries? Thanks in advance! On Tue, Feb 5, 2013 at 10:42 PM, Dev Kumkar wrote: > Hello everyone, > > Please can you point me to the contrib/dblink package links. > > What am actually looking for is query multiple database located on > different machines. Is there any documentation for same which tells the > metadata steps? > Existing archive link if exists please can you point me same. > > Regards - Dev > -- :o) dev
Re: [SQL] Querying multiple database
Hello Again,
Using dblink and now am facing a fatal error while tryinf to execute
dblink_connect as follows:
SELECT *
FROM dblink_connect('host=127.0.0.1 port=5432 dbname=postgres
password=test')
ERROR: could not establish connection
DETAIL: FATAL: password authentication failed for user "NETWORK SERVICE"
What this error is related to? Do I need to modify pg_hba.conf file by any
chance?
Regards...
[SQL] Facing authentication error on postgres 9.2 -> dblink functions
Hello Everyone,
I am using postgres 9.2 and when executing function dblink facing a fatal
error while trying to execute dblink_connect as follows:
* SELECT * FROM dblink_connect('host=127.0.0.1 port=5432 dbname=postgres
password=test')*
*ERROR*: could not establish connection DETAIL: FATAL: password
authentication failed for user "NETWORK SERVICE"
What this error is related to? Do I need to modify pg_hba.conf file by any
chance?
Thanks..
[SQL] Unquoted column names fold to lower case
Hello, All unquoted identifiers are assumed by PostgreSQL to be lower case by default. This means - SELECT MY_COLUMN FROM my_table - SELECT my_column FROM my_table - SELECT my_column as MY_COLUMN FROM my_table All refer to my_column and has column name in lowercase as my_column. If the aliases are in upper case and quoted then those are returned in upper case. - SELECT my_column as "MY_COLUMN" FROM my_table Is there any workaround to set the alias columns to be returned as upper case with adding quotes. Is there any connection level parameter to flip this behavior? Thanks in advance! Regards...
[SQL] Re: Unquoted column names fold to lower case
Resending On Wed, Jul 3, 2013 at 2:42 PM, Dev Kumkar wrote: > Hello, > > All unquoted identifiers are assumed by PostgreSQL to be lower case by > default. This means > - SELECT MY_COLUMN FROM my_table > - SELECT my_column FROM my_table > - SELECT my_column as MY_COLUMN FROM my_table > All refer to my_column and has column name in lowercase as my_column. > > If the aliases are in upper case and quoted then those are returned in > upper case. > - SELECT my_column as "MY_COLUMN" FROM my_table > > Is there any workaround to set the alias columns to be returned as upper > case without adding quotes. Is there any connection level parameter to flip > this behavior? > > Thanks in advance! > > Regards... >
Re: [SQL] Unquoted column names fold to lower case
Thanks! Sure this is not a bug. Just want to know if there is any possible way to achieve this. Reason being - need to change all the data layer of project where the column mapping has been done with Upper Case. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 8:54 PM, Bruce Momjian wrote: > Agreed. The original poster specifically wanted "MYTABLE" and mytable > to be the same, not "mytable" and mytable. Postgres is certainly > non-standard in this area. I think the ability visiually distinguish > lower-case letters better than upper-case letters has led to our > behavior. Not really, actually am looking for column aliases here and not the table. Here is the example again when the aliases are unquoted: - SELECT my_column as MY_COLUMN FROM my_table The above SELECT will fold the alias name as my_column and not MY_COLUMN. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 9:05 PM, Bruce Momjian wrote: > Yes, both the identifier names and alias names are folded to lower case. > I never thought of them as different, but you are right, they are, and > we are non-standard in both areas. Sorry. Any plans to fix this in next release or having a patch to fix this? The default PostgreSQL behavior is folding column names to lower case but when an alias is used it should fold as per alias name. Note that adding quotes for aliases will be blessed by PostgreSQL and then those will be folded to upper case BUT this adds up to lot of changes in the SQL layer. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 9:35 PM, Thomas Kellerer wrote: > I wonder why you need that. I never had the requirement for that. > > Which driver/interface do you use that requires an alias to be all > uppercase? Looks like you didn't get me, am not saying aliases to be all upper case by default (BTW upper case behavior is seen in Oracle by default which is exactly reverse of postgres). But what I am asking here is if an alias name is provided be it upper case, lower case, or a mix then shouldn't it be preserved as as it is given. All this talk is when alias names are unquoted, when quoted then its standard behavior as seen in other databases. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 9:50 PM, Tom Lane wrote: > > Any plans to fix this in next release or having a patch to fix this? > > No. > > This has been discussed (many times) before. There isn't any feasible > way to change this behavior without breaking an incredible amount of > code, much of which isn't even under our control. The marginal increase > in standards compliance is not worth the pain --- especially when the > aspect of the standard in question isn't even one that most of us like. > (All-upper-case is hard to read.) > > If this is a deal-breaker for you, then I'm sorry, but you need to find > another database. Postgres settled on this behavior fifteen years ago, > and we're not changing it now. > Again my question was not to change the default behavior and make then All-upper-case as there is no need to change it. Rather it was for alias names and if those are with upper case or mix case then wouldn't it will be good to preserve same. For me these changes to have aliases quoted are manageable and I was just checking for any thoughts here. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Thu, Jul 4, 2013 at 12:38 AM, Alvaro Herrera wrote: > Aliases are treated just like any other identifier. The downcasing > happens in the lexer (src/backend/parser/scan.l), which is totally > unaware of the context in which this is happening; so there's no way to > tweak the downcasing behavior for only aliases and not other > identifiers. > Oh OK, that makes sense and yes can see through complexity. So I believe when aliases are quoted then the scan process might be different and hence quotes aliases TEXT CASE gets preserved. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Thu, Jul 4, 2013 at 1:36 AM, Bruce Momjian wrote: > And let's not forget that column aliases can be used as indentifiers in > queries: > > test=> SELECT 1 AS x > test-> ORDER BY x; > x > --- > 1 > (1 row) > > test=> SELECT 1 AS "X" > ORDER BY x; > ERROR: column "x" does not exist > LINE 2: ORDER BY x; > > Changing this would mean that the same identifier would have different > case-folding rules depending on where it appeared in the query. Sorry but I am not sure about your point here. Currently if the alias is quoted then same needs to be used in queries as identifies: SELECT 1 AS "X" ORDER BY "X"; Regards...
Re: [SQL] Unquoted column names fold to lower case
On Thu, Jul 4, 2013 at 1:17 AM, Tom Lane wrote: > Quite aside from implementation difficulty, restricting the change to > just column aliases doesn't make it more palatable. You'd entirely lose > the argument that the change increases spec compliance, because the spec > is perfectly clear that a column alias is an identifier just like any > other. And you'd still be paying a large part of the application > breakage costs, because the identifiers coming back in query descriptors > are one of the main ways applications would notice such a change. True, applications will notice and map only the identifiers coming back in query descriptor and in cases when the application reading these resultsets is case-sensitive for descriptors then breakage cost will be there. Hence was all this discussion to reduce the application breakage cost in particular cases. Regards...
