[SQL] INSERT INTO ... SELECT

[Silke Trissl]

Hi,

I would like to insert into a table values from a table and user defined 
ones. Here is the example:

I found this statement to insert values from another table:

INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER;

But the test_table has another column, which should have the same value 
for all the customers.

Is there something like

INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, name 
from CUSTOMER:

and if so, what ist the correct statement? If not, what is an 
alternative to insert a single row at a time?

Thanks in advance

Silke

---(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

[SQL] date format in 7.4

[Silke Trissl]

Hi,

I have an application where users can enter the date via a web interface.

Recently I upgrated my PostgreSQL version from 7.3 to 7.4.1.

On 7.3 I run several tests about the format of the date and found,
that Postgres accepts almost everything. Today I found out, that 7.4.1 
only accepts dates in the format mm-dd-yy, although the documentation 
still states the following
#

   5.

  Otherwise the date field ordering is assumed to follow the 
DateStyle setting: mm-dd-yy, dd-mm-yy, or yy-mm-dd. Throw an error if a 
month or day field is found to be out of range.

#
ref: http://www.postgresql.org/docs/7.4/interactive/datetime-appendix.html
Does anyone know, if it is intentional to restrict it to mm-dd-yy format 
or is is just a bug of 7.4.1?

Thanks in advance for any suggestions

Silke

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] designer tool connect to PostgreSQL

[Silke Trissl]

[EMAIL PROTECTED] schrieb:
Hi,

i use postgresql as my database. does anyone know the designer tool that 
can connect to postgeSQL ??? meaning to say the tools
can handle design task like create table , etc . appreciate if u can 
give the specific URL. thanks in advance.


Try this one:

http://www.aquafold.com/

Here you can connect not only to Postgres, but also to MySQL, Oracle, 
... And you can handle several databases, even on remote systems. It's 
really good designed.

Silke

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[SQL] Using timestamp in function

[Silke Trissl]

Hi,
I am using PostgreSQL 7.4 and was trying to log the time each part of a 
function needs. I found a pretty helpful bit of code in the documentation:

http://www.postgresql.org/docs/7.4/static/plpgsql-expressions.html
I used the following function, called inside the another function:
CREATE or replace FUNCTION mylog_test(integer, varchar) RETURNS 
timestamp AS '
DECLARE
n ALIAS FOR $1;
logtxt ALIAS FOR $2;
curtime timestamp;
BEGIN
curtime := ''now'';
--INSERT INTO logger VALUES ( nextval(''seq_log''), curtime, 
substr(logtxt,0,200));
RAISE NOTICE ''TIME: %'',curtime;
RETURN curtime;
END;
' LANGUAGE plpgsql;

I expected, that the variable curtime gets a new time value, each time 
the function is called (at least that is what I understood from the 
documentation). This works fine, if I test it with
SELECT mylog_test(5, 'test');
But as soon as I call the funtion from another function (which I need) 
the variable curtime does not change anymore.

Can anyone tell me why this does not work and does anyone know a 
solution to this?

For test purposes here is a function called test, which does nothing 
else than to call mylog_test(..) and spend some time calculating.

CREATE or replace FUNCTION test() RETURNS text AS '
  DECLARE
i integer;
j integer;
k integer;
  BEGIN
FOR i IN 1..10 LOOP
  PERFORM mylog(3, ''val '' || i);
  FOR j IN 1..200 LOOP
k:=j;
  END LOOP;
END LOOP;
RETURN ''THE END'';
  END;
' LANGUAGE 'plpgsql';
SELECT test();
Any help is appreciated
Silke
---(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

[SQL] Problem on Geometric functions

[Silke Trissl]

Hello,
I have a table that has an attribute 'identifier', declared as integer 
and an attribute 'plane_coord' defined as 'point':

Table "reference.coord_test"
   Column|  Type   | Modifiers
-+-+---
 node_name   | integer |
 plane_coord | point   |
I would like to find all points from the table that are within a square. 
Is this possible to do so? I have just found a check-operator to find 
out if a specified point is contained in or on a figure:

point '(1,1)' @ box '((0,0),(2,2))'
(on http://www.postgresql.org/docs/7.4/interactive/functions-geometry.html).
I am looking for something like
SELECT point(x,y)
FROM reference.coord_test
WHERE point(x,y) € box '((0,0), (2,2))';
Has anyone experience with that?
Regards,
Silke
By the way, I am using PostGreSQL 7.4.1
---(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

[SQL] Indexing an array?

[Silke Trissl]

Hi,

I have a problem with arrays in Postgres. I want to create a really
large array, lets say 3 billion characters long.

As far I could read from the documentation - this should be possible.
But my question is, is there a kind of index on the array.

Lets say, I want to get element 2,675,345,328. Does Postgres have to
load the entire array into memory and then run through the 2.6 billion
characters to return the one I want or does Postgres have an index - as
where to find this element on disk?

Any advice is welcome and thanks in advance

Silke Trißl




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL]

[Silke Trissl]

Илья Конюхов wrote:
>>For example, consider the queres:
>>
>>SELECT * FROM table1 WHERE field1=1 AND field2=1;
>>
>>SELECT * FROM table1 WHERE field2=1 AND field1=1;
>>
>>
>>These two queries are logically equivalent. But in all cases the planner 
>>generates a query plan that performs field1=1 condition, and then field2=1 
>>condition, as there is a index on field1.
>>
>>Is it possible to instruct the PostgreSQL query planner to perform field2=1 
>>condition first, and then field1=1 condition?
>>
You might get the desired result, if you switch off the index scan:

set ENABLE_INDEXSCAN = OFF.

But there is no way to tell Postgres what to use first. Usually the
query planer is quite good, so there is no reason to fiddle around. And
why on earth would you like that. In the end you get the same result.


Hope, that helps

Silke


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster