Re: [SQL] Select clause in JOIN statement
På fredag 14. juni 2013 kl. 01:10:51, skrev Luca Vernini luca...@gmail.com: It works. Also consider views. Just used this on a my db: SELECT * FROM tblcus_customer INNER JOIN ( SELECT * FROM tblcus_customer_status WHERE status_id 0) AS b ON tblcus_customer.status = b.status_id This query is the same as a normal JOIN: SELECT * FROM tblcus_customer INNER JOIN tblcus_customer_status b ON tblcus_customer.status = b.status_id AND b.status_id 0 or SELECT * FROM tblcus_customer INNER JOIN tblcus_customer_status b ON tblcus_customer.status = b.status_id WHERE b.status_id 0 But you can JOIN on SELECTs selecting arbitrary stuff. -- Andreas Joseph Krogh andr...@officenet.no mob: +47 909 56 963 Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no Public key: http://home.officenet.no/~andreak/public_key.asc
[SQL] Select clause in JOIN statement
Is it valid to specify a SELECT statement as part of a JOIN clause? For example: SELECT table1.f1, table1.f2 FROM table1 INNER JOIN (SELECT table2.f1, table2.f2 FROM table2) table_aux ON table1.f1 = table_aux.f1 Respectfully, Jorge Maldonado
Re: [SQL] Select clause in JOIN statement
It works. Also consider views. Just used this on a my db: SELECT * FROM tblcus_customer INNER JOIN ( SELECT * FROM tblcus_customer_status WHERE status_id 0) AS b ON tblcus_customer.status = b.status_id You can even join with a function result. Regards, Luca. 2013/6/14 JORGE MALDONADO jorgemal1...@gmail.com: Is it valid to specify a SELECT statement as part of a JOIN clause? For example: SELECT table1.f1, table1.f2 FROM table1 INNER JOIN (SELECT table2.f1, table2.f2 FROM table2) table_aux ON table1.f1 = table_aux.f1 Respectfully, Jorge Maldonado -- 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] Select statement with except clause
Firstly, I want to thank you for responding. Secondly, I wonder if I should only reply to the mailing list (I clicked Reply All); if this is the case, I apologize for any inconvenience. Please let me know so I reply correctly next time. I will describe my issue with more detail. I need to perform 2 very similar queries as follows: *** QUERY 1 *** SELECT fldA, fldB, fldC, SUM(fldD) AS fldD FROM tableA WHERE condition1 GROUP BY fldA, fldB, fldC *** QUERY 2 *** SELECT fldA, fldB, fldC, SUM(fldD)*(-1) AS fldD FROM tableA WHERE condition2 GROUP BY fldA, fldB, fldC As you can see, both reference the same table and the same fields. The differences between the queries are: a) The last SELECTED field is multiplied by (-1) in the second query. b) The WHERE conditions. What I finally need is to exclude records generated by QUERY1 from QUERY2 when fldA, fldB and fldC are equal in both results. With respect, Jorge Maldonado On Thu, May 23, 2013 at 1:36 PM, David Johnston pol...@yahoo.com wrote: JORGE MALDONADO wrote How does the EXCEPT work? Do fields should be identical? I need the difference to be on the first 3 fields. Except operates over the entire tuple so yes all fields are evaluated and, if they all match, the row from the left/upper query is excluded. If you need something different you can use some variation of: IN EXISTS NOT IN NOT EXISTS with a sub-query (correlated or uncorrelated as your need dictates). For example: SELECT col1, col2, col3, sum(col4) FROM tbl WHERE (col1, col2, col3) NOT IN (SELECT col1, col2, col3 FROM tbl2) -- not correlated GROUP BY col1, col2, col3 SELECT col1, col2, col3, sum(col4) FROM tbl WHERE NOT EXISTS ( SELECT 1 FROM tbl AS tbl2 WHERE --make sure to alias the sub-query table if it matches the outer reference (tbl.col1, tbl.col2, tbl.col3) = (tbl2.col1, tbl2.col2, tbl2.col3) ) -- correlated; reference tbl within the query inside the where clause GROUP BY col1, col2, col3 I do not follow your example enough to provide a more explicit example/solution but this should at least help point you in the right direction. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clause-tp5756658p5756661.html Sent from the PostgreSQL - sql mailing list archive at Nabble.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] Select statement with except clause
Reply-all is acceptable; but standard list protocol is to respond at the end of the message after performing quote editing. JORGE MALDONADO wrote Firstly, I want to thank you for responding. Secondly, I wonder if I should only reply to the mailing list (I clicked Reply All); if this is the case, I apologize for any inconvenience. Please let me know so I reply correctly next time. I will describe my issue with more detail. I need to perform 2 very similar queries as follows: *** QUERY 1 *** SELECT fldA, fldB, fldC, SUM(fldD) AS fldD FROM tableA WHERE condition1 GROUP BY fldA, fldB, fldC *** QUERY 2 *** SELECT fldA, fldB, fldC, SUM(fldD)*(-1) AS fldD FROM tableA WHERE condition2 GROUP BY fldA, fldB, fldC As you can see, both reference the same table and the same fields. The differences between the queries are: a) The last SELECTED field is multiplied by (-1) in the second query. b) The WHERE conditions. What I finally need is to exclude records generated by QUERY1 from QUERY2 when fldA, fldB and fldC are equal in both results. Example query layout; not promising it is the most efficient but it works. WITH q1 AS ( SELECT fldA, fldB, fldC, sum(fldD) AS sumD ... ) , q2 AS ( SELECT fldA, fldB, fldC, sum(fldD)*-1 AS sumD ... WHERE ... AND (fldA, fldB, fldC) NOT IN (SELECT (q1.fldA, q2.fldB, q3.fldC) FROM q1) ) SELECT fldA, fldB, fldC, sumD FROM q1 UNION ALL SELECT fldA, fldB, fldC, sumD FROM q2 ; If you actually explain the goal and not just ask a technical question you might find that people suggest alternatives that you are not even considering. SELECT fldA, fldB, fldC, sum_positive, sum_negative FROM (SELECT fldA, fldB, fldC, sum(fldD) AS sum_positive) q1 NATURAL FULL OUTER JOIN (SELECT fldA, fldB, fldC, sum(fldD)*-1 AS sum_negative) q2 WHERE ... Food for thought. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clause-tp5756658p5756790.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select statement with except clause
I have one SELECT statement as follows: SELECT lpt_titulo as tmt_titulo, tmd_nombre as tmt_nombre, tmd_album as tmt_album, SUM(lpt_puntos) AS tmt_puntos FROM listas_pre_titulos INNER JOIN cat_tit_media ON lpt_titulo = tmd_clave WHERE condition The above statement must have an EXCEPT clause which includes another SELECT statement almost identical, the difference is in the WHERE condition and also in one of the fields; SUM(lpt_puntos) AS tmt_puntos should be SUM(lpt_puntos) * -1 AS tmt_puntos. I only need to convert such a field to a negative value. How does the EXCEPT work? Do fields should be identical? I need the difference to be on the first 3 fields. Respectfully, Jorge Maldonado
Re: [SQL] Select statement with except clause
JORGE MALDONADO wrote How does the EXCEPT work? Do fields should be identical? I need the difference to be on the first 3 fields. Except operates over the entire tuple so yes all fields are evaluated and, if they all match, the row from the left/upper query is excluded. If you need something different you can use some variation of: IN EXISTS NOT IN NOT EXISTS with a sub-query (correlated or uncorrelated as your need dictates). For example: SELECT col1, col2, col3, sum(col4) FROM tbl WHERE (col1, col2, col3) NOT IN (SELECT col1, col2, col3 FROM tbl2) -- not correlated GROUP BY col1, col2, col3 SELECT col1, col2, col3, sum(col4) FROM tbl WHERE NOT EXISTS ( SELECT 1 FROM tbl AS tbl2 WHERE --make sure to alias the sub-query table if it matches the outer reference (tbl.col1, tbl.col2, tbl.col3) = (tbl2.col1, tbl2.col2, tbl2.col3) ) -- correlated; reference tbl within the query inside the where clause GROUP BY col1, col2, col3 I do not follow your example enough to provide a more explicit example/solution but this should at least help point you in the right direction. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clause-tp5756658p5756661.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- 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 Loss from SQL SELECT (vs. COPY/pg_dump)
I am troubled to find out that a SELECT statement produces fewer rows than the
actual row count and have not been able to answer myself as to why. I hope
someone could help shedding some light to this.
I attempted to generate a set of INSERT statements, using a the following
SELECT statement, against my translations data to reuse elsewhere, but then
realized the row count was 8 rows fewer than the source of 2,178. COPY and
pg_dump don't seem to lose any data. So, I compare the results to identify the
missing data as follows. I don't even see any strange encoding in those
missing data.
What scenario could have caused my SELECT query to dump out the 8 blank rows,
instead of the expected data?
Here is how I find the discrepancy:
===
$ psql -c CREATE TABLE new_translation AS
SELECT display_name, name, type, translation
FROM translations t JOIN lang l USING (langid)
WHERE display_name = 'SPANISH_CORP'
ORDER BY display_name, name
SELECT 2178
$ psql -tAc SELECT
'INSERT INTO new_translation VALUES ('
||quote_literal(display_name)||
', '||quote_literal(name)||
', '||quote_literal(type)||
', '||quote_literal(translation)||');'
FROM new_translation
ORDER BY display_name, name /tmp/new_translation-select.sql
$ pg_dump --data-only --inserts --table=new_translation clubpremier |
sed -n '/^INSERT/,/^$/p' /tmp/new_translation-pg_dump.sql
$ grep ^INSERT /tmp/new_translation-pg_dump.sql | wc -l
2178
$ grep ^INSERT /tmp/new_translation-select.sql | wc -l
2170
$ diff /tmp/new_translation-select.sql /tmp/new_translation-pg_dump.sql
27c27
---
INSERT INTO new_translation VALUES ('SPANISH_CORP',
'AGENCY_IN_USE_BY_COBRAND', NULL, 'La cuenta no puede ser eliminada porque
está siendo utilizada actualmente por la co-marca #cobrand#');
506c506
---
INSERT INTO new_translation VALUES ('SPANISH_CORP', 'CAR_DISTANCE_UNIT',
NULL, 'MILLAS');
1115c1115
---
INSERT INTO new_translation VALUES ('SPANISH_CORP', 'HOTEL_PROMO_TEXT',
'label', NULL);
1131,1134c1131,1134
---
INSERT INTO new_translation VALUES ('SPANISH_CORP',
'INSURANCE_SEARCH_ADVERTISEMENT_SECTION_ONE', 'checkout', NULL);
INSERT INTO new_translation VALUES ('SPANISH_CORP',
'INSURANCE_SEARCH_ADVERTISEMENT_SECTION_THREE', 'checkout', NULL);
INSERT INTO new_translation VALUES ('SPANISH_CORP',
'INSURANCE_SEARCH_ADVERTISEMENT_SECTION_TWO', 'checkout', NULL);
INSERT INTO new_translation VALUES ('SPANISH_CORP',
'INSURANCE_SEARCH_FOOTER', 'checkout', NULL);
1615c1615
---
INSERT INTO new_translation VALUES ('SPANISH_CORP', 'PAGE_FORGOT_PASSWORD',
'page_titles', NULL);
2215a2216
===
Thank you in advance for your help,
-Kong
Re: [SQL] Data Loss from SQL SELECT (vs. COPY/pg_dump)
This seems to answer my question. I completely forgot about the behavior of NULL value in the text concatenation. http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE Because quote_literal is labelled STRICT, it will always return null when called with a null argument. In the above example, if newvalue or keyvalue were null, the entire dynamic query string would become null, leading to an error from EXECUTE. You can avoid this problem by using the quote_nullable function, which works the same as quote_literal except that when called with a null argument it returns the string NULL. For example,
[SQL] select on many-to-many relationship
Dear list, assuming I have the following n:n relationship: t1: id_project 1 2 t2: id_product 1 2 intermediary table: t3 id_project|id_product 1|1 1|2 2|1 How can I create an output like this: id_project|id_product1|id_product2 1|1|2 2|1|NULL -- View this message in context: http://postgresql.1045698.n5.nabble.com/select-on-many-to-many-relationship-tp5733696.html Sent from the PostgreSQL - sql mailing list archive at Nabble.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] select on many-to-many relationship
2012/11/27 ssylla stefansy...@gmx.de: assuming I have the following n:n relationship: intermediary table: t3 id_project|id_product 1|1 1|2 2|1 How can I create an output like this: id_project|id_product1|id_product2 1|1|2 2|1|NULL I'd said the sample is too simplified — not clear which id_product should be picked if there're more then 2 exists. I assumed the ones with smallest IDs. -- this is just a sample source generator WITH t3(id_project, id_product) AS (VALUES (1,1),(1,2),(2,1)) -- this is the query SELECT l.id_project, min(l.id_product) id_product1, min(r.id_product) id_product2 FROM t3 l LEFT JOIN t3 r ON l.id_project=r.id_project AND l.id_product r.id_product GROUP BY l.id_project; -- Victor Y. Yegorov -- 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] select on many-to-many relationship
On Tue, Nov 27, 2012 at 2:13 AM, ssylla stefansy...@gmx.de wrote: id_project|id_product 1|1 1|2 2|1 How can I create an output like this: id_project|id_product1|id_product2 1|1|2 2|1|NULL You can use the crostab() function from the tablefunc module (http://www.postgresql.org/docs/9.2/static/tablefunc.html). It does exactly what you need. -- View this message in context: http://postgresql.1045698.n5.nabble.com/select-on-many-to-many-relationship-tp5733696.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.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] Select row cells as new columns
danycxxx wrote: Yes, I've looked at it, but id doesn't create the desired output. After more research I've found that I my design is similar to Entity, Attribute and Value(EAV) design and I think I have to redesign. Any suggestion regarding EAV? Is there any other approach? EAV is controversial. I am uncomfortable with it because it implements keys as values. I suggest that you not use EAV. Its putative flexibility comes at a large implementation price. The other approach is relational database design. You model an entity as a collection of tables, each of which represents an aspect of the data pertinent to the entity. Each table has columns, the labels of which correspond generally to the names of attributes for that aspect. Each row of each table provides values for its respective named columns. So a table roughly models what I'll call an entitylet - a cohesive piece of the entity model. The rules to decompose entity models into relational data models constitute normalization. I suggest you create a relational data model normalized to at least third normal form. -- Lew -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select row cells as new columns
Hello. I hope you can help me with this or at least guide me into the right
direction:
I have 2 tables:
CREATE TABLE infos
(
id integer NOT NULL DEFAULT nextval('info_id_seq'::regclass),
name text NOT NULL,
id_member integer NOT NULL,
title text,
min_length integer NOT NULL DEFAULT 0,
max_length integer NOT NULL DEFAULT 30,
required boolean NOT NULL DEFAULT false,
type text NOT NULL DEFAULT 'text'::text,
CONSTRAINT info_pkey PRIMARY KEY (id ),
CONSTRAINT infos_id_member_fkey FOREIGN KEY (id_member)
REFERENCES members (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE infos
OWNER TO postgres;
-- Index: info_id_idx
-- DROP INDEX info_id_idx;
CREATE INDEX info_id_idx
ON infos
USING btree
(id );
and
CREATE TABLE info_data
(
id serial NOT NULL,
id_info integer,
value text,
CONSTRAINT info_data_pkey PRIMARY KEY (id ),
CONSTRAINT info_data_id_info_fkey FOREIGN KEY (id_info)
REFERENCES infos (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE info_data
OWNER TO postgres;
-- Index: info_data_id_idx
-- DROP INDEX info_data_id_idx;
CREATE INDEX info_data_id_idx
ON info_data
USING btree
(id );
with the following values:
infos:
COPY infos (id, name, id_member, title, min_length, max_length, required,
type) FROM stdin;
1 nume1 Nume0 30 t text
2 prenume 1 Prenume 0 30 t text
3 cnp 1 C.N.P. 13 13 t number
4 nume anterior 1 Nume anterior 0 30 f text
5 stare civila1 Starea civila 0 30 f text
6 cetatenie 1 Cetatenie 0 30 f text
7 rezidenta 1 Rezidenta 0 30 f text
9 tip act 1 C.I. / B.I. 0 10 t text
10 serie ci1 Serie C.I. / B.I. 0 30 t
text
11 numar ci1 Numar C.I. / B.I. 0 30 t
text
12 data eliberarii 1 Data eliberarii 0 30 t text
13 eliberat de 1 Eliberat de 0 30 t text
8 adresa 1 Adresa 0 50 f text
\.
info_data:
COPY info_data (id, id_info, value) FROM stdin;
1 1 a
2 2 a
3 3 100
4 4
5 5
6 6
7 7
8 8
9 9 ci
10 10 sv
11 11 13
12 12 132
13 13 123
14 1 b
15 2 b
16 3 100
17 4
18 5
19 6
20 7
21 8
22 9 BI
23 10 XT
24 11 123
25 12 10
26 13 10
\.
The question:
How can I achive this output?
nume, prenume, cnp, nume anterior, stare civila, ... (as columns - built
from unique rows from infos)
a , a, ...
b , b, ... (as rows)
http://postgresql.1045698.n5.nabble.com/file/n5709987/info_data.sql
info_data.sql
http://postgresql.1045698.n5.nabble.com/file/n5709987/infos.sql infos.sql
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Select-row-cells-as-new-columns-tp5709987.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.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] Select row cells as new columns
On Fri, 25 May 2012 02:03:41 -0700 (PDT), danycxxx wrote: [...] The question: How can I achive this output? nume, prenume, cnp, nume anterior, stare civila, ... (as columns - built from unique rows from infos) a , a, ... b , b, ... (as rows) Did you look at crosstab functions? http://www.postgresql.org/docs/9.1/static/tablefunc.html Jan -- professional: http://www.oscar-consult.de private: http://neslonek.homeunix.org/drupal/ -- 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] Select row cells as new columns
Yes, I've looked at it, but id doesn't create the desired output. After more research I've found that I my design is similar to Entity, Attribute and Value(EAV) design and I think I have to redesign. Any suggestion regarding EAV? Is there any other approach? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Select-row-cells-as-new-columns-tp5709987p5710005.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select every first/last record of a partition?
Hi, suppose a table that has records with some ID and a timestamp. id,ts 3,2012/01/03 5,2012/01/05 7,2012/01/07 3,2012/02/03 3,2012/01/05 5,2012/03/01 7,2012/04/04 to fetch every last row of those IDs I do: select id, ts from ( select id, ts, row_number() over ( partition by id order by ts desc ) as nr from mytab ) as x where nr = 1 Is there a another way without a subselect? There might be more columns so the window-functions first/last won't help. -- 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] Select every first/last record of a partition?
I think this would work: select distinct on (id) id, ts --and whatever other columns you want from mytab order by id, timestamp desc; On Mon, May 21, 2012 at 12:04 PM, Andreas maps...@gmx.net wrote: Hi, suppose a table that has records with some ID and a timestamp. id, ts 3, 2012/01/03 5, 2012/01/05 7, 2012/01/07 3, 2012/02/03 3, 2012/01/05 5, 2012/03/01 7, 2012/04/04 to fetch every last row of those IDs I do: select id, ts from ( select id, ts, row_number() over ( partition by id order by ts desc ) as nr from mytab ) as x where nr = 1 Is there a another way without a subselect? There might be more columns so the window-functions first/last won't help. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SELECT 1st field
Hi, I've spend some time checking the documentation but haven't been able to find what I'm looking for. I've got a function that returns a set of integers and a view that selects from the function. What I need is the ability to name the column in the view, ie. create function func(i int) returns setof integer as $$ ... ...code ... $$ language plpythonu volatile; create view v as select 1 as id from func(5); In other words I'd like to refer to the first (and only) field returned and give that an alias, in this case id. In some SQL dialects you can use select 1 to select the first field, select 2 to select the 2nd field and so on. Any suggestions? regards, Jan smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] SELECT 1st field
Try this create view v(id) as select * from func(5); if your function returns one column. 15.05.2012, 10:01, Jan Bakuwel jan.baku...@greenpeace.org: Hi, I've spend some time checking the documentation but haven't been able to find what I'm looking for. I've got a function that returns a set of integers and a view that selects from the function. What I need is the ability to name the column in the view, ie. create function func(i int) returns setof integer as $$ ... ...code ... $$ language plpythonu volatile; create view v as select 1 as id from func(5); In other words I'd like to refer to the first (and only) field returned and give that an alias, in this case id. In some SQL dialects you can use select 1 to select the first field, select 2 to select the 2nd field and so on. Any suggestions? regards, Jan -- 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] SELECT 1st field
When you select from function I think column name is the same as function name. So if function name is func query would be: SELECT func AS id FROM func(5); Sent from my Windows Phone From: Jan Bakuwel Sent: 15/05/2012 08:02 To: pgsql-sql@postgresql.org Subject: [SQL] SELECT 1st field Hi, I've spend some time checking the documentation but haven't been able to find what I'm looking for. I've got a function that returns a set of integers and a view that selects from the function. What I need is the ability to name the column in the view, ie. create function func(i int) returns setof integer as $$ ... ...code ... $$ language plpythonu volatile; create view v as select 1 as id from func(5); In other words I'd like to refer to the first (and only) field returned and give that an alias, in this case id. In some SQL dialects you can use select 1 to select the first field, select 2 to select the 2nd field and so on. Any suggestions? regards, Jan -- 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] SELECT 1st field
Jan Bakuwel jan.baku...@greenpeace.org writes: What I need is the ability to name the column in the view, ie. create view v as select 1 as id from func(5); I think what you're looking for is the ability to re-alias a column name, for example select id from func(5) as foo(id); 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] SELECT 1st field
Both works fine: SELECT generate_series AS id FROM generate_series(1,5); and SELECT id FROM generate_series(1,5) AS foo(id); Technically dont know is there any differenece... Thanks, Misa 2012/5/15 Tom Lane t...@sss.pgh.pa.us Jan Bakuwel jan.baku...@greenpeace.org writes: What I need is the ability to name the column in the view, ie. create view v as select 1 as id from func(5); I think what you're looking for is the ability to re-alias a column name, for example select id from func(5) as foo(id); 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] SELECT 1st field
Hi Misa, Tom msi77, On 16/05/12 00:21, Misa Simic wrote: SELECT id FROM generate_series(1,5) AS foo(id); Thanks for the suggestions - all sorted! cheers, Jan smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] select xpath ...
(Note: catching up on a severe list backlog, thought I'd complete this
thread for the archives)
Brian -
In case Boris never sent anything directly, I'll extend his example and
show a solution. The usual problem w/ namespaces is getting your head
wrapped around the fact that they're local aliases: the fully expanded form of
each tag name is what any XSL actually operates on. This is convenient, since
while XML documents allow you to define a default (anonymous) namespace, XSL
does not. But since matching is done on the namespace value, not the alias, you
can work around that by using an explicit alias in the XSL.
The postgresql xpath() function takes a third argument, which is an ARRAY of
ARRAYs of namespace aliases. Since
So, w/ Boris's example:
CREATE temp TABLE tempxml ( record xml);
insert into tempxml values ('?xml version=1.0?
document xmlns:s1=urn:myorg/s1 xmlns=urn:myorg
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
id num=111-222-333-/
titlezz/title
/document'
);
select * from tempxml ;
record
document xmlns:s1=urn:myorg/s1
xmlns=urn:myorg
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
id num=111-222-333-/
titlezz/title
/document
(1 row)
No namespaces retrieves nothing:
select (xpath('document/title/text()', record))[1] from tempxml; xpath
---
(1 row)
Correct namespace (on all the tags):
select (xpath('/my:document/my:title/text()', record,
ARRAY[ARRAY['my','urn:myorg']]))[1] from tempxml;
xpath
zz
Attempt to use a 'default' namespace:
select (xpath('/document/title/text()', record,
ARRAY[ARRAY['','urn:myorg']]))[1] from tempxml;
ERROR: could not register XML namespace with name and URI urn:myorg
Hope that helps,
Ross
--
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer Admin, Research Scientistphone: 713-348-6166
Connexions http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
On Wed, Sep 21, 2011 at 12:27:41PM -0400, Brian Sherwood wrote:
Boris,
Can you send me your final solution?
I am trying to do something similar and I think I am stuck at the namespace.
Thanks
On Mon, Sep 19, 2011 at 11:49 AM, boris boris@localhost.localdomain wrote:
On 09/19/2011 10:49 AM, Rob Sargent wrote:
Having a name space in the doc requires it's usage in the query.
yeah, I got it... I was using wrong one...
thanks.
On 09/17/2011 11:48 AM, boris wrote:
hi all,
I've inserted xml file :
?xml version=1.0?
document xmlns:s1=urn:myorg/s1 xmlns=urn:myorg
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
id num=111-222-333-/
titlezz/title
..
to a table:
CREATE TABLE temp.tempxml
(
record xml
)
I can get it using:
select * from temp.tempxml
but, I can't get any values using xpath. ex:
select (xpath('/document/title/text()', record ))[1] from temp.tempxml
am I doing it right?
thanks.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
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] select xpath ...
Boris,
Can you send me your final solution?
I am trying to do something similar and I think I am stuck at the namespace.
Thanks
On Mon, Sep 19, 2011 at 11:49 AM, boris boris@localhost.localdomain wrote:
On 09/19/2011 10:49 AM, Rob Sargent wrote:
Having a name space in the doc requires it's usage in the query.
yeah, I got it... I was using wrong one...
thanks.
On 09/17/2011 11:48 AM, boris wrote:
hi all,
I've inserted xml file :
?xml version=1.0?
document xmlns:s1=urn:myorg/s1 xmlns=urn:myorg
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
id num=111-222-333-/
titlezz/title
..
to a table:
CREATE TABLE temp.tempxml
(
record xml
)
I can get it using:
select * from temp.tempxml
but, I can't get any values using xpath. ex:
select (xpath('/document/title/text()', record ))[1] from temp.tempxml
am I doing it right?
thanks.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select xpath ...
hi all,
I've inserted xml file :
?xml version=1.0?
document xmlns:s1=urn:myorg/s1 xmlns=urn:myorg
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
id num=111-222-333-/
titlezz/title
..
to a table:
CREATE TABLE temp.tempxml
(
record xml
)
I can get it using:
select * from temp.tempxml
but, I can't get any values using xpath. ex:
select (xpath('/document/title/text()', record ))[1] from temp.tempxml
am I doing it right?
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] select xpath ...
Having a name space in the doc requires it's usage in the query.
On 09/17/2011 11:48 AM, boris wrote:
hi all,
I've inserted xml file :
?xml version=1.0?
document xmlns:s1=urn:myorg/s1 xmlns=urn:myorg
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
id num=111-222-333-/
titlezz/title
..
to a table:
CREATE TABLE temp.tempxml
(
record xml
)
I can get it using:
select * from temp.tempxml
but, I can't get any values using xpath. ex:
select (xpath('/document/title/text()', record ))[1] from temp.tempxml
am I doing it right?
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] select xpath ...
On 09/19/2011 10:49 AM, Rob Sargent wrote:
Having a name space in the doc requires it's usage in the query.
yeah, I got it... I was using wrong one...
thanks.
On 09/17/2011 11:48 AM, boris wrote:
hi all,
I've inserted xml file :
?xml version=1.0?
document xmlns:s1=urn:myorg/s1 xmlns=urn:myorg
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
id num=111-222-333-/
titlezz/title
..
to a table:
CREATE TABLE temp.tempxml
(
record xml
)
I can get it using:
select * from temp.tempxml
but, I can't get any values using xpath. ex:
select (xpath('/document/title/text()', record ))[1] from temp.tempxml
am I doing it right?
thanks.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select random lines of a table using a probability distribution
Hi! Let's consider I have a table like this idqualificationgenderageincome I'd like to select (for example 100) lines of this table by random, but the random mechanism has to follow a certain probability distribution. I want to use this procedure to construct a test group for another selection. Example: I filter all lines having the qualification plumber. I get 50 different ids consisting of 40 males, 10 females and a certain age distribution. I also get some information concerning the income of the plumbers. Now I want to know if the income is more influenced by the gender and age distribution or by the qualification plumber. Therefore I would like to select a test group (of 50 or more) without any plumbers. This test group has to follow the same age and gender distribution. Then I would be able to compare this groups income statistics with the plumbers income statistics. Is this possible (and doable with reasonable effort) in PostgreSQL? Thank you in advance. Best regards, Marcel Jira ? ~~~ * ~~~ ? Mag. Marcel Jira ? Institut für Sozialpolitik, Wirtschaftsuniversität Wien ? +43 1 313 36-5890 ? UZA IV, D 317 ? http://www.wu.ac.at/sozialpolitik/team/wimi/jira ? ~~~ * ~~~
Re: [SQL] Select random lines of a table using a probability distribution
On Wed, Jul 13, 2011 at 03:27:10PM +0200, Jira, Marcel wrote: Hi! Let's consider I have a table like this idqualificationgenderageincome I'd like to select (for example 100) lines of this table by random, but the random mechanism has to follow a certain probability distribution. I want to use this procedure to construct a test group for another selection. Example: I filter all lines having the qualification plumber. I get 50 different ids consisting of 40 males, 10 females and a certain age distribution. I also get some information concerning the income of the plumbers. Now I want to know if the income is more influenced by the gender and age distribution or by the qualification plumber. Therefore I would like to select a test group (of 50 or more) without any plumbers. This test group has to follow the same age and gender distribution. Then I would be able to compare this groups income statistics with the plumbers income statistics. Is this possible (and doable with reasonable effort) in PostgreSQL? Thank you in advance. Best regards, Marcel Jira You may want to take a look at pl/R which make the R system available to PostgreSQL as a function language. Regards, Ken -- 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] Select For Update and Left Outer Join
On Apr 28, 2:00 am, pate...@patearl.net (Patrick Earl) wrote: This is a follow-up to an old message by Tom Lane: http://archives.postgresql.org/pgsql-sql/2006-10/msg00080.php [...] select * from Pet left join Dog on Dog.Id = Pet.Id left join Cat on Cat.Id = Pet.Id Now suppose you want to lock to ensure that your Cat is not updated concurrently. You add FOR UPDATE, but then PostgreSQL gets upset and complains that locking on the nullable side of an outer join is not allowed. Any resolve to or update on this issue? Thanks, greg.fenton -- greg dot fenton at gmail (yes, dot com) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select and merge rows?
Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 I need to get this table, divided by ID, like this: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 1 2 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
Re: [SQL] Select and merge rows?
Howdy! Is there a maximum ceilling of three values per order ID? or an ID can have an arbitrary number of values? Best, Oliveiros 2011/5/5 Claudio Adriano Guarracino elni...@yahoo.com Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: orderIDvalue -- 110003 210005 310006 110011 210012 110024 210024 I need to get this table, divided by ID, like this: idvalue1value2value3 1000356 100112 100212 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
[SQL] Select and merge rows?
Excuse me, The original table is: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 The result of table should be: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 4 4 Thanks! Regards, --- On Thu, 5/5/11, Claudio Adriano Guarracino elni...@yahoo.com wrote: From: Claudio Adriano Guarracino elni...@yahoo.com Subject: Select and merge rows? To: pgsql-sql@postgresql.org Date: Thursday, May 5, 2011, 4:18 PM Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 I need to get this table, divided by ID, like this: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 1 2 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
[SQL] Re: [SQL] Select and merge rows?
While there is insufficient information provided (a couple of table snippets), you may consider and experiment with the snippet below to get you started. SELECT ids.id, f1.value AS value1, f2.value AS value2, f3.value AS value3 FROM ( SELECT DISTINCT id FROM foo ) AS ids LEFT JOIN foo f1 ON f1.id = ids.id AND f1.order = 1 LEFT JOIN foo f2 ON f2.id = ids.id AND f2.order = 2 LEFT JOIN foo f3 ON f3.id = ids.id AND f3.order = 3 ORDER BY ids.id; - Reply message - From: Claudio Adriano Guarracino elni...@yahoo.com Date: Thu, May 5, 2011 5:18 pm Subject: [SQL] Select and merge rows? To: pgsql-sql@postgresql.org Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 I need to get this table, divided by ID, like this: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 1 2 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
Re: [SQL] Select and merge rows?
Thank you very much! Your example help me a lot! The original query is more complex, but I can continue with this example. Thanks again! --- On Thu, 5/5/11, scorpda...@hotmail.com scorpda...@hotmail.com wrote: From: scorpda...@hotmail.com scorpda...@hotmail.com Subject: Re: [SQL] Select and merge rows? To: Claudio Adriano Guarracino elni...@yahoo.com, pgsql-sql@postgresql.org Date: Thursday, May 5, 2011, 5:41 PM While there is insufficient information provided (a couple of table snippets), you may consider and experiment with the snippet below to get you started. SELECT ids.id, f1.value AS value1, f2.value AS value2, f3.value AS value3 FROM ( SELECT DISTINCT id FROM foo ) AS ids LEFT JOIN foo f1 ON f1.id = ids.id AND f1.order = 1 LEFT JOIN foo f2 ON f2.id = ids.id AND f2.order = 2 LEFT JOIN foo f3 ON f3.id = ids.id AND f3.order = 3 ORDER BY ids.id; - Reply message - From: Claudio Adriano Guarracino elni...@yahoo.com Date: Thu, May 5, 2011 5:18 pm Subject: [SQL] Select and merge rows? To: pgsql-sql@postgresql.org Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 I need to get this table, divided by ID, like this: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 1 2 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
Re: [SQL] Select and merge rows?
Hi again: I can did the same with crosstab: SELECT * FROM crosstab ( 'select id, order, value from test ORDER BY 1', 'select distinct order from test ORDER BY 1' ) AS ( id numeric(20), value1 text, value2 text, value3 text ); http://www.postgresql.org/docs/current/interactive/tablefunc.html In this case, i use: F.36.1.4. - crosstab(text, text). Thanks to Osvaldo Kussama for this help! --- On Thu, 5/5/11, Claudio Adriano Guarracino elni...@yahoo.com wrote: From: Claudio Adriano Guarracino elni...@yahoo.com Subject: Re: [SQL] Select and merge rows? To: pgsql-sql@postgresql.org, scorpda...@hotmail.com scorpda...@hotmail.com Date: Thursday, May 5, 2011, 9:06 PM Thank you very much! Your example help me a lot! The original query is more complex, but I can continue with this example. Thanks again! --- On Thu, 5/5/11, scorpda...@hotmail.com scorpda...@hotmail.com wrote: From: scorpda...@hotmail.com scorpda...@hotmail.com Subject: Re: [SQL] Select and merge rows? To: Claudio Adriano Guarracino elni...@yahoo.com, pgsql-sql@postgresql.org Date: Thursday, May 5, 2011, 5:41 PM While there is insufficient information provided (a couple of table snippets), you may consider and experiment with the snippet below to get you started. SELECT ids.id, f1.value AS value1, f2.value AS value2, f3.value AS value3 FROM ( SELECT DISTINCT id FROM foo ) AS ids LEFT JOIN foo f1 ON f1.id = ids.id AND f1.order = 1 LEFT JOIN foo f2 ON f2.id = ids.id AND f2.order = 2 LEFT JOIN foo f3 ON f3.id = ids.id AND f3.order = 3 ORDER BY ids.id; - Reply message - From: Claudio Adriano Guarracino elni...@yahoo.com Date: Thu, May 5, 2011 5:18 pm Subject: [SQL] Select and merge rows? To: pgsql-sql@postgresql.org Hello! I have a doubt about a query that I tried to do, but I cant.. This is the scenario: I have a table, with this rows: order ID value -- 1 1000 3 2 1000 5 3 1000 6 1 1001 1 2 1001 2 1 1002 4 2 1002 4 I need to get this table, divided by ID, like this: id value1 value2 value3 1000 3 5 6 1001 1 2 1002 1 2 How I can do this? I tried with cursors and view, but i can't Any help is welcome! Thanks in advance! Regards,
[SQL] Select For Update and Left Outer Join
This is a follow-up to an old message by Tom Lane: http://archives.postgresql.org/pgsql-sql/2006-10/msg00080.php In ORMs like NHibernate, there are a few strategies for mapping inheritance to SQL. One of these is Joined Subclass, which allows for the elimination of duplicate data and clean separation of class contents. With a class hierarchy such as this: Pet Dog : Pet Cat : Pet The query to get all the pets is as follows: select * from Pet left join Dog on Dog.Id = Pet.Id left join Cat on Cat.Id = Pet.Id Now suppose you want to lock to ensure that your Cat is not updated concurrently. You add FOR UPDATE, but then PostgreSQL gets upset and complains that locking on the nullable side of an outer join is not allowed. From our data model, we know that for every single Pet, there can never be a Dog or Cat that spontaneously appears, so locking in this case is totally safe. Unfortunately, PostgreSQL doesn't seem to provide any mechanism to lock just the rows involved in this query. Any advice? I'd be happy if such a thing was implemented in the engine, as it's supported by other databases without trouble. As another note, I'm one of the NHibernate developers and I'm working to get all the NHibernate tests working with PostgreSQL. The two significant cases I've had to avoid testing are the FOR UPDATE mentioned above and null characters in UTF strings. Storing a UTF char which defaults to zero doesn't work on PostgreSQL because it's apparently still using zero-terminated string functions. :( Aside from those two things, it looks like PostgreSQL is going to be passing all the tests soon, so that's good news. :) Patrick Earl -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select c1, method(c2) group by c1 returns all values of c2 for c1
Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) - (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: 1, val1, val2, val3 2, val1 3, val5, val6 Thanks a lot! -- Lu Ying -- 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] select c1, method(c2) group by c1 returns all values of c2 for c1
Hello you can use a string%agg function if you have a 9.0. On older version there is a array_agg function select c1, array_to_string(array_agg(c2),',') from T1 group by c1 regards Pavel Stehule 2011/2/8 Emi Lu em...@encs.concordia.ca: Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) - (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: 1, val1, val2, val3 2, val1 3, val5, val6 Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] select c1, method(c2) group by c1 returns all values of c2 for c1
SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where A.c1=B.c1),', ') from T1 A order by c1; Give it a Try !!! Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Emi Lu Sent: Tuesday, February 08, 2011 2:36 PM To: pgsql-sql@postgresql.org Subject: [SQL] select c1, method(c2) group by c1 returns all values of c2 for c1 Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) - (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: 1, val1, val2, val3 2, val1 3, val5, val6 Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] select c1, method(c2) group by c1 returns all values of c2 for c1
I'm not saying this is good or best but: select distinct a.c1, array_to_string(array(select c2 from t1 as b where b.c1 = a.c1),',') from t1 as a; c1 | array_to_string +- 1 | val1,val2,val3 2 | val1 3 | val5,val6 (3 rows) On Tue, Feb 8, 2011 at 2:35 PM, Emi Lu em...@encs.concordia.ca wrote: Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) - (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: 1, val1, val2, val3 2, val1 3, val5, val6 Thanks a lot! -- Lu Ying -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] select c1, method(c2) group by c1 returns all values of c2 for c1
On 02/08/2011 02:51 PM, Rolando Edwards wrote: SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where A.c1=B.c1),', ') from T1 A order by c1; Give it a Try !!! Thanks a lot! Very helpful! array_to_string() + array() is exactly what I am looking for! I just wonder that array_to_string() + array() will provide me good performance, right? If the calculation will be based on millions records. Thanks again! -- Lu Ying Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Emi Lu Sent: Tuesday, February 08, 2011 2:36 PM To: pgsql-sql@postgresql.org Subject: [SQL] select c1, method(c2) group by c1 returns all values of c2 for c1 Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) - (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: 1, val1, val2, val3 2, val1 3, val5, val6 Thanks a lot! -- Lu Ying -- Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 em...@encs.concordia.ca+1 514 848-2424 x5884 -- 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] select c1, method(c2) group by c1 returns all values of c2 for c1
2011/2/8 Emi Lu em...@encs.concordia.ca: On 02/08/2011 02:51 PM, Rolando Edwards wrote: SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where A.c1=B.c1),', ') from T1 A order by c1; Give it a Try !!! Thanks a lot! Very helpful! array_to_string() + array() is exactly what I am looking for! I just wonder that array_to_string() + array() will provide me good performance, right? If the calculation will be based on millions records. it depend on number of groups. This is correlated subquery - it must not be a best. Regards Pavel Stehule the best speed gives a string_agg, but it is only in 9.0 Thanks again! -- Lu Ying Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Emi Lu Sent: Tuesday, February 08, 2011 2:36 PM To: pgsql-sql@postgresql.org Subject: [SQL] select c1, method(c2) group by c1 returns all values of c2 for c1 Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) - (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: 1, val1, val2, val3 2, val1 3, val5, val6 Thanks a lot! -- Lu Ying -- Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 em...@encs.concordia.ca +1 514 848-2424 x5884 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select points of polygons
Hi there,
I have polygons with 5 points (left, center, right, top, bottom)
Now I would like to select an individual point out of the polygon. Are there
any functions to provide this in an readable manner other than:
e.g. SELECT replace(split_part(p::text,',(',1),'((','(')::point as point FROM (
SELECT
'((0.001329116037,0.007391900417),(0.001371765621,0.1608393682),(0.1502391498,0.00733744679),(0.001500387404,-0.1468751078),(-0.1472653422,0.007425591447))'::polygon
as p) as poly
è (0.001329116037,0.007391900417)
Thanks
Andreas
___
SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R D
Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.dehttp://www.scanlab.de
Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___
Re: [SQL] select points of polygons
Andreas Gaab a.g...@scanlab.de writes: I have polygons with 5 points (left, center, right, top, bottom) Now I would like to select an individual point out of the polygon. Are there any functions to provide this Doesn't look like it :-(. Seems like rather an oversight. 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
[SQL] combine SQL SELECT statements into one
Good Evening, Good Morning Wherever you are whenever you may be reading this. I am new to this email group and have some good experience with SQL and PostgreSQL database. I am currently working on a PHP / PostgreSQL project and I came upon something I could not figure out in SQL. I was wondering if anyone here could take a look and perhaps offer some guidance or assistance in helping me write this SQL query. Please Consider the following information: --- I have a postgresql table called 'inventory' that includes two fields: 'model' which is a character varying field and 'modified' which is a timestamp field. So the table inventory looks something like this: model modified - -- I778288176 2010-02-01 08:27:00 I778288176 2010-01-31 11:23:00 I778288176 2010-01-29 10:46:00 JKLM112345 2010-02-01 08:25:00 JKLM112345 2010-01-31 09:52:00 JKLM112345 2010-01-28 09:44:00 X22TUNM765 2010-01-17 10:13:00 V8893456T6 2010-01-01 09:17:00 Now with the table, fields and data in mind look at the following three queries: SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-02-01'; SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-20'; SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-01'; All three of the above queries work and provide results. However, I want to combine the three into one SQL Statement that hits the database one time. How can I do this in one SQL Statement? Is it possible with sub select? Here is what result I am looking for from one SELECT statement using the data example from above: count1 | count2 | count3 --- 2 2 4 Can this be done with ONE SQL STATEMENT? touching the database only ONE time? Please let me know. Thanx :) NEiL
Re: [SQL] [NOVICE] combine SQL SELECT statements into one
In response to Neil Stlyz : Good Evening, Good Morning Wherever you are whenever you may be reading this. I am new to this email group and have some good experience with SQL and PostgreSQL database. I am currently working on a PHP / PostgreSQL project and I came upon something I could not figure out in SQL. I was wondering if anyone here could take a look and perhaps offer some guidance or assistance in helping me write this SQL query. Please Consider the following information: --- I have a postgresql table called 'inventory' that includes two fields: 'model' which is a character varying field and 'modified' which is a timestamp field. So the table inventory looks something like this: model modified --- I7782881762010-02-01 08:27:00 I778288176 2010-01-31 11:23:00 I778288176 2010-01-29 10:46:00 JKLM112345 2010-02-01 08:25:00 JKLM112345 2010-01-31 09:52:00 JKLM112345 2010-01-28 09:44:00 X22TUNM7652010-01-17 10:13:00 V8893456T6 2010-01-01 09:17:00 Now with the table, fields and data in mind look at the following three queries: SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-02-01'; SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-20'; SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-01'; All three of the above queries work and provide results. However, I want to combine the three into one SQL Statement that hits the database one time. How can I do this in one SQL Statement? Is it possible with sub select? Here is what result I am looking for from one SELECT statement using the data example from above: count1 | count2 | count3 --- 2 2 4 Can this be done with ONE SQL STATEMENT? touching the database only ONE time? test=# select * from inventory ; model| modified +- I778288176 | 2010-02-01 08:27:00 I778288176 | 2010-01-31 11:23:00 I778288176 | 2010-01-29 10:46:00 JKLM112345 | 2010-02-01 08:25:00 JKLM112345 | 2010-01-31 09:52:00 JKLM112345 | 2010-01-28 09:44:00 X22TUNM765 | 2010-01-17 10:13:00 V8893456T6 | 2010-01-01 09:17:00 (8 rows) test=*# select count(distinct count1), count(distinct count2), count(distinct count3) from (select distinct case when modified = '2010-02-01' then model else null end as count1, case when modified = '2010-01-20' then model else null end as count2, case when modified = '2010-01-01' then model else null end as count3 from inventory) foo ; count | count | count ---+---+--- 2 | 2 | 4 (1 row) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] combine SQL SELECT statements into one
Hi, SELECT (SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-02-01') as count1, (SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-20') as count2, (SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-01') as count3 Serge Good Evening, Good Morning Wherever you are whenever you may be reading this. I am new to this email group and have some good experience with SQL and PostgreSQL database. I am currently working on a PHP / PostgreSQL project and I came upon something I could not figure out in SQL. I was wondering if anyone here could take a look and perhaps offer some guidance or assistance in helping me write this SQL query. Please Consider the following information: --- I have a postgresql table called 'inventory' that includes two fields: 'model' which is a character varying field and 'modified' which is a timestamp field. So the table inventory looks something like this: model modified --- I7782881762010-02-01 08:27:00 I778288176 2010-01-31 11:23:00 I778288176 2010-01-29 10:46:00 JKLM112345 2010-02-01 08:25:00 JKLM112345 2010-01-31 09:52:00 JKLM112345 2010-01-28 09:44:00 X22TUNM7652010-01-17 10:13:00 V8893456T6 2010-01-01 09:17:00 Now with the table, fields and data in mind look at the following three queries: SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-02-01'; SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-20'; SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-01'; All three of the above queries work and provide results. However, I want to combine the three into one SQL Statement that hits the database one time. How can I do this in one SQL Statement? Is it possible with sub select? Here is what result I am looking for from one SELECT statement using the data example from above: count1 | count2 | count3 --- 2 2 4 Can this be done with ONE SQL STATEMENT? touching the database only ONE time? Please let me know. Thanx :) NEiL Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] combine SQL SELECT statements into one
Hi, SELECT (SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-02-01') as count1, (SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-20') as count2, (SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-01') as count3 Serge Good Evening, Good Morning Wherever you are whenever you may be reading this. I am new to this email group and have some good experience with SQL and PostgreSQL database. I am currently working on a PHP / PostgreSQL project and I came upon something I could not figure out in SQL. I was wondering if anyone here could take a look and perhaps offer some guidance or assistance in helping me write this SQL query. Please Consider the following information: --- I have a postgresql table called 'inventory' that includes two fields: 'model' which is a character varying field and 'modified' which is a timestamp field. So the table inventory looks something like this: model modified --- I7782881762010-02-01 08:27:00 I778288176 2010-01-31 11:23:00 I778288176 2010-01-29 10:46:00 JKLM112345 2010-02-01 08:25:00 JKLM112345 2010-01-31 09:52:00 JKLM112345 2010-01-28 09:44:00 X22TUNM7652010-01-17 10:13:00 V8893456T6 2010-01-01 09:17:00 Now with the table, fields and data in mind look at the following three queries: SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-02-01'; SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-20'; SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-01'; All three of the above queries work and provide results. However, I want to combine the three into one SQL Statement that hits the database one time. How can I do this in one SQL Statement? Is it possible with sub select? Here is what result I am looking for from one SELECT statement using the data example from above: count1 | count2 | count3 --- 2 2 4 Can this be done with ONE SQL STATEMENT? touching the database only ONE time? Please let me know. Thanx :) NEiL Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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] combine SQL SELECT statements into one
msi77 wrote: Hi, SELECT (SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-02-01') as count1, (SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-20') as count2, (SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-01') as count3 But this statement will seq scan the table inventory three times as an explain analyze easily will show, while the solution from Andreas will do only one seq scan. This can be a big difference, depending on the size of the table. Serge Good Evening, Good Morning Wherever you are whenever you may be reading this. I am new to this email group and have some good experience with SQL and PostgreSQL database. I am currently working on a PHP / PostgreSQL project and I came upon something I could not figure out in SQL. I was wondering if anyone here could take a look and perhaps offer some guidance or assistance in helping me write this SQL query. Please Consider the following information: --- I have a postgresql table called 'inventory' that includes two fields: 'model' which is a character varying field and 'modified' which is a timestamp field. So the table inventory looks something like this: model modified --- I7782881762010-02-01 08:27:00 I778288176 2010-01-31 11:23:00 I778288176 2010-01-29 10:46:00 JKLM112345 2010-02-01 08:25:00 JKLM112345 2010-01-31 09:52:00 JKLM112345 2010-01-28 09:44:00 X22TUNM7652010-01-17 10:13:00 V8893456T6 2010-01-01 09:17:00 Now with the table, fields and data in mind look at the following three queries: SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-02-01'; SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-20'; SELECT COUNT(distinct model) FROM inventory WHERE modified = '2010-01-01'; All three of the above queries work and provide results. However, I want to combine the three into one SQL Statement that hits the database one time. How can I do this in one SQL Statement? Is it possible with sub select? Here is what result I am looking for from one SELECT statement using the data example from above: count1 | count2 | count3 --- 2 2 4 Can this be done with ONE SQL STATEMENT? touching the database only ONE time? Please let me know. Thanx :) NEiL Здесь спама нет http://mail.yandex.ru/nospam/sign -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select result into string's array
Hi,
I need to store the result of select into an array of string:
/create or replace function search_engine.test/
/(/
/)/
/returns integer as $$/
/declare/
/m_array text[];/
/begin/
/for m_array in select * from my_table loop/
/raise notice 'valor 1: %',m_array;/
/end loop;/
/return 1;/
/end; $$ LANGUAGE plpgsql;/
This launch this errors:
/ERROR: array value must start with { or dimension information
SQL state: 22P02
Context: PL/pgSQL function test line 4 at FOR over SELECT rows/
Is it possible do this?? May I choose another way?
Thanks in advance
Alberto,
--
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] select result into string's array
In response to Alberto Asuero Arroyo :
Hi,
I need to store the result of select into an array of string:
test=*# select * from foo;
t
--
foo
bar
batz
(3 rows)
test=*# select array_agg(t) from foo;
array_agg
{foo,bar,batz}
(1 row)
Helps that?
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
--
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] select result into string's array
Hello.
You should use an array constructor:
DECLARE
m_array text[];
[..]
BEGIN
FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP
[..]
END LOOP;
Regards,
Igrishin Dmitriy.
2009/10/9 Alberto Asuero Arroyo albertoasu...@gmail.com
Hi,
I need to store the result of select into an array of string:
/create or replace function search_engine.test/
/(/
/)/
/returns integer as $$/
/declare/
/m_array text[];/
/begin/
/for m_array in select * from my_table loop/
/raise notice 'valor 1: %',m_array;/
/end loop;/
/return 1;/
/end; $$ LANGUAGE plpgsql;/
This launch this errors:
/ERROR: array value must start with { or dimension information
SQL state: 22P02
Context: PL/pgSQL function test line 4 at FOR over SELECT rows/
Is it possible do this?? May I choose another way?
Thanks in advance
Alberto,
--
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] select result into string's array
Dmitriy Igrishin wrote:
Hello.
You should use an array constructor:
DECLARE
m_array text[];
[..]
BEGIN
FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP
[..]
END LOOP;
Regards,
Igrishin Dmitriy.
2009/10/9 Alberto Asuero Arroyo albertoasu...@gmail.com
mailto:albertoasu...@gmail.com
Hi,
I need to store the result of select into an array of string:
/create or replace function search_engine.test/
/(/
/)/
/returns integer as $$/
/declare/
/m_array text[];/
/begin/
/for m_array in select * from my_table loop/
/raise notice 'valor 1: %',m_array;/
/end loop;/
/return 1;/
/end; $$ LANGUAGE plpgsql;/
This launch this errors:
/ERROR: array value must start with { or dimension information
SQL state: 22P02
Context: PL/pgSQL function test line 4 at FOR over SELECT rows/
Is it possible do this?? May I choose another way?
Thanks in advance
Alberto,
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
mailto:pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
It's has been really useful for my to solve the dinamic Record
Introspection problem that I had.
Thanks,
Alberto
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SELECT max() group by problem
hi, I have table CREATE TABLE table ( id integer NOT NULL, timest timestamp with time zone NOT NULL, db_time timestamp with time zone NOT NULL DEFAULT now(), values text[], CONSTRAINT table_pkey PRIMARY KEY (id, timest) ) „id“ have foreign key with table1 and when I try to do SELECT MAX(table.timest) FROM table, table1 WHERE table.id=table1.id and table1.id in (1,2,3) GROUP BY id then it is terrible slow, when I use strange syntax SELECT table.timest FROM table,table1 WHERE table.id=table1.id and table1.id in(1,2,3) and table.timest= (SELECT max(timest) FROM table WHERE table.id=table1.id) I receive all needed data very fast. My questions are 1) why this first query is slow and what I can do to make it faster (some more indexes??)? 2) what kind of danger I have with second query (so far I have right data)? I have Postgres 8.3 and table have over million rows. Regards, Heigo
[SQL] SELECT max(time) group by problem
hi, I have table CREATE TABLE table ( id integer NOT NULL, timest timestamp with time zone NOT NULL, db_time timestamp with time zone NOT NULL DEFAULT now(), values text[], CONSTRAINT table_pkey PRIMARY KEY (id, timest) ) „id“ have foreign key with table1 and when I try to do SELECT MAX(table.timest) FROM table, table1 WHERE table.id=table1.id and table1.id in (1,2,3) GROUP BY table.id then it is terrible slow, when I use strange syntax SELECT table.timest FROM table,table1 WHERE table.id=table1.id and table1.id in(1,2,3) and table.timest= (SELECT max(timest) FROM table WHERE table.id=table1.id) I receive all needed data very fast. My questions are 1) why this first query is slow and what I can do to make it faster (some more indexes??)? 2) what kind of danger I have with second query (so far I have right data)? I have Postgres 8.3 and table have over million rows. Regards, Heigo
Re: [SQL] SELECT max(time) group by problem
Hello, Le 30/07/09 11:38, Heigo Niilop a écrit : hi, I have table CREATE TABLE table ( id integer NOT NULL, timest timestamp with time zone NOT NULL, db_time timestamp with time zone NOT NULL DEFAULT now(), values text[], CONSTRAINT table_pkey PRIMARY KEY (id, timest) ) [...] SELECT MAX(table.timest) FROM table, table1 WHERE table.id=table1.id and table1.id in (1,2,3) GROUP BY table.id [...] it is terrible slow, when I use strange syntax SELECT table.timest FROM table,table1 WHERE table.id=table1.id and table1.id in(1,2,3) and table.timest= (SELECT max(timest) FROM table WHERE table.id=table1.id) I receive all needed data very fast. My questions are 1) why this first query is slow and what I can do to make it faster (some more indexes??)? 2) what kind of danger I have with second query (so far I have right data)? I have Postgres 8.3 and table have over million rows. [...] Q1) EXPLAIN ANALYZE output would likely give (at least partially) some clarification about observed performance. According to table definition, the implicit index created on table_pkey would not be efficiently used in the 1st query because of explicit aggregation on column id (ie. partial key of table). Full scan of table is assumed for the join despite index scan on table1 with id index. Each table1 row is then joined with million of rows of table before matching WHERE clauses (as these latter apply for each row resulting from join). Slowness is expected. 3 ideas (with or without combination) of improvement come to my mind at this point: ID1- Definition of explicit index on table.id: this would lightly quicken aggregation by id and join on id; ID2- Aggregation on table1.id instead of table.id: because of reference declaration of table.id on table1.id, table1.id is assumed to be a (primary) key of table1 (and thence bound to an index, speeding up aggregation); ID3- Integration of WHERE clause table1.id IN (1,2,3) into a subquery on table1 (because this filter is independent from table) and use of this subquery instead of table1 call for join. This would reduce the size of the table to join to table and thence reduce the number of join rows at a sooner stage of query execution. A possible rewritten query would express as follows: SELECT MAX(t.timest) FROM table t INNER JOIN (SELECT id FROM table1 WHERE id IN (1,2,3)) t1 ON t.id = t1.id GROUP BY t1.id; (In this case, indexing on table1.id is not necessary although recommended.) Inviting you to assess this proposal (or a derivative according to ideas 1, 2, and/or 3). A 4th idea may consist in directly looking up table.id IN (1,2,3) in case of certainty on these 3 values in column table1.id. Such a way strengthens the efficiency of an index to declare on table.id so that lookup quickens. The query would look like the following: SELECT MAX(timest) FROM table WHERE id IN (1,2,3) GROUP BY id; Q2) On the other hand, the 2nd query seems to take advantage of table index on its primay key (id,timest) as the 2 columns are explicitely referred in WHERE clauses. The sub-select would be a bottleneck; but the WHERE clause of this sub-select refers to a literal against column id, ie. hash join is assumed to be efficient and used in this case by the database engine for a faster evaluation. Eventually I do not guess what you have in mind by danger from this 2nd query. Maybe I missed some elements. Regards. -- nha / Lyon / France. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select regexp_matches('a a a', '([a-z]) a','g');
Hello,
I wonder if someone has an idea for this problem:
I have a string that contains a serie of chars, separated by single
spaces.
e.g 'a b x n r a b c b'
Having such a string, I d'like to get a list of all predecessors of a
given character.
In the example, the predecessors of b are a,a,c.
If I now have the string 'a a a', the predecessors of 'a' are a,a
I tried to use regexp_matches for this:
select regexp_matches('a a a', '([a-z]) a','g');
= {a } only
As the second parameter of the function matches the first 2 'a',
only the trailing ' a' will be used to seek for further matching...
Cheers,
Marc Mamin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select regexp_matches('a a a', '([a-z]) a','g');
First: Please don't reply to an existing message to create a new thread.
Your mail client copies the replied-to message ID into the References:
header, and well-implemented mail clients will thread your message under
a now-unrelated thread.
Compose a new message instead.
Marc Mamin wrote:
I have a string that contains a serie of chars, separated by single
spaces.
e.g 'a b x n r a b c b'
Having such a string, I d'like to get a list of all predecessors of a
given character.
In the example, the predecessors of b are a,a,c.
OK, so wherever `b' occurs, you want the character at index `b -2'.
select regexp_matches('a a a', '([a-z]) a','g');
= {a } only
The issue is that regular expressions don't like to overlap matches. The
first match consumes _two_ leading `a' characters.
What you need is a zero-width lookahead assertion, available in
Perl-style extended regular expressions. Handily, recent PostgreSQL
versions support these, so you can write:
test= select regexp_matches( 'a a a', '([a-z]) (?=a)', 'g');
regexp_matches
{a}
{a}
(2 rows)
--
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] select count of all overlapping geometries and return 0 if none.
On Thursday 12 March 2009 19:28:19 Duffer Do wrote: I want to return the following: locations | number_visits Frankfurt | 6 Manhattan | 3 Talahassee | 0 My query only returns: Frankfurt | 6 Manhattan | 3 My query: SELECT count(user_name) as number_visits, location_name from locations, user_tracker WHERE user_geometry location_geometry I think something like this: SELECT count(user_name) as number_visits, location_name FROM locations LEFT JOIN user_tracker ON (user_geometry location_geometry) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select count of all overlapping geometries and return 0 if none.
Hello all, I have 2 tables locations and user_tracker: locations has 2 columns location_name location_geometry user_tracker has 3 columns user_name user_geometry user_timestamp locations table is coordinates and names of areas of interest. user_tracker basically is an archive of a user's movements as he pans his map. I have a need to assign a ranking of locations based on how many times users have intersected this location. The problem I am having is that my query only returns locations that have been intersected by a user. I need it to return ALL locations and a zero if this location has not been intersected. As an example: LOCATIONS 1: Talahassee, FL | talahassee's bounding box 2: Manhattan, NY | Manhattan's bounding box 3: Frankfurt, GE | Frankfurt's bounding box USER_TRACKER john doe | geometry that overlaps Frankfurt | today john doe | geometry that overlaps Frankfurt | today john doe | geometry that overlaps Frankfurt | today john doe | geometry that overlaps Frankfurt | yesterday john doe | geometry that overlaps Frankfurt | Monday john doe | geometry that overlaps Frankfurt | Sunday Mary Jane | geometry that overlaps Manhattan | today Rob Roy | geometry that overlaps Manhattan | today Rob Roy | geometry that overlaps Manhattan | today I want to return the following: locations | number_visits Frankfurt | 6 Manhattan | 3 Talahassee | 0 My query only returns: Frankfurt | 6 Manhattan | 3 Now I have really simplified this example for readability, my actual tables are more complex. How can I accomplish this? My query: SELECT count(user_name) as number_visits, location_name from locations, user_tracker WHERE user_geometry location_geometry Thanks in advance
Re: [SQL] SELECT multiple MAX(id)s ?
On Tuesday 14 October 2008 18:27:01 Fernando Hevia wrote: -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Aarni Ruuhimäki Enviado el: Viernes, 10 de Octubre de 2008 07:56 Para: pgsql-sql@postgresql.org Asunto: [SQL] SELECT multiple MAX(id)s ? Hello list, table diary_entry entry_id SERIAL PK d_entry_date_time timestamp without time zone d_entry_company_id integer d_entry_location_id integer d_entry_shift_id integer d_user_id integer d_entry_header text ... Get the last entries from companies and their locations? The last, i.e. the biggest entry_id holds also the latest date value within one company and its locations. One can not add an entry before the previuos one is 'closed'. Names for the companies, their different locations, or outlets if you like, users and shifts are stored in company, location, user and shift tables respectively. Again something I could do with a bunch of JOIN queries and loops + more LEFT JOIN queries within the output loops, but could this be done in a one single clever (sub select?) query? Output (php) should be something like: Date | User | Shift | Company | Location - 02.10.2008 | Bobby | Nightshift 1 | Company 1 | Location X 04.10.2008 | Brian | Dayshift 2 | Company 1 | Location Y 09.10.2008 | Jill | Dayshift 1 | Company 2 | Location A 05.10.2008 | Jane | Dayshift 1 | Company 2 | Location B 07.10.2008 | Frank | Dayshift 2 | Company 2 | Location C ... Someone please give me a start kick? TIA and have a nice weekend too! -- Aarni Burglars usually come in through your windows. Aarni, you should take a look at aggregate functions. Anyway, I think this is what you are asking for: select max(d.d_entry_date_time) as Date, u.name, s.shift, c.name, l.location_name from diary_entry d, company c, location l, user u, shift s where d.d_entry_company_id = c.company_id and d.d_entry_location_id = l.location_id and d.d_user_id = u.user_id and d.d_entry_shift_id = s.shift_id group by u.name, s.shift, c.name, l.location_name order by d.d_entry_date_time Cheers. Thanks Fernando! I will try this out. Although I already did it in the more clumsy way ... Very best regards, Aarni -- Burglars usually come in through your windows. -- 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] SELECT multiple MAX(id)s ?
-Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Aarni Ruuhimäki Enviado el: Viernes, 10 de Octubre de 2008 07:56 Para: pgsql-sql@postgresql.org Asunto: [SQL] SELECT multiple MAX(id)s ? Hello list, table diary_entry entry_id SERIAL PK d_entry_date_time timestamp without time zone d_entry_company_id integer d_entry_location_id integer d_entry_shift_id integer d_user_id integer d_entry_header text ... Get the last entries from companies and their locations? The last, i.e. the biggest entry_id holds also the latest date value within one company and its locations. One can not add an entry before the previuos one is 'closed'. Names for the companies, their different locations, or outlets if you like, users and shifts are stored in company, location, user and shift tables respectively. Again something I could do with a bunch of JOIN queries and loops + more LEFT JOIN queries within the output loops, but could this be done in a one single clever (sub select?) query? Output (php) should be something like: Date | User | Shift | Company | Location - 02.10.2008 | Bobby | Nightshift 1 | Company 1 | Location X 04.10.2008 | Brian | Dayshift 2 | Company 1 | Location Y 09.10.2008 | Jill | Dayshift 1 | Company 2 | Location A 05.10.2008 | Jane | Dayshift 1 | Company 2 | Location B 07.10.2008 | Frank | Dayshift 2 | Company 2 | Location C ... Someone please give me a start kick? TIA and have a nice weekend too! -- Aarni Burglars usually come in through your windows. Aarni, you should take a look at aggregate functions. Anyway, I think this is what you are asking for: select max(d.d_entry_date_time) as Date, u.name, s.shift, c.name, l.location_name from diary_entry d, company c, location l, user u, shift s where d.d_entry_company_id = c.company_id and d.d_entry_location_id = l.location_id and d.d_user_id = u.user_id and d.d_entry_shift_id = s.shift_id group by u.name, s.shift, c.name, l.location_name order by d.d_entry_date_time Cheers. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SELECT multiple MAX(id)s ?
Hello list, table diary_entry entry_id SERIAL PK d_entry_date_time timestamp without time zone d_entry_company_id integer d_entry_location_id integer d_entry_shift_id integer d_user_id integer d_entry_header text ... Get the last entries from companies and their locations? The last, i.e. the biggest entry_id holds also the latest date value within one company and its locations. One can not add an entry before the previuos one is 'closed'. Names for the companies, their different locations, or outlets if you like, users and shifts are stored in company, location, user and shift tables respectively. Again something I could do with a bunch of JOIN queries and loops + more LEFT JOIN queries within the output loops, but could this be done in a one single clever (sub select?) query? Output (php) should be something like: Date | User | Shift | Company | Location - 02.10.2008 | Bobby | Nightshift 1 | Company 1 | Location X 04.10.2008 | Brian | Dayshift 2 | Company 1 | Location Y 09.10.2008 | Jill | Dayshift 1 | Company 2 | Location A 05.10.2008 | Jane | Dayshift 1 | Company 2 | Location B 07.10.2008 | Frank | Dayshift 2 | Company 2 | Location C ... Someone please give me a start kick? TIA and have a nice weekend too! -- Aarni Burglars usually come in through your windows. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select function with set return type
Hi all, I am writing some functions with retrun type as a SETOF of a datatype that I have defined. How can I test them with a select statement. Doing select my_function(); return set valued function called in context that cannot accept a set Thanks to all Nacef
Re: [SQL] Select function with set return type
Nacef LABIDI [EMAIL PROTECTED] schrieb: Hi all, I am writing some functions with retrun type as a SETOF of a datatype that I have defined. How can I test them with a select statement. Doing select my_function(); return set valued function called in context that cannot accept a set Try 'select * from my_function();' Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Select default values
On 24/lug/08, at 23:15, Richard Broersma wrote: On Thu, Jul 24, 2008 at 12:35 PM, Giorgio Valoti [EMAIL PROTECTED] wrote: Um - there is no default value for a function. Without this feature you have to overload the function arguments. You could pass a casted null to the function. The would eliminate function overloading. Then internally you could handle the null by passing DEFAULTS to you INSERT or UPDATE statements. I don't know if this would work for you in this case. It could work but only if I use a domain that allows NULLs, which reduces the usefulness of domains even if you can work around that by simply stating the not null clause in the table definition. -- Giorgio Valoti -- 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] Select default values
On 23/lug/08, at 11:28, Pavel Stehule wrote: Hello 2008/7/23 Maximilian Tyrtania [EMAIL PROTECTED]: Hi there, just a quickie: Is there a way to select all default values of a given table? Something like Select Default values from sometable ? Unfortunately this syntax doesn't seem to be supported. I know i can select the default values for each column, but being able to select them in one go would be handy... it's not possible directly, you can find expressions used as default in system tables or postgres=# create table f(a integer default 1, b integer); CREATE TABLE postgres=# insert into f(a,b) values(default, default) returning *; It seems that you can’t use the same syntax with function calls: select function(default,default); gives a syntax error. Is it expected? -- Giorgio Valoti -- 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] Select default values
Giorgio Valoti wrote: postgres=# insert into f(a,b) values(default, default) returning *; It seems that you can’t use the same syntax with function calls: select function(default,default); gives a syntax error. Is it expected? Um - there is no default value for a function. -- Richard Huxton Archonet Ltd -- 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] Select default values
On 24/lug/08, at 12:42, Richard Huxton wrote: Giorgio Valoti wrote: postgres=# insert into f(a,b) values(default, default) returning *; It seems that you can’t use the same syntax with function calls: select function(default,default); gives a syntax error. Is it expected? Um - there is no default value for a function. Yes, but you could define a domain with a default value and using it as an IN argument for a function. In that case it would handy to be able to use the default value, wouldn’t it? Without this feature you have to overload the function arguments. -- Giorgio Valoti -- 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] Select default values
On Thu, Jul 24, 2008 at 12:35 PM, Giorgio Valoti [EMAIL PROTECTED] wrote: Um - there is no default value for a function. Without this feature you have to overload the function arguments. You could pass a casted null to the function. The would eliminate function overloading. Then internally you could handle the null by passing DEFAULTS to you INSERT or UPDATE statements. I don't know if this would work for you in this case. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select default values
Hi there, just a quickie: Is there a way to select all default values of a given table? Something like Select Default values from sometable ? Unfortunately this syntax doesn't seem to be supported. I know i can select the default values for each column, but being able to select them in one go would be handy... tia, Maximilian Tyrtania -- 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] Select default values
am Wed, dem 23.07.2008, um 10:32:58 +0200 mailte Maximilian Tyrtania folgendes: Hi there, just a quickie: Is there a way to select all default values of a given table? Something like Select Default values from sometable ? Unfortunately this syntax doesn't seem to be supported. I know i can select the default values for each column, but being able to select them in one go would be handy... test=# create table t_with_defaults( s1 int default 1, s2 int default 2); CREATE TABLE test=*# select ordinal_position, column_name, column_default from information_schema.columns where table_name='t_with_defaults' order by 1; ordinal_position | column_name | column_default --+-+ 1 | s1 | 1 2 | s2 | 2 (2 rows) HTH, 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
Re: [SQL] Select default values
Hi again, Hi there, just a quickie: Is there a way to select all default values of a given table? Something like Select Default values from sometable ? it's not possible directly, you can find expressions used as default in system tables or postgres=# create table f(a integer default 1, b integer); CREATE TABLE postgres=# insert into f(a,b) values(default, default) returning *; a | b ---+--- 1 | (1 row) INSERT 0 1 regards Pavel Stehule ah, I see, smart, the problem with this is just that I don't actually want to insert the record just yet. I just want to hand the default values over to my app. Well, of course my app could still delete the record later on, but still that seems cumbersome.. Thanks a lot and best wishes from Berlin, Maximilian Tyrtania -- 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] Select default values
Hi,
am Wed, dem 23.07.2008, um 10:32:58 +0200 mailte Maximilian Tyrtania
folgendes:
Hi there, just a quickie: Is there a way to select all default values of a
given table? Something like Select Default values from sometable ?
test=# create table t_with_defaults( s1 int default 1, s2 int default 2);
CREATE TABLE
test=*# select ordinal_position, column_name, column_default from
information_schema.columns where table_name='t_with_defaults' order by 1;
ordinal_position | column_name | column_default
--+-+
1 | s1 | 1
2 | s2 | 2
(2 rows)
This is probably what I should do, the only problem is that the output of
the given query looks a lot less nice when the default looks like this
nextval('mitarbeiter_serial'::regclass)
I'd prefer to just receive the actual value of that function. Okay, I could
just execute that statement, but, hmm, still, that seems akward.
Thanks and best wishes from Berlin
Maximilian Tyrtania
--
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] Select default values
2008/7/23 Maximilian Tyrtania [EMAIL PROTECTED]: Hi again, Hi there, just a quickie: Is there a way to select all default values of a given table? Something like Select Default values from sometable ? it's not possible directly, you can find expressions used as default in system tables or postgres=# create table f(a integer default 1, b integer); CREATE TABLE postgres=# insert into f(a,b) values(default, default) returning *; a | b ---+--- 1 | (1 row) INSERT 0 1 regards Pavel Stehule ah, I see, smart, the problem with this is just that I don't actually want to insert the record just yet. I just want to hand the default values over to my app. Well, of course my app could still delete the record later on, but still that seems cumbersome.. begin insert ... rollback; it's not best solution, but it just works. regards Pavel Thanks a lot and best wishes from Berlin, Maximilian Tyrtania -- 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] Select default values
Hi, begin insert ... rollback; it's not best solution, but it just works. Ah, yes, of course, haven't thought of that. Okay, here is one final (i hope) obstacle. My db has 200 tables and I'd love to be able to write some function that would just take a tablename and return the default values for a new record of that table. If Select default values from sometable was supported than that would be a piece of cake (I'd just do: Execute Select default values from '||sometable||' into somerecord in a plpgsql function). With your way (insert into f(a,b) values(default, default) returning *) i need to know everything about the given table. Hmm. Any ideas? Best, Maximilian Tyrtania -- 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] Select default values
On Wed, Jul 23, 2008 at 12:35:08PM +0200, Maximilian Tyrtania wrote: With your way (insert into f(a,b) values(default, default) returning *) i need to know everything about the given table. Hmm. Any ideas? Do look at the information schema. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Select default values
2008/7/23 Maximilian Tyrtania [EMAIL PROTECTED]:
Hi,
begin
insert ...
rollback;
it's not best solution, but it just works.
Ah, yes, of course, haven't thought of that.
Okay, here is one final (i hope) obstacle. My db has 200 tables and I'd
love to be able to write some function that would just take a tablename and
return the default values for a new record of that table. If Select default
values from sometable was supported than that would be a piece of cake (I'd
just do: Execute Select default values from '||sometable||' into
somerecord in a plpgsql function).
that is out of SQL principles :(. And you cannot have functions that
returns different number of columns - your function, can return array
or table
CREATE OR REPLACE FUNCTION defaults(text, OUT attname name, OUT type
varchar, OUT default_val varchar)
RETURNS SETOF RECORD AS $$
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT eval(pg_catalog.pg_get_expr(d.adbin, d.adrelid))
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = $1::regclass::oid AND a.attnum 0 AND NOT a.attisdropped
ORDER BY a.attnum
$$ LANGUAGE SQL STRICT;
postgres=# \d fg
Table public.fg
Column | Type | Modifiers
+--+-
t | date | default ('now'::text)::date
postgres=# \d f
Table public.f
Column | Type | Modifiers
+-+---
a | integer | default 1
b | integer |
postgres=# select * from defaults('fg');
attname | type | default_val
-+--+-
t | date | 2008-07-23
(1 row)
postgres=# select * from defaults('f');
attname | type | default_val
-+-+-
a | integer | 1
b | integer |
(2 rows)
regards
Pavel Stehule
create or replace function eval(varchar) returns varchar as $$
declare result varchar;
begin
execute 'SELECT ' || $1 into result;
return result;
end;$$ language plpgsql strict;
With your way (insert into f(a,b) values(default, default) returning *) i
need to know everything about the given table.
Hmm. Any ideas?
Best,
Maximilian Tyrtania
--
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] Select default values
Pavel,
fantastic, that's exactly what I wanted, thank you very much!
Maximilian Tyrtania
Von: Pavel Stehule [EMAIL PROTECTED]
Okay, here is one final (i hope) obstacle. My db has 200 tables and I'd
love to be able to write some function that would just take a tablename and
return the default values for a new record of that table. If Select default
values from sometable was supported than that would be a piece of cake (I'd
just do: Execute Select default values from '||sometable||' into
somerecord in a plpgsql function).
that is out of SQL principles :(. And you cannot have functions that
returns different number of columns - your function, can return array
or table
CREATE OR REPLACE FUNCTION defaults(text, OUT attname name, OUT type
varchar, OUT default_val varchar)
RETURNS SETOF RECORD AS $$
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT eval(pg_catalog.pg_get_expr(d.adbin, d.adrelid))
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = $1::regclass::oid AND a.attnum 0 AND NOT a.attisdropped
ORDER BY a.attnum
$$ LANGUAGE SQL STRICT;
postgres=# \d fg
Table public.fg
Column | Type | Modifiers
+--+-
t | date | default ('now'::text)::date
postgres=# \d f
Table public.f
Column | Type | Modifiers
+-+---
a | integer | default 1
b | integer |
postgres=# select * from defaults('fg');
attname | type | default_val
-+--+-
t | date | 2008-07-23
(1 row)
postgres=# select * from defaults('f');
attname | type | default_val
-+-+-
a | integer | 1
b | integer |
(2 rows)
regards
Pavel Stehule
create or replace function eval(varchar) returns varchar as $$
declare result varchar;
begin
execute 'SELECT ' || $1 into result;
return result;
end;$$ language plpgsql strict;
--
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] Select default values
On Wed, Jul 23, 2008 at 3:57 AM, Maximilian Tyrtania
[EMAIL PROTECTED] wrote:
Hi,
am Wed, dem 23.07.2008, um 10:32:58 +0200 mailte Maximilian Tyrtania
folgendes:
Hi there, just a quickie: Is there a way to select all default values of a
given table? Something like Select Default values from sometable ?
test=# create table t_with_defaults( s1 int default 1, s2 int default 2);
CREATE TABLE
test=*# select ordinal_position, column_name, column_default from
information_schema.columns where table_name='t_with_defaults' order by 1;
ordinal_position | column_name | column_default
--+-+
1 | s1 | 1
2 | s2 | 2
(2 rows)
This is probably what I should do, the only problem is that the output of
the given query looks a lot less nice when the default looks like this
nextval('mitarbeiter_serial'::regclass)
I'd prefer to just receive the actual value of that function. Okay, I could
just execute that statement, but, hmm, still, that seems akward.
Until you run that function, you don't know what the output might be
because of possible race condtitions.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select across two database
hi guys. I want know if it's possible create a select from 2 database or create a view in one of them. -- Jorge Andrés Medina Oliva. Systems Manager and Developer. BSDCHiLE. -- 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] select across two database
On 17/06/2008, Jorge Medina [EMAIL PROTECTED] wrote: hi guys. I want know if it's possible create a select from 2 database or create a view in one of them. The short answer is no. -- 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] select across two database
Helo
it is possible if you don't mind some work :)
We are doing it with plproxy.
Simple scenario would be
1. install plproxy
2. create sql functon with needed sql in remote db
3. create plproxy function in current db
4. create sql or function that combines the results from data in current db
and plproxy function
regards,
Asko
skype: askoja
[EMAIL PROTECTED] ~$ createdb oltpdb
CREATE DATABASE
[EMAIL PROTECTED] ~$ createdb archdb
CREATE DATABASE
[EMAIL PROTECTED] ~$ psql oltpdb
/usr/share/postgresql/8.2/contrib/plproxy.sql
CREATE FUNCTION
CREATE LANGUAGE
archdb=# create table archive ( data text );
CREATE TABLE
archdb=# insert into archive values ('archive row 1');
INSERT 0 1
archdb=# insert into archive values ('archive row 2');
INSERT 0 1
archdb=# insert into archive values ('archive row 3');
INSERT 0 1
archdb=# create function get_archive_data() returns setof text as $$ select
data from archive; $$ language sql;
CREATE FUNCTION
oltpdb=# create table online ( data text );
CREATE TABLE
oltpdb=# insert into online values ('online row');
INSERT 0 1
oltpdb=# create function get_archive_data() returns setof text as $$ connect
'dbname=archdb'; $$ language plproxy;
CREATE FUNCTION
oltpdb=# create view all_data as select data from online union all select
get_archive_data as data from get_archive_data();
CREATE VIEW
oltpdb=# select * from all_data;
data
---
online row
archive row 1
archive row 2
archive row 3
(4 rows)
On Tue, Jun 17, 2008 at 12:55 AM, Andrej Ricnik-Bay [EMAIL PROTECTED]
wrote:
On 17/06/2008, Jorge Medina [EMAIL PROTECTED] wrote:
hi guys.
I want know if it's possible create a select from 2 database or create
a view in one of them.
The short answer is no.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select into
Hi Everyone, I have asked our DBA at work and h is not too sure either... so I thought it best to on the list. Basically, what I am after is a way to copy the contents of one record into another. Something like select into; but where the destination record already exists, as opposed to creating a new record. Thanks in advance for anything you might come up with. Warmest regards, Gavin Baumanis -- 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] Select into
am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis folgendes: Hi Everyone, I have asked our DBA at work and h is not too sure either... so I thought it best to on the list. Basically, what I am after is a way to copy the contents of one record into another. Something like select into; but where the destination record already exists, as opposed to creating a new record. insert into table select from table where ... 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
Re: [SQL] Select into
On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer [EMAIL PROTECTED] wrote: am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis folgendes: Hi Everyone, I have asked our DBA at work and h is not too sure either... so I thought it best to on the list. Basically, what I am after is a way to copy the contents of one record into another. Something like select into; but where the destination record already exists, as opposed to creating a new record. insert into table select from table where ... He specifically asked for snip where the destination record already exists, as opposed to creating a new record. /snip I think an UPDATE with joins would be helpful. Though, it may become lengthy if the tables have too many columns. Can you post your exact requirement? -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad * 18° 32' 57.25N, 73° 56' 25.42E - Pune 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [SQL] Select into
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. BEGIN; CREATE TEMP TABLE tempfoo AS SELECT * FROM foo WHERE id = 123; UPDATE tempfoo SET id = 456; DELETE FROM foo WHERE id = 456; INSERT INTO foo SELECT * FROM tempfoo; COMMIT; - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200803200737 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfiTIYACgkQvJuQZxSWSsiCMwCdESkEe8Hc5xHhJ2B3qX3V7EqX Z2IAoMy65D2OhdUpYVtfEq182PhfsEfZ =fx5V -END PGP SIGNATURE- -- 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] Select into
On Thu, Mar 20, 2008 at 4:39 PM, Gavin 'Beau' Baumanis [EMAIL PROTECTED] wrote: HI Gurjeet, You're right. But what information do you need to know? The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. I am happy enough to give you a table schema, if that's required... but I just don't see why it would be needed - but of course am happy to be told something new! Even a small example of what you wanted would have worked. Anyway, lets see if I can got your problem. There are two records in your table emp: id | name | salary - 21 | scott | 2000 31 | greg | 3000 So you want to copy all the data from 'scott' row on to 'greg' row, but keep the id (id obviously being your unique identifier). UPDATE emp SET (salary, name) = ( (select salary from emp where id = 21 ), (select name from emp where id = 21) ) where id = 31; HTH, Best regards, Thanks again Gavin Baumanis On 20/03/2008, at 9:58 PM, Gurjeet Singh wrote: On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer [EMAIL PROTECTED] wrote: am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis folgendes: Hi Everyone, I have asked our DBA at work and h is not too sure either... so I thought it best to on the list. Basically, what I am after is a way to copy the contents of one record into another. Something like select into; but where the destination record already exists, as opposed to creating a new record. insert into table select from table where ... He specifically asked for snip where the destination record already exists, as opposed to creating a new record. /snip I think an UPDATE with joins would be helpful. Though, it may become lengthy if the tables have too many columns. Can you post your exact requirement? -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad * 18° 32' 57.25N, 73° 56' 25.42E - Pune 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad * 18° 32' 57.25N, 73° 56' 25.42E - Pune 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [SQL] Select into
Hi Everyone, I want to thank everyone for their help / suggestions... I really appreciate it. Though I think I have found a winner. craig=# update x set val = foundrow.val from ( select val from x where id = 2 ) as foundrow where id = 1 ; UPDATE 1 Very elegant, very clean... Very nice! 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] Select into
craig=# update x set val = foundrow.val from ( select val from x where id = 2 ) as foundrow where id = 1 ; UPDATE 1 Thinking about it, it'd actually be better written as: UPDATE x SET val = foundrow.val FROM ( SELECT val FROM x AS x2 WHERE x2.id = 2 ) AS foundrow WHERE id = 1; ... because it's nicer to use a table alias for x within the subquery and elimate any ambiguity for the reader about which id you're referring to. After all, it's also valid to reference the id field of the x outside the subquery within it, like in the following valid but rather nonsensical query: UPDATE x SET val = (SELECT id+1) WHERE id = 1; Using the table alias will not change the query plan at all, it just makes the reference to id within the subquery unambiguous to the reader. Sorry for the repeat post. -- 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] Select into
Gavin 'Beau' Baumanis wrote: The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. I think what you want is something like this: Given (col1 being the id or PK): col1 | col2 | col3 --+--+--- 1 | 123 | first record 2 | 456 | second record 3 | 789 | third record then update t1 set col2 = t1copy.col2, col3 = t1copy.col3 from t1 as t1copy where t1.col1 = 1 and t1copy.col1 = 3; will result in: col1 | col2 | col3 --+--+--- 1 | 789 | third record 2 | 456 | second record 3 | 789 | third record So, it is a join ... of a table with a virtual copy of itself. Joe -- 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] Select into
On Thu, Mar 20, 2008 at 5:40 PM, Joe [EMAIL PROTECTED] wrote: Gavin 'Beau' Baumanis wrote: The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. I think what you want is something like this: Given (col1 being the id or PK): col1 | col2 | col3 --+--+--- 1 | 123 | first record 2 | 456 | second record 3 | 789 | third record then update t1 set col2 = t1copy.col2, col3 = t1copy.col3 from t1 as t1copy where t1.col1 = 1 and t1copy.col1 = 3; will result in: col1 | col2 | col3 --+--+--- 1 | 789 | third record 2 | 456 | second record 3 | 789 | third record So, it is a join ... of a table with a virtual copy of itself. Except that it doesn't work... Did you try to execute that query; I am assuming not. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad * 18° 32' 57.25N, 73° 56' 25.42E - Pune 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [SQL] Select into
Gurjeet Singh wrote: Except that it doesn't work... Did you try to execute that query; I am assuming not. It does, or at least a query written to work the same way works fine for me. Not only that, but at least in the presence of a unique index the query planner optimises it to the same query plan as the one I proposed. From my earlier test data: craig=# update x set val = x2.val from x as x2 where x.id = 1000 and x2.id = 1024; UPDATE 1 craig=# select * from x where id in (1000,1024); id | val --+-- 1024 | 1021 1000 | 1021 (2 rows) craig=# explain update x set val = x2.val from x as x2 where x.id = 1000 and x2.id = 1024; QUERY PLAN --- Nested Loop (cost=0.00..16.55 rows=1 width=14) - Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=10) Index Cond: (id = 1000) - Index Scan using x_id_idx on x x2 (cost=0.00..8.27 rows=1 width=4) Index Cond: (x2.id = 1024) (5 rows) The above query actually executes slightly faster, presumably because the query planner has to do less work to reach the same point than it does with the subquery-based one I proposed. You should probably use this one instead of the subquery one. -- 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] Select into
Gurjeet Singh wrote: Except that it doesn't work... Did you try to execute that query; I am assuming not. Of course I did, do you think I create results by editing them into my email? The script: delete from t1; insert into t1 values (1, 123, 'first record'); insert into t1 values (2, 456, 'second record'); insert into t1 values (3, 789, 'third record'); select * from t1; update t1 set col2 = t1copy.col2, col3 = t1copy.col3 from t1 as t1copy where t1.col1 = 1 and t1copy.col1 = 3; select * from t1; select version(); The output: DELETE 3 INSERT 0 1 INSERT 0 1 INSERT 0 1 col1 | col2 | col3 --+--+--- 1 | 123 | first record 2 | 456 | second record 3 | 789 | third record (3 rows) UPDATE 1 col1 | col2 | col3 --+--+--- 2 | 456 | second record 3 | 789 | third record 1 | 789 | third record (3 rows) version PostgreSQL 8.1.9 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3 (1 row) And BTW, I also tried your UPDATE SET (salary, name) but that only works on PG 8.2 and above. I don't see why my query would fail in subsequent releases. Joe -- 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] Select into
On Mar 20, 2008, at 7:10 AM, Joe wrote: Gavin 'Beau' Baumanis wrote: The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. I think what you want is something like this: Given (col1 being the id or PK): col1 | col2 | col3 --+--+--- 1 | 123 | first record 2 | 456 | second record 3 | 789 | third record then update t1 set col2 = t1copy.col2, col3 = t1copy.col3 from t1 as t1copy where t1.col1 = 1 and t1copy.col1 = 3; will result in: col1 | col2 | col3 --+--+--- 1 | 789 | third record 2 | 456 | second record 3 | 789 | third record So, it is a join ... of a table with a virtual copy of itself. Note that in 8.2.x and above you can write that as: update t1 set (col2, col3) = (t1copy.col2, t1copy.col3) from t1 as t1copy where t1.col =1 and t1copy.col1=3; Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SELECT DISTINCT
Hello I have 2 records with the same value. Even when i do a select with DISTINCT, it returns me both the records instead of one. SELECT DISTINCT ON (TE_COGNOME) TE_COGNOME, TE_NOME, N_GEN_TEST, TE_SESSO, TE_ATTNASC, TE_LUONASC, TE_INDI, TE_DTNASC, TE_PROVSTATO, TE_PROV, TE_PATERNITA, TE_RICHIESTA FROM MOD48_02 WHERE TE_COGNOME LIKE 'WIJ%' AND TRIM(DELETED) IS NULL ORDER BY TE_COGNOME, N_GEN_TEST DESC; What should I do?? Thanks Shavonne
Re: [SQL] SELECT DISTINCT
Shavonne Marietta Wijesinghe wrote: Hello I have 2 records with the same value. Even when i do a select with DISTINCT, it returns me both the records instead of one. SELECT DISTINCT ON (TE_COGNOME) TE_COGNOME, TE_NOME, N_GEN_TEST, TE_SESSO, TE_ATTNASC, TE_LUONASC, TE_INDI, TE_DTNASC, TE_PROVSTATO, TE_PROV, TE_PATERNITA, TE_RICHIESTA FROM MOD48_02 WHERE TE_COGNOME LIKE 'WIJ%' AND TRIM(DELETED) IS NULL ORDER BY TE_COGNOME, N_GEN_TEST DESC; Can you provide: 1. The output of your query (just the duplicate rows will be fine) 2. The output of \d MOD48_02? 3. The output of: SELECT xmin,xmax, ':' || TE_COGNOME || ':' FROM MOD48_02 WHERE TE_COGNOME = ??? For the duplicate value of course -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
