Re: joining tables

[TedJones]

Hi Patrick

Thanks for your response. The tables that I gave were just an example and
there is no relevance in the friend/yes or names/contact details in
different tables.

To clarity, another example would be three tables - shop1, shop2 and shop3.
Each would have products so that would be a common column but product price
may also be a common column. However, there would be also different column
names for each shop. (columns with same names would be of the same data
type).

I have no control over the three tables that I get as csv files. It should
be easy enough, if needed to add a primary key column (to each?) after
reading in the csv files into tables.

So, generally, what I am trying to achieve is to combine three tables into
one table where some of the columns are the same and some are not. e.g table
1 with 12 columns; table 2 with 10 columns where 4 are the same as in table
1; table 3 with 4 columns where 1 is the same as in table 1 and 1 the same
as in table 2. The number of columns in the result table would then be 12
(table 1) +  6 (new ones from table 2) + 2 (new ones from table 3) = 20
columns. 

Usually the data rows from each table will be different but not always. If
data in a column common to all three tables e.g product name, was the same
from all tables i.e. same product name then all the information about that
product from the three tables would be in all the columns for that data row.
I hope that’s clear. i.e. the difference between example1 and example2 in my
original question.

In this was in reverse it would start with the large result table and use
three SELECT statements to create the three tables.

Thanks
Ted Jones





--
Sent from: 
https://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html

Negative values for obj_id: SELECT statements throwing errors in query editor

[Holger Kopp-Musick]

Hi,

I'm using pgAdmin 4.12 in desktop mode on Win 10 1809, databases are 10.9 on 
CentOS 7.6. When executing any SELECT in the query editor, I get no data but 
the following error message:
Operator does not exist: - oid

I understand that converting the negative value of attrelid causes that error 
(full internal statement below, apparently by 
\web\pgadmin\tools\sqleditor\templates\sqleditor\sql\default\primary_keys.sql):

SELECT at.attname, at.attnum, ty.typname
FROM pg_attribute at LEFT JOIN pg_type ty ON (ty.oid = at.atttypid)
WHERE attrelid=-1769161114::oid AND attnum =
ANY ((SELECT con.conkey FROM pg_class rel LEFT OUTER JOIN pg_constraint con ON 
con.conrelid=rel.oid AND con.contype='p' WHERE rel.relkind IN ('r','s','t') AND 
rel.oid = -1769161114::oid)::oid[])

Strange enough, showing the first 100 rows via the menu works as expected and I 
couldn't reproduce that error neither on a 9.6 database (on Windows localhost) 
nor with pgAdmin 4.12 running on Linux (also desktop mode); pgAdmin 4.10 and 
pgAdmin 3 are also working without flaws.

Any ideas or suggestions?

Thanks in advance,
Holger

pgAdmin - migration

[Edson Richter]

Hi!

I’ve pgAdmin 4.12 running on Windows Server 2008r2. Run smoothly.

Right now, we are migrating from this 8 yrs old server to a new one, running 
Windows Server 2019.

Since each db developers already have about 40 servers registered in old 
server, I would like to know if is possible to migrate configurations (or where 
they are store) in order to pull everything to the new server with minimal 
impact. I don’t need to migrate databases, only pgAdmin configuration to avoid 
re-configure everything.


Thanks for your guidance,


Atenciosamente,

Edson Richter



Soli Deo Gloria.

Re: joining tables

[Jack Royal-Gordon]

Hi Ted,

In essence, you want to merge the three tables, removing records that are 
duplicated by another table, right?

Here are two approaches:

