Re: [SQL] MSSQL versus Postgres timing

[Bricklen Anderson]

Michael Fuhr wrote:
On Tue, Feb 01, 2005 at 11:54:11AM -0500, Joel Fradkin wrote:
A table with 645,000 records for associates has view (basically select *
from tblassociates where clientnum = 'test')
This is taking 13 seconds in postgres and 3 seconds in MSSQL.

Please post the EXPLAIN ANALYZE output for the slow query, once
with enable_seqscan on and once with it off.  For example:
SET enable_seqscan TO on;  -- if not already on
EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test';
SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test';

Be glad to provide the view and tables etc.

Please do -- it might help us spot something that could be improved.
What version of PostgreSQL are you using?
Also, is clientnum a string datatype, or are you doing implicit type 
conversion?
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Junk queries with variables?

[Bricklen Anderson]

Steve - DND wrote:
I don't know about pgAdmin, but in psql you can use \set:
\set id 1
SELECT * FROM foo WHERE id = :id;
\set name '\'Some Name\''
SELECT * FROM foo WHERE name = :name;

Whenever I try the above I get an error at the backslash. Do I need to
create a different language for this? Right now I only have plpgsql
available.
Thanks,
Steve
\set name text('Some Name')
SELECT * FROM foo WHERE name = :name;
---(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] Building a database from a flat file

[Bricklen Anderson]

Casey T. Deccio wrote:
Question: is there an "easy" way to duplicate an existing schema
(tables, functions, sequences, etc.)--not the data; only the schema?
This way, I would only need to modify one schema (public) to make
changes, and the build schema could be created each time as a duplicate
of the public schema.  Maintenance would be much simpler.
check the docs for pg_dump (-s) for doing structural dumps of your schema.
--
___
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] triggering an external action

[Bricklen Anderson]

Jay Parker wrote:
I am trying to find the best way for a database trigger to signal a 
client process to take an action.

Specifically, I am working on the classic problem of creating and 
modifying system accounts based on the updates to a "person registry" 
database.

The basic model I'm working with has triggers on my tables of interest 
that stick a person's unique ID into a "todo queue" table whenever 
modifications are made.  The queue is periodically polled by a script 
which processes and deletes each "todo" record.  The polling script goes 
to sleep for gradually increasing periods of time whenever it polls the 
queue and finds it empty.

What I want is a trigger on the "todo" table that will "kick" my 
processing script to make it wake up and process the queue immediately. 
 In an Oracle environment, I think I could use a database pipe to 
achieve more or less the behavior I'm looking for, but I can't find 
anything that does what I want in pgsql at the database level.

I could write a trigger in C or Perl or something that would do 
something at the OS level, like send a signal, but when I go down that 
path I start having to deal with unix issues like having to elevate to 
root privs to send a signal to a process that isn't running as the pgsql 
user, etc.  It is doable but gets complex quickly.

Is there anything I'm missing at the database level that would help me 
process updates in realtime?  (And if not, which of the other mailing 
lists would be the most appropriate place for me to discuss the best way 
to implement an OS-level solution?)

Thanks,
-jbp
How about LISTEN and NOTIFY, would they work for this?
--
___
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] SQL command Error: "create table ... Like parentTable

[Bricklen Anderson]

Ying Lu wrote:


Greetings,

I have a simple question about SQL command :

create table tableName1 LIKE parentTable   INCLUDING defaults ;


I was trying to create table "tableName1" with the same structure as 
"parentTable" without any data. I got a syntax error: 'syntax error at 
or near "like" ... '


I guess there must be something wrong with my sql command, could 
somebody help?


Thanks a lot,
Emi


I've never used the "LIKE..INCLUDING" clause before so I can't comment on that, 
but as an alternative, you could try a CTAS:
create table tableName1 as select * from parentTable where 1=0; (will not get 
the rows, just the structure).



Cheers,

Bricklen
--
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

---(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] UPDATEABLE VIEWS ... Examples?

[Bricklen Anderson]

Dmitri Bichko wrote:
> warn "WARNING: dmitrisms are on, some assumptions may not make sense"

beauty!

:)


-- 
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

---(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] Create connection with Oracle database from Postgres plpgsql

[Bricklen Anderson]

Dinesh Pandey wrote:
> How can we create connection with Oracle database from Postgres plpgsql
> function and execute some oracle stored procedure?
> 
> Thanks
> Dinesh
You can use perl DBI to access Oracle, providing you have DBI and the plperlu
language installed.

