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)

On 06/26/10 17:09, wrote: On 2010-06-25, Lee Hachadoorian wrote: Is it documented anywhere that floating-point numbers round "scientifically", that is 0.5 rounds to the nearest even number? That's swiss rounding. And no, as I understand it documented that most arithmetic) is platform specif

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