Re: [SQL] Inserting values in arrays
[EMAIL PROTECTED] wrote:
CREATE TABLE test ( details varchar[]);
CREATE TABLE test2 ( textvalue1 varchar, textvalue2 varchar);
INSERT INTO test2 VALUES ('Hello1', 'World1');
INSERT INTO test2 VALUES ('hello2', 'World2');
and I am expecting the following rows in test
{'Hello1', 'World1'}
{'Hello2', 'World2'}
Postgres version I am using is 7.3.4
Well, from 7.4 you can do:
INSERT INTO test SELECT ARRAY[textvalue1, textvalue2] FROM test2;
INSERT 0 2
richardh=> SELECT * FROM test;
details
-
{Hello1,World1}
{hello2,World2}
(2 rows)
I think in 7.3 you might have to write your own function to assemble the
array. I'm not an array expert though, so might be worth checking the
mailing list archives.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] select multiple immediate values, but in multiple rows
Luca Pireddu wrote: Hello all. I'd like to write a query does a set subtraction A - B, but A is is a set of constants that I need to provide in the query as immediate values. I thought of something like select a from (1,2,3.4) except select col_name from table; richardh=> SELECT * FROM foo; a --- 1 2 3 (3 rows) richardh=> SELECT * FROM foo WHERE a NOT IN (1,2); a --- 3 (1 row) Run some tests with nulls in the column and the constant list too so you understand what happens in those cases. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Inserting values in arrays
Richard,
So the solution can be:
(i)
either write a function to insert the values into the array one by one
(ii)
or else upgrade to 7.4 (or 8) to use the ARRAY syntax.
Thanks a lot.
Regards
Robert
Richard Huxton
03/15/2005 09:08 AM
To
[EMAIL PROTECTED]
cc
pgsql-sql@postgresql.org
Subject
Re: [SQL] Inserting values in arrays
[EMAIL PROTECTED] wrote:
> CREATE TABLE test ( details varchar[]);
> CREATE TABLE test2 ( textvalue1 varchar, textvalue2 varchar);
> INSERT INTO test2 VALUES ('Hello1', 'World1');
> INSERT INTO test2 VALUES ('hello2', 'World2');
> and I am expecting the following rows in test
> {'Hello1', 'World1'}
> {'Hello2', 'World2'}
> Postgres version I am using is 7.3.4
Well, from 7.4 you can do:
INSERT INTO test SELECT ARRAY[textvalue1, textvalue2] FROM test2;
INSERT 0 2
richardh=> SELECT * FROM test;
details
-
{Hello1,World1}
{hello2,World2}
(2 rows)
I think in 7.3 you might have to write your own function to assemble the
array. I'm not an array expert though, so might be worth checking the
mailing list archives.
--
Richard Huxton
Archonet Ltd
Re: [SQL] How to cast VARCHAR to BYTEA and vice-versa?
Seems my remembrance was wrong, ::TEXT::BYTEA causes
ERROR: cannot cast type text to bytea
on my 7.4.5 version too.
Looking into "Other Binary String Functions" within the manual,
sheds light on how to do it:
::TEXT::BYTEA
Function decode(string text, type text) Return Type bytea
select decode('123\\000456'::text, 'escape');
decode
123\000456
(1 row)
::BYTEA::TEXT
Function encode(string bytea, type text) Return Type text
select encode('123\\000456'::bytea, 'escape');
encode
123\000456
(1 row)
Sorry for the confusion I have caused,
especially because there already was a correct answer
sent by Ragnar.
Regards, Christoph
Lance Peterson wrote:
>
> Christoph, is that recent functionality? I'm running 7.4 and I get:
> ERROR: cannot cast type text to bytea
>
> I get similar errors when trying to go the other direction.
>
> On Mon, 14 Mar 2005 11:32:26 +0100, Christoph Haller <[EMAIL PROTECTED]>
> wrote:
> > "Moran.Michael" wrote:
> > >
> > > Hello all,
> > >
> > > I have a table with a VARCHAR column that I need to convert to a BYTEA.
> > >
> > > How do I cast VARCHAR to BYTEA?
> > >
> > > The following doesn't seem to work as it yields the 'cannot cast varchar
> > > to
> > > bytea' error message:
> > >
> > > varchar_data::bytea
> > >
> > > On the same topic, how do I do the reverse, that is, how to I cast from
> > > BYTEA back to VARCHAR?
> > >
> > > Thanks,
> > > -Michael Moran
> > >
> > >
> > As far as I remember
> > varchar_data::TEXT::BYTEA
> > works. Vice versa dto.
> > Be aware BYTEA data may contain character codes
> > unconvertable to VARCHAR, especially '\0'.
> >
> > Regards, Christoph
> >
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] comparing 2 tables
I've two Ms Access tables with the same structure. Tablestructure is as follows: Table table1 SbPartNo char(50) PartDesc char(200) manPartNo char(200) manufacturer char(100) vendor char(100) refDesi char(200) qty char(5) My requirement is as follows: table 1 partDesc1 11 xyz1 vendor1 refdesi1 2 partDesc2 22 xyz2 vendor2 refdesi2 4 partDesc3 33 xyz3 vendor3 refdesi3 6 table 2 partDesc2 22 xyz2 vendor2 refdesi2 4 partDesc3 343434 xyz3 vendor3 refdesi3 6 partDesc4 44 xyz4 vendor4 refDesi4 8 Table 3 structure is: oldSbPartNo char(50) oldPartDesc char(200) oldmanPartNo char(200) oldmanufacturer char(100) oldvendor char(100) oldrefDesi char(200) oldqty char(5) newSbPartNo char(50) newPartDesc char(200) newmanPartNo char(200) newmanufacturer char(100) newvendor char(100) newrefDesi char(200) newqty char(5) After comparing the above 2 tables, i want the result to be stored in third table as follows. partDesc1 11 xyz1 vendor1 refdesi1 2 null null null null null null null partDesc3 33 xyz3 vendor3 refdesi3 6 partDesc3 343434 xyz3 vendor3 refdesi3 6 null null null null null null null partDesc4 44 xyz4 vendor4 refDesi4 8 Here what we should observe is, 1st row in table 1 not present in table2. 2nd row in table1 is modified in table2. 3rd row in table 2 is not present in table1. So, after comparing table 1 and table2, the differences are captured as old and new. Old referes to table1 data and new referes to table2 data. The rows that are equal in both table 1 and table 2 should not be captured in table 3. Is itpossible to do this in Ms Access? If so, how. I appreciate if anyone can provide me the sql syntax for it. I'm using this for a web application. So, pure SQL is needed. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Parameterized views proposition
... > > I wonder if it could be possible to improve CREATE VIEW > syntax by adding parameters? Something like this: > > CREATE VIEW product_sales(date,date) AS > select p.product_id, coalesce(sum(s.amount), 0) > from product p > left join sales s on p.product_id = s.product_id and s.date > between $1 and $2 group by p.product_id > I noticed that braces after view name are already reserved for renaming view columns. OK, then this syntax won't do. Isn't there some suggestion in SQL standards? Quick search with Google revealed only that FoxPro has parameterized views, but the syntax is quite different. I've heard people talking about parameterized views since I started database programming. So, PostgreSQL could be the first to really implement them :). How tough it would be to implement CREATE VIEW xxx PARAMETERS (yyy) AS zzz; as CREATE TYPE xxx; CREATE FUNCTION xxx(yyy) RETURNING SETOF xxx LANGUAGE sql AS 'zzz'; Would you suggest it as first step in hacking PostgreSQL sources? Tambet ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] comparing 2 tables
U K Laxmi wrote: I've two Ms Access tables with the same structure. This is a PostgreSQL list - you'd probably be better using a Microsoft-Access list. [snip] Here what we should observe is, 1st row in table 1 not present in table2. 2nd row in table1 is modified in table2. 3rd row in table 2 is not present in table1. So, after comparing table 1 and table2, the differences are captured as old and new. Old referes to table1 data and new referes to table2 data. The rows that are equal in both table 1 and table 2 should not be captured in table 3. Is itpossible to do this in Ms Access? If so, how. In general terms, with MS-Access, I'd build separate queries for each step. So you'd want a query for: 1. Reset table3 2. Insert to table3 rows in table1 and not in table2 3. Insert to table3 rows in table2 and not in table1 ...etc... HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] comparing 2 tables
Thank you for the reply. Sorry for posting this problem here. Thought SQL syntax for Ms Access and PostgreSQL are similar. So, i posted here. My problem is: I've original version of a table called PMM (Product Material Master). Thro' a web interface, user can change that table contents. Once changed i need to raise a ECN (Engineering CHange Note) specifying what changes happened to original PMM table whether rows are deleted, new rows are added or existing rows are modified etc. I've both old and new version of PMM tables. The difference between two PMM tables are captured in a third table called ECN and it has both original & new PMM table entries which are not same. Table table1 SbPartNo char(50) PartDesc char(200) manPartNo char(200) manufacturer char(100) vendor char(100) refDesi char(200) qty char(5) My requirement is as follows: table 1 partDesc1 11 xyz1 vendor1 refdesi1 2 partDesc2 22 xyz2 vendor2 refdesi2 4 partDesc3 33 xyz3 vendor3 refdesi3 6 table 2 partDesc2 22 xyz2 vendor2 refdesi2 4 partDesc3 343434 xyz3 vendor3 refdesi3 6 partDesc4 44 xyz4 vendor4 refDesi4 8 Table 3 structure is: oldSbPartNo char(50) oldPartDesc char(200) oldmanPartNo char(200) oldmanufacturer char(100) oldvendor char(100) oldrefDesi char(200) oldqty char(5) newSbPartNo char(50) newPartDesc char(200) newmanPartNo char(200) newmanufacturer char(100) newvendor char(100) newrefDesi char(200) newqty char(5) After comparing the above 2 tables, i want the result to be stored in third table as follows. partDesc1 11 xyz1 vendor1 refdesi1 2 null null null null null null null (null for new entries b'cozthis row is deleted in new version ofPMM table) partDesc3 33 xyz3 vendor3 refdesi3 6 partDesc3 343434 xyz3 vendor3 refdesi3 6 (this row has entries for both old & new fields b'coz this row is modified from original one) null null null null null null null partDesc4 44 xyz4 vendor4 refDesi4 8 (this row has old entries as null, bcoz this is a new row of data that is being added to original PMM table) I already posted this issue to 3 of the free web forums for MS Access. No reply yet. So, thought i will post here. If it's not acceptible, i will stop this issue in this list. If somebody can give some insights to this problem, it will be a great help. I won't mind implementing this logic in multiple queries. Thank you so much. --- Richard Huxton wrote: > U K Laxmi wrote: > > I've two Ms Access tables with the same structure. > > This is a PostgreSQL list - you'd probably be better > using a > Microsoft-Access list. > > [snip] > > Here what we should observe is, 1st row in table 1 > not > > present in table2. 2nd row in table1 is modified > in > > table2. 3rd row in table 2 is not present in > table1. > > So, after comparing table 1 and table2, the > > differences are captured as old and new. Old > referes > > to table1 data and new referes to table2 data. The > > rows that are equal in both table 1 and table 2 > should > > not be captured in table 3. > > > > Is itpossible to do this in Ms Access? If so, how. > > In general terms, with MS-Access, I'd build separate > queries for each > step. So you'd want a query for: > 1. Reset table3 > 2. Insert to table3 rows in table1 and not in table2 > 3. Insert to table3 rows in table2 and not in table1 > ...etc... > > HTH > -- >Richard Huxton >Archonet Ltd > __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] How to force subquery scan?
Another (possibly design?) problem of mine. I have a function product_cost(product_id, date), which does simple SELECT call. I declared it as STABLE, hoping that multiple invocations of the same function are replaced with one. Query is something like this: SELECT p.product_id, avg(product_cost(s.product_id, s.date)) as average_cost, sum(product_cost(s.product_id, s.date) * s.amount) as cost_total FROM products p LEFT JOIN sales s ON s.date between '2004-01-01' and '2005-01-01' GROUP BY p.product_id; (For those interested in the intent of the query - imagine there is internal cost associated with a product, which is different in different periods. There is no cost column in sales table, because it might change, for previous periods too). When I ran the query for long periods I observed that my assumption about STABLE was wrong. It did not help to reduce function invocations, as one could think after reading the documentation. It was also confirmed in mailing lists, that STABLE only allows function to be used in index scan, there is no function result cacheing. I was able to reduce function calls to just one per row by using subquery: SELECT p.product_id, avg(s.cost) as average_cost, sum(s.cost * s.amount) as cost_total FROM products p LEFT JOIN (SELECT *, product_cost(product_id, date) as cost FROM sales) s ON s.date between '2004-01-01' and '2005-01-01' GROUP BY p.product_id; But it did work only as long I used LEFT JOIN. When I used regular JOIN, the optimizer happily optimized subquery scan to just table scan and elevated the function call to next query level, where it was executed twice. My question is, is there a trick that would force subquery scan when I want it? Tambet ---(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] comparing 2 tables
U K Laxmi wrote: Thank you for the reply. Sorry for posting this problem here. Thought SQL syntax for Ms Access and PostgreSQL are similar. So, i posted here. My problem is: I've original version of a table called PMM (Product Material Master). Thro' a web interface, user can change that table contents. Once changed i need to raise a ECN (Engineering CHange Note) specifying what changes happened to original PMM table whether rows are deleted, new rows are added or existing rows are modified etc. I've both old and new version of PMM tables. The difference between two PMM tables are captured in a third table called ECN and it has both original & new PMM table entries which are not same. Another option I've used in similar cases is to add a "version" column to the relevant tables (PMM in your case). Users can only change rows with version="editing" (or "live" or other code) and after review can confirm their changes. This makes a copy of all the data but with a new version-code (usually auto-generated). You can now compare any two versions to track changes. This system works well if you have relatively infrequent changes in large batches. In my case it was company-profile data (services, specialisations, contact personnel etc) and users would update their data at most every few months. I actually had a review phase in my system between editing and publishing a new version of a company's data. -- Richard Huxton Archonet Ltd ---(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] Generic Function
- Original Message - From: <[EMAIL PROTECTED]> To: Sent: Monday, March 14, 2005 12:15 PM Subject: [SQL] Generic Function Hi, Can I built a generic function like: CREATE FUNCTION f (text) RETURNS TEXT as $$ return 'select * from $1'; $$ I know its impossible as writed. Also I have looked for EXECUTE procedure but it not run the correct function. If you show us what you've tried and the results you received we may be able to help more. Some points: 1. To create a string to run with EXECUTE you would need to concatenate the above phrase: (Assuming "query" has been declared as text): query := 'Select * from ' || $1; 2. Since you're likely returning multiple rows, you need to write your function as a set-returning function. See: http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING and Executing Dynamic Commands in http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-PERFORM Is there a way to construct this clause? Using plpgsql/pltcl/anything ??? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to force subquery scan?
Tambet Matiisen wrote: > > Another (possibly design?) problem of mine. I have a function > product_cost(product_id, date), which does simple SELECT call. I > declared it as STABLE, hoping that multiple invocations of the same > function are replaced with one. Query is something like this: > > SELECT > p.product_id, > avg(product_cost(s.product_id, s.date)) as average_cost, > sum(product_cost(s.product_id, s.date) * s.amount) as cost_total > FROM products p > LEFT JOIN sales s ON s.date between '2004-01-01' and '2005-01-01' > GROUP BY p.product_id; > > (For those interested in the intent of the query - imagine there is > internal cost associated with a product, which is different in different > periods. There is no cost column in sales table, because it might > change, for previous periods too). > > When I ran the query for long periods I observed that my assumption > about STABLE was wrong. It did not help to reduce function invocations, > as one could think after reading the documentation. It was also > confirmed in mailing lists, that STABLE only allows function to be used > in index scan, there is no function result cacheing. > > I was able to reduce function calls to just one per row by using > subquery: > > SELECT > p.product_id, > avg(s.cost) as average_cost, > sum(s.cost * s.amount) as cost_total > FROM products p > LEFT JOIN (SELECT *, product_cost(product_id, date) as cost FROM sales) > s ON s.date between '2004-01-01' and '2005-01-01' > GROUP BY p.product_id; > > But it did work only as long I used LEFT JOIN. When I used regular JOIN, > the optimizer happily optimized subquery scan to just table scan and > elevated the function call to next query level, where it was executed > twice. My question is, is there a trick that would force subquery scan > when I want it? > > Tambet > Does this do better: SELECT prodid, avg(prodcost) as average_cost, sum(prodcost * salesamount) as cost_total FROM ( SELECT p.product_id as prodid, product_cost(s.product_id, s.date) as prodcost, s.amount as salesamount FROM products p LEFT JOIN sales s ON s.date between '2004-01-01' and '2005-01-01' ) q GROUP BY prodid; Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] outputting dates
Kenneth Gonsalves wrote: > > hi, > i have set my datetype to 'European, SQL'. This correctly accepts > dates of the form dd/mm/. However, the output is still in the > '-mm-dd' format. How do i get the default output as dd/mm/? > -- > regards > kg > I have select version(); version - PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 (SuSE Linux) I did set datestyle to 'European, SQL'; SET select date_trunc( 'day' , now() ); date_trunc - 15/03/2005 00:00:00 CET How did you set your datetype? Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] select multiple immediate values, but in multiple rows
You need a function like this :
CREATE OR REPLACE FUNCTION array_srf( integer[] ) RETURNS SETOF integer
LANGUAGE PLPGSQL etc... AS
$$
DECLARE
_data ALIAS FOR $1;
_i INTEGER;
BEGIN
FOR _i IN 1..icount(_data) LOOP
RETURN NEXT _data[_i];
END LOOP;
RETURN;
END;
$$
select * from array_srf('{1,2,3,4}');
array_srf
---
1
2
3
4
(4 lignes)
This will avoid you the UNION.
Hello all. I'd like to write a query does a set subtraction A - B, but
A is
is a set of constants that I need to provide in the query as immediate
values. I thought of something like
select a from (1,2,3.4)
except
select col_name from table;
but I don't know the syntax to specify my set of constants. I thought of
doing
select 1 union select 2 union select 3 union ... except ...
but I figure there must be a better way. The size of the set would be
anywhere between 1 and about 5 or 6 elements. Is there a better way to
do
this?
Thanks.
Luca
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(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] Parameterized views proposition
"Tambet Matiisen" <[EMAIL PROTECTED]> writes: > How tough it would be to implement > CREATE VIEW xxx PARAMETERS (yyy) AS zzz; > as > CREATE TYPE xxx; > CREATE FUNCTION xxx(yyy) RETURNING SETOF xxx LANGUAGE sql AS 'zzz'; What's the point? It'd be nonstandard anyway, so just use the function. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Scheme not dropping
I am dropping a database with an additional scheme other than public on version 7.3.2. When I come to recreate the database with the same scheme it gives me the error: ERROR: namespace "xxx" already exists Is this temp table issue, if so how do I get round it? Many thanks in advance. Graham ---(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] Scheme not dropping
On Tue, 2005-03-15 at 18:44 +, Graham Vickrage wrote: > I am dropping a database with an additional scheme other than public on > version 7.3.2. > > When I come to recreate the database with the same scheme it gives me > the error: > > ERROR: namespace "xxx" already exists does the scheme exist in template1 (or the database you are using as template when you create database) ? gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
