[SQL] Set generating functions and subqueries
Hello, Today, I stumbled across the following: postgres=# select generate_series(1,2),'othercol'; generate_series | ?column? -+-- 1 | othercol 2 | othercol (2 rows) postgres=# select (select generate_series(1,2)),'othercol'; ERROR: more than one row returned by a subquery used as an expression So it seems that set-returning functions "blow up" the resultset by duplicating rows - so why is this not allowed for subqueries? It is easy to refactor a subquery into a set-returning function, so I think this violates the principle of orthogonality. But there may be subtle reasons of ambiguity here I don't see right now. (I know that usually, a JOIN would be the right thing to do here, but I'm just curious why multi-row subqueries are not allowed.) Btw, having several set-returning functions with equal or different set lengths produce interesting results: postgres=# select generate_series(1,2),generate_series(3,4),'othercol'; generate_series | generate_series | ?column? -+-+-- 1 | 3 | othercol 2 | 4 | othercol (2 rows) postgres=# select generate_series(1,2),generate_series(3,5),'othercol'; generate_series | generate_series | ?column? -+-+-- 1 | 3 | othercol 2 | 4 | othercol 1 | 5 | othercol 2 | 3 | othercol 1 | 4 | othercol 2 | 5 | othercol (6 rows) Is there any way to indicate that I want the cross-product if both set-returning functions have the same length? This could lead to strange effects if the sets have varying lengths otherwhise. (One workaround would be to join two selects, each one having one set-returning function.) Btw, it is not possible to trick PostgreSQL into accepting multi-row selects this way: postgres=# select (select generate_series(1,2)),generate_series(3,4),''; ERROR: more than one row returned by a subquery used as an expression Have fun, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL]
Hi, Klay, Klay Martens wrote: > I am really battling to figure out how to do the same in a postgres > function. http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS could be helpful. HTH Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg reserved words
Hi, Irina, Irina Gadassik wrote: > I am trying to create a table freeze and it seems "freeze" is a reserved > word, but I don't see it in > the list. Also in a different table I cann't create a column freeze. > However it is possible in MySQL and Ingres. It is a PostgreSQL specific reserved word, see http://www.postgresql.org/docs/current/interactive/sql-keywords-appendix.html However, it is possible to use it quoted: postgres=# create table freeze (foo int); ERROR: syntax error at or near "freeze" at character 14 LINE 1: create table freeze (foo int); ^ postgres=# create table "freeze" (foo int); CREATE TABLE postgres=# select * from "freeze"; foo - (0 rows) postgres=# DROP TABLE "freeze" ; DROP TABLE HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] dbLink Query
Hi, Richard, Richard C wrote: > How do I install or setup the function dbLink so that I can use this > feature. In your PostgreSQL installation, there should be a contrib directory, that contains a file dblink.sql - execute it via psql against your database, and it installs the function definitions. On my debian machine, it is under: /usr/share/postgresql/7.4/contrib/dblink.sql /usr/share/postgresql/8.1/contrib/dblink.sql HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] How can I selet rows which have 2 columns values cross equal?
Hi All: I have a table like this: Table test Id | a | b -++--- 1| 100| 101 2| 101| 100 3| 100| 3 4| 20 | 30 5| 11 | 13 6| 3 | 33 7| 30 | 20 I want to get row 1, 2,4 and 7 selected. Because their values of a and b are cross equal. i.e., for each pair of rows, a.Row1 = b.Row2 and b.Ro1 = a.Row2 a.Row4 = b.Row7 and b.Ro4 = a.Row7 How can I construct a sql statement to select them? Thanks in advance. Fay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How can I selet rows which have 2 columns values cross equal?
On Friday 10 March 2006 14:24, Fay Du wrote: > Table test > Id | a | b > -++--- > 1 | 100| 101 > 2 | 101| 100 > 3 | 100| 3 > 4 | 20 | 30 > 5 | 11 | 13 > 6 | 3 | 33 > 7 | 30 | 20 > select a.* from test a, test b where a.a = b.b and a.b = b.a order by id Regards, Milorad Poluga -- --- Milorad Poluga HK CORES Beograd, Makenzijeva 31 [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How can I selet rows which have 2 columns values cross equal?
What are your conditions on a and b ? Can a be equal to b on a row ? If
so, do you want this row ?
If you want to avoid duplicates, I suggest first removing them, then
adding a constraint CHECK( aapplication (or in an ON INSERT trigger), swap a and b if a>b.
I added some values to your table for completeness :
SELECT * FROM test;
a | b | id
-+-+
100 | 101 | 1
101 | 100 | 2
100 | 3 | 3
20 | 30 | 4
11 | 13 | 5
3 | 33 | 6
30 | 20 | 7
666 | 666 | 8
666 | 666 | 9
500 | 666 | 10
666 | 500 | 11
123 | 123 | 12
456 | 789 | 13
456 | 789 | 14
Try :
SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a;
xid | yid | xa | xb | ya | yb
-+-+-+-+-+-
1 | 2 | 100 | 101 | 101 | 100
2 | 1 | 101 | 100 | 100 | 101
4 | 7 | 20 | 30 | 30 | 20
7 | 4 | 30 | 20 | 20 | 30
8 | 9 | 666 | 666 | 666 | 666
8 | 8 | 666 | 666 | 666 | 666
9 | 9 | 666 | 666 | 666 | 666
9 | 8 | 666 | 666 | 666 | 666
10 | 11 | 500 | 666 | 666 | 500
11 | 10 | 666 | 500 | 500 | 666
12 | 12 | 123 | 123 | 123 | 123
You'll get 2 rows for each match. You can add a condition to remove the
dupes :
SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a AND x.id<=y.id;
xid | yid | xa | xb | ya | yb
-+-+-+-+-+-
1 | 2 | 100 | 101 | 101 | 100
4 | 7 | 20 | 30 | 30 | 20
8 | 9 | 666 | 666 | 666 | 666
8 | 8 | 666 | 666 | 666 | 666
9 | 9 | 666 | 666 | 666 | 666
10 | 11 | 500 | 666 | 666 | 500
12 | 12 | 123 | 123 | 123 | 123
If you don't want the rows with a=b, replace x.id<=y.id with x.idSELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a AND x.id
xid | yid | xa | xb | ya | yb
-+-+-+-+-+-
1 | 2 | 100 | 101 | 101 | 100
4 | 7 | 20 | 30 | 30 | 20
8 | 9 | 666 | 666 | 666 | 666
10 | 11 | 500 | 666 | 666 | 500
It is going to be slow, though. Basically a full self join. Let's hack
this :
CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
I'll leave this one as an exercice to the reader ;)))
SELECT array_accum(id), CASE WHEN aasum(sign(a-b)) = 0 AND count(*)>1;
array_accum | low | high
-+-+--
{10,11} | 500 | 666
{4,7} | 20 | 30
{1,2} | 100 | 101
{8,9} | 666 | 666
On Fri, 10 Mar 2006 14:24:44 +0100, Fay Du <[EMAIL PROTECTED]> wrote:
Hi All:
I have a table like this:
Table test
Id | a | b
-++---
1| 100| 101
2| 101| 100
3| 100| 3
4| 20 | 30
5| 11 | 13
6| 3 | 33
7| 30 | 20
I want to get row 1, 2,4 and 7 selected. Because their values of a and b
are cross equal. i.e., for each pair of rows,
a.Row1 = b.Row2 and b.Ro1 = a.Row2
a.Row4 = b.Row7 and b.Ro4 = a.Row7
How can I construct a sql statement to select them?
Thanks in advance.
Fay
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Set generating functions and subqueries
Markus Schaber <[EMAIL PROTECTED]> writes: > postgres=# select (select generate_series(1,2)),'othercol'; > ERROR: more than one row returned by a subquery used as an expression > So it seems that set-returning functions "blow up" the resultset by > duplicating rows - so why is this not allowed for subqueries? The behavior of the subquery expression is dictated by the SQL spec: 1) If the cardinality of a or a is greater than 1, then an exception condition is raised: cardinal- ity violation. The fact that the other form is even allowed is more of a holdover from PostQUEL than something we have consciously decided is a good idea. (IMHO it's actually a fairly *bad* idea, because it does not work nicely when there's more than one SRF in the same targetlist.) It'll probably get phased out someday, if we can find a way to replace the functionality. I seem to recall speculating that SQL2003's LATERAL tables might do the job. > Btw, having several set-returning functions with equal or different set > lengths produce interesting results: No kidding. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How can I selet rows which have 2 columns values cross equal?
On Fri, Mar 10, 2006 at 08:24:44 -0500, Fay Du <[EMAIL PROTECTED]> wrote: > Hi All: > I have a table like this: > >Table test > Id | a | b > -++--- > 1| 100| 101 > 2| 101| 100 > 3| 100| 3 > 4| 20 | 30 > 5| 11 | 13 > 6| 3 | 33 > 7| 30 | 20 > > I want to get row 1, 2,4 and 7 selected. Because their values of a and b > are cross equal. i.e., for each pair of rows, > a.Row1 = b.Row2 and b.Ro1 = a.Row2 > a.Row4 = b.Row7 and b.Ro4 = a.Row7 > > How can I construct a sql statement to select them? > Thanks in advance. Self join the table. Something like (untested): SELECT t1.id, t1.a, t1.b FROM test t1, test t2 WHERE t1.a = t2.b AND t1.b = t2.a ORDER BY t1.id ; ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Locking row
Hi all, I need to mark a row with a value in a column, but first i need to select the first row without this mark. But in some concurrents cases i mark the row twice. How can i lock the row to avoid others session get it? TABLE TICKET TICKET_NUMBER | MARK 1 |1 2 |0 I need to select the first row with 0 in MARK column and then mark it with 1. regards, Flavio Suguimoto ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Locking row
Flavio Suguimoto wrote: > Hi all, > > I need to mark a row with a value in a column, but first i need to > select the first row without this mark. But in some concurrents cases > i mark the row twice. How can i lock the row to avoid others session > get it? > > TABLE TICKET > TICKET_NUMBER | MARK > 1 |1 > 2 |0 > > I need to select the first row with 0 in MARK column and then mark it > with 1. look at the FOR UPDATE in the select docs. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Advice on setting up a grid like view for spreadsheet users
All; I have a need to create a view. The data table does not exist yet, the question is on how to best set it up in postgres. The resulting view has to be spreadsheet-like, and will be loaded directly via ODBC into Excel for number crunching. Maybe something like: Report Type #1 Date Location 1 Location 2 Location 3 Location 4 Jan 2006 5 77 23 233 Feb 2006 7 556 233 269 March 2006 8 5666 1024 100 I could mirror that exact structure in a SQL table, but I'd loose the relation (since the column headings actually refer to an associated table). Now if I were just working in pure SQL, I'd build it in a relational manner, with a long series of rows: report_type date xx_location value 1 Jan 2006 2 77 1 Feb 2006 2 556 3 Jan 2006 4 99.5 1 Jan 2006 1 5 3 Jan 2006 4 3.14159 It would be relational, but I don't know how to convert it to a sorted and grid-like "view", without a (perl) script. Any thoughts on this? One thought is to swap the rows and columns. But then I have dates as the column names, which seems inelegant.
[SQL] READ COMMITTE without START TRANSACTION?
Hello, I need to run some SELECT queries that take a while (10+ minutes) to complete, and I'm wondering about the isolation about the results I get. More precisely, while my SELECT is running, the DB is being updated by another application, and I am wondering which, if any, data changes my SELECT will see. Example: If I start my SELECT at 10:00, and it finishes at 10:10, will my results include data that was inserted between 10:00 and 10:10? Similarly, will my result include data that was updated between 10:00 and 10:10? The same question for data that was deleted during that period. If it matters, my SELECT runs from psql client, while concurrent inserts, updates, and deletes are executed from a separate application (webapp). For my purposes in this case I need the SELECT to get the results that represent data right at the beginning of the query - a snapshot. I read this: http://www.postgresql.org/docs/8.1/static/transaction-iso.html and it looks like this is the default PG behaviour (READ COMMITTED) Question: If I do not explicitly START TRANSACTION before the SELECT, will this READ COMMITTED XA behaviour still be in effect? Thanks, Otis ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] READ COMMITTE without START TRANSACTION?
On 3/10/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hello, > > I need to run some SELECT queries that take a while (10+ minutes) to > complete, and I'm wondering about the isolation about the results I get. > More precisely, while my SELECT is running, the DB is being updated by > another application, and I am wondering which, if any, data changes my SELECT > will see. > > Example: > If I start my SELECT at 10:00, and it finishes at 10:10, will my results > include data that was inserted between 10:00 and 10:10? > Similarly, will my result include data that was updated between 10:00 and > 10:10? > The same question for data that was deleted during that period. > no > If it matters, my SELECT runs from psql client, while concurrent inserts, > updates, and deletes are executed from a separate application (webapp). > doesn't really matters > For my purposes in this case I need the SELECT to get the results that > represent data right at the beginning of the query - a snapshot. I read > this: http://www.postgresql.org/docs/8.1/static/transaction-iso.html and it > looks like this is the default PG behaviour (READ COMMITTED) > yes, it is > Question: > If I do not explicitly START TRANSACTION before the SELECT, will this READ > COMMITTED XA behaviour still be in effect? > yes. all statements not executed inside a transaction block are in an implicit transaction > Thanks, > Otis > > -- regards, Jaime Casanova "What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast." Randal L. Schwartz ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Locking row
On Fri, Mar 10, 2006 at 02:57:51PM -0600, Larry Rosenman wrote: > Flavio Suguimoto wrote: > > I need to mark a row with a value in a column, but first i need to > > select the first row without this mark. But in some concurrents cases > > i mark the row twice. How can i lock the row to avoid others session > > get it? > > > > TABLE TICKET > > TICKET_NUMBER | MARK > > 1 |1 > > 2 |0 > > > > I need to select the first row with 0 in MARK column and then mark it > > with 1. > > look at the FOR UPDATE in the select docs. If you use FOR UPDATE with LIMIT then see the following in the SELECT documentation: It is possible for a SELECT command using both LIMIT and FOR UPDATE/SHARE clauses to return fewer rows than specified by LIMIT. This is because LIMIT is applied first. The command selects the specified number of rows, but might then block trying to obtain lock on one or more of them. Once the SELECT unblocks, the row might have been deleted or updated so that it does not meet the query WHERE condition anymore, in which case it will not be returned. For example, suppose you have the following data: ticket_number | mark ---+-- 1 |1 2 |0 3 |0 (3 rows) Two concurrent transactions, T1 and T2, both run the following query: SELECT * FROM ticket WHERE mark = 0 LIMIT 1 FOR UPDATE; One transaction, say T1, will get the following row: ticket_number | mark ---+-- 2 |0 (1 row) T2 will block until T1 ends. If T1 rolls back or doesn't update the row then T2 will get the above row. But if T1 updates the row so that mark = 1 and then commits, then T2 will get an empty result set instead of getting the next row with mark = 0. The queries could use LIMIT 2 instead of LIMIT 1 and update only the first row that came back, but then you'd have the same problem with a third concurrent transaction (and with LIMIT 3 and a fourth transaction, and so on). -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How can I selet rows which have 2 columns values cross equal?
On Mar 10, 2006, at 22:24 , Fay Du wrote: I want to get row 1, 2,4 and 7 selected. Because their values of a and b are cross equal. i.e., for each pair of rows, a.Row1 = b.Row2 and b.Ro1 = a.Row2 a.Row4 = b.Row7 and b.Ro4 = a.Row7 You need to use subqueries: create table test ( id integer primary key , a integer not null , b integer not null ); copy test (id, a, b) from stdin; 1 100 101 2 101 100 3 100 3 4 20 30 5 11 13 6 3 33 7 30 20 \. select t1.id as t1_id, t2.id as t2_id from test t1 join test t2 on (t1.a = t2.b and t1.b = t2.a); t1_id | t2_id ---+--- 7 | 4 4 | 7 2 | 1 1 | 2 (4 rows) And if you don't want to have each pair listed twice, just add WHERE t1.a < t2.a, e.g., select t1.id as t1_id, t2.id as t2_id from test t1 join test t2 on (t1.a = t2.b and t1.b = t2.a) where t1.a < t2.a; t1_id | t2_id ---+--- 4 | 7 1 | 2 (2 rows) Hope this helps! Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
