Re: [SQL] Finding duplicated values

[Bricklen]

Kent Anderson wrote:
I have a few tables that have duplicated values from an import from a 
different database. I have two keys I tried to set as primary and got an 
error
ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.
 
Is there some join I can use to compare the hmhmkey, wmwmkey pairs 
against the table to find duplicate values? Each pair key should be 
unique but the old database was less than normalized.
 
I was trying to use the code below but it returned no rows.
 
SELECT hmhmkey, wmwmkey
FROM   exceptions
EXCEPT
SELECT hmhmkey, wmwmkey
FROM  exceptions;
 
Any suggestions?
 
Kent Anderson
EZYield.com
407-629-0900
www.ezyield.com 
Try http://archives.postgresql.org/pgsql-sql/1999-03/msg00239.php
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Simple SQL Question

[Bricklen]

Andras Kutrovics wrote:
Hi All!
In one of my application, I would like to implement incremental
fetching. The problem is that I would like to run it
from a stateless application server, so I have to tell where
to begin fetching data. It is simple for a table which has single column
primary key, but causes difficulties (at least for me) in
a more-column primary key..
Let say I have a table wich has the primary key: itemkey,location
table1
--
itemkey
location
...

select * from table1 LIMIT x
gives me the first x row of the result.
After that, I save the last value, and next time, I adjust
the query as
select * from table1 where itemkey>:lastvalue LIMIT x
that should be enough for an 'Item' table, but I have another
column in the primary key.
let say, I have the following in Table1:
itemkeylocation

11
12
...
51
52
53 <--- lets say this is the last value
next time i want to run a query, which starts from
54
65
and so on..
How can I specify that in sql?
I dont want to use cursor:), I would like to do it in plain sql.
(It it is possible).
Thank you in advance
Andras Kutrovics
maybe the OFFSET keyword will help here?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

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

[Bricklen]

Andrei Bintintan wrote:
//Is it possible to have another transatction in a transaction??? In the 
following example the last ROLLBACK is totally ignored(transaction1).

///connect to database/
$database = dbConnect($dbhost, $dbuser, $dbpass, $dbname);
dbExec($database, "BEGIN"); //transaction1
///*/
//dbExec($database, "BEGIN");//transaction2
$sql = "UPDATE orders SET technikernotiz='51' WHERE id=16143";
dbExec($database, $sql);
dbExec($database, "COMMIT");//transaction2
//**/
/
$sql = "UPDATE orders SET reklamationsdetail='51' WHERE id=16143";
dbExec($database, $sql);
dbExec($database, "ROLLBACK");//transaction1
dbClose($database);
 
This appears to be the same as Oracle's "autonomous transactions", fwiw.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] Bucketing Row Data in columns

[bricklen]

Assuming you know your dates beforehand, you could try a CASE
statement. Something like:
select
  order_id,
  sum(case when timestamp::date = 01/01/2009'' then amount else 0 end)
as amount_day1,
  sum(case when timestamp::date = '02/01/2009' then amount else 0 end)
as amount_day2,
  sum(case when timestamp::date = '03/01/2009' then amount else 0 end)
as amount_day3
from orders
group by order_id

On Wed, Jun 24, 2009 at 9:39 AM, Sandeep wrote:
> Hi all,
> I need help on creating a sql, not a problem even if its pl/sql
>
> I have orders table schema is as follow
>
> orders(order_id,user_id, create_timestamp, amount)
>
> and I want to generate a report like
> for the past 3 days bucketing purchases i.e SUM(amount) every day in columns
> i.e result will be having these columns.
>
> (user_id, amount_day1, amount_day2, amount_day3)
>
> ex:
> am leaving order_id assume they are auto incrementing and unique, date
> format dd/mm/
> (user_id, create_timestamp, amount)
> (user1, 01/01/2009,100)
> (user1, 01/01/2009,100)
> (user2, 01/01/2009,100)
> (user2, 02/01/2009,100)
> (user2, 02/01/2009,100)
> (user1, 02/01/2009,100)
> (user2, 03/01/2009,100)
> (user2, 03/01/2009,100)
> (user3, 03/01/2009,100)
>
>
> result
>
> (user_id, amount_day1, amount_day2, amount_day3)
> (user1, 200, 200, 0)
> (user2, 100, 200, 200)
> (user3, 0, 0, 100)
>
>
> hope you guys got what I am trying to generate through sql.
>
> I could get this data in each row, but I want it in columns.
> Can anyone help me on this? lets assume the buckets are fixed i.e 3 only.
> but I wish to get them unlimited i.e day 1 to day 20.
>
> Regards
> Sandeep Bandela

-- 
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] Show CAS, USD first; the left ordered by currency name

[bricklen]

Alternatively,

...
ORDER BY (case when code in ('USD','CAD') then 0 else 1 end),code

On Fri, Jul 31, 2009 at 4:37 AM, Harald Fuchs wrote:
> In article <4a71f9cb.9050...@encs.concordia.ca>,
> Emi Lu  writes:
>
>> Good morning,
>> I have a currency table (code, description).
>
>> Example values:
>>  ADF | Andorran Franc
>>  ... ...
>>  ANG | NL Antillian Guilder
>>  AON | Angolan New Kwanza
>>  AUD | Australian Dollar
>>  AWG | Aruban Florin
>>  BBD | Barbados Dollar
>>  USD | US Dollar
>>  CAD | Canadian Dollar
>
>> Is there a way I can query to display USD AND CAD first, while other
>> rows are ordered by Code.
>
>> For example,
>
>> CAS | Canadian Dollar
>> USD | US Dollar
>> ADF | Andorran Franc
>> ...
>
> Probably the shortest solution is
>
>  SELECT code, description
>  FROM currency
>  ORDER BY code != 'CAD', code != 'USD', code;
>
> BTW: your data are obsolete.  Andorra has the Euro.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

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

[SQL] Rewrite without correlated subqueries

[bricklen]

Hi All,

I'm having some trouble wrapping my head around the syntax to rewrite a
query using correlated subqueries, to using outer joins etc.

The query:

SELECT  ps.userid,
   SUM( ps.hits ) as numhits
FROM primarystats AS ps
  INNER JOIN camp ON camp.id = ps.idcamp
  INNER JOIN sites ON sites.id = ps.idsite
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE
sitescategory.idsites = ps.idsites )
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE
sitescategory.idsites = ps.idsites )
GROUP BY ps.userid;

Because I am rewriting this query to use Greenplum, I cannot use correlated
subqueries (they are not currently supported).

Can anyone suggest a version that will garner the same results? I tried with
OUTER JOINS and some IS NULLs, but I couldn't get it right.

Thanks!

bricklen

Re: [SQL] Rewrite without correlated subqueries

[bricklen]

Interesting idea. Preferably this operation could be done in straight SQL in
a single transaction, to fit in with the way our application works, but if
that's not possible I may need to go the temporary table route.

On Thu, Aug 20, 2009 at 1:40 PM, Mark Fenbers  wrote:

>  Try putting your subqueries into temporary tables, first, inside a BEGIN
> ... COMMIT block.  But your subqueries would produce the negative, i.e.,
> everything except where sitescategory.idsites = ps.idsites.  Then reference
> these temp tables in your query with inner or outer joins as appropriate.
> Your new query would not include the ... IN (  ) syntax...
>
> Mark
>
>
> bricklen wrote:
>
> Hi All,
>
> I'm having some trouble wrapping my head around the syntax to rewrite a
> query using correlated subqueries, to using outer joins etc.
>
> The query:
>
> SELECT  ps.userid,
>SUM( ps.hits ) as numhits
> FROM primarystats AS ps
>   INNER JOIN camp ON camp.id = ps.idcamp
>   INNER JOIN sites ON sites.id = ps.idsite
> WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE
> sitescategory.idsites = ps.idsites )
> AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE
> sitescategory.idsites = ps.idsites )
> GROUP BY ps.userid;
>
> Because I am rewriting this query to use Greenplum, I cannot use correlated
> subqueries (they are not currently supported).
>
> Can anyone suggest a version that will garner the same results? I tried
> with OUTER JOINS and some IS NULLs, but I couldn't get it right.
>
> Thanks!
>
> bricklen
>
>
>

