[SQL] Multiple databases
Hi, I am converting sql code from sql server to postgresql. Data is currently being retrieved from multiple databases, how do I do this using postgresql. I have tried using eg. select * from datbasename.tablename but this does not work. Any ideas? Thanks, Sumaya
Re: [SQL] Multiple databases
am Wed, dem 16.04.2008, um 8:59:46 +0200 mailte Sumaya folgendes: > Hi, > > I am converting sql code from sql server to postgresql. Data is currently > being retrieved from multiple databases, how do I do this using postgresql. I > have tried using eg. select * from datbasename.tablename but this does not > work. Any ideas? You can use multiple databases, but you can't work across multiple databases. PostgreSQL offers schemas, you can use them. http://www.postgresql.org/docs/current/static/ddl-schemas.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Index on elements of an array
Is it possible to create an index on the elements of an array, or a functional index on a set-returning function? The index only needs to speed up queries for specific elements (using a simple membership test, position in the array does not matter) and perhaps range queries. The indexed types include integers and text strings, and possibly more (IP addresses, for instance). The number of elements per indexed array rarely exceeds 4, I think. I fear that the distribution of values makes the intarray module and full text search inappropriate choices (lots of values unique to a specific row). -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Multiple databases
Sumaya wrote: Hi, I am converting sql code from sql server to postgresql. Data is currently being retrieved from multiple databases, how do I do this using postgresql. I have tried using eg. select * from datbasename.tablename but this does not work. Any ideas? Thanks, Sumaya Are you saying that you want a particular function stored in say database A to be able to retrieve some data from dabatase X? If that so, you may take a look to the dblink contrib package. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Data Comparison Single Table Question
I can handle this outside sql, but it seems like I should be able to do this in sql as well. 1 table: countries. 3 columns: id, name, price What I'm trying to get is a result of the price differences between every country. So if the data looks like (ignoring the id field) Taiwain 30 UK 50 US 40 I'm trying to build a matrix that looks like: Taiwan UK US Taiwan 0 -20 -10 UK 20 0 10 US 10 -10 0 Any pointers would be appreciated. Bryan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to find double entries
On Apr 15, 2008, at 11:23 PM, Tom Lane wrote: What's really a duplicate sounds like a judgment call here, so you probably shouldn't even think of automating it completely. I did a consulting gig about 10 years ago for a company that made software to normalize street addresses and names. Literally dozens of people worked there, and that was their primary software product. It is definitely not a trivial task, as the rules can be extremely complex. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to find double entries
Vivek Khera wrote:
>
> On Apr 15, 2008, at 11:23 PM, Tom Lane wrote:
>> What's really a duplicate sounds like a judgment call here, so you
>> probably shouldn't even think of automating it completely.
>
> I did a consulting gig about 10 years ago for a company that made
> software to normalize street addresses and names. Literally dozens of
> people worked there, and that was their primary software product. It is
> definitely not a trivial task, as the rules can be extremely complex.
>From what little I've personally seen of others' addressing handling,
some (many/most?) people who blindly advocate full normalisation of
addresses either:
(a) only care about a rather restricted set of address types ("ordinary
residential addresses in ", though that can be bad enough);
or
(b) don't know how horrible addressing is yet ... and are going to
find out soon when their highly normalized addressing schema proves
incapable of representing some address they've just been presented with.
with most probably falling into the second category.
Overly strict addressing, without the associated fairly extreme
development effort to get it even vaguely right, seems to lead to users
working around the broken addressing schema by entering bogus data.
Personally I'm content to provide lots of space for user-formatted
addresses, only breaking out separate fields for the post code
(Australian only), the city/suburb, the state, and the country - all
stored as strings. The only DB level validation is a rule preventing the
entry of invalid & undefined postcodes for Australian addresses, and
preventing the entry of invalid Australian states. The app is used
almost entirely with Australian addresses, and there's a definitive, up
to date list of australian post codes available from the postal
services, so it's worth a little more checking to protect against basic
typos and misunderstandings.
The app provides some more help at the UI level for users, such as
automatically filling in the state and suburb if an Australian post code
is entered. It'll warn you if you enter an unknown Australian
suburb/city for an entry in Australia. For everything else I leave it to
the user and to possible later validation and reporting.
I've had good results with this policy when working with other apps that
need to handle addressing information, and I've had some truly horrible
experiences with apps that try to be too strict in their address checking.
--
Craig Ringer
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Data Comparison Single Table Question
Bryan Emrys escreveu: I can handle this outside sql, but it seems like I should be able to do this in sql as well. 1 table: countries. 3 columns: id, name, price What I'm trying to get is a result of the price differences between every country. So if the data looks like (ignoring the id field) Taiwain 30 UK 50 US 40 I'm trying to build a matrix that looks like: Taiwan UK US Taiwan 0 -20 -10 UK 20 0 10 US 10 -10 0 Not in a matrix form: bdteste=# SELECT f1.name,f1.price,f2.name,f1.price-f2.price AS difference FROM foo f1 CROSS JOIN foo f2; name | price | name | difference -+---+-+ Taiwain |30 | Taiwain | 0 Taiwain |30 | UK |-20 Taiwain |30 | US |-10 UK |50 | Taiwain | 20 UK |50 | UK | 0 UK |50 | US | 10 US |40 | Taiwain | 10 US |40 | UK |-10 US |40 | US | 0 (9 registros) Osvaldo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SQL/XML Multi table join question
Hi all,
I have the following tables (parent and two children)
CREATE SEQUENCE person_seq;
CREATE TABLE person(
_id integer DEFAULT nextval('person_seq') NOT NULL,
_timestamp TIMESTAMP NOT NULL,
_lastModified TIMESTAMP NOT NULL,
name VARCHAR(255) NOT NULL,
age INTEGER DEFAULT NULL,
PRIMARY KEY (_id)
);
CREATE TABLE person_nationality(
_id serial NOT NULL,
_parent INTEGER NOT NULL,
nationality VARCHAR(255) NOT NULL,
FOREIGN KEY (_parent) REFERENCES person (_id) ON DELETE CASCADE,
PRIMARY KEY (_id)
);
CREATE TABLE person_variables(
_id serial NOT NULL,
_parent INTEGER NOT NULL,
variable VARCHAR(255) DEFAULT NULL,
value VARCHAR(255) DEFAULT NULL,
FOREIGN KEY (_parent) REFERENCES person (_id) ON DELETE CASCADE,
PRIMARY KEY (_id)
);
I'm trying to generate XML element for each person which also contains
the person nationality and variables in one result set
SELECT
XMLROOT (
XMLELEMENT (
NAME information,
XMLATTRIBUTES (
person._id AS pid
),
XMLAGG(
XMLELEMENT(
name "nationality",
person_nationality.nationality
)
),
XMLAGG(
XMLELEMENT(
name "value",
person_variables.value
)
)
),
VERSION '1.0',
STANDALONE YES
)
FROM person
INNER JOIN person_nationality ON person_nationality._parent = person._id
INNER JOIN person_variables ON person_variables._parent = person._id
GROUP BY person._id
LIMIT 100 OFFSET 1;
The above query return number of variables * nationality for each
person which is expected (for me) because of the join logic. Also i
can't use DISTINCT keyword in XMLAGG function.
Any idea how to do this? or is there is any aggregate function returns
array from row set, i didn't find any in the documenation.
Thanks in advance
Mina.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] using string functions on plpgsql
I'm starting to create my firsts plpgsql functions, and I was wondering how to use the string function 'length' in the code of the fuction. I have tried so far and I get an error. I'm trying this: declare i integer; begin ... i = length(texto) ... where texto is a varchar(200) I pass as a parameter to the function calling. Any help will help. Thanks!!! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using string functions on plpgsql
"Marcelo Edgardo Paniagua Lizarraga" <[EMAIL PROTECTED]> writes: > I'm starting to create my firsts plpgsql functions, and I was > wondering how to use the string function 'length' in the code of the > fuction. I have tried so far and I get an error. There's nothing obviously wrong with what you posted, so the problem is somewhere in what you left out. If you want useful help, it's generally advisable to show a complete example of what you did and exactly what error message you got. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using string functions on plpgsql
Marcelo Edgardo Paniagua Lizarraga wrote: declare i integer; begin ... i = length(texto) ^^^ i := length(texto) -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using string functions on plpgsql
Craig Ringer wrote: Marcelo Edgardo Paniagua Lizarraga wrote: declare i integer; begin ... i = length(texto) ^^^ i := length(texto) Whoops, I spoke too soon - it seems both are valid for assignment. Has that always been true? The one time I don't write a small test before posting -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] What does mod - in "mod statement" - stand for?
Hi, Could you please tell me what does mod - in "mod statement" - stand for? "log_statement (string) Controls which SQL statements are logged. Valid values are none, ddl, mod, and all. (...). mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. (...)" [http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html] I do know about DDL, DML, DCL, which are commonly used in RBDMS, however I ignore about MOD, even if I understand now what it means from the documentation. If somebody can enlighten me. Thanks, -- Daniel http://www.majormode.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] What does mod - in "mod statement" - stand for?
Daniel CAUNE wrote: > Hi, > > Could you please tell me what does mod - in "mod statement" - stand for? `mod' is shorthand for "modify" or "modifying", ie statements that modify the data. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using string functions on plpgsql
Craig Ringer <[EMAIL PROTECTED]> writes: > i = length(texto) >> ^^^ >> >> i := length(texto) > Whoops, I spoke too soon - it seems both are valid for assignment. Has > that always been true? Yeah, it's not documented, but AFAIK plpgsql has always taken both. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
