Re: [SQL] subtract two dates to get the number of days

On 07/13/10 18:58, Campbell, Lance wrote: I want to subtract to dates to know the number of days different. it would be far more interesting operation if u want to divide an INTERVAL by INTERVAL to get a ratio :-) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes

Re: [SQL]

On 07/08/10 11:38, Dave Page wrote: On Thu, Jul 8, 2010 at 8:31 AM, silly sad wrote: On 07/07/10 18:35, Justin Graf wrote: On 7/7/2010 12:00 AM, silly sad wrote: On 07/06/10 21:52, Justin Graf wrote: I wrote an article covering this on the wiki http://wiki.postgresql.org/wiki

Re: [SQL]

On 07/07/10 18:35, Justin Graf wrote: On 7/7/2010 12:00 AM, silly sad wrote: On 07/06/10 21:52, Justin Graf wrote: I wrote an article covering this on the wiki http://wiki.postgresql.org/wiki/BinaryFilesInDB there are some "red flags" in communication (particularly reading pape

Re: [SQL]

On 07/06/10 21:52, Justin Graf wrote: > I wrote an article covering this on the wiki > > http://wiki.postgresql.org/wiki/BinaryFilesInDB there are some "red flags" in communication (particularly reading papers) one of them is "binary data" which ITSELF IS NONSENCE. -- Sent via pgsql-sql mailing

Re: [SQL]

On 07/05/10 11:18, Pavel Stehule wrote: P.S. Practically for storing pictures i prefer regular files. how I say - it depends on application - sometime can be useful have to access to all data only from db connect - for million small pictures the bytea can be best. i really love postgres TEX

Re: [SQL]

On 07/05/10 11:03, Pavel Stehule wrote: 2010/7/5 silly sad: On 07/05/10 10:43, Pavel Stehule wrote: The good size for text or bytea is less than 100M and real max isn't 2G but it is 1G. LO isn't these limits because it isn't accessable on SQL level. any regular file on my f

Re: [SQL]

On 07/05/10 10:43, Pavel Stehule wrote: The good size for text or bytea is less than 100M and real max isn't 2G but it is 1G. LO isn't these limits because it isn't accessable on SQL level. any regular file on my filesystem isn't accessible on SQL level. i am happy with them and never tried to

Re: [SQL]

On 07/05/10 10:30, Trinath Somanchi wrote: Hi, I'm new in using BLOB. How will the insert for storing very large byte strings into a column of data type Blob. i didn't advice you to use BLOB. you may store a string as long as 2GB at any TEXT or BYTEA field. -- Sent via pgsql-sql mailing lis

Re: [SQL]

On 07/05/10 09:57, wrote: Hi, How can I store Byte strings into a postgresql database. Is there any special command to store it. How will be the sql query. there is only '\0' byte incapable to input-output. so u have to have it escaped at all costs _AND NOTHING MORE_. "escaped" doesn't mean

Re: [SQL] How do I remove selected words from text field?

On 07/02/10 03:25, Frank Bax wrote: Osvaldo Kussama wrote: 2010/7/1 Frank Bax : Create some tables; then add some data: create table t1 (i int, v varchar); insert into t1 values(1,'A B C D'); insert into t1 values(2,'B D E F'); insert into t1 values(3,'G H I J'); insert into t1 values(4,'E');

Re: [SQL] How to select text field as interger

| SELECT Name FROM Test WHERE Name::INT = 1; But note that this is going to throw an error if there are any table rows where the name field *doesn't* contain a valid integer. and it recalls the problem of error suppressing once again -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.

Re: [SQL] round(Numeric)

ic) is platform specific. Postgres is written in C and the relevant portions of the C standards douments (and discussions thereof) give a good picture of the functioning of postgres arithmetic. IT IS REALLY SAD ! the round(Numeric) does not behave as Math science prescribes :( Shame on PostgresQL

Re: [SQL] ORDER BY is case insensitive

On 06/23/10 17:45, Jasen Betts wrote: On 2010-06-22, Bryan White wrote: I was suprised to find out that ORDER BY is case insensitive. Is there a way to do a case sensitive ORDER BY clause? use bytea instead of a text type. try this: select * from t order by replace(f,e'\\', e'')::b

Re: [SQL] How to Insert and retrieve multilingual (Hindi "an Indian language") into PostgreSQL

On 06/22/10 14:48, venkat wrote: I want to insert and retrieve multilingual (Hindi) into database.is PostgreSQL supports that ?if it is ... please guide me how to enable multilingual in the table. in addition to the previous advice ("just use UTF-8 for entire database c

Re: [SQL] oracle to postgres migration question

On 06/16/10 02:45, Bruce Momjian wrote: Scott Marlowe wrote: Note that psql automagically right justifies numerics and dynamically sizes all columns so you don't have to do as much of this stuff. Oracle always made me feel like I was operating the machine behind the curtain in the Wizard of Oz,

Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function

On 05/18/10 23:27, Kenneth Marshall wrote: It works using 'now' and I assume that since curtime is now() is NOT the CURRENT timestamp in fact, it is about the timestamp of the current transaction has been started. it is the really USEFUL value, still u have to remember this meaning. -- Sent

Re: [SQL] plperlu user function.

On 05/19/10 01:29, David Harel wrote: I am trying to write a user function on the server to retrive image files. Currently I wrote the following: my $tmpfile = shift; open my $IFHAND, '<', $tmpfile Any recommendation how to do it right? first of all, stop opening files at all, return to

Re: [SQL] Postgresql database

On 05/14/10 13:28, Trinath Somanchi wrote: yes... But attribute of this table asl has an instance .. that two year of releases are possible also... I'm speaking of a very trivial case. it doesnt multiplies the multitude of table. it makes no problems with the "joins of ALL the tables" -- Sent

Re: [SQL] Postgresql database

On 05/14/10 12:42, Trinath Somanchi wrote: Hi All, I have Tree structured database to maintain. Its as follows. Vehicles u r talking about a single entity vehicles it is very common to represent a single entity with a single table and IF u also have to have an entity "class of vehicles" u r

Re: [SQL] best paging strategies for large datasets?

First u count(*) the rows and select a requested page returning to a client the count result bundled "with a page of rows" (1) client renders the acquired rows (2)__memorize__ what part of the data he just got (3) and stores the count result to calculate "the pager div" all the subsequent clic

Re: [SQL] best paging strategies for large datasets?

On 05/12/10 09:41, Louis-David Mitterrand wrote: Hi, I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and am in the process of developping a pager to let users leaf through it (30K rows). Ideally I'd like to know when requesting any 'page' of data where I am within the datas

Re: [SQL] [Trigger] Help needed with NEW.* and TG_TABLE_NAME

On 05/11/10 18:26, Torsten Zühlsdorff wrote: Tom Lane schrieb: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= writes: NEW.revision := addContentRevision (OLD.content_id, OLD.revision); /* not working line, just a stub: EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW; */ RETU

Re: [SQL] [GENERAL] Schema design / joins

__Orgs__ id name __Seasons__ id org_id fk(orgs.id) name __Teams__ id season_id fk(seasons.id) name __TeamFees__ id team_id fk(teams.id) *org_id<--- (?put extra fk here to avoid many joins?) NO. instead of it use triggers before insert/update CREATE FUNCTION foo() RE

Re: [SQL] LIMIT problem

On 05/01/10 03:58, Jasen Betts wrote: On 2010-04-30, silly sad wrote: suppose i request SELECT foo(t.x) FROM t LIMIT 1; Whither it DEFINED how many times foo() will be executed? foo will be executed repeatedly until it returns a result or all the rows in t are exhausted. May anyone rely

Re: [SQL] LIMIT problem

On 04/30/10 16:57, Nilesh Govindarajan wrote: On 04/30/2010 06:20 PM, silly sad wrote: suppose i request SELECT foo(t.x) FROM t LIMIT 1; Whither it DEFINED how many times foo() will be executed? May anyone rely on it? Or we have to avoid this non SQLish trick? It will execute foo only

[SQL] LIMIT problem

suppose i request SELECT foo(t.x) FROM t LIMIT 1; Whither it DEFINED how many times foo() will be executed? May anyone rely on it? Or we have to avoid this non SQLish trick? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresq

Re: [SPAM]-D] Re: [SPAM]-D] [SQL] How to find broken UTF-8 characters ?

