Re: [SQL] new table with a select

* Julien Cigar: > create table foo as select * from bar; > > just add "where 1=2" if you just want the schema There's also this: CREATE TABLE foo (LIKE bar); This gives you more control over what aspects of the table are duplicated; see the documentation for deta

Re: [SQL] Subselects not allowed?

MAX(person_id)) FROM persons; > ALTER TABLE persons ALTER COLUMN person_id SET DEFAULT > NEXTVAL('persons_person_id_seq'); > ALTER SEQUENCE persons_person_id_seq OWNED BY persons.person_id; I think you should acquire an exclusive lock on the table, too. -- Florian Weimer

[SQL] Transaction-specific global variable

ready something similar I could reuse. For instance, I could reuse the application_name configuration variable, but this seems a bit gross. (If you could recommend logging functionality I could learn from, that would be welcome, too.) -- Florian Weimer BFK edv-consulting GmbH

Re: [SQL] Is there a solution for "SELECT OR INSERT"

* Stefan Becker: > Is there a way to get the ID row OR create a new one in > ONE single statement? You could create a stored procedure. But if you have concurrent inserts, locking is a bit tricky. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegs

Re: [SQL] Jagged/ragged arrays

rays must have array expressions with > matching dimensions > > (OP) Correct? Yes, this is what I'm after. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96

[SQL] Jagged/ragged arrays

It seems that PostgreSQL 8.4 does not support ragged arrays. Is there a workaround to get similar functionality (mainly the ability to extract values in SQL expressions)? -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49

[SQL] Deleting rows from a table not contained in another table

re some way to turn this into a merge join, short of introducing primary keys and using them to guide the join operation? -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-962

Re: [SQL] Generating table rows from arrays

lin/index.php?/archives/4-fun-with-arrays.html Ah, neat trick. Thanks. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sq

[SQL] Generating table rows from arrays

wonder if there are better options nowadays. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sq

[SQL] Index on elements of an array

). -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes t

Re: [SQL] Selecting exactly one row for each column value

* Tom Lane: > Florian Weimer <[EMAIL PROTECTED]> writes: >> For each value in the first column, I need one (and only one) matching >> row from the table. A possible solution is: > > SELECT DISTINCT ON would do it, if you don't mind a non-portable solution.

[SQL] Selecting exactly one row for each column value

columns increases. Is there a better way to implement this? -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99

Re: [SQL] Is this a bug? Deleting a column deletes the constraint.

bles are not in > themselves very useful, but disallowing them creates odd special cases > for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec > restriction. And you need the syntax for table partitioning. -- Florian Weimer<[EMAIL PROTECTED]> BFK

Re: [SQL] Using bitmap index scans-more efficient

ritten some PHP code to implement it. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--

Re: [SQL] Rows with exclusive lock

* Martin Marques: >> That's what SELECT FOR UPDATE does. > > Hi Alvaro, > > After the SELECT FOR UPDATE other transactions can still see the > locked rows. I want a read/write lock, so no one can access does rows. You should probably run the other transactions at SERIALIZABLE level. I suppose thi

Re: [SQL] INSERT/UPDATEs cycles and lack of phantom locking

* Florian Weimer: > In addition, it occurred to me that I get the INSERT failure only if > there is a suitable PRIMARY KEY/UNIQUE constraint on the table. I > haven't got that in all cases, so I need that advisory locking anyway, It seems that LOCK TABLE ... IN EXCLUSIVE MODE doe

Re: [SQL] INSERT/UPDATEs cycles and lack of phantom locking

ransaction might require it). In addition, it occurred to me that I get the INSERT failure only if there is a suitable PRIMARY KEY/UNIQUE constraint on the table. I haven't got that in all cases, so I need that advisory locking anyway, I fear. -- Florian Weimer<[EMAIL PROTEC

[SQL] INSERT/UPDATEs cycles and lack of phantom locking

rucial), but the reading part of an updating transaction would be blocked. For bonus points, deadlocks would be automatically detected by PostgreSQL (although I would order the locks properly in the usual case, but I can't guarantee this for all codepaths due to the modularity of the a

Re: [SQL] SELECT DISTINCT too slow

* Alvaro Herrera: > Miroslav ?ulc wrote: >> The GROUP BY is really fast :-) > > Doh! How does it do it? It uses a hash table and can therefore discard duplicate rows more quickly (essentially linear time in the number of rows if the number of different rows is bounded).

Re: [SQL] Index to support LIKE '%suffix' queries

* Alvaro Herrera: >> > Florian Weimer <[EMAIL PROTECTED]> writes: >> >> Is it possible to create an index to support queries of the form >> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE >> >> 'prefix%&

Re: [SQL] Index to support LIKE '%suffix' queries

* Tom Lane: > Florian Weimer <[EMAIL PROTECTED]> writes: >> Is it possible to create an index to support queries of the form >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE >> 'prefix%', which I also need)? > > S

[SQL] Index to support LIKE '%suffix' queries

Is it possible to create an index to support queries of the form "column LIKE '%suffix'" (similar to an ordinary index for LIKE 'prefix%', which I also need)? I could define a function which reverts strings (or revert them in the application) and use a normal B-tree index, but I wonder if there is

[SQL] Calling stored procedures in table constraint checks

I guess I need an example how I can pass an entire row to a stored procedure called in a table constraint check. Is this possible at all? -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT

Re: [SQL] Determining if two subnets intersect

Tom Lane <[EMAIL PROTECTED]> writes: > Florian Weimer <[EMAIL PROTECTED]> writes: > > Is there some efficient PostgreSQL expression which is true if and > > only if two subnets (given as values of type cidr) have non-empty > > intersection (even if the i

[SQL] Determining if two subnets intersect

Is there some efficient PostgreSQL expression which is true if and only if two subnets (given as values of type cidr) have non-empty intersection (even if the intersection is not a CIDR network)? -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http

[SQL] Inherited tables: How stable is that feature?

Is it likely that table inheritance is going to be removed in future PostgreSQL versions (or that the semantics change radically)? Or can I built a database on top of this feature without running the risk of a major restructuring task in the next few years? -- Florian Weimer