Re: [SQL] Rewrite without correlated subqueries

[bricklen]

On Thu, Aug 20, 2009 at 2:59 PM, Scott Marlowe wrote:

> On Thu, Aug 20, 2009 at 3:16 PM, bricklen wrote:
> > Interesting idea. Preferably this operation could be done in straight SQL
> in
> > a single transaction, to fit in with the way our application works, but
> if
> > that's not possible I may need to go the temporary table route.
>
> Temp tables can be included in a transaction, and they're not visible
> to other connections.
>

Yeah I know, but I was thinking more of replacing this query with vanilla
SQL. Maybe that's just not be feasible.

Re: [SQL] I'm stuck - I just can't get this small FUNCT to run!

[bricklen]

You appear to be missing a trailing semi-colons.

On Wed, Nov 3, 2010 at 5:00 PM, Ralph Smith  wrote:

 Here:

>     vFieldName= ''offer_'' || ''$1''

and here:

>   vBusID=daRec.bus_id

-- 
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] "compressing" consecutive values into one

[bricklen]

On Tue, Nov 23, 2010 at 10:13 AM, Louis-David Mitterrand
 wrote:
> On Tue, Nov 23, 2010 at 03:31:59PM -, Oliveiros d'Azevedo Cristina wrote:
>> Salut, Louis-David,
>>
>> Can you please state the columns belonging to price table
>> and give a concrete example?
>> Say, data before and data after you want to do?
>
> Hi Cristina,
>
> Data before:
>
>        id_price | price
>        
>        1        | 23
>        3        | 45
>        4        | 45
>        6        | 45
>        8        | 45
>        9        | 89
>
> Data after:
>
>        id_price | price
>        
>        1        | 23
>        8        | 45
>        9        | 89
>

select max(id_price),price
from price_table
group by price
order by 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] Union Question

[bricklen]

On Fri, Dec 3, 2010 at 8:58 AM, Plugge, Joe R.  wrote:
> Hello,
>
> I need to union three PostgreSQL tables and this won’t be a problem but the
> tables are on different servers.  Basically, I have an administrative server
> that needs the tables viewable in a web administrator and three query
> servers that log the needed data locally.  Is there a way I can do this
> without using Slony-I to replicate the data to the administrative server?

PL/Proxy might fit the bill also.

http://pgfoundry.org/projects/plproxy/
http://wiki.postgresql.org/wiki/PL/Proxy

-- 
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] " in transaction" can't be killed with pg_cancel_backend(). Is it a bug?

[bricklen]

On Tue, Jan 4, 2011 at 2:47 PM, Bryce Nesbitt  wrote:
> Michael Glaesemann wrote:
>> Likely you're looking for pg_terminate_backend().
>> Michael Glaesemann
>> grzm seespotcode net
>>
>
> Hmm, yes.  Though it seems to not be in the documentation, or, for that
> matter, the current code:

8.4+

-- 
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 to checking the existance of constraints for a table?

[bricklen]

On Wed, Feb 2, 2011 at 12:40 PM, creationw
 wrote:
>
> Hello,
>
> I have a sample table describe as follows, anyone knows how to checking the
> existence of a constraint?
>
> oviddb=# \d myTable
>
>  Column  |   Type   | Modifiers
> -+--+---
>  orderid | smallint | not null
>  modelid | smallint | not null
>
> Indexes:
>    "mytable_orderid_key" UNIQUE, btree (orderid)
>
> For example, how to know that myTable has a constraint with name
> "mytable_orderid_key"?

You could try checking the information_schema.table_constraints view.
Eg. select * from information_schema.table_constraints where
table_name='myTable'

-- 
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] Oracle Equivalent queries in Postgres

[bricklen]

On Wed, Feb 16, 2011 at 2:42 AM, Sivannarayanreddy <
sivannarayanre...@subexworld.com> wrote:

>  Hello,
> I am checking the compatibility of my product with Postgres database and i
> stucked in forming the below oracle equivalent queries in Postgres database,
> Could some one help me pleaseee
>
>
>
There is a sourceforge project that attempts to port the information_schema
to Oracle, though I haven't tried it.

http://sourceforge.net/projects/ora-info-schema/

Re: [SQL] ARRAY_AGG and COUNT

[bricklen]

On Thu, Feb 17, 2011 at 6:20 AM, Andreas Forø Tollefsen
 wrote:
> Hi all!
>
> I have tried the below query, but i cannot find a way to select only
> DISTINCT into the ARRAY_AGG function. I also do not want 0 to be included in
> the array or 0 to be counted.
>
> Code:
>
> SELECT priogrid_land.gid, priogrid_land.cell,
> array_to_string(array_agg(g1id), ';') AS g1list,
> array_to_string(array_agg(g2id), ';') AS g2list,
> array_to_string(array_agg(g3id), ';') AS g3list,
> count(distinct g1id) AS g1count, count(distinct g2id) AS g2count,
> count(distinct g3id) AS g3count
> INTO greg_list
> FROM "GREG", priogrid_land WHERE ST_Intersects("GREG".the_geom,
> priogrid_land.cell)
> GROUP BY priogrid_land.gid, priogrid_land.cell;
>
>
> As you see i.e. in g1list 482 is counted twice in row 2. 0 is also counted.
> The distinct works for count, but not for array_agg.
>

I don't have a version earlier than 8.4, but in 8.4+ you can use
DISTINCT in array_agg().
Eg.
array_to_string(array_agg(distinct g1id),';') as ...

-- 
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] ARRAY_AGG and COUNT

[bricklen]

On Thu, Feb 17, 2011 at 12:11 PM, Andreas Forø Tollefsen
 wrote:
> Great. Thanks. Do you have a suggestion on how to ignore the group id's with
> 0 as value?
> I dont want these to be counted.

You can probably select your values in a subquery and filter out the 0
value results in the WHERE clause, then apply your existing query
(with distinct array_agg) to the outer query.
Or another way would be to use a CASE statement to skip the 0 values.
eg.
array_to_string(array_agg(distinct (case when g1id <> 0 then g1id end)
),';') ...

The array_to_string transformation should trim out the NULLs from the
CASE statement.

-- 
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 function to count all ocurrences of a character within a string.

[bricklen]

On Mon, Mar 7, 2011 at 1:20 PM, Piotr Czekalski  wrote:
> Hello pgsql community,
>
> Is there any string function (other than regex / scan & compare loop) to
> obtain a list (or even a count) of characters within a string?
> strpos and position seems to return only first occurence of a char/string
> within substring.
> The goal is to check if a string contains equal number of opening and
> closing parenthesis, and if there are any of them.
>
> Regards,
>
> Piotr Czekalski

Couple examples here that might help to get you started
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Get_count_of_substrings_in_string

-- 
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] plpgsql exception handling

[bricklen]

On Wed, Mar 9, 2011 at 2:08 PM, Samuel Gendler
 wrote:
> when I run 'select 1count(*) from table' in my postgres 8.4 installation, I
> get the exact same error message.  Assuming the '1count()' function does
> exist, perhaps you need to full qualify it with a schema name?
> It looks to me like the query you are passing to the procedure is invalid
> and is generating the error.  Perhaps the 1 in front of count(*) is a typo?

Also seem to be missing "p_id" from your execute statement:

execute 'create table result_'|| p_id ||' as '||p_sql;

-- 
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] CTE or Subselect and outer joins not behaving as expected?

[bricklen]

On Mon, Oct 24, 2011 at 5:46 PM, Joel Stevenson  wrote:
> Hi all, I'm trying to enumerate a list of months between a date in the past 
> and now and display a value from an existing table if there is one for the 
> date or NULL if there isn't.
>
> I'm using this SQL to generate the months:
>
> select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date 
> as tally_mon
> from generate_series( 0, ( select current_date - '2011-06-01'::date ) ) as 
> tally_day
>
> [[ produces ]]
>  tally_mon
> 
>  2011-06-01
>  2011-07-01
>  2011-08-01
>  2011-09-01
>  2011-10-01
> (5 rows)
>
> and I am trying to use it as the left hand side of a left join against my 
> data table:
>
> select tally_table. tally_mon, met.datum
> from (
>  select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date 
> as tally_mon
>  from generate_series( 0, ( select current_date - '2011-06-01'::date ) ) as 
> tally_day
> ) as tally_table full outer join my_existing_table as met on( tally_mon = 
> met.month )
> where met.series = 1;
> -- ** See SETUP below **
>
> This produces rows only for those that exist in my_existing_table and no left 
> join output which I'd expect for the rest of the tally_table rows.
>
> What am I missing?
>
> Many thanks,
> Joel
>
> SETUP:
> create temp table my_existing_table ( month date not null, series int not 
> null, datum int not null );
> insert into my_existing_table values ( '2011-08-01', 1, 5 ), ( '2011-10-01', 
> 1, 4 );

UNION ALL should do it for you. Something along these lines should work

select tally_mon as mon, max(datum) as datum
from (
select distinct date_trunc( 'month', '2011-06-01'::date + tally_day
)::date as tally_mon,0 as datum from generate_series( 0, ( select
current_date - '2011-06-01'::date ) ) as tally_day
union all
select month as tally_mon,datum from my_existing_table
) as tally_table
group by mon
order by 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] Handling mutliple clients access with views

[bricklen]

On Mon, Oct 24, 2011 at 8:50 PM, Craig Ringer  wrote:
>
> Declarative row-level security (row ownership) would be really nice...
> here's hoping the SELinux work can be extended to support a simpler,
> OS-agnostic non-SELinux-based row-level RBAC mechanism.
> --
> Craig Ringer

Veil might do what you mention above.

http://veil.projects.postgresql.org/curdocs/index.html

-- 
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] intervals

[bricklen]

On Thu, Nov 3, 2011 at 10:29 AM, Edward W. Rouse  wrote:
> expire := '%  days'::interval, limit;

A couple ways spring to mind immediately. Using 10 as the example:

expire := 10 * '1 day'::INTERVAL;
expire := ('10' || ' days')::INTERVAL;

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

[bricklen]

On Fri, Nov 4, 2011 at 7:38 AM, John Fabiani  wrote:
> Hi,
> I just discovered that I can use $$string$$ to account for the problem of
> single quotes in the string (or other strange char's).  However, I noticed
> that the table field contained E'string'.  I actually tried to find info on
> this but I did not find anything.

E'...' is the escape string syntax. You can find examples of it in
this page (among others)
http://www.postgresql.org/docs/9.0/interactive/functions-matching.html
You might want to read up on standard_conforming_strings and
escape_string_warning too, as they influence how the escaping works.

-- 
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] copy from csv, variable filename within a function

[bricklen]

Note: "DELEMITER" should be "DELIMITER".


On Thu, Apr 18, 2013 at 1:34 AM, basti  wrote:

>  I have fixed it with dollar-quoting.
>
>  Original-Nachricht   Betreff: [SQL] copy from csv,
> variable filename within a function  Datum: Thu, 18 Apr 2013 09:26:09
> +0200  Von: bastiAn:
> pgsql-sql@postgresql.org
>
>
>  Hello,
> i have try the following:
>
> -- Function: wetter.copy_ignore_duplicate(character varying)
>
> -- DROP FUNCTION wetter.copy_ignore_duplicate(character varying);
>
> CREATE OR REPLACE FUNCTION wetter.copy_ignore_duplicate(_filename
> character varying)
>   RETURNS void AS
> $BODY$
> declare sql text;
>
> BEGIN
> CREATE TEMP TABLE tmp_raw_data
> (
>   "timestamp" timestamp without time zone NOT NULL,
>   temp_in double precision NOT NULL,
>   pressure double precision NOT NULL,
>   temp_out double precision NOT NULL,
>   humidity double precision NOT NULL,
>   wdir integer NOT NULL,
>   wspeed double precision NOT NULL,
>   CONSTRAINT tmp_raw_data_pkey PRIMARY KEY ("timestamp")
> )
> ON COMMIT DROP;
>
>
> --copy tmp_raw_data(
> --   "timestamp", temp_in, pressure, temp_out, humidity, wdir,
> wspeed)
>
> --FROM '/home/wetter/csv/data/raw/2013/2013-04/2013-04-16.txt'
> --WITH DELIMITER ',';
>
> sql := 'COPY  tmp_raw_data(
> --"timestamp", temp_in, pressure, temp_out, humidity, wdir,
> wspeed) FROM ' || quote_literal(_filename) || 'WITH DELEMITER ',' ';
> execute sql;
>
> -- prevent any other updates while we are merging input (omit this if
> you don't need it)
> LOCK wetter.raw_data IN SHARE ROW EXCLUSIVE MODE;
> -- insert into raw_data table
> INSERT INTO wetter.raw_data(
> "timestamp", temp_in, pressure, temp_out, humidity, wdir,
> wspeed)
>
>SELECT "timestamp", temp_in, pressure, temp_out, humidity, wdir, wspeed
>FROM tmp_raw_data
>WHERE NOT EXISTS (SELECT 1 FROM wetter.raw_data
>  WHERE raw_data.timestamp = tmp_raw_data.timestamp);
> END;
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100;
> ALTER FUNCTION wetter.copy_ignore_duplicate(character varying)
>   OWNER TO postgres;
>
>
>
> But when i execute it i get the this error:
> (sorry i don't know how to switch the error messages to English lang)
> I think this a problem with escaping the delimiter
>
>
> SELECT wetter.copy_ignore_duplicate(
> '/home/wetter/csv/data/raw/2013/2013-04/2013-04-16.txt'
> );
> #
> #
>
>
> HINWEIS:  CREATE TABLE / PRIMARY KEY erstellt implizit einen Index
> »tmp_raw_data_pkey« für Tabelle »tmp_raw_data«
> CONTEXT:  SQL-Anweisung »CREATE TEMP TABLE tmp_raw_data ( "timestamp"
> timestamp without time zone NOT NULL, temp_in double precision NOT NULL,
> pressure double precision NOT NULL, temp_out double precision NOT NULL,
> humidity double precision NOT NULL, wdir integer NOT NULL, wspeed double
> precision NOT NULL, CONSTRAINT tmp_raw_data_pkey PRIMARY KEY
> ("timestamp") ) ON COMMIT DROP«
> PL/pgSQL function "copy_ignore_duplicate" line 4 at SQL-Anweisung
> FEHLER:  Anfrage »SELECT  'COPY  tmp_raw_data(
> --"timestamp", temp_in, pressure, temp_out, humidity, wdir,
> wspeed) FROM ' || quote_literal( $1 ) || 'WITH DELEMITER ',' '« hat 2
> Spalten zurückgegeben
> CONTEXT:  PL/pgSQL-Funktion »copy_ignore_duplicate« Zeile 29 bei Zuweisung
>
> ** Fehler **
>
> FEHLER: Anfrage »SELECT  'COPY  tmp_raw_data(
> --"timestamp", temp_in, pressure, temp_out, humidity, wdir,
> wspeed) FROM ' || quote_literal( $1 ) || 'WITH DELEMITER ',' '« hat 2
> Spalten zurückgegeben
> SQL Status:42601
> Kontext:PL/pgSQL-Funktion »copy_ignore_duplicate« Zeile 29 bei Zuweisung
>
>
>
> --
> 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] value from max row in group by

[bricklen]

On Thu, Jul 25, 2013 at 10:45 AM, Gary Stainburn <
gary.stainb...@ringways.co.uk> wrote:

> Hi folks,
>
> I need help please.
>
> I have a table of trip section details which includes a trip ID, start
> time as
> an offset, and a duration for that section.
>
> I need to extract the full trip duration by adding the highest offset to
> it's
> duration. I can't simply use sum() on the duation as that would not include
> standing time.
>
> Using the data below I would like to get:
>
> 1  | 01:35:00
> 2  | 01:35:00
> 3  | 01:06:00
> 4  | 01:38:00
> 5  | 01:03:00
> 6  | 01:06:00
>

How about using a WINDOW function?

Eg.

select  stts_id, total
from (select stts_id, stts_offset+stts_duration as total, row_number() over
(partition by stts_id order by stts_offset desc) as rank from sts) s
where rank = 1
order by stts_id;

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