How can I get rid of them? iconv -c AFAIK iconv would translate on file system level but I would think that messed up a allready messed up Excel workmap even further. I'd be glad to handle csv, too. pg_dump | iconv -c | psql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To mak

Re: [SPAM]-D] [SQL] How to find broken UTF-8 characters ?

On 04/26/10 04:12, Andreas wrote: Excel files > pgAdmin > Access. looks like a complete offtopic How can I find those broken UTF-8 characters? How can I get rid of them? iconv -c BUT u should not have those characters at all if one is occured it most probably an error AND u should get ri

Re: [SQL] How to max() make null as biggest value?

On 04/14/10 08:33, Feixiong Li wrote: Hi , guys , I am newbie for sql, I have a problem when using max() function, I need get null when there are null in the value list, or return the largest value as usual, who can do this? i.e. max([1,2,3,4,5]) => 5 max([1,2,3,4,5,null]) => null if u want a

Re: [SQL] Cascading sum in tree with CTE?

to select a whole subtree of a particular node of a tree. u have to modify the tree representation in one of the two ways (according to you fine-tuned needs) (1) store in the EACH node the PATH from root to this node (2) store (l,r) segment representing the INCLUSIONS of nodes into other nodes s

Re: [SQL] Table Design for Hierarchical Data

On 04/07/10 11:00, Achilleas Mantzios wrote: Column | Type| Modifiers -+---+--- id | integer | not null default nextval(('public.paintgentypes_id_seq'::text)

Re: [SQL] Table Design for Hierarchical Data

P.S. almost foget, do not try any oracle-like "tree-jouns" or "special types" or such a crap. your problem as plain as to store a pair of integers (or numerics (i prefer)) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.

Re: [SQL] Table Design for Hierarchical Data

single table. nested tree + ordinal parent reference. nests are calculated in a trigger on insert. -- 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] SQL Developer accessing PostgreSQL

On 03/29/10 20:33, Snyder, James wrote: Hello, Is there a way to configure Oracle’s SQL Developer to access a PostgreSQL database? IMHO, no. And (even if it possible) it is completely useless, since the Postgresql has the "psql" program far better than any oracle tool and than all oracle too

Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

On 03/17/10 17:52, Ignacio Balcarce wrote: -- IF EXISTS A ROW IN THE TABLE STARTING WITH THE CURRENT_DATE Sorry, your field is not an atom => your database does not met a FIRST normal form. it needs normalization urgently. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To mak

Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

On 03/17/10 17:52, Ignacio Balcarce wrote: CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID @NEWID VARCHAR(20) OUTPUT AS SET @NEWID = ( SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','') + CAST(REPLICATE(0,8-LEN (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) +

Re: [SQL] I, nead to capture the IP number from the PC how is running the script ...

On 03/15/10 22:18, John Dizaro wrote: I, nead to capture the IP number from the PC how is running the script first of all you must tell us how your client is connected to your database? there are a lot of different variants, and the most common of them is not PG related at all while the connec

Re: [SQL] list of all months

It looks like a procedural problem. I would solve it in plpgsql. -- 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] Odd query behavior

On 03/12/10 18:41, Dan McFadyen wrote: Hello, I've come across an odd situation. I've had access to a database where a the following happens: " SELECT * FROM table WHERE name LIKE 'abc%' " returns 2 rows... but... " SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name LIKE 'ab

Re: [SQL] Private functions

On 03/14/10 06:21, Jasen Betts wrote: On 2010-03-13, Gianvito Pio wrote: Hi all, is there a way to write a function that can only be called by another function but not directly using SELECT function_name ( )? not really. but there may be another way to get the effect you want. read the se

