Re: [SQL] order by different on mac vs linux
On lör, 2012-05-26 at 21:47 -0600, Wes James wrote: > on the ascii table here: > > http://www.ascii-code.com/ > > upper case letters should sort before lowercase letters. ASCII has nothing to do with how letters "should" be sorted. It is not a sorting standard, it is a character encoding standard. If you open a dictionary, you won't (usually) find all upper case letters before all lower case letters. The sort orders provided by glibc are, individual bugs aside, correct, where "correct" means, they implement various national and international standards. (Equally, the sort orders on Mac OS X are, degenerate cases aside, broken, because they don't implement any recognized sorting standard.) Of course, you are not required to like them, and there are many people who have particular issues with them, but you won't get far claiming they are wrong, if you don't have a recognized alternative source to point to that defines correctness. -- 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] the use of $$string$$
On mån, 2011-11-07 at 08:44 +, Richard Huxton wrote:
> > myvarString = "long string that contains single quotes"
> > cusor.execute("insert into table (pkid, myfield) values (%s, $$%s
> $$)",(123,
> > myvarString))
> >
> > When I execute the above I'm seeing:
> > E'long string that contains single quotes' in the field. When I do
> a "select
> > * from table" I get E'long string that contains single quotes'.
>
> OK, so it seems psycopg is quoting your strings for you (as you'd
> expect). It's presumably turning your query into:
> ... values (E'123', $$E''$$)
> So - the $$ quoting is unnecessary here - just use the % placeholders.
>
> Incidentally, should it be %s for the numeric argument?
Yes.
--
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] a strange order by behavior
On tor, 2011-06-23 at 05:57 +0200, Pavel Stehule wrote: > 2011/6/22 Peter Eisentraut : > > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote: > >> Pavel suggested using a collation of ucs_basic, but I get an error > >> when I > >> try that on linux: > >> > >> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test > >> createdb: database creation failed: ERROR: invalid locale name ucs_basic > > > > ucs_basic is a collation name, which is an SQL object. The argument of > > createdb --lc-collate is an operating system locale name. You can't mix > > the two, even though they are similar. > > > > ok, what I can to select, when I would to use a C like default order? createdb --locale=C --encoding=UTF8 -- 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] a strange order by behavior
On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote: > Pavel suggested using a collation of ucs_basic, but I get an error > when I > try that on linux: > > $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test > createdb: database creation failed: ERROR: invalid locale name ucs_basic ucs_basic is a collation name, which is an SQL object. The argument of createdb --lc-collate is an operating system locale name. You can't mix the two, even though they are similar. -- 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] a strange order by behavior
On ons, 2011-06-22 at 01:43 -0700, Samuel Gendler wrote: > I seem to recall a thread here about it ignoring spaces entirely in that > collation (and maybe ignoring capitalization, too?). The way it works is that every collating element (letter or other character or character group that you sort as a unit) is assigned four weights (primary, secondary, tertiary, and quaternary), and the sorting then first compares the primary weights, then the secondary weights, etc. The primary weight typically indicates the overall sort order, like A before B, the secondary weight has to do with diacritic marks, the tertiary with letter case, and the fourth level is only used in special cases. So that's why it looks as though the capitalization is "ignored" unless both the primary and secondary weights are the same. > This worked: > > createdb -E UTF-8 --lc-collate=C some_db > > A quick google search > reveals that there is some kind of standard for unicode collation ( > http://www.unicode.org/reports/tr10/ ) and I have no idea if that is what is > represented by the en_US.UTF-8 collation or not. At least the collate category of the en_US.UTF-8 locale on glibc is unaltered from the ISO 14651 default ordering, which is equivalent to the Unicode default ordering. There several other locales for which that is also the case. Unfortunately, this is not exposed outside of the glibc source code. So you can't just select "give me a neutral default ordering". -- 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] How strings are sorted by LC_COLLATE specifically?
On tor, 2010-11-25 at 14:42 +0900, Chang Chao wrote: > How strings are sorted when LC_COLLATE = ja_JP.UTF-8. > I tried to read the documention on that,but there are just a few > words, > like LC_COLLATE determines string sort order, > Is there a specific reference about this? > So I can implement an equivalent string sort function in JAVA. > because some of the sort logic is here. The actual string comparison is done by the strcoll() function in the operating system's C library. -- 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] Using count on a join, group by required?
On tis, 2010-08-10 at 22:21 -0430, Jose Ildefonso Camargo Tolosa wrote: > And it works, it gives me something like: > > product_id | name | code | manufacturer_id | > manufacturer_name | num_serials > +--+---+-++- > 17 | THE product | 1235711131719 | 19 | > THE product's manufacturer | 5 > 6 | Car Battery 500A 12V | 7591512021575 | 8 | > Acumuladores Duncan, C.A. | 11 > 1 | Test product 1 | 123456789012 | 1 | > Test Manufacturer | 6 > > Which is correct, and exactly what I wanted. > > So far, so good. The thing is: the group by clause, I had to add it > because the parser forced me to, because it complained like this: > > ERROR: column "manufacturer.name" must appear in the GROUP BY clause > or be used in an aggregate function > > and I had to include *all* the requested columns on the group by > clause, can anybody tell me why? or at least point to some doc that > help me understanding this? This is fixed in PostgreSQL 9.1devel (*); there you only need to put the primary key into the GROUP BY clause. Earlier versions didn't know that that was enough to ensure a deterministic result. (*) -- It will probably be a bit over a year before that is released. -- 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] type cast
On tis, 2010-08-10 at 18:38 +0200, Imre Horvath wrote:
> Hi!
>
> I don't know if it's the right place or the psycopg2 list:
>
> I've got a plpython function, with a character varying param.
> I can call it from sql.
> But when i try to call it with psycopg2.callproc('testfunc', ['test']),
> i've got the error:
> function testfunc(unknown) does not exist
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
psycopg list. But you should add version information about psycopg and
PostgreSQL.
--
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] How oids of the base data types often change?
On mån, 2010-07-19 at 14:47 +0400, Dmitriy Igrishin wrote: > For example, when using libpq(-xx), it is possible to get the oid of > the type of any column. > Is there a guarantee that oids of base types (void, integer, ... ) > will not change in future > releases of Postgres? There is no actual "guarantee" written in stone, but the OIDs of those types haven't changed in 10+ years, and there is no conceivable reason to change them, so you can be pretty sure that they'll remain the same. -- 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] Pg_Restore with --clean option
On Wed, 2009-09-30 at 22:14 -0700, Jyoti Seth wrote: > I want to restore data of a single table. Before restoring the data I > disabled all the triggers and constraints on that table. I used the restore > command with --clean option so that data gets deleted from that table and > then fresh data get inserted. --clean drops the tables, not the data. > But this is throwing error: duplicate key value violates unique constraint. > > How can I restore the data to a table that already has data? Well, you could just not use --clean, which would insert the data into existing tables. But if you have conflicting data in your tables, you need a more elaborate scheme, depending on how exactly you plan to resolve those conflicts. I think you might be a bit confused about the proper workflow with pg_dump and pg_restore and therefore are not giving us accurate information. Perhaps some more detail would 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] Question about encoding
On mån, 2009-08-24 at 18:42 +0200, Denis BUCHER wrote: > Question 1 : > Is it the expected behavior ? These characters have a SQL_ASCII > equivalent because I already have them stored in another table of the > same database SQL_ASCII is not the same as ASCII. SQL_ASCII means, take the bytes as they come. So a 40-character UTF-8 string might indeed be longer than 40 bytes, which is what SQL_ASCII will look at. The best bet is to avoid SQL_ASCII altogether. It's pretty bogus and inconsistent. > Question 2 : > If yes, then I suppose I should have the database as LATIN1 or UTF8. > Can I change/convert the encoding of the database ? Or at least of the > schema (which would be even better) Dump, recreate database with right encoding, restore. -- 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] Double aggregate problem
On Wednesday 22 July 2009 19:16:21 David Weilers wrote: > I have the following query: > > select v.id, array_to_string(array_accum(s.name),', ') as sector , > array_to_string(array_accum(p.name),', ') as provincie from tblvacature > v, tblaccount a , tblvacaturesector vs, tblsector s , > tblvacatureprovincie vp, tblprovincie p where v.id = 11 and v.account = > a.id and vs.vacature = v.id and s.id = vs.sector and vp.vacature = v.id > and p.id = vp.provincie group by v.id, v.inserted order by v.inserted > desc > > That currently produces the following output: No one is going to be able to reproduce that without the table definitions and data. -- 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] Xml Schemas
On Saturday 06 June 2009 22:53:19 Isaac Dover wrote: > Hello, I am attempting to build an xml representation of any database, but > I'm having trouble doing so. I was interested in using the existing xml > functions, such as "schema_to_xmlschema", but the results are strange and > unusable. The resulting schema doesn't even contain the column names, > though it doesn't include the table names. Often, the type information is > incomplete as well. > > Are there any examples for using this family of functions, > "schema_to_xmlschema", etc? My end goal is any xml representation of the > database that includes type information. It appears to me that there is some kind of bug in schema_to_xmlschema. As you say, it misses a bunch of information, such as the column names of the tables. As far as I can tell, the SQL standard actually defines it that way, but that must be a bug. If you use table_to_xmlschema, you will get the proper output, separately for each table. Note, however, that the XML mapping is not designed to be reversible. Depending on what you want to do, this may not be the right format for you. -- 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] ENUM vs DOMAIN vs FKyed loookup table
On Wednesday 08 April 2009 20:08:55 Mario Splivalo wrote: > What are your practices, when do you use ENUMs and when Domains? When given the choice, pretty much ENUMs. Domains weren't really conceived for this sort of thing in the first place, so it's good to move away from them. -- 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
Re: [SQL] Create index on xml field
On Tuesday 13 January 2009 18:56:33 Brad Balmer wrote:
> Why would the following not work?
> create index tstTbl_idx on test_tbl (cast(xpath ('//uim:upcCode/text()',
> job) as text[]));
Looks like you are missing a namespace definition.
--
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] Display message to user
Bart van Houdt wrote: Oracle has a nice package procedure (dbms_output.put_line) to display a message in SQL*Plus, which can display a message to the user. I use this a lot, to notify users of the progress being made during the execution of a script. Is there a way to do this with Postgres as well? If you use the orafce module from pgfoundry, you get that same function. -- 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] Performing intersection without intersect operator
Nacef LABIDI wrote: I want to perform an intersection between several select queries but without using the INTERSECT keyword. select userid from orders where productid=1 INTERSECT select userid from orders where productid=2 I want to transform it without the INTERSECT. (select userid from orders where productid=1 UNION select userid from orders where productid=2) EXCEPT (select userid from orders where productid=1 EXCEPT select userid from orders where productid=2) EXCEPT (select userid from orders where productid=2 UNION select userid from orders where productid=1) -- 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] DIfference between max() and greatest() ?
Ruben Gouveia wrote: What is the difference between these two. I know that max() is an aggregate function ... and greatest() is a normal single-row function. One works vertically, one works horizontally, if that helps you. :-) Or max() is like sum() and greatest is like +. -- 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] uuid-ossp contrib in pg 8.1
James Neethling wrote: I want to add uuid support to a postgres 8.1 installation. Can I install 8.3 uuid-contrib into 8.1? 8.1 does not have a uuid type, so this won't work. -- 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] nesting XmlAgg
Am Donnerstag, 3. Juli 2008 schrieb Isaac Dover: > select > XmlElement(name "Catalog", > XmlElement(name "Tables", > XmlAgg(XmlElement(name "Table", XmlAttributes(T.table_name as > "Name"), XmlElement(name "Columns", > -- i was attempting to aggregate here as well > (select XmlElement(name "Column", C.Column_Name)) > ) > )) > ) > ) > from information_schema.tables T > inner join information_schema.columns C > on T.table_name = C.table_name and T.table_schema = C.table_schema > where T.table_schema = 'public' Try this: select XmlElement(name "Catalog", XmlElement(name "Tables", XmlAgg(XmlElement(name "Table", XmlAttributes(T.table_name as "Name"), XmlElement(name "Columns", (select XmlAgg(XmlElement(name "Column", C.Column_Name)) from information_schema.columns C where T.table_name = C.table_name and T.table_schema = C.table_schema) ) )) ) ) from information_schema.tables T where T.table_schema = 'public'; -- 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] query results in XML format?
Emi Lu wrote: > Can someone suggestion some tutorial/hyperlinks/docs about how > postgresql output query results into xml files? http://www.postgresql.org/docs/8.3/static/functions-xml.html#FUNCTIONS-XML-MAPPING -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
Re: [SQL] Backward compatibility psql 8.1 to 8.2
Scott Marlowe wrote: > Does anyone who know about packaging know if this a limitation of the > packaging spec in rpm, or is there a relatively simple way to get an > rpm based machine to run >1 ver of pgsql at a time? The difference is merely that the packaging doesn't support that setup. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] PostgreSQL does not support updateable cursors
Premsun Choltanwanich wrote: > I found some information on internet about the PostgreSQL does not > support updateable cursors so I change my code to be read only cursors as > 'rsSystem.Open "SELECT * FROM v_memocatlist ORDER BY memocategory", > connSystem, adOpenForwardOnly, adLockReadOnly' then it work fine. However, > I need to use this query for make data ready to be updated on some record. > How can I fix this problem? 8.3 supports updatable cursors. Try that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Usage of UUID with 8.3 (Windows)
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Figure out how to build ossp-uuid on Windows ... > > > > I think Windows has its own UUID generator, so the best bet would be to > > make that work. > > Only if it can be made to present the same SQL-level API as we have for > OSSP. Otherwise we'll be faced with boatloads of platform-dependent > client code ... Indeed. Linux, for example, also has "its own" UUID generator, but I intentionally used the OSSP library, because it is platform independent. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] table column names - search
Am Montag, 14. Januar 2008 schrieb Marcin Krawczyk: > Hi all. I would like to know if there's a way to obtain a list of tables > containing specified column name? Using standard LIKE '%string' syntax > would be great. SELECT table_schema, table_name FROM information_schema.columns WHERE column_name LIKE '%name%'; Add DISTINCT and other columns to taste. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Loading 8.2 data into 8.1
Am Dienstag, 20. November 2007 schrieb Andreas Joseph Krogh: > Is it considered "safe" to use 8.1's pg_dump to dump an 8.2-db and load it > into 8.1? No, pg_dump will complain if you try that. It could work, with manual fixups perhaps, but it is far from "safe". -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SUPERUSER vs CREATEUSER causes foul 'code smell'
Am Donnerstag, 16. August 2007 17:36 schrieb Joshua_Kramer: > In the pg_users view - is there a way to differentiate between a role with > SUPERUSER priveleges, and a user who merely has the CREATEUSER flag? No, because they are the same. > If I want to create a role who can create other roles, but not have other > SUPERUSER priveleges - how can I do that? See CREATEROLE privilege. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Database system identifier from SQL
Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Is there a way to query the database system identifier that > > pg_controldata outputs from SQL? > > Don't think so. Do you have a use-case for providing a function to > return that? I'd like to find out whether two connections are really to the same server. This is in the context of an installation routine for a database application which pulls the connection parameters for several databases from a configuration file and creates various things there. It should, however, only create roles once if the connection parameters point to the same server (if the user only expects a small installation, say). A workaround is to compare inet_server_addr() and inet_server_port(), but that is not 100% safe because it doesn't cover Unix-domain sockets using different paths. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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
[SQL] Database system identifier from SQL
Is there a way to query the database system identifier that pg_controldata outputs from SQL? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Potential bug in postgres 8.2.4
Am Donnerstag, 24. Mai 2007 13:20 schrieb Tomas Doran: > CREATE TABLE testtable ( > col1 char(1), > data text > ); > The following queries all work: > INSERT INTO testtable (col1, data) VALUES (3::int, 'foobarbazquux'); > SELECT * FROM testtable WHERE col1 = 3::int; > SELECT * FROM testtable WHERE col1 IN (1); > SELECT * FROM testtable WHERE col1 IN (1::int); > However these querys fail on 8.2.4, but work correctly on 8.1: > SELECT * FROM testtable WHERE col1 IN (1::int, 2::int); > SELECT * FROM testtable WHERE col1 IN (1, 2); All of this is strictly speaking incorrect anyway. And the queries that do work will most likely start not working in a future version. All of this is a gradual effort to reduce excessive automatic type casting. I suggest you fix your application. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] CPU statistics
Am Mittwoch, 4. April 2007 14:36 schrieb [EMAIL PROTECTED]: > It is possible to retrieve information about the server hardware via > postgreSQL ? You'd have to write your own function. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] LOCK command inside a TRANSACTION
Am Dienstag, 3. April 2007 14:48 schrieb Carlos Santos: > - if an user query a select on a table, the rows of the table in the result > of this select can not be updated or deleted by another user until this one > update, delete or discard the changes on those rows. Sounds like SELECT FOR UPDATE. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Calling void functions
I'm informed that the last statement of a function that returns void cannot be a SELECT. How else is one supposed to call another function which also returns void? E.g., CREATE FUNCTION foo (a int, b int) RETURNS void LANGUAGE plpgsql AS $$ do important things $$; CREATE FUNCTION foo (a int) RETURNS void LANGUAGE sql AS $$ SELECT foo($1, default-value); $$; -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Foreign Unique Constraint
Am Mittwoch, 28. März 2007 00:17 schrieb Phillip Smith: > Perhaps this...? It would work, but depending how many rows are in the > table, it could become incredibly slow. > > ALTER TABLE table1 ADD CHECK (extension NOT IN (SELECT extension FROM > table2)); Subqueries are not allowed in check constraints. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Foreign Unique Constraint
Jon Horsman wrote: > Basically table 1 and table 2 both have the concept of an extension > that must be unique but the rest of the info in the tables are > different. I need to ensure that if i add an entry to table 1 with > extension 1000 that it will fail if there is already an entry in > table2 with the same extension. Make a third table that contains all the extension and add two columns, one referencing table 1 and one referencing table 2, and then add a constraint that only one of the two can be not null. It's not very pretty, but you can use views to make the access simpler. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] select vs. select count
Claus Guttesen wrote: > Why does select and select(count) produce two different results? count(expression) only counts nonnull values. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] GiST index question: performance
Steve Midgley wrote: > my ISP that manages my Pg SQL server is (in my interests) > concerned about installing anything non-standard (read: unstable) > onto their server. I was able to get them to install your TSearch2 > b/c it's been proven many times, but I'm hesitant to even bring up > Q3C since it's less widely deployed. How do you manage to get your own code installed under that theory? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Case with Char(1)
Am Mittwoch, 28. Februar 2007 14:02 schrieb Ezequias Rodrigues da Rocha: > it is possible to use case with character (1) ? Have you tried it? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values
D'Arcy J.M. Cain wrote: >SELECT * FROM table WHERE column IS NULL; >SELECT * FROM table WHERE column = NULL; > > The latter violates the SQL spec and is not allowed by PostgreSQL > without setting a special flag. It doesn't violate any spec and it's certainly allowed by PostgreSQL without any flags. It's just that the result is not what some people expect. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Create View
Mark Simon wrote: > create view things as select * from whatever; > > and then examine my saved view, I find a list of all of the fields > from whatever. I thought it should be possible to save the star (*) > as part of the view. Otherwise, I run into the problem of not getting > all of the fields if I add columns to the table (whatever). > > Is this an SQL thing or a PostGreSQL thing? SQL -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] double precision vs. numeric
Aarni Ruuhimäki wrote: > ' ... type double precision ... will be depreciated / unsupported in > future releases ... ' That is completely false. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL92 compliance
Scott Marlowe wrote: > Would it be possible if we required postfix operators and related to > be inside parens? > > select x ~~ y as yabba > OR > select (x ~~ y) yabba That's pretty much what you get if you restrict the expression to c_expr. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SQL92 compliance
Tom Lane wrote: > I think it's a big stretch to say that that patch fixes it, since it > only allows an AS-less target expression to be c_expr rather than > a_expr as it ought to. Well, it works for a useful subset. I remember that some other database did not *allow* the AS, so there were porting troubles, but I don't remember the details. I'm just saying that it's doable if it's a necessity for someone. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] SQL92 compliance
Am Mittwoch, 23. August 2006 03:40 schrieb Daniel CAUNE: > Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to be SQL92 > compliant? No. I have a patch at <http://developer.postgresql.org/~petere/select-without-as/select-without-as.patch> that fixes this at least for 7.4. I don't think it works for newer versions, but it should give you an idea what is required to get it working. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Postgres 8.1 sequences and 'CALL'-syntax
Schnabl, Sebastian wrote:
> I use postgres 8.1 and trie to run jboss over sequoia-ha
> (http://sequoia.continuent.org/HomePage). But i have an problem with
> sequences. Sequoia claims to support for good reasons and
> db-independece only "sql-standard(s)". Therefore they DON'T support
> the postgres-specific "select nextval('seq_name')". Instead they gave
> me the hint to use the sql-conform "call nexval('seq_name')".
That statement is not any more SQL conforming than the other.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Bitfields always atomic? Other way to store attributes?
Bryce Nesbitt wrote: > If I have two threads modifying the same "bit" field: > thread1=> update table set bf=bf | '01000' > thread2=> update table set bf=bf | '1' > Will this operation always be safe (e.g. result in bf='11000')? Or > must I wrap things in > explicit transactions? Each of these commands will be its own transaction if you don't explicitly start one. > My application is to give attributes to an address table. But maybe > there is a better way? Create 5 boolean fields. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote: > input. then what a difference bitween those types except strlen() ? bytea does not consider character set encodings and locales, and it handles null bytes. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote: > the problem is: you'll get this four byte sequence '\000' _instead_ > of NUL-byte anyway. What you seem to be missing is that PostgreSQL data can be represented in textual and in binary form. What you in psql is the textual form. If you want the binary form you need to select it. Then you can pass the exact bytes back and forth. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote:
> the bytea does not output NULs at all.
> don't mock me.
peter=# create table test (a bytea);
CREATE TABLE
peter=# insert into test values ('a\\000b');
INSERT 0 1
peter=# select * from test;
a
----
a\000b
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote: > you may decide to print something else, aint'you ? > BUT > if they print them then they at least OUTPUT them. I'm not sure what you are getting at here. The only data type in PostgreSQL that has a notion of null bytes is bytea, and bytea prints out null bytes in unambigious form. Note that printing out a space will lose the null byte on restore, so that solution does not seem satisfactory. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
Eugene E. wrote: > PFC wrote: > >> have you feel anything when you read this ? > > > > Business as usual... > > > > It's more fun to grep "crash" on this page, which gets about 27 > > results... > > i am not trying to fight against or for any brandname: not Mesql nor > postgres. > > just sed 's/MySQL/SomeDBMS/g' and concentrate on the fact: > that even so stupid DBMS handling NULs properly. :-) So printing a space is "properly"? Curious ... -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] PostgreSQL Handling of Special Characters
Christian Paul B. Cosinas wrote: > My Database uses SQL_ASCII encoding. Do yourself a favor and use something else. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Change date format through an environmental variable?
Am Donnerstag, 2. März 2006 08:33 schrieb Michael Fuhr: > I see LC_COLLATE and LC_CTYPE in a few places but not in the > documentation for postgres/postmaster, which is where they appear > to be used (backend/main/main.c). Should those pages mention them? No, these variables are not used there, only by initdb. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Feature, my misconception or bug??
Am Mittwoch, 22. Februar 2006 13:04 schrieb Achilleus Mantzios: > foodb=# SELECT '255.255.255.255/24'::cidr; > ERROR: invalid cidr value: "255.255.255.255/24" > DETAIL: Value has bits set to right of mask. > in this case > ... > has no bits set to right of > 8 LSB ^ I'm sure you are aware that "1" is a set bit, so which part are you not understanding? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Modifying postgresql.org file
Ken Hill wrote: > Can someone point me in a > direction as to where I can learn how to modify the postgresql.org > file to increase work_mem? RTFM -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Very slow updates when using IN syntax subselect
Bryce Nesbitt wrote: > > It seems pretty clear that you've never vacuumed nor analyzed these > > tables ... else the planner would have some clue about their sizes. > > Do that and then see what you get. > > They occur in fine time. That's good, thanks. But jeeze, can't > postgres figure this out for itself? I'm sure you wouldn't appreciate it if PostgreSQL did a full table scan before each query to figure out the total size of the involved tables. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] filtering after join
andrew wrote: > How will the query planner do for a nesting query? Treat the > subqueries as multiple queries and then link them together? > where can I find the information (codes or documents)? Look at the execution plan using the EXPLAIN command. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] filtering after join
andrew wrote: > But the function foo() would produce different values for the two > queries, so the result will be different. > A simple example is foo() computes the sum of all the integer fields > of the input record. OK, I see now where you're getting at. You want to combine the record type of A and B into one. Then the proposed solution is right. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] filtering after join
andrew wrote: > Sorry for the confusion. This is what i meant. Thanks, Michael. > > select * > from (select * from A, B where A.a = B.b) as s > where foo(s) < 2; > > On 1/25/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > > andrew wrote: > > > I want to use a UDF to filter tuples t that are generated after a > > > join. More specifially, I have a UDF foo(record), which computes > > > a value for a given tuple. I can do the filtering before the > > > join. e.g.: > > > > > > select * from A, B where foo(A)<2 and A.a=B.b; Note that these two queries will produce the same internal execution plan, so if you wanted to make some difference you will not. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] filtering after join
andrew wrote: > I want to use a UDF to filter tuples t that are generated after a > join. More specifially, I have a UDF foo(record), which computes a > value for a given tuple. I can do the filtering before the join. > e.g.: > > select * from A, B where foo(A)<2 and A.a=B.b; What makes you think that the filtering happens before the join here? And why would it matter? The results should be all the same. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Locale and pattern matching
Hélder M. Vieira wrote: > For instance, if the pattern specifies an uppercase 'A' with an > accute accent, then it will match a lowercase 'A' with an accute > accent, but not an upper or lower case 'A' with a tilde. > This behaviour seems inconsistent with that of the ORDER BY clause, > which considers all forms of a vowel as equal This is not exactly true. Sorting just goes through the strings in multiple passes, looking at the "general" letter first, then at the case, then at the accents, until it finds a difference. > Shouldn't case insensitive pattern matching follow the same collation > weights that ORDER BY uses ? Even if it did, it would not help you, as explained above. Moreover, the collation rules of some locales are sufficiently weird that a consistent behavior between sorting and pattern matching is impossible to achieve. > Until now, I've been selecting LATIN1 encoding, but after a few > tests, I came to think that LATIN9 is a better option (the euro > sign...). For those who regularly use LATIN9, what is your opinion ? > Is it indeed a better option ? Yes. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Trigger / rule question
Joost Kraaijeveld wrote: > 1. Does an insert, update or delete statement return before or after > an "After" trigger (is such a trigger or rule synchronous or > a-synchronous? Synchronous > 2. Is there a concept of a rule or trigger that fails? > If so, if a trigger or rule fails, does the insert, update or delete > statement (transaction) fail? Yes -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Is it possible to redirect an update/insert/delete to a different table?
Andy Ballingall wrote: > I've looked through rules, and as far as I can make out, they are > only useful for explicit actions. I'm looking for something that > behaves as though it simply substitutes the table name for a > different table name before executing the command, no matter what the > command looks like. You can write a couple of rules for that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] add column if doesn't exist
Brandon Metcalf wrote: > Is there a way to check for the existence of a column in a table > other than, say, doing a SELECT on that column name and checking the > output? SELECT * FROM information_schema.columns; Customize to taste. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] What should this query match?
Am Mittwoch, 14. September 2005 13:09 schrieb Joost Kraaijeveld: > Could you give me an example? I understand that the following query > returns all matches but I want everything smalle than the match If you need that then you will need to use two separate conditions, one for less-than and one for LIKE. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] What should this query match?
Am Mittwoch, 14. September 2005 11:54 schrieb Joost Kraaijeveld:
> Why do the following queries return the same count (may be related to
> the first question)
>
> SELECT COUNT(*) FROM customers
> WHERE lower(lastName) < lower('Jan%')
>
> SELECT COUNT(*) FROM customers
> WHERE lower(lastName) <= lower('Jan%')
These queries should return the same answer if the query does not contain the
value 'Jan%' (or differently capitalized versions). I suspect that you are
actually attempting to use % as a wild card, but none of your queries use a
pattern matching operator, so I'd go back and check if your queries really
are what you think they should be.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(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] a "catch all" type ... such a thing?
Marc G. Fournier wrote: > Are there any data types that can hold pretty much any type of > character? UTF-16 isn't supported (or its missing from teh docs), and > UTF-8 doesn't appear to have a big enough range ... UTF-8 has exactly the same "range" as UTF-16. In any case, the UTF-8 encoding in PostgreSQL is probably your best choice, unless you want to dig into the weirdness that is MULE_INTERNAL. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] POSIX Regular Expression question
Am Montag, 5. September 2005 17:40 schrieb Harald Fuchs: > How so? I thought character ranges are more an encoding than a locale > issue. That is incorrect. > I dunno the details of the supported encodings, but is there > any encoding where 'a-z' includes more or less than 26 letters? Well, it seems that our regexp library interprets [a-z] as exactly 26 letters, but that seems to be a lack of locale support rather than a feature. There are statements in the documentation of other regexp libraries that directly contradict this behavior. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] POSIX Regular Expression question
Am Montag, 5. September 2005 17:13 schrieb Alvaro Herrera: > Note that [:alpha:] and such character classes are defined by POSIX to > be locale independent: > > alvherre=# select 'á' ~ '[a-z]'; > ?column? > -- > f > (1 fila) > > alvherre=# select 'á' ~ '[[:alpha:]]'; > ?column? > -- > t > (1 fila) I don't think this addresses the concern I intended to raise. The first query should succeed for all letters between a and z, the second should succeed for all letters. Neither is guaranteed to succeed only for all "normal" Latin letters a, b, c, ... z. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] POSIX Regular Expression question
Am Montag, 5. September 2005 17:10 schrieb A. Kretschmer: > > colname ~ '^[A-Za-z]*$' > > This match also a empty string. An empty string also fulfulls the condition "only with characters A-Za-z". Or maybe not. :-) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] POSIX Regular Expression question
Am Montag, 5. September 2005 15:57 schrieb Aldor: > I want to get out a string only with characters A-Za-z. > Any idea how to do this in Postgres with POSIX Regex? Presumably, colname ~ '^[A-Za-z]*$' If you want to be independent of locale issues, then you'd have to enumerate all the letters instead of using a range specification. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] bug in information_schema?
Am Samstag, 30. Juli 2005 17:15 schrieb Tom Lane: > The unconstrained join against pg_user is clearly unnecessary, > and in fact I took it out a few days ago. I'm not sure whether the > SELECT DISTINCT is still needed --- it might be, if there can be > multiple pg_depend entries linking the same entities. That would have been my guess, but it seems that even if a column or table is used multiple times, a dependency is recorded only once, as it should be. It might have been related to the duplicate pg_user mention. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL Query question
Am Donnerstag, 30. Juni 2005 11:27 schrieb Nick Stone: > SELECT > tbl1."TermTypeID", > tbl1."ParentID", > tbl1."KeywordID", > tbl1."Term", > tbl2."KeywordID" > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" > WHERE > (tbl1."TermTypeID" >= 200) AND > (tbl1."TermTypeID" < 600) AND > (tbl1."IsSynonym" = false) AND > (tbl1."LanguageID" = 1) AND > (tbl2."StockID" = 1) > ORDER BY > tbl1."TermTypeID", > tbl1."Term"; > > Just to be obvious both queries as far as I can should return everything > from Terms and anything if it exists from SearchStore subject to the WHERE > clause parameters - obviously! The condition (tbl2."StockID" = 1) will remove all rows that have null values in the tbl2 fields, thus making your left join useless. Perhaps you should change that to (tbl2."StockID" = 1 OR tbl2."StockID" IS NULL) -- or use your first version. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ENUM like data type
Am Donnerstag, 30. Juni 2005 00:55 schrieb Tom Lane: > It's not that hard to make your own type using the builtin textin and > textout functions, and then add just the functions you wish to provide. Implementing the "distinct type" feature of SQL would probably amount to something like that. Might be worth looking into. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] ENUM like data type
Rod Taylor wrote: > Indeed. A CHECK constraint on a DOMAIN is an ENUM plus some. Not really. A domain doesn't create a new type. If you base your enum domains on the text type, as would usually be the case, then nothing stops you from using, say, text concatenation operators and the like. I suppose in practice this won't matter too much, but it can't be called a clean design. What you'd really need is a way to create a distinct type. SQL has a feature for that, but PostgreSQL hasn't implemented it. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] encoding
Aarni Ruuhimäki wrote: > You might also want (re?)configure your Pg-system with > --enable-locale and set your preferred locale and db default encoding > in initdb to suit your needs, in order to have alphabetical sortings > etc. work ok. If you're still using a PostgreSQL version that has the --enable-locale option then you rather need to upgrade. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] lower and unicode
pginfo wrote: > I am uusing pg 8.0.1 on FreeBSD 5.3 but I am ready t use the version > taht supports correct unicode. FreeBSD doesn't support Unicode, so you need to use something else. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Multiples schemas
Am Mittwoch, 2. MÃrz 2005 12:30 schrieb [EMAIL PROTECTED]: > Could I create a multi schema into another schema ??? or is there only one > level for schema sctructs? No and yes. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] No triggers visible for different user in information_schema.triggers
Am Donnerstag, 17. Februar 2005 18:43 schrieb Michael Fuhr: > SQL:2003 (Working Draft) says: > > Identify the triggers on tables in this catalog that are accessible > to a given user or role. > > Apparently PostgreSQL implements the SQL:1999 specification of > information_schema.triggers. While that is true, superuserdom consistently does not play any role in the information schema for the purpose of figuring out what is accessible to a certain user. This may be considered a bug, but it would also create inconsistencies because the state "accessible to" is defined by other information schema tables where superuserdom cannot be represented. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] case sensitive/insensitive confusion
Christoph Haller wrote: > It seems to me under hpux the sort is done case sensitive, > as would one expect on SQL_ASCII encoding, whereas > under linux a case insensitive sort is done. The sort order depends entirely on the locale that you specify to initdb (not the encoding). Please check the documentation there. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] HowTo change encoding type....
Andrew M wrote: > how do I change the encoding type in postgreSQL (8) from UTF-8 to > ISO-8859-1? Dump your database, drop your database, recreate your database with the different encoding, reload your data. Make sure the client encoding is set correctly during all this. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Table definition
Am Dienstag, 16. November 2004 16:40 schrieb Bruno Prévost: > I need to use it in sql. There is no direct way to do this in SQL, but I can offer you the following alternative: CREATE FUNCTION get_table_definition(text) RETURNS text AS ' #!/bin/sh pg_dump -t $1 ' LANGUAGE plsh; :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Table definition
Am Dienstag, 16. November 2004 15:04 schrieb Bruno Prévost: > Anybody know how to obtain the table definition in text. Use pg_dump. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] A transaction in transaction? Possible?
Achilleus Mantzios wrote: > In other words, now with savepoints, BEGIN; COMMIT; ROLLBACK; > can be replaced with > SAVEPOINT foo; RELEASE foo; ROLLBACK TO foo; respectively. > > If only transactions weren't a requirement for SAVEPOINTs, > what would we then need BEGIN; COMMIT; ROLLBACK; for? Note that under the current arrangement, it doesn't make much sense to "commit" a subtransaction. It will be committed anyway when the main transactions commits, and you cannot commit it earlier because the main transaction could still roll back. So savepoint blocks are not really transactions, but more like semi-transactions. In other nested transaction models, things can be different. If you have so-called open nested transactions, which expose their results to other transactions already before the parent transaction commits, then a subtransaction commit is useful. But that behavior violates the isolation criterion of transactions and therefore needs additional facilities to behave tolerably. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] A transaction in transaction? Possible?
Achilleus Mantzios wrote: > Wouldn't make more sense to allow nested begin/commit/rollback > blocks? Possibly. But that consideration would have been more relevant about 6 years ago when they wrote the SAVEPOINT syntax into the SQL standard. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Information about the command SQL " create synonym".
[EMAIL PROTECTED] wrote: > Could you say if this command will be implemanted in a future version > of a postgres database ? I'm not currently aware of any concrete proposals to implement this feature, but previous discussion has not shown any strong resistance against the concept. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to check postgres running or not ?
Am Montag, 20. September 2004 12:05 schrieb Gaetano Mendola: > It only depends on your distribution, in your case: > > ps aux | grep /usr/lib/postgresql/bin/postmaster | grep -v grep > > consider also the if you run different postmaster version in different > location this is the only way I believe Or maybe simply: $ ps -C postmaster -F UIDPID PPID CSZ RSS PSR STIME TTY TIME CMD postgres 1172 1 0 4339 2160 0 09:14 ?00:00:00 /usr/lib/postgresql/bin/postmaster -D /var/lib/postgres/data postgres 1177 1172 0 2039 2776 0 09:14 ?00:00:00 postgres: stats buffer process postgres 1178 1177 0 1826 2048 0 09:14 ?00:00:00 postgres: stats collector process postgres 9935 1172 0 4443 2848 0 13:46 ?00:00:00 postgres: pei template1 [local] idle -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] casting BOOL to somthng
sad wrote: > since you printed it you poke a convention (of casting to string) > > if you can print it on screen why not to print it in string? Allow me an attempt at a philosophical explanation: The external representation to the API is arbitrary, because it's part of the API specification, and it varies. If you use libpq, you get a character 't' or 'f', if you use ECPG you get a C bool (int) datum, if you use JDBC, you get a Java bool value, etc. psql uses libpq, so you see 't' or 'f'. MS Access maybe uses ODBC and you might see a checkbox or something. It's part of the interface definition. The cast to text, however, is part of the data model, and it has to be both natural and universal. I think you agree that there is no universal, obvious correspondence between character strings and boolean values, at least not nearly as universal and obvious as the well-known correspondence between character strings and numbers. We could pick one arbitrary correspondence and implement it, and if we did we would probably pick one that is consistent with the mapping used by libpq and other frontends. But doing that gains no functionality, so why bother? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] any chance SQL ASSERTIONS will be implemented?
Markus Bertheau wrote: > in the course of designing a database schema and ensuring integrity > everywhere I have stumbled over a problem that Josh told me > ASSERTIONS would solve. Is there any chance ASSERTIONS will be > implemented in PostgreSQL? There is a pretty good chance, but there is no telling when it will happen. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Inserts with incomplete rows... NOT NULL constraints
Am Mittwoch, 11. August 2004 14:35 schrieb Lars Erik Thorsplass: > I would expect NULL fields not specified in the insert to get NULL > inserted automatically. But that fields which are NOT NULL in the > table structure gets inserted a NULL value too seems odd. More accurately, the default value is inserted, which in turn is null if you didn't specify one. You might find it odd that default values that are inconsistent with constraints are allowed, but I don't see any reasonable alternative. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] LIKE on index not working
Am Donnerstag, 22. Juli 2004 09:38 schrieb Chris Cox: > For some reason I just can't get this to use the index for the following > query. I'm using PostgreSQL 7.3.4. In 7.3, LIKE cannot use an index unless you set the locale to C. In 7.4, LIKE can use an index, but it has to be a different kind of index, as explained here: http://www.postgresql.org/docs/7.4/static/indexes-opclass.html -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [JDBC] [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
Dario V. Fassi wrote: > Yes you are right , the original data come from a DB2 with CodePage > IBM-850 and was inserted without complains in a Postgres 7.3.6 with > SQL_ASCII. If you have a program named "recode" installed you could try using that to recode the dump file to a supported encoding. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] BYTEA output presentation
Peter Wang wrote: > The BYTEA data look like "/031/024/001/003?/022/". > How can I use some PostgreSQL function to remove "/" when I use > select statement ? What type of format is the BYTEA datatype? Can I > output it to hexadecimal or octal format ? If you can, how ? Or do > you know any third party tool or script which can output the > hexadecimal or octal format for PostgreSQL's BYTEA datatype ? Your > help is appreciated. Thank you. libpq has functions to deal with bytea data: http://www.postgresql.org/docs/7.4/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-BYTEA -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Custom type where not all elements are comparable
Markus Bertheau wrote: > Is it possible to define or implement a type in PostgreSQL not all > values of which are comparable to each other? In particular I'm > thinking of a duration type similar to the XML Schema duration > type[1]. For example P2D (2 days) is less than P4D (4 days), but P1M > (1 month) and P30D (30 days) are not comparable. Smells like the interval type. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: FW: [SQL] "=" operator vs. "IS"
Rich Hall wrote: > My question is why is the form > > "(anything) = NULL" > > allowed? > > Since > > "(anything) = NULL" is always Null, this cannot be what the coder > intended. Using that same line of argument, why is 1+1 allowed? The coder clearly knows that it is 2, so why is he writing that? Many queries are generated by automatic tools that definitely intend what they say. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] In 7.4 ensure you have DEFAULT now () with no spaces
David B wrote: > We had been testing 7.4 for a few days and just noticed that some > tables had created_timestamp rows with a date/time of the date the DB > was created...not the date/time the insert was done. > > Looking at those tables the create DDL's for those few tables > contained now () > as in: > > created_timestamp timestamp DEFAULT now () -- note the space > between now and () > > Most had correctly been defined without the space - as in now() Whatever it was, that was not the problem. With 7.4.1: peter=# create table test1 (foo text, create_timestamp timestamp default now()); CREATE TABLE peter=# create table test2 (foo text, create_timestamp timestamp default now ()); CREATE TABLE peter=# create table test3 (foo text, create_timestamp timestamp default now peter(# /* blah */ peter(# ( peter(# /* blah */ peter(# ) peter(# ) peter-# ; CREATE TABLE peter=# \d test1 Table "public.test1" Column |Type | Modifiers --+-+--- foo | text| create_timestamp | timestamp without time zone | default now() peter=# \d test2 Table "public.test2" Column |Type | Modifiers --+-+--- foo | text| create_timestamp | timestamp without time zone | default now() peter=# \d test3 Table "public.test3" Column |Type | Modifiers --+-+--- foo | text| create_timestamp | timestamp without time zone | default now() They're all identical. ---(end of broadcast)--- TIP 3: 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] Getting FK relationships from information_schema
Tom Lane wrote: > Kyle <[EMAIL PROTECTED]> writes: > > I think this is only an issue when the user relies on postgres to > > choose a constraint name automatically. Seems like a reasonable > > approach would be to have postgres choose a name for the constraint > > that happens to be unique in the schema (like tablename_fkey_$1). > > We have discussed changing the default names of FK constraints > before. I have no problem with doing something like the above --- any > objection out there? I think it's a good idea. It will also make the error messages of the kind "foreign key $1 violated" a bit clearer by default. There will, however, be complaints that the constraint names are not automatically renamed with the table; but we are used to those by now. ---(end of broadcast)--- TIP 3: 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] SELECT - ORDER BY Croatian characters ....
Kornelije wrote: > I'm using PostgreSQL and my database contains Croatian Characters > (ccz...) so when I pose a query, and I use order by clause, the > result is not sorted properly. You need to initdb your database with the proper locale (hr_HR, probably). Also, choose the right encoding to go along with the locale (LATIN2?). ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] start
H.J. Sanders wrote: > I have difficulties starting the postmaster automatically at boot > time (everything I tried is done by 'root'). > > Can someone give me an example for LINUX (SUSE 8). Maybe you would rather want to download the binary packages, which take care of that. RPMs for SuSE are available on the PostgreSQL mirrors in the directory "binary". ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
