Re: [SQL] Comparing two tables of different database
In case dblink was not installed, you could try the following: 1. dump only the data from the table from database 1 pg_dump -U username -a -d -t tablename dbname > tablename.sql 2. create a (temp) table in database 2 SELECT * INTO tablename_bak from tablename WHERE 1 = 2 3. restore the dumped data in the bak table in database 2 pg_restore -U username -a -t tablename_bak dbname tablename.sql 4.select * from tablename except select * from tablename_bak or you could dump the data from both tables and use some kind of diff tool Nicholas I wrote: Hi, can anybody me suggest me, how to compare two tables of different database. -Nicholas I -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Comparing two tables of different database
Nicholas,
To use the dblink:
1. In your postgres server you should find a file *dblink.sql*.
In my beta installation is in *share/postgresql/contrib*. It is the
installation for the dblink contrib module that usually is already compiled
in. It will create a lot of dblink functions.
2. on database2 create a function nammed db_datbase1() which returns
"dbname=database1" (if you need a login use "dbname=database1 password=xxx",
you can also specify host= port= to connect in a remote postgresql database)
3. now execute the sql:
select * from dblink(db_database1(), 'select "id", "name", "time" from
pr_1') as pr_1("id" integer, "name" text, "time" time)
then you will see the table "pr_1" on the datbase2
--
Lucas Brito
[SQL] using a list to query
I have a list (or array) of primary keys stored in a field (data type text). I would like to use the list to retrieve all the data from a table based on the list. my text field contains: '123,134,343,345' I would to do something like the following: Select * from table1 where table1.pkid in (select myTextfield from table2) So the question is there a simple way to use the list to retrieve my data? -- John Fabiani -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Fwd: [SQL] Comparing two tables of different database
i've not tried this in postgres, but using information_schema makes
comparing structures of databases trivial. i've been using this query for a
while with MSSQL. Maybe this helps to answer the question.
- isaac
select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
from [database].information_schema.Columns ST
full outer join [other database].information_schema.Columns DV
on ST.Table_Name = DV.Table_name
and ST.Column_Name = DV.Column_Name
where ST.Column_Name is null or DV.Column_Name is NULL
On Sat, May 2, 2009 at 11:01 AM, Lucas Brito wrote:
> Nicholas,
>
> To use the dblink:
>
>1. In your postgres server you should find a file *dblink.sql*.
>In my beta installation is in *share/postgresql/contrib*. It is the
>installation for the dblink contrib module that usually is already compiled
>in. It will create a lot of dblink functions.
>
>2. on database2 create a function nammed db_datbase1() which returns
>"dbname=database1" (if you need a login use "dbname=database1
> password=xxx",
>you can also specify host= port= to connect in a remote postgresql
> database)
>
>3. now execute the sql:
>select * from dblink(db_database1(), 'select "id", "name", "time" from
>pr_1') as pr_1("id" integer, "name" text, "time" time)
>then you will see the table "pr_1" on the datbase2
>
> --
> Lucas Brito
Re: [SQL] Comparing two tables of different database
2009/5/2 Isaac Dover
> i've not tried this in postgres, but using information_schema makes
> comparing structures of databases trivial. i've been using this query for a
> while with MSSQL. Maybe this helps to answer the question.
>
> - isaac
>
> select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
> from [database].information_schema.Columns ST
> full outer join [other database].information_schema.Columns DV
> on ST.Table_Name = DV.Table_name
> and ST.Column_Name = DV.Column_Name
> where ST.Column_Name is null or DV.Column_Name is NULL
>
>
Isaac, this query will return "ERROR: cross-database references are not
implemented".
Postgres does not support queries in databases other than current one. Even
a simple select * from otherdatabase.information_schema.columns will not
work and throw the same error.
However this can be done with dblink function like:
select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
from information_schema.Columns ST
full outer join dblink('dbname=otherdatabase','select Table_Name,
Column_Name from information_schema.Columns') DV(Table_Name text,
Column_Name text)
on ST.Table_Name = DV.Table_name
and ST.Column_Name = DV.Column_Name
where ST.Column_Name is null or DV.Column_Name is NULL
--
Lucas Brito
Re: [SQL] using a list to query
johnf wrote: > I have a list (or array) of primary keys stored in a field (data type text). > I would like to use the list to retrieve all the data from a table based on > the list. > > my text field contains: > '123,134,343,345' > > I would to do something like the following: > > Select * from table1 where table1.pkid in (select myTextfield from table2) > > So the question is there a simple way to use the list to retrieve my data? http://www.postgresql.org/docs/8.3/static/functions-matching.html SELECT * FROM table1 WHERE table1.pkid IN ( SELECT x FROM regexp_split_to_table(myTextfield, ',') AS x ); ... but you should consider storing your list in an array instead, or using a more conventional child table with a (pkid, refid) pair list. -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Comparing two tables of different database
Thank you, Lucas. I'm from the MS world, still learning these PG things.
Though, it appears that the difference is somewhat minor. In my actual
implementation, [other database] would be a linked server, which sounds like
it would be similar to the PG dblink. Regardless, I've found information
schema to be incredibly valuable.
Thanks,
- Isaac
On Sat, May 2, 2009 at 5:25 PM, Lucas Brito wrote:
> 2009/5/2 Isaac Dover
>
>> i've not tried this in postgres, but using information_schema makes
>> comparing structures of databases trivial. i've been using this query for a
>> while with MSSQL. Maybe this helps to answer the question.
>>
>> - isaac
>>
>> select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
>> from [database].information_schema.Columns ST
>> full outer join [other database].information_schema.Columns DV
>> on ST.Table_Name = DV.Table_name
>> and ST.Column_Name = DV.Column_Name
>> where ST.Column_Name is null or DV.Column_Name is NULL
>>
>>
>
> Isaac, this query will return "ERROR: cross-database references are not
> implemented".
>
> Postgres does not support queries in databases other than current one. Even
> a simple select * from otherdatabase.information_schema.columns will not
> work and throw the same error.
>
> However this can be done with dblink function like:
>
> select ST.Table_Name, ST.Column_Name, DV.Table_Name, DV.Column_Name, *
> from information_schema.Columns ST
> full outer join dblink('dbname=otherdatabase','select Table_Name,
> Column_Name from information_schema.Columns') DV(Table_Name text,
> Column_Name text)
> on ST.Table_Name = DV.Table_name
> and ST.Column_Name = DV.Column_Name
> where ST.Column_Name is null or DV.Column_Name is NULL
>
> --
> Lucas Brito
>
>