[SQL] client_timezone to server_timezone and reverse

HELO i am looking for strange things: a timezone conversion AS transparent AS a charset encoding conversion are. for __example__ (not intending to override the current behavior) i want to see now()::timestampTZ always the same (the server side time with the timezone predefined firmly) and to s

Re: [SQL] what exactly is a query structure?

On 02/26/10 09:50, silly sad wrote: hello. Postgresql 8.3.9 CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT); CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$ BEGIN RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob; RETURN; END; $

Re: [SQL] what exactly is a query structure?

On 02/26/10 10:19, A. Kretschmer wrote: In response to silly sad : hello. Postgresql 8.3.9 CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT); CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$ BEGIN RETURN QUERY SELECT login, '*' as pass,

[SQL] what exactly is a query structure?

hello. Postgresql 8.3.9 CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT); CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$ BEGIN RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER; SELECT *

Re: [SQL] very frustrating feature-bug

On 02/17/10 13:51, Jasen Betts wrote: On 2010-02-17, silly sad wrote: acc=> CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT) RETURNS usr AS $$ INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4) RETURNING usr.*; $$ LANGUAGE sql SECURITY DEFINER; acc=>

[SQL] very frustrating feature-bug

acc=> CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT) RETURNS usr AS $$ INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4) RETURNING usr.*; $$ LANGUAGE sql SECURITY DEFINER; acc=> ERROR: return type mismatch in function declared to return usr DETAIL: Function's fin

Re: [SQL] Multiple postgresql functions in a single transaction

Robins Tharakan wrote: > > It can be done, but it depends on how you are generating the value in the > > first function. > > If you sequences though you may have to take care of reverting it > > yourself. Sequences had been constructed in this manner not to cause pain for users -- think of it. S

Re: [SQL] currval() within one statement

Richard Huxton wrote: sad wrote: A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that i still

Re: [SQL] currval() within one statement

A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that i still want to discuss the titled problem or to

Re: [SQL] Single row tables

On Tuesday 11 January 2005 17:45, KÖPFERL Robert wrote: > Hi, > > with what constraint or how can I ensure that one of my tables has exact > one record or 0..1 records? A trigger procedure BEFORE INSERT would help you. And a column type with only one possible field value would help you if you de

Re: [SQL] SET AUTOCOMMIT TO OFF

On Wednesday 01 December 2004 18:42, Tom Lane wrote: > Christoph Haller <[EMAIL PROTECTED]> writes: > > Please, could someone point me to the right list > > or tell me how to do a "SET AUTOCOMMIT TO OFF" within libpq. > > libpq does not have any support for that. Does this mean libpq calls always

Re: [SQL] tree structure photo gallery date quiery

On Tuesday 16 November 2004 14:29, Gary Stainburn wrote: > Hi folks. > > I'm looking at the possibility of implementing a photo gallery for my > web site with a tree structure > How would I go about creating a view to show a) the number of photos in > a gallery and b) the timestamp of the most rec

Re: [SQL] A transaction in transaction? Possible?

On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote: > I thought nested transactions are available in the new > release (8) coming up. how to commit/rollback them ? ---(end of broadcast)--- TIP 7: don't forget to increase your free space ma

Re: [SQL] CREATE TYPE

Hello i note something related to this discussion > create table mytmp(name myvarchar(10,"en_US")); i meant that "en_US" is a locale name, then it means natural language and also character encoding -- those both things are not a matter of SQL-TYPE at all. It is wrong to represent application

[SQL] Offtopic: psql