Sample code that may help you get started (lookout for typos):

create or replace function connect_ora() returns void as $$
use DBI;
&main;
sub main {
  my $query="select 1 from dual";
  my $dbh=openDatabase();
  if ($dbh==0) { return; }
  my $sth = $dbh->prepare( $query, {ora_check_sql => 0} ) || elog NOTICE, "Can't
prepare SQL statement: $DBI::errstr\n";
  $sth->execute() || elog ERROR, "Cant execute SQL statement: $DBI::errstr\n";
  my $array_ref = $sth->fetchall_arrayref();
  $sth->finish();
  $dbh->disconnect() || elog WARNING, "Disconnection from db failed\n";
  RETURN;
}
sub openDatabase {
$dbh =
DBI->connect_cached("dbi:Oracle:host=;sid=;port=",,)
|| elog ERROR, $DBI::errstr;
$dbh->{RowCacheSize} = 100;
  return $dbh;
}
$$ language plperlu;


Customize as you see fit. YMMV

-- 
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Tigger

[Bricklen Anderson]

David Hofmann wrote:
> I've look throught the docs and from what I can see the bellow code
> should work, however I keep getting the error:
> 
> ERROR:  parser: parse error at or near "$" at character 53
> 
> CREATE FUNCTION session_update() RETURNS trigger AS $session_update$
> BEGIN
> -- Check date exists
> IF NEW.stamp_lastupdate IS NULL THEN
> NEW.stamp_lastupdate := 'now';
> END IF;
> RETURN NEW;
> END;
> 
> $session_update$ LANGUAGE plpgsql;
> 
> CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR
> EACH ROW EXECUTE PROCEDURE session_update();
> 
> 
> Any help or suggestions of websites I should read would be appercated.
> 
> David

Which version of postgresql are you using? I don't believe that the "$" quoting
was available in older versions than 8 (or late 7?).

-- 
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Tigger

[Bricklen Anderson]

David Hofmann wrote:
> I'm using 7.3.
> 
>> From: Bricklen Anderson <[EMAIL PROTECTED]>
>> To: David Hofmann <[EMAIL PROTECTED]>
>> CC: pgsql-sql@postgresql.org
>> Subject: Re: [SQL] Tigger
>> Date: Fri, 22 Jul 2005 12:17:41 -0700
>>
>> David Hofmann wrote:
>> > I've look throught the docs and from what I can see the bellow code
>> > should work, however I keep getting the error:
>> >
>> > ERROR:  parser: parse error at or near "$" at character 53
>> >
>> > CREATE FUNCTION session_update() RETURNS trigger AS $session_update$
>> > BEGIN
>> > -- Check date exists
>> > IF NEW.stamp_lastupdate IS NULL THEN
>> > NEW.stamp_lastupdate := 'now';
>> > END IF;
>> > RETURN NEW;
>> > END;
>> >
>> > $session_update$ LANGUAGE plpgsql;
>> >
>> > CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR
>> > EACH ROW EXECUTE PROCEDURE session_update();
>> >
>> >
>> > Any help or suggestions of websites I should read would be appercated.
>> >
>> > David
>>
>> Which version of postgresql are you using? I don't believe that the
>> "$" quoting
>> was available in older versions than 8 (or late 7?).

I don't think that it worked then. Simple test:

CREATE FUNCTION session_update() RETURNS trigger AS '
BEGIN
-- Check date exists
IF NEW.stamp_lastupdate IS NULL THEN
NEW.stamp_lastupdate := ''now'';
END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;

-- 
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

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

[Bricklen Anderson]

Jaime Casanova wrote:
> This seems bad to me also:
> 
>>>CREATE FUNCTION session_update() RETURNS trigger AS $session_update$
>>>[..function body..]
>>>$session_update$ LANGUAGE plpgsql;
> 
> 
> I think it should be:
> CREATE FUNCTION session_update() RETURNS trigger AS $$
> [..function body..]
> $$ LANGUAGE plpgsql;
> 

No, the identifier between the $$ is legit, providing you're at v8 and above.


-- 
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

---(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] how to do a find and replace

[Bricklen Anderson]

