[GENERAL] Arrays and LIKE
Done a bit of hunting and can't seem to find an answer as to if this sort of thing is possible: SELECT * FROM mail WHERE recipients ILIKE 'david%'; Where recipients is a VARCHAR(128)[] The above doesn't work but thats the sort of thing I want to do... If this is possible and can use an index as well that would be wonderful... -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Arrays and LIKE
David wrote:
> Done a bit of hunting and can't seem to find an answer as to if this sort of
> thing is possible:
>
> SELECT * FROM mail WHERE recipients ILIKE 'david%';
>
> Where recipients is a VARCHAR(128)[]
>
> The above doesn't work but thats the sort of thing I want to do...
> If this is possible and can use an index as well that would be wonderful...
test=*# \d foo
Tabelle »public.foo«
Spalte | Typ | Attribute
++---
t | text[] |
test=*# select * from foo;
t
-
{foo,bla,blub}
{xyz,bla,fasel}
(2 Zeilen)
Zeit: 0,393 ms
test=*# select * from ( select t, generate_subscripts(t, 1) AS s from foo) bla
where t[s] ~ '^xy.*';
t| s
-+---
{xyz,bla,fasel} | 1
(1 Zeile)
(http://www.postgresql.org/docs/8.4/interactive/arrays.html#ARRAYS-SEARCHING)
But i don't know how to create an index for that...
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] duplicate key violates unique constraint
Hello all,
I have a table named "t_i_shift_shadow" with following spec
Column | Type |
Modifiers
-+---+---
cid | character varying(20) | not null
shift_rev_id| character varying(20) | not null
start_time | bigint| not null
end_time| bigint| not null
lunch_from_time | bigint| default -1
lunch_to_time | bigint| default -1
shift_date | date | not null
is_cyclic | integer | not null
serial | integer | not null default
nextval('t_i_shift_shadow_serial_seq'::regclass)
Indexes:
"t_i_shift_shadow_pkey" PRIMARY KEY, btree (serial)
Triggers:
_replcluster_logtrigger_4 AFTER INSERT OR DELETE OR UPDATE ON
t_i_shift_shadow FOR EACH ROW EXECUTE PROCEDURE
_replcluster.logtrigger('_replcluster', '4', 'k')
I am trying to replicate this table using slony.
But I get the following error... I wanted to know if this is a postgres
error ??? If it is what is causing it??
ERROR -->
PGRES_FATAL_ERROR select "_replcluster".setAddTable(8, 36,
'public.t_i_shift_shadow', 't_i_shift_shadow_pkey', 'shift shadow
table'); - ERROR: duplicate key violates unique constraint
"sl_table_tab_reloid_key"
I am a bit confused about where to post it.. So I am posting it here first...
Thanks and Regards,
Sweta.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Arrays and LIKE
On 2009-08-08, David wrote: > Done a bit of hunting and can't seem to find an answer as to if this sort of > thing is possible: > > SELECT * FROM mail WHERE recipients ILIKE 'david%'; > > Where recipients is a VARCHAR(128)[] > > The above doesn't work but thats the sort of thing I want to do... > If this is possible and can use an index as well that would be wonderful... hmm tricky: you can't use any(recipients) ilike 'david%' -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 'a' = any( $${'a','x'} )
jasen=# select 'a' = any( $${'a','x'}$$ ) ;
?column?
--
f
(1 row)
I expect it to be true instead. what am I doing wrong?
PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
4.3.2-1.1) 4.3.2
jasen.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 'a' = any( $${'a','x'} )
On Sat, Aug 8, 2009 at 12:08 PM, Jasen Betts wrote:
> select 'a' = any( $${'a','x'}$$ ) ;
postgres=# select 'a' = any( $${"a","x"}$$ ) ;
?column?
--
t
(1 row)
--
greg
http://mit.edu/~gsstark/resume.pdf
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] duplicate key violates unique constraint
2009/8/8
> Hello all,
>
> I have a table named "t_i_shift_shadow" with following spec
>
>
> Column | Type |
> Modifiers
>
> -+---+---
> cid | character varying(20) | not null
> shift_rev_id| character varying(20) | not null
> start_time | bigint| not null
> end_time| bigint| not null
> lunch_from_time | bigint| default -1
> lunch_to_time | bigint| default -1
> shift_date | date | not null
> is_cyclic | integer | not null
> serial | integer | not null default
> nextval('t_i_shift_shadow_serial_seq'::regclass)
> Indexes:
>"t_i_shift_shadow_pkey" PRIMARY KEY, btree (serial)
> Triggers:
>_replcluster_logtrigger_4 AFTER INSERT OR DELETE OR UPDATE ON
> t_i_shift_shadow FOR EACH ROW EXECUTE PROCEDURE
> _replcluster.logtrigger('_replcluster', '4', 'k')
>
>
> I am trying to replicate this table using slony.
>
> But I get the following error... I wanted to know if this is a postgres
> error ??? If it is what is causing it??
>
> ERROR -->
>
> PGRES_FATAL_ERROR select "_replcluster".setAddTable(8, 36,
> 'public.t_i_shift_shadow', 't_i_shift_shadow_pkey', 'shift shadow
> table'); - ERROR: duplicate key violates unique constraint
> "sl_table_tab_reloid_key"
>
Looks like you are trying to add a table with same ID second time.
Table ID has to be unique.
>
> I am a bit confused about where to post it.. So I am posting it here
> first...
>
You can post such questions to slony1-general mailing list.
--
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/
Re: [GENERAL] 'a' = any( $${'a','x'} )
Try
select 'a' = any( $${a,x}$$ ) ;
or
select 'a' = any( array['a','x'] ) ;
On Sat, Aug 8, 2009 at 3:08 PM, Jasen Betts wrote:
> jasen=# select 'a' = any( $${'a','x'}$$ ) ;
> ?column?
> --
> f
> (1 row)
>
> I expect it to be true instead. what am I doing wrong?
>
>
> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
> 4.3.2-1.1) 4.3.2
>
>
> jasen.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] Arrays and LIKE
On Sat, Aug 08, 2009 at 05:04:29PM +0930, David wrote:
> Done a bit of hunting and can't seem to find an answer as to if this
> sort of thing is possible:
>
> SELECT * FROM mail WHERE recipients ILIKE 'david%';
>
> Where recipients is a VARCHAR(128)[]
It's a bit of a fiddle:
CREATE FUNCTION flipilike(text,text)
RETURNS boolean IMMUTABLE LANGUAGE SQL
AS $$ SELECT $2 ILIKE $1; $$;
CREATE OPERATOR ~~~ (
leftarg = text,
rightarg = text,
procedure = flipilike
);
PG now understands:
SELECT 'x%' ~~~ 'fred';
To be the same as:
SELECT 'fred' ILIKE 'x%';
So you can solve your original problem as:
SELECT * FROM mail WHERE 'david%' ~~~ ANY(recipients);
> The above doesn't work but thats the sort of thing I want to do...
> If this is possible and can use an index as well that would be wonderful...
No idea about that, but I'd look to a GIN index to start with. I think
you really want to stop using arrays and do it "properly" with a
relation:
CREATE TABLE mailaddrs (
msgid TEXT REFERENCES mail,
ord INTEGER,
PRIMARY KEY (msgid, ord),
type TEXT CHECK (type IN ('to','from','cc','bcc')),
address TEXT
);
CREATE INDEX mailaddrs_address_idx ON mailaddrs (address);
then you can do:
SELECT DISTINCT msgid
FROM mailaddrs
WHERE address ILIKE 'david%';
and it should do the right thing. Not sure if you have this
flexibility though.
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql Backups
On Fri, Aug 7, 2009 at 3:54 PM, Scott Marlowe wrote: > You're probably better off switching to explicitly backing up > databases in the custom format and using a single pg_dumpall --globals > to backup the users accounts and such. That's how I prefer to do it > and it makes life much easier. This is precisely how we do it. Make for recovering certain data easier, especially when you have a lot of databases on your server. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A compare and/or sync. database structure?
The free, open-source SchemaCrawler for SQL Server tool is desinged to do just this. You can take human-readable snapshots of the schema and data, for later comparison. Comparisons are done using a standard diff tool such as WinMerge. SchemaCrawler outputs details of your schema (tables, views, procedures, and more) in a diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can also output data (including CLOBs and BLOBs) in the same plain-text formats. SchemaCrawler is available at SourceForge: http://schemacrawler.sourceforge.net/ Sualeh Fatehi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql and Emacs on Windows
Thanks for the replies. I tried Cygwin and unfortunately failed to get it to work with the "native" emacs binary for windows. I didn't spend too much time to try though, since the version of postgresql with cygwin is still 8.2.*, and I'm not sure how good the future support will be... I did try another option, using both emacs and psql from Portable Ubuntu, that works fine as expected. The version of psql is 8.3. Now, I need to see how well it can connect to other databases from within Ubuntu, ;-) But the worst case the user can use two emacs (from windows and from Ubuntu) if they have to. It would be nice to have native psql for Windows and native emacs for Windows work out of box in the future though. Thanks again. Wenjian On Fri, Aug 7, 2009 at 1:12 PM, Bill Bartlett < bbartl...@softwareanalytics.com> wrote: > On Friday, August 07, 2009 12:44 PM, Joshua D. Drake wrote: > > On Fri, 2009-08-07 at 17:36 +0100, Sam Mason wrote: > > > On Fri, Aug 07, 2009 at 11:11:10AM -0500, Wenjian Yang wrote: > > > > We currently installed emacs 23.1 and PostgreSQL 8.4.0 for Windows on > a > > > > windows desktop. When issue "sql-postgres" in emacs, after providing > > > > User/Password/Database/Server, nothing happens. There is no prompt > from > > > > emacs. Checking the server log, it doesn't seem to have received any > > > > connection request. > > > > > > The current setup on my windows box uses the psql that comes with > > > cygwin. Unfortunately I don't use it enough to remember the details of > > > how or why I settled on this setup. > > > > readline? > > > > Yes, the problem is readline. When running the "native" psql.exe from > inside > any sort of a "shell" program on Windows (such as from inside emacs, rxvt, > remote SSH command line, etc.), psql thinks it is running in batch mode so > you > see no prompts. It is indeed running, but you'll see no interactive > information. > > From my 8/4/2007 response to a similar posting: > > (start) > We use the Cygwin version of the psql.exe program under Windows instead of > the > native psql.exe program, even though the rest of the PostgreSQL > installation > uses the standard native Windows versions of all the other PostgreSQL > components > (database, tools, etc.). (So before I get flamed, I want to clarify that > the > ONLY component of PostgreSQL that we use from Cygwin is psql.exe, and it's > run > from an alternate directory.) > > The main advantage that we get by using the Cygwin version of psql is that > is > runs in "interactive" mode regardless of how it is run, whereas the native > psql > program runs in non-interactive mode (showing almost no output, no prompts, > no > readline support, etc.) when run from most "shell" programs. We frequently > run > psql via a remote SSH connection or from the RXVT terminal program or from > inside Emacs, and in all of these cases the native psql program runs in > non-interactive mode whereas the Cygwin version of psql is > fully-functional." > (end) > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] libpq
I am trying to compile a program with libpq using boirland c++ 5.5.1 free
edition and with mingw(gcc), and SELECT queries are returning PQntuples with
results of 0, but I can do the same query in pgadmin and get 1 result.
there is no way to link on the .lib files because they are in COFF format.
what compiler did they use to make these libs? could somebody please make libs
and separate DLLs (that is important - they are different inside) available for
the mingw compiler? It is a free compiler available at mingw.org.
the borland c++ 5.5.1 (free)-compiled program does absolutely nothing, I think
because the DLL's are not compatible.
sprintf(querystr,
"SELECT * FROM s_phonelist.phonelist\n"
"WHERE %s\n"
"ORDER BY
lastname,firstname,middlename,country,stateprovince,city,mailstop,company"
, s
);
#if defined(_DEBUG)
printf("QUERY:\"%s\"\n", querystr);
#endif
pgr = (PQexec)(pgc,querystr);
if (0 == ntuples) {
printf("--no records.--\n");
(PQfinish)(pgc);
return 0;
} else {
for (index=0; index < ntuples; index++) {
//copy values from row to Crecord row
val = (PQgetvalue)(pgr,index,
0);strcpy(row.firstname, val);
val = (PQgetvalue)(pgr,index,
1);strcpy(row.middlename , val);
val = (PQgetvalue)(pgr,index,
2);strcpy(row.lastname , val);
val = (PQgetvalue)(pgr,index,
3);strcpy(row.homephone, val);
val = (PQgetvalue)(pgr,index,
4);strcpy(row.workphone, val);
val = (PQgetvalue)(pgr,index,
5);strcpy(row.cellphone, val);
val = (PQgetvalue)(pgr,index, 6);strcpy(row.pager
, val);
val = (PQgetvalue)(pgr,index, 7);strcpy(row.company
, val);
val = (PQgetvalue)(pgr,index,
8);strcpy(row.address1 , val);
val = (PQgetvalue)(pgr,index,
9);strcpy(row.address2 , val);
val = (PQgetvalue)(pgr,index,10);strcpy(row.city
, val);
val =
(PQgetvalue)(pgr,index,11);strcpy(row.mailstop , val);
val =
(PQgetvalue)(pgr,index,12);strcpy(row.stateprovince, val);
val =
(PQgetvalue)(pgr,index,13);strcpy(row.postalcode , val);
val = (PQgetvalue)(pgr,index,14);strcpy(row.country
, val);
val = (PQgetvalue)(pgr,index,15);strcpy(row.comment
, val);
val = (PQgetvalue)(pgr,index,16);strcpy(row.website
, val);
val =
(PQgetvalue)(pgr,index,17);strcpy(row.emailhome, val);
val =
(PQgetvalue)(pgr,index,18);strcpy(row.emailwork, val);
//now display in an orderly fashion.
showrecord(row);
}
}
Jim Michaels
jmich...@yahoo.com
http://JesusnJim.com
while (stone != rolling) moss++;
---
Computer memory/disk size measurements:
[KB KiB] [MB MiB] [GB GiB] [TB TiB]
[10^3B=1000B=1KB][10^6B=100B=1MB][10^9B=10B=1GB][10^12B=1B=1TB]
[2^10B=1024B=1KiB][2^20B=1048576B=1MiB][2^30B=1073741824B=1GiB][2^40B=1099511627776B=1TiB]
Note that with disks, a disk size is measured in GB or TB, not in GiB or TiB.
computer memory (RAM) is measured in MiB and GiB.
---
new cyber dog food: Cables n' Bits
---
Re: [GENERAL] Arrays and LIKE
Thanks all normally I would have gone with a linked table but since support for
arrays has improved in pg lately I thought I would give them a go again but I
guess they are still not ready for what I want.
I did think of another solution overnight though that still uses arrays but
also a subtable. where I add address to a another table with a id sequence and
then store the seqid in the array then I could do
the like on the subtable returning ids and use the array of ids it returns to
compare to the recipient ids in the array (this should use a GIN index as
overlapping arrays is listed on the page
http://www.postgresql.org/docs/8.3/interactive/functions-array.html). This has
the added bonus that I can store stats about each email address seen with them.
(Haven't tried it yet next on the list to do)
On Sat, Aug 08, 2009 at 02:10:18PM +0100, Sam Mason wrote:
> On Sat, Aug 08, 2009 at 05:04:29PM +0930, David wrote:
> > Done a bit of hunting and can't seem to find an answer as to if this
> > sort of thing is possible:
> >
> > SELECT * FROM mail WHERE recipients ILIKE 'david%';
> >
> > Where recipients is a VARCHAR(128)[]
>
> It's a bit of a fiddle:
>
> CREATE FUNCTION flipilike(text,text)
> RETURNS boolean IMMUTABLE LANGUAGE SQL
> AS $$ SELECT $2 ILIKE $1; $$;
> CREATE OPERATOR ~~~ (
> leftarg = text,
> rightarg = text,
> procedure = flipilike
> );
>
> PG now understands:
>
> SELECT 'x%' ~~~ 'fred';
>
> To be the same as:
>
> SELECT 'fred' ILIKE 'x%';
>
> So you can solve your original problem as:
>
> SELECT * FROM mail WHERE 'david%' ~~~ ANY(recipients);
>
> > The above doesn't work but thats the sort of thing I want to do...
> > If this is possible and can use an index as well that would be wonderful...
>
> No idea about that, but I'd look to a GIN index to start with. I think
> you really want to stop using arrays and do it "properly" with a
> relation:
>
> CREATE TABLE mailaddrs (
> msgid TEXT REFERENCES mail,
> ord INTEGER,
> PRIMARY KEY (msgid, ord),
> type TEXT CHECK (type IN ('to','from','cc','bcc')),
> address TEXT
> );
> CREATE INDEX mailaddrs_address_idx ON mailaddrs (address);
>
> then you can do:
>
> SELECT DISTINCT msgid
> FROM mailaddrs
> WHERE address ILIKE 'david%';
>
> and it should do the right thing. Not sure if you have this
> flexibility though.
>
> --
> Sam http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