1) SELECT DISTINCT (field list) FROM (SELECT * from table1 UNION SELECT * from 
table2 UNION SELECT * from table 3).
This will remove all duplicates, regardless of their source (e.g. if table1 as 
two “Ted” fields, one of the rows will be eliminated. Overlapping fields
will be combined (e.g. only one field in the output), and non-overlapping 
fields will be merged. For example if t1 has f1, f2, and f3, and t2 has
f1, f3, and f4, and table 3 has f1, f2, and f5, the resulting output will have 
f1, f2, f3, f4, and f5; fields that did not exist in the source table will be 
NULL
in the output.

2) SELECT CASE WHEN t1.field1 IS NOT NULL THEN t1.field1 WHEN t2.field1 IS NOT 
NULL THEN t2.field1 ELSE t3.field1 END, … 
FROM t1 LEFT OUTER JOIN t2 ON (key field) LEFT OUTER JOIN t3 ON (key 
field)
Using LEFT OUTER JOIN ensures that all records from all three tables will be 
included and that duplicates among the three tables will result
in only one record. Duplicates within a table create a problem. For example, if 
t1 has “Ted” twice and t2 has “Ted” twice, there will be four “Ted”
records in the output.

If you can live with the elimination of duplicate rows within a table, option 1 
is the easiest as you don’t have to specifically code each field to be selected.

> On Sep 3, 2019, at 4:01 AM, TedJones  wrote:
> 
> Hi Patrick
> 
> Thanks for your response. The tables that I gave were just an example and
> there is no relevance in the friend/yes or names/contact details in
> different tables.
> 
> To clarity, another example would be three tables - shop1, shop2 and shop3.
> Each would have products so that would be a common column but product price
> may also be a common column. However, there would be also different column
> names for each shop. (columns with same names would be of the same data
> type).
> 
> I have no control over the three tables that I get as csv files. It should
> be easy enough, if needed to add a primary key column (to each?) after
> reading in the csv files into tables.
> 
> So, generally, what I am trying to achieve is to combine three tables into
> one table where some of the columns are the same and some are not. e.g table
> 1 with 12 columns; table 2 with 10 columns where 4 are the same as in table
> 1; table 3 with 4 columns where 1 is the same as in table 1 and 1 the same
> as in table 2. The number of columns in the result table would then be 12
> (table 1) +  6 (new ones from table 2) + 2 (new ones from table 3) = 20
> columns. 
> 
> Usually the data rows from each table will be different but not always. If
> data in a column common to all three tables e.g product name, was the same
> from all tables i.e. same product name then all the information about that
> product from the three tables would be in all the columns for that data row.
> I hope that’s clear. i.e. the difference between example1 and example2 in my
> original question.
> 
> In this was in reverse it would start with the large result table and use
> three SELECT statements to create the three tables.
> 
> Thanks
> Ted Jones
> 
> 
> 
> 
> 
> --
> Sent from: 
> https://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html
> 
> 

Re: joining tables

[TedJones]

Hi Jack

I'm not sure if I understand your comment about two 'Ted' fields and
duplication of rows. In the example below there is 'Ted' twice in Table3 1
and 3 and must appear twice as I've shown in the result as the data in the
rest of the row is different. I agree with no duplication of rows if all of
the row is the same.

Ted

Table: 1
Author  Title   Sales   Publication Date
Jim A   aa  I   
Ted B   bb  J   
DaveC   cc  K   
Ted D   dd  L   

Table: 2
Author  Publisher   
Jim him 
Ted me  
Daveme  
Willyou
Garyhim

Table:3
Author  Title   Country
Ted B   UK
Ted D   US
Jim A   UK
DaveC   UK

Combined table: Result
Author  Title   Publication DatePublisher   Sales   Country
Ted B   J   me  bb  UK
Ted D   L   me  dd  US
Jim A   I   him aa  UK
DaveC   K   me  cc  UK
Garynullnullhim nullnull
Willnullnullyou nullnull




--
Sent from: 
https://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html

Re: pgAdmin - migration

[Murtuza Zabuawala]

Hi,

Please refer
https://www.pgadmin.org/docs/pgadmin4/4.12/import_export_servers.html

Configurations are stored in the "%APPDATA%\pgAdmin\pgadmin4.db" file.

--
Murtuza


On Tue, Sep 3, 2019 at 8:14 PM Edson Richter 
wrote:

