Re: [SQL] insert related data into two tables
[EMAIL PROTECTED] wrote: Hello, I have two tables like these: TABLE_1: people registry fields: ID_T1, SURNAME, NAME ID_T1 is primary key TABLE_2: work groups fields: ID_T2, TASK ID_T2 is foreign key related to ID_T1 the first table is the list of employees, the second the task. Sometime I need to insert a new employee and at the same time the task for him: I want to INSERT TO table 1 JOHN DOE (the ID is assigned automatically since it's a primary key) and INSERT TO table 2 the ID and the task name for JOHN DOE. Is it possible to create a single query? What is the best way to do this? Thanks, Filippo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq maybe you could create a view for the join of the two tebles, then make that view writable by creating the appropriate triggers. Then a single insert into the view would in fact create everything. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Does PG have a database
I have working PostgreSQL databases on 3 of my ~30 Linux boxes. I want my software to be able to determine which of my 30 boxes have functional databases on them. Since Pg is part of the baseline distro, merely checking for the existence of an executable doesn't solve my problem. I tried looping through my list of hosts and running the command: psql -h $host --list but this fails on a box with a database with the same error code as on a box that doesn't have a database, if my PGUSER isn't set and my login ID is not the username of the database. The PGUSER setting might differ for each host, so specifying this in the software is not practical. Bottom line: What trick can I use to determine whether a box has a living, breathing Pg database if I don't know the DB owner? Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Does PG have a database
Hi, Mark, Mark Fenbers schrieb: > Bottom line: What trick can I use to determine whether a box has a > living, breathing Pg database if I don't know the DB owner? If all your Postmasters run on the default port, use nmap on the port, so you see that postmasters are running there. When they run, then there must be a cluster with databases, at least the template ones. If your "baseline distro" is debian, ssh into the machines, and do pg_lsclusters. HTH, Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Does PG have a database
Mark Fenbers ha scritto: > I have working PostgreSQL databases on 3 of my ~30 Linux boxes. I want > my software to be able to determine which of my 30 boxes have functional > databases on them. Since Pg is part of the baseline distro, merely > checking for the existence of an executable doesn't solve my problem. > > I tried looping through my list of hosts and running the command: >psql -h $host --list > but this fails on a box with a database with the same error code as on a > box that doesn't have a database, if my PGUSER isn't set and my login ID > is not the username of the database. The PGUSER setting might differ > for each host, so specifying this in the software is not practical. > > Bottom line: What trick can I use to determine whether a box has a > living, breathing Pg database if I don't know the DB owner? > Mark > Just write a custom client that connects to the given host, on some standard ports. If the connection is accepted, there is a possibility that on the other side there is a PostgreSQL server. To make sure send a StartupMessage http://www.postgresql.org/docs/8.1/static/protocol-message-formats.html You can reuse some code of a library I have written: pglib. http://developer.berlios.de/projects/pglib/ It is released under the MIT license, and it is written in Python and Twisted (a framework for writing asynchronous applications). By the way: pglib aims to be a complete replacement for libpq. Regards Manlio Perillo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Question on UNION
Everyone,
I ran into something I wasn't expecting while developing a new application. I
have two similar tables that are occasionally unioned pulling only about 3
fields from each. During my testing phase I noticed that the union statement
was returning what appeared to be a distinct list rather than a pure union such
as is illustrated below:
create table t1 (
f1 serial primary key,
f2 text,
f3 text,
f4 integer
);
create table t2 (
f1 serial primary key,
f2 text,
f3 text,
f4 integer
);
insert into t1 (f2, f3, f4) values ('A', 'a', 1);
insert into t1 (f2, f3, f4) values ('A', 'a', 1);
insert into t1 (f2, f3, f4) values ('B', 'b', 2);
insert into t1 (f2, f3, f4) values ('B', 'b', 2);
insert into t2 (f2, f3, f4) values ('C', 'c', 3);
insert into t2 (f2, f3, f4) values ('C', 'c', 3);
insert into t2 (f2, f3, f4) values ('D', 'd', 4);
insert into t2 (f2, f3, f4) values ('D', 'd', 4);
temp=# select * from t1;
f1 | f2 | f3 | f4
+++
1 | A | a | 1
2 | A | a | 1
3 | B | b | 2
4 | B | b | 2
(4 rows)
temp=# select * from t2;
f1 | f2 | f3 | f4
+++
1 | C | c | 3
2 | C | c | 3
3 | D | d | 4
4 | D | d | 4
(4 rows)
When I leave out f1 I receive a distinct list:
temp=# (select f2, f3, f4 from t1) UNION (select f2, f3, f4 from t2) order by
f4;
f2 | f3 | f4
++
A | a | 1
B | b | 2
C | c | 3
D | d | 4
(4 rows)
When the key field is included I get a full list:
temp=# (select * from t1) UNION (select * from t2) order by f4;
f1 | f2 | f3 | f4
+++
1 | A | a | 1
2 | A | a | 1
3 | B | b | 2
4 | B | b | 2
1 | C | c | 3
2 | C | c | 3
3 | D | d | 4
4 | D | d | 4
(8 rows)
Can anyone explain the underlying principle(s) in the UNION that would cause
this?
Thanks in advance
Mark
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Question on UNION
Hi, Mark, [EMAIL PROTECTED] schrieb: > I ran into something I wasn't expecting while developing a new > application. I have two similar tables that are occasionally unioned > pulling only about 3 fields from each. During my testing phase I > noticed that the union statement was returning what appeared to be a > distinct list rather than a pure union such as is illustrated below: This is the documented behaviour of UNION, if you don't wand duplicate elimination, use UNION ALL. http://www.postgresql.org/docs/8.1/static/sql-select.html documents this nicely. HTH, Schabi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] i need information regarding schema
Hi good morning to every one….. I am using pgAdmin to gui for postgresql…I want to know how to create schema in pgAdmin and how to set roles ……. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-4133-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9886774209|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
Re: [SQL] Does PG have a database
O Mark Fenbers έγραψε στις May 22, 2006 : > I have working PostgreSQL databases on 3 of my ~30 Linux boxes. I want > my software to be able to determine which of my 30 boxes have functional > databases on them. Since Pg is part of the baseline distro, merely > checking for the existence of an executable doesn't solve my problem. > > I tried looping through my list of hosts and running the command: > psql -h $host --list > but this fails on a box with a database with the same error code as on a > box that doesn't have a database, if my PGUSER isn't set and my login ID > is not the username of the database. The PGUSER setting might differ > for each host, so specifying this in the software is not practical. > > Bottom line: What trick can I use to determine whether a box has a > living, breathing Pg database if I don't know the DB owner? Use a net mgmt tool (like opennms). netcat, telnet to 5432, etc... > > Mark > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