Hi (B (BPostgres has a perfect tool - psql (Bwhat libraries did you (developers) use to develop psql console and (Bparticulary command-line editor. (B (Bthnx (B (B (B---(end of broadcast)--- (BTIP 6: Have you searched our list archives? (B

[SQL] libpq-fe: PQgetvalue() ?

hi does PQgetvalue() allocate memory rof its result, it returns ? the answer will help me in problem: should i free some cstring_variable if { cstring_variable=PQgetvalue(pgresult_variable,0,0); } and could i PQclear(pgresult_varible) while cstring_varible is in use. thnx --

Re: [SQL] help on a query

On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote: > A query that should get the job done is: > > SELECT registration_id > FROM registrations r > WHERE NOT EXISTS ( > SELECT 1 > FROM receipts > WHERE registration_id = r.registration_id > ); Don't, PLEASE, don't !!! drive

Re: [SQL] Howto turn an integer into an interval?

hello SELECT ('3600'::int::abstime-'epoch'::abstime)::interval; try to modify this idea to fit your purpose. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Isnumeric function?

On Friday 10 September 2004 04:20, Theo Galanakis wrote: > I was just thinking, wouldn't it be great if the pg community had a site > where anyone could contribute their generic functions, or request for a > particular function. i vote positive. ---(end of broadcast)-

[SQL] casting UNKNOWN to REGCLASS

> select 't'::text::bool; > ERROR: cannot cast type text to boolean > > If you're thinking 't'::bool, that's something different. Ok i have nothing to opppose and by the way (!!!) why TEXT can not be casted to REGCLASS ? ---(end of broadcast)---

Re: [SQL] casting BOOL to somthng

> There's a difference between an output function and a cast to text. > One gives you an external representation of the data for end use. The > other gives you an internal representation for manipulation. And at the same time 't'::TEXT can be casted to BOOL 't'::BOOL but reverse.

Re: [SQL] casting BOOL to somthng

On Wednesday 01 September 2004 10:38, Michael Glaesemann wrote: > On Sep 1, 2004, at 2:41 PM, sad wrote: > > On Wednesday 01 September 2004 09:24, Stephan Szabo wrote: > >> There's a fairly accepted convention for integer representations. > >> There's no such c

Re: [SQL] casting BOOL to somthng

On Wednesday 01 September 2004 09:24, Stephan Szabo wrote: > On Wed, 1 Sep 2004, sad wrote: > > On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote: > > > On Aug 31, 2004, at 8:24 PM, sad wrote: > > > > and i am still desire to know _WHY_ there are no pr

Re: [SQL] casting BOOL to somthng

On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote: > On Aug 31, 2004, at 8:24 PM, sad wrote: > > and i am still desire to know _WHY_ there are no predefined cast for > > BOOL ? > > and at the same time there are predefined casts for INT and FLOAT.. > > I th

Re: [SQL] casting BOOL to somthng

On Tuesday 31 August 2004 16:22, Geoffrey wrote: > sad wrote: > > you wrote: > >>you can use CREATE CAST to make your own cast from boolean to text. > > > > thnx it helps. > > > > and i am still desire to know _WHY_ there are no predefined cast for

Re: [SQL] casting BOOL to somthng

you wrote: > you can use CREATE CAST to make your own cast from boolean to text. thnx it helps. and i am still desire to know _WHY_ there are no predefined cast for BOOL ? and at the same time there are predefined casts for INT and FLOAT.. ---(end of broadcast)

[SQL] casting BOOL to somthng

hello why BOOL can not be casted to TEXT ...nevertheless BOOL has a textual (output) representation 't' and 'f' letters why not to use this fact to define cast to TEXT ? ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] surrogate key or not?

On Sunday 08 August 2004 04:29, Kenneth Gonsalves wrote: > but why would anyone want to change the value of an autogenerated serial > row? because of catenating data from two or more databases. ---(end of broadcast)--- TIP 8: explain analyze is y

[SQL] surrogate keys and replication.

Josh, I agree to treat this case as a (1) convinence. But it is still very cpecific, more than just simplifying FKs And you have said about general GUID problem. Let we disscuss this problem ? I hope to here good ideas from you again. Now I solve the GUID problem, with one sequence of IDs on the

Re: [SQL] surrogate key or not?

hello > This reminds me of a project I worked on many years ago, I was pretty much > fresh out of university writing a system for a large medical practice - > itwas principally for accounting purposes. I made lots of suggestions like > Josh's, only to get replies like Karsten's. I the progammer wa

Re: [SQL] surrogate key or not?

I want to add some notes > create table diagnosis ( > pk serial primary key, > fk_patient integer > not null > references patient(pk) > on update cascade > on delete cascade, > narrative text > not null, > unique(fk_patient, narrative) > ); 1) a seque

[SQL] ? on announcement of 7.5

Hello can anyone comment the announcement of 7.5 about "nested transactions" ? doesn't the nesting hurt the matter of transaction ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] surrogate key or not?

On Wednesday 21 July 2004 14:58, Markus Bertheau wrote: > > 4) Replication: to identify an object of ANY type (record of any table > > regardless to datamodel), to store lists of deleted or modified objects > > (regardless to datamodel) > > That sounds like a datamodel on data that belongs to ano

Re: [SQL] surrogate key or not?

Thnx, Josh (Byou are very helpful. (B (B> There are, in fact, three very good reasons to use surrogate keys, all of (B> which are strictly due to limitations of technology; that is, (B> implementation and performance issues, NOT business logic. They are: (B> (B> 1) Convenience: It's very a

Re: [SQL] locks and triggers. give me an advice please

thnx. i try to sound the idea to ensure myself that you are right. > begin transaction; > lock t1 in access exclusive mode; > > Turn off triggers and do your updates. > (Note, "truncate t1" is faster than "delete from t1" followed by a > "vacuum full" and you might consider running "reindex table

[SQL] locks and triggers. give me an advice please

Good day. often, I am turning triggers off and on to perform a mass operation on a table, and i am interested how should i care of another user operations. the scene is: table t1 with user defined triggers and many tables reference t1, (so FK triggers defined on t1) the operation i want to per

Re: [SQL] feature request ?

> sad wrote: > > On Friday 25 June 2004 09:37, Rosser Schwarz wrote: > >>On Fri, 25 Jun 2004 08:16:47 +0400, sad <[EMAIL PROTECTED]> wrote: > >>>>Very simply, a boolean may have to values: true or false. It's also > >>>>possible t

Re: [SQL] feature request ?

On Friday 25 June 2004 09:37, Rosser Schwarz wrote: > On Fri, 25 Jun 2004 08:16:47 +0400, sad <[EMAIL PROTECTED]> wrote: > > > Very simply, a boolean may have to values: true or false. It's also > > > possible that it's not been set to anything (NULL). > &g

Re: [SQL] feature request ?

> Very simply, a boolean may have to values: true or false. It's also > possible that it's not been set to anything (NULL). really ? what about (13 < NULL)::BOOL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map sett

Re: [SQL] feature request ?

> then lots of currently perfectly correct > programs break. If they're the same, then ELSE has different meanings > depending on whether NULL is specified, and that's generally bad from an > understanding the language standpoint. i've already thougth on this new control structure needed but the

Re: [SQL] feature request ?

> If you were to add a NULL block you'd have to deal with things > like, if you only have a then and else, do you run the else on NULL or do > you do nothing? If you do nothing, what if you want the null and else to > be the same, do you add another way to specify that? If you do the else, > the

Re: [SQL] feature request ?

On Thursday 24 June 2004 14:32, Alexander M. Pravking wrote: > On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote: > > Now you treat NULLs as false. > > Nope. NULL is neither true, nor false. It's "unknown", or "undefined". > > fduch=# SELECT 1 WHER

Re: [SQL] feature request ?

...IF ELSEIF ELSE it's all clear but what about unequality of BOOL type possible value set and IF alternatives set ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] feature request ?

> I don't see what your point is. That SQL is wrong ? Or that SQL is not "C" > ? Or that SQL is not a "programming language" ? Who said wrong ? who said SQL ? I thougth _WHY_ the IF control structure has exactly two alternate blocks ? a BOOLEAN expression has exactly two possible values, that's

Re: [SQL] feature request ?

On Wednesday 23 June 2004 21:12, you wrote: > Sad, > > > since BOOL expression has three possible values: TRUE,FALSE,NULL > > plpgsql IF control structure should have three alternate blocks: > > THEN,ELSE,NULL > > > > shouldn't it ? > > No, why? &

Re: [SQL] feature request ?

On Thursday 24 June 2004 09:32, Michael Glaesemann wrote: > On Jun 24, 2004, at 2:12 AM, Josh Berkus wrote: > > Sad, > > > >> since BOOL expression has three possible values: TRUE,FALSE,NULL > >> plpgsql IF control structure should have three alte

[SQL] feature request ?

Hello since BOOL expression has three possible values: TRUE,FALSE,NULL plpgsql IF control structure should have three alternate blocks: THEN,ELSE,NULL shouldn't it ? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ?

[SQL] feature request

hello it might be stupid... sometimes i am starving UPDATE OR INSERT command thnx ---(end of broadcast)--- TIP 8: explain analyze is your friend

[SQL] casting int to bit

PLEASE NOTE : select 1::int8::bit(64); 0001 select 1::int4::bit(64); 0001 select 1::int2::bit(64); ERROR: Cannot cast type smallint to bit the last is a great surprise for me

[SQL] empty string casting to typed value

Hello It is clear that '' is a bad integer or timestamp representation but during the user input NULLs are usually represented with empty strings sometimes bunch of 'if empty' instructions grows huge (and in case of casting to timestamp apostrophes make sense) Why you prohibit casting ''::int

[SQL] type regclass casting

hello select * from pg_class where oid = 'sometablename'::regclass; (1 row) select * from pg_class where oid = 'sometablename'::text::regclass; ERROR can not cast TEXT to regclass What does this mean ? version 7.3.3 please try it on 7.4.x ---(end of broadcast)---

[SQL] type regclass casting

hello look select * from pg_class where oid = 'sometablename'::regclass; (1 row) select * from pg_class where oid = 'sometablename'::text::regclass; ERROR can not cast TEXT to regclass What does this mean ? version 7.3.3 please try it on 7.4.x ---(end of broadcast)

Re: [SQL] The PostgreSQL

Dear developers, many tricks were discussed here, and many feature requests generated. Every discussed problem ALREADY have solution in the current version of the SQL. Most of the problems are forced by incorrect design of a database ! I ask you: do not burden the PostgreSQL with features !!! P

Re: [SQL] a wierd query

> I forgot one situation: > > if I run the query like so: > select distinct on (task_id, begindate) task_id, workhour_id, > begindate as date from workhour UNION > select distinct on (task_id, enddate) task_id, workhour_id, enddate > as date from workhour I get yet another value: 2961 r

Re: [SQL] a wierd query

On Thursday 13 May 2004 19:27, you wrote: > sad wrote: > > select distinct a as F from table > > union > > select distinct b as F from table; > > Note that UNION only returns the unique values of the union > You can get repeated values by using UNION ALL. read th

Re: [SQL] a wierd query

> i require the dictinct values from (visualizing each column > result as a set) the union of the two columns select distinct a as F from table union select distinct b as F from table; ---(end of broadcast)--- TIP 7: don't forget to increase your

[SQL] type conversions

hi SELECT 'tbl'::regclass; works fine SELECT 'tbl'::text::regclass; says cannot convert type text to regclass what to do ? ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] perfomance question

On Thursday 18 March 2004 21:38, you wrote: > Sad, > > > what are perfomance difference bitween > > a) update t1 set f1 = 'x', f2 = 'y'; > > b) update t1 set f1 = 'x', f2 = f2; > > c) update t1 set f1 = 'x'; > > ? &g

[SQL] perfomance question

hello. what are perfomance difference bitween a) update t1 set f1 = 'x', f2 = 'y'; b) update t1 set f1 = 'x', f2 = f2; c) update t1 set f1 = 'x'; ? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if yo

Re: [SQL] bytea or blobs?

On Tuesday 17 February 2004 18:08, you wrote: > I'd recommend to let the application convert the binary > data to and from base64, Don't, please don't ! Since you have the good bytea rule to convert so called "binary" data into so called "text". You have no need another encoding at all. Genera

  1   2   >