Dawn Buie wrote:
> Hello-
> I'm using postgres 7.4
> 
> I have a column of data with the wrong prefix for many items.
> 
> The wrong entries are entered ' /0/v.myimage.jpg'
> While the correct ones are ' /0/myimage.jpg'
> 
> 
> I need to remove all the 'v.' characters from this column.
> 
> 
> I'm able to do a
> 
> SELECT * FROM myTable
> WHERE location = '%/0/v.%'
> 
> 
> I'm just confused about how I should write code to update the selected
> items to remove the 'v.'
> 
> Would I use substring? An example would be much appreciated.
> 
> thank you
> Dawn

How about update myTable set location=replace(location,'v.','');

-- 
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

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

Re: [SQL] how to do a find and replace

[Bricklen Anderson]

Dawn Buie wrote:
> I used:
> 
> update media_instance set location=replace(location,'v.','')
> where location like '%/0/v.%'
> 
> and that did work-
> 
> thank you very much.
> 
> it seems to me that the replace function is the same as translate()- no?
> 
Right, I forgot your WHERE clause.

Some more details on those functions can be found here:
http://www.postgresql.org/docs/8.1/interactive/functions-string.html#FUNCTIONS-STRING-OTHER

-- 
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Defaulting a column to 'now'

[Bricklen Anderson]

Ken Winter wrote:
How can a column’s default be set to ‘now’, meaning ‘now’ as of when 
each row is inserted?


 


For example, here’s a snip of DDL:

 


create table personal_data (…

effective_date_and_time TIMESTAMP WITH TIME ZONE not null default 'now',…



try with now(), instead of now
...
effective_date_and_time TIMESTAMPTZ not null default now()...

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

Re: [SQL] Arrays in PL/pgSQL routines?

[Bricklen Anderson]

Ken Winter wrote:

Can arrays be declared in PL/pgSQL routines?  If so, how?
 



DECLARE


try:
my_array VARCHAR[] := '{}';

not sure if this works in 7.4 though, if that's the version that you are 
using.


---(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] Syntax for "IF" clause in SELECT

[Bricklen Anderson]

[EMAIL PROTECTED] wrote:

Greetings,

the following is an MySQL statement that I would like to
translate to PostgreSQL:

Could someone point me to a documentation of a coresponding
Systax for an "IF" clause in the a SELECT, 
or is the some other way to do this


select
 if(spektrum is null,' ','J'),
 if(s19 is null,' ','J'),
 if(OhneGrenze is null,' ','J'),
 from namen;


Do I need to create my own function to allow this behaviour!


my best regards,

Stefan


use CASE

Since I'm not a user of MySQL, and if I'm reading your query correctly:
try
select (CASE when spektrum is null then 'J' else spektrum end),
...

or if you are just trying to replace nulls, then try COALESCE

---(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] Query from shell

[Bricklen Anderson]

Owen Jacobson wrote:

Judith wrote:


  Hi every body, somebody can show me hot to execute a 
query from a shell



echo QUERY HERE | psql databasename

Or, if you want to run several queries, run psql and run your queries there.


or
psql -d  -c "your query here"

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Get max value from an comma separated string

[Bricklen Anderson]

Mauro Bertoli wrote:

Hi, I've a field that contain values-comma-separated
like
A) 1;2;3;;5  -- ;2;;4;5
but also
B) 12;34;18
how I can get the max value?
For A I tried:
SELECT max(array_upper(string_to_array(answer,';'),1))
FROM values;
and work fine, but for B case I don't find a solution
like
SELECT max(string_to_array(answer,';')) FROM values;

Any ideas?
Thanks for any hint


You could try rearranging the values into rows, like so:

CREATE OR REPLACE FUNCTION text2rows (TEXT,TEXT) RETURNS SETOF TEXT AS $$
SELECT (string_to_array($1, $2))[x.i]
FROM generate_series(1,array_upper(string_to_array($1,$2),1)) AS x(i);
$$ language sql strict;

select max(val)
from (SELECT text2rows(answer,';') as val FROM answer) as t;

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Concat two fields into one at runtime

[Bricklen Anderson]

George Handin wrote:
Is there a way using built-in PostgreSQL functions to combine two data 
fields into a single field at runtime when querying data?


For example, the query now returns:

idfirstlast
---   ---  --
1 Goerge   Handin
2 Joe  Rachin

I'd like it to return:

idname
---   ---
1 George Handin
2 Joe Rachin