> Hi!
>
>
>
> I’ve pgAdmin 4.12 running on Windows Server 2008r2. Run smoothly.
>
>
>
> Right now, we are migrating from this 8 yrs old server to a new one,
> running Windows Server 2019.
>
>
>
> Since each db developers already have about 40 servers registered in old
> server, I would like to know if is possible to migrate configurations (or
> where they are store) in order to pull everything to the new server with
> minimal impact. I don’t need to migrate databases, only pgAdmin
> configuration to avoid re-configure everything.
>
>
>
>
>
> Thanks for your guidance,
>
>
>
>
>
> Atenciosamente,
>
>
>
> Edson Richter
>
>
>
>
>
>
>
> Soli Deo Gloria.
>
>
>

Re: pgAdmin - migration

[Edson Richter]

Perfect, thanks!

Enviado do meu Telefone LG

-- Mensagem original--
De: Murtuza Zabuawala
Data: ter, 3 de set de 2019 14:04
Para: Edson Richter;
Cc:Pgadmin-Support@Postgresql. Org;
Assunto:Re: pgAdmin - migration

Hi,

Please refer 
https://www.pgadmin.org/docs/pgadmin4/4.12/import_export_servers.html

Configurations are stored in the "%APPDATA%\pgAdmin\pgadmin4.db" file.

--
Murtuza


On Tue, Sep 3, 2019 at 8:14 PM Edson Richter 
mailto:edsonrich...@hotmail.com>> wrote:
Hi!

I’ve pgAdmin 4.12 running on Windows Server 2008r2. Run smoothly.

Right now, we are migrating from this 8 yrs old server to a new one, running 
Windows Server 2019.

Since each db developers already have about 40 servers registered in old 
server, I would like to know if is possible to migrate configurations (or where 
they are store) in order to pull everything to the new server with minimal 
impact. I don’t need to migrate databases, only pgAdmin configuration to avoid 
re-configure everything.


Thanks for your guidance,


Atenciosamente,

Edson Richter



Soli Deo Gloria.

Re: joining tables

[Patrick Headley]

Not having unique IDs from the source tables will present a problem in 
that you could have more than one record in one of the tables with the 
same data.


It looks like you will need to determine the fields that make each row 
unique but common in all three tables. Then, you can match up records 
from the three tables.


The next issue is if there is similar data in similar columns, which 
data takes prescience. That will be something you have to decide. I 
think the best way to do that is to import the most accurate table 
first. If a record from the second table matches a record from the first 
table, only import column values that weren't already populated by the 
first import. Then do the same thing with the 3rd table and so on.


I think the overall process will be to import each CSV file into a 
Postgres work table of it's own. Then, import the first table into a 
master table that contains every field. Do the same with the second 
import with the additional logic to only set a column value if it wasn't 
already set by the first (prior) import. Then, do the same with the 
third table and so on. By getting the CSV files into Postgres first, it 
will be easier to manage cases where a column between the two tables is 
the same data but the names are different. I think it will also perform 
better.


The import of the first table into the main table will be a simple 
INSERT query. For each of the rest of the tables I think I would first 
run an UPDATE query with an INNER JOIN that updates column values of 
matching records but only if the column isn't already populated. Then, 
run an INSERT query using the second table on the left side of a LEFT 
OUTER JOIN and the main table on the right side and only return records 
from the left hand table that don't match any records on the right hand 
table (i.e. fields in right table are null). Do the same for the rest of 
the tables.


A different way to perform the INSERT query would be to use a sub query 
in the WHERE clause of the second table that only returns records that 
don't have matches in the main table. This will be easier if each of the 
source tables has it's own unique ID across all source tables. If you 
don't have unique IDs, you can use a concatenation of key field values.


SELECT columns
FROM table2
WHERE [concatenation of ] key_column(s) in table2 NOT IN (SELECT 
[contenation of] key_column(s) FROM table1);


*/Patrick Headley/*
Linx Consulting, Inc.
(303) 916-5522
phead...@linxco-inc.com
www.linxco-inc.com

On 9/3/19 5:01 AM, TedJones wrote:

Hi Patrick

Thanks for your response. The tables that I gave were just an example and
there is no relevance in the friend/yes or names/contact details in
different tables.

To clarity, another example would be three tables - shop1, shop2 and shop3.
Each would have products so that would be a common column but product price
may also be a common column. However, there would be also different column
names for each shop. (columns with same names would be of the same data
type).

I have no control over the three tables that I get as csv files. It should
be easy enough, if needed to add a primary key column (to each?) after
reading in the csv files into tables.

So, generally, what I am trying to achieve is to combine three tables into
one table where some of the columns are the same and some are not. e.g table
1 with 12 columns; table 2 with 10 columns where 4 are the same as in table
1; table 3 with 4 columns where 1 is the same as in table 1 and 1 the same
as in table 2. The number of columns in the result table would then be 12
(table 1) +  6 (new ones from table 2) + 2 (new ones from table 3) = 20
columns.

Usually the data rows from each table will be different but not always. If
data in a column common to all three tables e.g product name, was the same
from all tables i.e. same product name then all the information about that
product from the three tables would be in all the columns for that data row.
I hope that’s clear. i.e. the difference between example1 and example2 in my
original question.

In this was in reverse it would start with the large result table and use
three SELECT statements to create the three tables.

Thanks
Ted Jones





--
Sent from: 
https://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html

Re: joining tables

[Jack Royal-Gordon]

Let me step back a bit, as I realize upon further reflection that the first 
method will not work.

As far as the issue about two “Ted” records, read it as though I said two “Ted 
D” records. It refers to two records in the same table with the same key value. 
If “Ted D” appeared twice in table 1 and twice in table 2, the join would give 
four resulting records (all combinations of the records from table 1 and the 
records from table 2 (and the records from table 3) — that’s fundamentally how 
a join works. If you don’t want that, then make sure that there are no 
duplicates within each of the tables.

> On Sep 3, 2019, at 9:35 AM, TedJones  wrote:
> 
> Hi Jack
> 
> I'm not sure if I understand your comment about two 'Ted' fields and
> duplication of rows. In the example below there is 'Ted' twice in Table3 1
> and 3 and must appear twice as I've shown in the result as the data in the
> rest of the row is different. I agree with no duplication of rows if all of
> the row is the same.
> 
> Ted
> 
> Table: 1  
> AuthorTitle   Sales   Publication Date
> Jim   A   aa  I   
> Ted   B   bb  J   
> Dave  C   cc  K   
> Ted   D   dd  L   
> 
> Table: 2
> AuthorPublisher   
> Jim   him 
> Ted   me  
> Dave  me  
> Will  you
> Gary  him
> 
> Table:3
> AuthorTitle   Country
> Ted   B   UK
> Ted   D   US
> Jim   A   UK
> Dave  C   UK
> 
> Combined table: Result
> AuthorTitle   Publication DatePublisher   Sales   Country
> Ted   B   J   me  bb  UK
> Ted   D   L   me  dd  US
> Jim   A   I   him aa  UK
> Dave  C   K   me  cc  UK
> Gary  nullnullhim nullnull
> Will  nullnullyou nullnull
> 
> 
> 
> 
> --
> Sent from: 
> https://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html
> 
> 

Re: joining tables

[Michel Feinstein]

Also, be aware that this is NOT the postgresql email list, but the pgAdmin
email list, you would get a lot more help about SQL there.

On Tue, Sep 3, 2019, 19:44 Jack Royal-Gordon  wrote:

> Let me step back a bit, as I realize upon further reflection that the
> first method will not work.
>
> As far as the issue about two “Ted” records, read it as though I said two
> “Ted D” records. It refers to two records in the same table with the same
> key value. If “Ted D” appeared twice in table 1 and twice in table 2, the
> join would give four resulting records (all combinations of the records
> from table 1 and the records from table 2 (and the records from table 3) —
> that’s fundamentally how a join works. If you don’t want that, then make
> sure that there are no duplicates within each of the tables.
>
> > On Sep 3, 2019, at 9:35 AM, TedJones  wrote:
> >
> > Hi Jack
> >
> > I'm not sure if I understand your comment about two 'Ted' fields and
> > duplication of rows. In the example below there is 'Ted' twice in Table3
> 1
> > and 3 and must appear twice as I've shown in the result as the data in
> the
> > rest of the row is different. I agree with no duplication of rows if all
> of
> > the row is the same.
> >
> > Ted
> >
> > Table: 1
> > AuthorTitle   Sales   Publication Date
> > Jim   A   aa  I
> > Ted   B   bb  J
> > Dave  C   cc  K
> > Ted   D   dd  L
> >
> > Table: 2
> > AuthorPublisher
> > Jim   him
> > Ted   me
> > Dave  me
> > Will  you
> > Gary  him
> >
> > Table:3
> > AuthorTitle   Country
> > Ted   B   UK
> > Ted   D   US
> > Jim   A   UK
> > Dave  C   UK
> >
> > Combined table: Result
> > AuthorTitle   Publication DatePublisher   Sales
>  Country
> > Ted   B   J   me  bb  UK
> > Ted   D   L   me  dd  US
> > Jim   A   I   him aa  UK
> > Dave  C   K   me  cc  UK
> > Gary  nullnullhim nullnull
> > Will  nullnullyou nullnull
> >
> >
> >
> >
> > --
> > Sent from:
> https://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html
> >
> >
>
>
>
>

Re: joining tables

[Avin Kavish]

yes, what you need is a full outer join. How are your actual results
different from the expected ones?

On Wed, Sep 4, 2019 at 4:18 AM Michel Feinstein 
wrote:

> Also, be aware that this is NOT the postgresql email list, but the pgAdmin
> email list, you would get a lot more help about SQL there.
>
> On Tue, Sep 3, 2019, 19:44 Jack Royal-Gordon  wrote:
>
>> Let me step back a bit, as I realize upon further reflection that the
>> first method will not work.
>>
>> As far as the issue about two “Ted” records, read it as though I said two
>> “Ted D” records. It refers to two records in the same table with the same
>> key value. If “Ted D” appeared twice in table 1 and twice in table 2, the
>> join would give four resulting records (all combinations of the records
>> from table 1 and the records from table 2 (and the records from table 3) —
>> that’s fundamentally how a join works. If you don’t want that, then make
>> sure that there are no duplicates within each of the tables.
>>
>> > On Sep 3, 2019, at 9:35 AM, TedJones  wrote:
>> >
>> > Hi Jack
>> >
>> > I'm not sure if I understand your comment about two 'Ted' fields and
>> > duplication of rows. In the example below there is 'Ted' twice in
>> Table3 1
>> > and 3 and must appear twice as I've shown in the result as the data in
>> the
>> > rest of the row is different. I agree with no duplication of rows if
>> all of
>> > the row is the same.
>> >
>> > Ted
>> >
>> > Table: 1
>> > AuthorTitle   Sales   Publication Date
>> > Jim   A   aa  I
>> > Ted   B   bb  J
>> > Dave  C   cc  K
>> > Ted   D   dd  L
>> >
>> > Table: 2
>> > AuthorPublisher
>> > Jim   him
>> > Ted   me
>> > Dave  me
>> > Will  you
>> > Gary  him
>> >
>> > Table:3
>> > AuthorTitle   Country
>> > Ted   B   UK
>> > Ted   D   US
>> > Jim   A   UK
>> > Dave  C   UK
>> >
>> > Combined table: Result
>> > AuthorTitle   Publication DatePublisher   Sales
>>  Country
>> > Ted   B   J   me  bb  UK
>> > Ted   D   L   me  dd  US
>> > Jim   A   I   him aa  UK
>> > Dave  C   K   me  cc  UK
>> > Gary  nullnullhim nullnull
>> > Will  nullnullyou nullnull
>> >
>> >
>> >
>> >
>> > --
>> > Sent from:
>> https://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html
>> >
>> >
>>
>>
>>
>>

Re: Negative values for obj_id: SELECT statements throwing errors in query editor

[Khushboo Vashi]

Hi,


On Tue, Sep 3, 2019 at 7:31 PM Holger Kopp-Musick  wrote:

> Hi,
>
> I'm using pgAdmin 4.12 in desktop mode on Win 10 1809, databases are 10.9
> on CentOS 7.6. When executing any SELECT in the query editor, I get no data
> but the following error message:
> Operator does not exist: - oid
>
> I understand that converting the negative value of attrelid causes that
> error (full internal statement below, apparently by
> \web\pgadmin\tools\sqleditor\templates\sqleditor\sql\default\primary_keys.sql):
>
> SELECT at.attname, at.attnum, ty.typname
> FROM pg_attribute at LEFT JOIN pg_type ty ON (ty.oid = at.atttypid)
> WHERE attrelid=-1769161114::oid AND attnum =
> ANY ((SELECT con.conkey FROM pg_class rel LEFT OUTER JOIN pg_constraint
> con ON con.conrelid=rel.oid AND con.contype='p' WHERE rel.relkind IN
> ('r','s','t') AND rel.oid = -1769161114::oid)::oid[])
>
> Strange enough, showing the first 100 rows via the menu works as expected
> and I couldn't reproduce that error neither on a 9.6 database (on Windows
> localhost) nor with pgAdmin 4.12 running on Linux (also desktop mode);
> pgAdmin 4.10 and pgAdmin 3 are also working without flaws.
>
> Any ideas or suggestions?
>
> Are you executing Select query with joins?
As the first 100 rows are working fine, have you tried to execute the same
query (which executes accessing the first 100 rows) ?

Can you please provide the logs? To enable logging please refer,
https://www.pgadmin.org/faq/#8
Also, try to provide the sample query to reproduce this issue.

Thanks,
Khushboo


> Thanks in advance,
> Holger
>
>
>
>

Re: Negative values for obj_id: SELECT statements throwing errors in query editor

[Khushboo Vashi]

Hi,

Please try to replace the
\web\pgadmin\tools\sqleditor\templates\sqleditor\sql\default\primary_keys.sql
file with the attached file and do not forget to take a backup of the
original file.
Restart the server and check the issue gets resolved or not.

Thanks,
Khushboo




On Tue, Sep 3, 2019 at 7:31 PM Holger Kopp-Musick  wrote:

> Hi,
>
> I'm using pgAdmin 4.12 in desktop mode on Win 10 1809, databases are 10.9
> on CentOS 7.6. When executing any SELECT in the query editor, I get no data
> but the following error message:
> Operator does not exist: - oid
>
> I understand that converting the negative value of attrelid causes that
> error (full internal statement below, apparently by
> \web\pgadmin\tools\sqleditor\templates\sqleditor\sql\default\primary_keys.sql):
>
> SELECT at.attname, at.attnum, ty.typname
> FROM pg_attribute at LEFT JOIN pg_type ty ON (ty.oid = at.atttypid)
> WHERE attrelid=-1769161114::oid AND attnum =
> ANY ((SELECT con.conkey FROM pg_class rel LEFT OUTER JOIN pg_constraint
> con ON con.conrelid=rel.oid AND con.contype='p' WHERE rel.relkind IN
> ('r','s','t') AND rel.oid = -1769161114::oid)::oid[])
>
> Strange enough, showing the first 100 rows via the menu works as expected
> and I couldn't reproduce that error neither on a 9.6 database (on Windows
> localhost) nor with pgAdmin 4.12 running on Linux (also desktop mode);
> pgAdmin 4.10 and pgAdmin 3 are also working without flaws.
>
> Any ideas or suggestions?
>
> Thanks in advance,
> Holger
>
>
>
>


primary_keys.sql
Description: Binary data