select id,first||' '||last from your_table

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] "CASE" is not a variable

[Bricklen Anderson]

Keith Worthington wrote:

"Keith Worthington" <[EMAIL PROTECTED]> writes:
The following is a section of code inside an SQL function.

On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote
SQL, or plpgsql?  It looks to me like misuse of the plpgsql INTO clause
(there can be only one).

regards, tom lane


plpgsql

This is part of a function inside a v8.0.2 database.

I didn't realize that the INTO clause was only limited to one instance.  I was
trying to accomplish

SELECT col_a INTO var1,
   col_b INTO var2,
   col_c INTO var3,
   ...
  FROM foo
 WHERE fobar;

Kind Regards,
Keith


try it like
select col_a,col_b,col_c INTO va1,var2,var3 

not sure if 8.0.2 allows you to do that, however.

---(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] hi let me know the solution to this question

[Bricklen Anderson]

Aaron Bono wrote:

On 7/18/06, *Michael Fuhr* <[EMAIL PROTECTED] > wrote:




http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS


 
OK, this question got me wondering: is there a way to determine, in a 
function/stored procedure, the number of rows that were 
inserted/updated/deleted?


I could not find any documentation about getting the count of updates 
but when you run the update by itself, PostgreSQL reports the number of 
records updated so I have to believe the information is available 
somewhere.


Thanks,
Aaron

Look for the section entitled "36.6.6. Obtaining the Result Status" on 
the link that Michael Fuhr supplied (above). Is that what you are 
looking for?


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

Re: [SQL] spliting a row to make several rows

[Bricklen Anderson]

Gerardo Herzig wrote:

Hi all: What a want to do is something like this:
suppose i have this record

aa--bb--cc

I guess if im able to do some sql/plsql procedure to get something like it
aa
bb
cc
(3 records, rigth?)

Thanks a lot
Gerardo



dev=#select split_to_rows('aa--bb--cc','--');

split_to_rows
---
 aa
 bb
 cc
(3 rows)


This function was written by David Fetter,
http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php

CREATE OR REPLACE FUNCTION split_to_rows(TEXT,TEXT) RETURNS SETOF TEXT
AS $$
SELECT (string_to_array($1, $2))[s.i]
FROM generate_series(
1,
array_upper(string_to_array($1, $2), 1)
) AS s(i);
$$ language sql strict;

---(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] hiding column values for specific rows

[Bricklen Anderson]

Luca Ferrari wrote:

Hi,
I don't know if this's possible but I'd like to hide column values for 
specific rows within a query. Imagine I've got a table with columns username 
and password: users(username,password). Now I'd like the user registered in 
the table to see her password, to see who is registered but not to see the 
other people passwords. For example, if the table contains:

username  | password
--+-
luca| myPaswd
roberto   | otherPaswd
gianna| thirdPaswd

I'd like to do a query like: "SELECT * FROM users where username=luca" 
obtaining something like:

username  | password
--+-
luca| myPaswd
roberto   | x
gianna| X

Is it possible to specify in the above query that all password columns that do 
not belong to selected row (username=luca) must be hidden? Anyone has an idea 
about how to implement this on the database side?


Thanks,
Luca


You could try:
select username,case when username='luca' then password else 'XX' 
end as password from users;


Note that the the username 'luca' corresponds to the user querying the 
table.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Using Control Flow Functions in a SELECT Statement

[Bricklen Anderson]

Ashish Ahlawat wrote:
 
Hi Team
 
I am unable to fetch data using following simple query it prompts 
following error
 
*ORA: 00907: Missing right parenthesis*


Query :-

SELECT

Name AS Title, StatID AS Status, RatingID AS Rating,

IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify



i. That's an Oracle error message.
ii. IF does not belong in an SQL query. Use CASE.


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

Re: [SQL] to_chat(bigint)

[Bricklen Anderson]

Ezequias Rodrigues da Rocha wrote:

Hi list,

Now I noticed that it is impossible to convert a bigint field to char 
with the function to_char. Is it correct ?


If not please tell me how to convert a bigint using to_char.


Couple ways I can see immedately:

select 123123123123123123123::BIGINT::TEXT;
or
select 
to_char(12312312312312312312::BIGINT,'fm99');


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

Re: [SQL] interval as hours or minutes ?

[Bricklen Anderson]

Aarni Ruuhimäki wrote:

Hi all,

Could anyone please tell an easy way to get total hours or minutes from an 
interval ?


SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE 
user_id = 1;

tot_time
-
 2 days 14:08:44

I'd like to have this like ... AS tot_hours ...
tot_hours
-
62

and ... AS tot_minutes ...
tot_minutes
-
3728

Maybe even ... AS tot_hours_minutes_seconds
tot_hours_minutes_seconds
-
62:08:44


start_date_time and stop_date_time are stored as timestamp without time zone, 
using Pg 8.1.5 on CentOs 4.4


???

Thanks,



I have been using the following function (watch for line wrap)

CREATE OR REPLACE function convert_interval(interval,text) returns text 
as $$

declare
retval TEXT;
my_interval INTERVAL := $1;
my_type TEXT := $2;
qry TEXT;
begin
if my_type ~* 'hour' then
select into retval extract(epoch from 
my_interval::interval)/3600 || ' hours';

elsif my_type ~* 'min' then
select into retval extract(epoch from my_interval::interval)/60 
|| ' minutes';

elsif my_type ~* 'day' then
select into retval extract(epoch from 
my_interval::interval)/86400 || ' days';

elsif my_type ~* 'sec' then
select into retval extract(epoch from my_interval::interval) || 
' seconds';

end if;
RETURN retval;
end;
$$ language plpgsql strict immutable;

pqsl=# select convert_interval(now() - (now()-interval '1 day 4 hours 6 
minutes'),'minutes') as minutes;

   minutes
--
 1686 minutes

There may be something built-in now, but I haven't looked recently.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Regular Expressions

[Bricklen Anderson]

Ezequias R. da Rocha wrote:

Hi list,

I would like to know if postgresql has a Regular Expressions (Regex) 
implemented already.


With it we could implement queries like

Select * from myClientes where name = 'E[zs]equias'

where the result occurs even if the field has Ezequias or Esequias.

Regards
Ezequias


Pretty easy to find matches in the documentation at 
http://search.postgresql.org/


eg.
http://www.postgresql.org/docs/8.2/interactive/functions-matching.html

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

Re: [SQL] request for help with COPY syntax

[Bricklen Anderson]

Chuck D. wrote:

Pardon me on this, the cat -A report for the failed line (and subsequent 
lines) shows ^M$ within the field, not just $.


I assume that is probably a \r\n and postgres wants \r for field data and \n 
to end a line.


I've tried working this over with sed but can't get the syntax right.  I also 
have iconv installed if that would help any.  Are there any good tools that 
will tell me what this really is instead of just ^M$ ?


If all you just want to do is strip out the ^M, you can run dos2unix on 
it, assuming that you are running a *nix distro.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date

[Bricklen Anderson]

Emi Lu wrote:

Good morning,

Could someone tell me the command to get the weekly day name and day 
number please.



I am expecting something like:

sql> select data_part('day name', current_date);
sql> Monday

sql> select data_part('day number', current_date);
sql> 1

(Mon =1 ... Sun =7?)

Thanks a lot!



http://www.postgresql.org/docs/current/static/functions-formatting.html


select to_char(current_date,'Day');
select to_char(current_date,'D');

--
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] Function Syntax involving pipes and ' marks?

[Bricklen Anderson]

Ruben Gouveia wrote:

v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'||
' and m.jb_date >='||p_date||'';


Try wrapping your p_date in a quote_literal like
...
'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ...

eg.

CREATE OR REPLACE FUNCTION fcn_job(p_date date, p_type varchar,p_jobid 
numeric) RETURNS numeric AS $$

DECLARE
v_job numeric := 0;
v_stmt varchar(1024);
v_where varchar(256) := 'where m.jb_date < '||quote_literal(p_date 
+ integer '1')||' and m.jb_date >='||quote_literal(p_date);

BEGIN
v_stmt := fcn_gen_statement(p_type, v_where, p_newonly);
execute v_stmt into v_job;
RAISE NOTICE 'sql looks like this: % . ',v_stmt;
return v_job;
END;
$$ LANGUAGE 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] Function Syntax involving pipes and ' marks?

[Bricklen Anderson]

Ruben Gouveia wrote:

Is that more expensive to run than just useing a bunch of ticks?



Try wrapping your p_date in a quote_literal like
...
'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ...


I personally have never noticed any increased overhead from quote_literal.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql