>> On Thu, Sep 7, 2017 at 3:28 PM wrote:
>>
>> > Example query
>> > select a.col1, case when a.col2 > 0 then a.col3 else b.xcol1 end as mycol3
>> > from a left join b on
>> > Expected response
>> > col1 mycol3
>> >
>>
>> This may be overkill, but works:
>>
>> postgres=# \copy (select 1 as
>> SELECT table_name, column_name
>> FROM information_schema.columns
>> WHERE table_name = 'your_name';
>>
>> ----- Original Message -From: hamann w To:
>> pgsql-general@postgresql.orgSent: Thu, 07 Sep 2017 07:18:12 -
>> (UTC)Sub
>> On Thu, Sep 7, 2017 at 9:18 AM, wrote:
>> > is there a simple way to retrieve column names from a query - basically
>> > the way psql adds
>> > column headings when I do a select?
>>
>> How do you do the query? I mean, JDBC, PERL? After all psql is just a
>> C program doing a query using l
Hi,
is there a simple way to retrieve column names from a query - basically the way
psql adds
column headings when I do a select?
Best regards
Wolfgang Hamann
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.o
>> On 31/08/2017 09:56, haman...@t-online.de wrote:
>> > Hi,
>> >
>> > is there a way to add a table create (and perhaps schema modify) timestamp
>> > to the system?
>> > I do occasionally create semi-temporary tables (meant to live until a
>> > problem is solved, i.e. longer
>> > than a session)
Hi,
is there a way to add a table create (and perhaps schema modify) timestamp to
the system?
I do occasionally create semi-temporary tables (meant to live until a problem
is solved, i.e. longer
than a session) with conveniently short names.
Also, is there a simple query to identify tables with
>> Hi all,
>> I need to pg_dump a database to another server.
>> The particularity is that the database is bigger than remaining space on
>> disk. Obviously, this is not a problem because i'm dumping to another
>> host, but I need to know if the procedure streams data to remote host or
>> t
Hi,
I have a table cv with custid and vendid columns. Every entry represents the
purchase of a product
available from a specific vendor.
Now, for a set of "interesting" vendors, I would like to select a new table
custid, c415, c983, c1256
based upon part queries
select custid, count(vendid) as
Hi Bjorn,
I have experienced that some subqueries can be quite slow, and would suspect
the NOT IN
clause. I occasionally rewrite
NOT IN (select key from unwanted_candidates)
as
IN (select key from possible_candidates except select key from
unwanted_candidates)
Admittedly, I am not running late
>> On 2016-07-09 08:20, haman...@t-online.de wrote:
>> > Hi,
>> >
>> > a table is associated with a trigger for normal use.
>> > An admin (someone with ALTER privilege) can disable tthe trigger, run some
>> > bulk update,
>> > and then re-enable it. This means, however, that normal user activity
>> On 2016-07-09 08:20, haman...@t-online.de wrote:
>> > Hi,
>> >
>> > a table is associated with a trigger for normal use.
>> > An admin (someone with ALTER privilege) can disable tthe trigger, run some
>> > bulk update,
>> > and then re-enable it. This means, however, that normal user activity
Hi,
a table is associated with a trigger for normal use.
An admin (someone with ALTER privilege) can disable tthe trigger, run some bulk
update,
and then re-enable it. This means, however, that normal user activity has to
be locked out.
There are two possible scenarios: the bulk update would
>> On Sat, Apr 30, 2016 at 1:38 AM, wrote:
>> > I have a table with a row update trigger that is quite slow.
>> > The trigger finction basically sets some bits in a "changed" column
>> > depending on which values really changed.
>> > For some bulk updates it can be determined in advance that the
>> On 2016-04-30 02:08, wolfg...@alle-noten.de wrote:
>> > Hi,
>> >
>> > I have a table with a row update trigger that is quite slow.
>> > The trigger finction basically sets some bits in a "changed" column
>> > depending on which values really changed.
>> > For some bulk updates it can be determi
Adrian Klaver wrote:
>> On 07/28/2015 08:28 PM, haman...@t-online.de wrote:
>> >
>> > Hi,
>> >
>> > can any of the programming languages (perl, tcl) be used to exec a process
>> > on the server
>> > and open files?
>>
>> You are looking for untrusted.
>>
>> For Python that is the only way it com
Hi,
can any of the programming languages (perl, tcl) be used to exec a process on
the server
and open files?
The scenario I have in mind would
- copy some fields of a table to an external text file
- have a script generate binary data from the text file
- load that data into a large object
The c
Hi,
on a server running 8.4 I observe that, since a while, the pg_attribute table
is growing
enormously.
Soon after reloading I have one file
ls -s 1249
1048580 1249
a day later this is
1048580 1249
1048580 1249.1
1048580 1249.10
1048580 1249.11
1048580 1249.12
1048580 1249.13
1048580 1249.14
>> Hi,
>> �
>> How is it possible to debug user defined functions written in C.
>> I can't just include all the files and run it.
>> �
>> Does anyone have an idea?
>> �
>> Janek Sendrowski
>>
Hi,
I recall compiling with debug as normal, adding a printf followed by 30 sec
sleep, and
use the paus
>> On 8/1/13, haman...@t-online.de wrote:
>> > Hi,
>> > I want to store copies of our data on a remote machine as a security
>> > measure.
>>
>>
>> > Wolfgang
>>
>> 2 questions:
>>
>> 1. How secure is the remote site?
>> 2. How much data are we talking about?
>> --
>> Mike Nolan
Hi Mike,
c
Luca Ferrari wrote:
On Fri, Aug 2, 2013 at 6:55 PM, wrote:
> thanks for the hint - this is probably one of the things to do.
> I have something else in mind, but at present I just suspect that this might
> happen:
> when I modify data and select _without an ordering_, I am pretty sure to get
>> On 08/01/2013 02:59 AM, haman...@t-online.de wrote:
>> >
>> > However, the diff files seem to be considerably larger than one would
>> > expect.
>> > One obvious part of the problem is the fact that diff shows old and new
>> > text,
>>
>> You could try using
>> diff --suppress-common-line
Hi,
I want to store copies of our data on a remote machine as a security measure.
My first attempt was a full dump (which takes too long to upload)
followed by diffs between the pgdump files.
This provides readable / searchable versioned data (I could alway apply
the diffs on the remote machine and
>> yes, when i tried to start, postgresql service using init.d its gave me the
>> error removed stale pid, postgresql failed to start.
>>
>> Regards,
>> Itishree
>>
>>
Hi,
have you tried to execute the startup sequence step by step?
On systems without systemd, something like
sh -x /etc/init.
Hi,
I have a table with mainly a list of keywords and a few odd columns referring
to their origin.
There is a standard btree index, and searches are carried out as anchored regex.
Entries in the table and queries are uppercased; this turned out to have a big
speed
advantage over a ~* condition.
>>
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: RIPEMD160
>>
>>
>> > is there any way to use a module within a pltcl script, i.e. have
>> > load /path/to/mystuff.so
>> > or
>> > package require mystuff
>> > in a script.
>>
>> You can load tcl code by putting it in the pltcl_modules tables.
Hi,
is there any way to use a module within a pltcl script, i.e. have
load /path/to/mystuff.so
or
package require mystuff
in a script.
The reason why I am asking: I have recently converted a fairly slow script
(lots of regex) into one compiled module that basically does all regex at once,
and cr
Pavel Stehule wrote:
>>
>> but maybe https://github.com/dimitri/prefix can help
>>
Hi Pavel,
thanks - this works perfect. However, it does not seem to play well
with the optimizer, so I ended up with
select all candidates into a temp table using prefix operator
apply all other conditions by
Alexander Farber wrote:
delete from pref_users
where id not in (select distinct id from pref_money);
Hi,
try instead
delete from pref_users
where id in (select id from pref_users except select id from pref_money);
Regards
Wolfgang Hamann
--
Sent via pgsql-general mailing list (pgsql-general@
Pavel Stehlule wrote:
>> >> Hi,
>> >>
>> >> I am trying to match items from 2 tables based on a common string.
>> >> One is a big table which has one column with entries like XY123, ABC44, =
>> etc
>> >> The table has an index on that column.
>> >> The second table is, typically, much smaller
>>
Bob Futrelle wrote:
If looking for the variants with a single suffixed character is all you'll
ever need to do:
Do a single pass on the large table, after creating a field, 'trunc' that
contains a truncated version of the item, e.g., adding XY423 to trunc for
each entry of the form XY423A, or XY4
Hi,
I am trying to match items from 2 tables based on a common string.
One is a big table which has one column with entries like XY123, ABC44, etc
The table has an index on that column.
The second table is, typically, much smaller
select from tab1, tab2 where tab1.code = tab2.code;
This wor
>>
>> "David Johnston" writes:
>> > [optionally go look at " ~* " in the documentation at this point; or just
>> > try a simple flip-flop of the expression]
>>
>> > SELECT 'Kumar' ~* 'kuma' -> TRUE !!! (ah ha! I had the order reversed)
>>
>> > SELECT any( ARRAY['Kumar','Gozales']::text ) ... wa
Hi,
I have a column declared as array of text. I can get a single backslash into
one of the array elements by
update ... set mycol[1] = E'blah \\here'
If I try to update the whole array
update ... set mycol = E'{"blah \\here"}'
the backslash is missing. I can get two backslashes there.
Is there a
>> >>
>> >> Hi,
>> >>
>> >> I am looking for a feature that would select from a table with
>> >>
>> >> If such an operator exists, would there be a "remove duplicates" option?
>> >>
>> >> Regards
>> >> Wolfgang Hamann
>> >>
>> >
>> > select k, array_agg(distinct val ) from t;
>> >
>> > See the do
>>
>> haman...@t-online.de writes:
>> > Tom Lane wrote:
>> > If you want it to be bulletproof, what I'd think about is something like
>> >WHERE second.path LIKE quote_like(first.path)||'%'
>>
>> > Just out of curiosity: wouldn't that (as well as using non-static like)
>> > be an enormous perf
Hi,
I am looking for a feature that would select from a table with
k1 a
k1 b
k1 c
k2 a
k3 b
k3 c
something like
k1 a b c
k2 a
k3 b c
(Just all elements next to each other, with a space in between)
or perhaps an array output
k1 {a,b,c]
k2 {a}
k3 {b,c}
If such an operator exists, woul
Tom Lane wrote:
patrick keshishian writes:
> Thanks for the quick reply. Would be tough choosing another
> "reasonable" ESCAPE character while dealing with paths. Will think
> more about this.
If you want it to be bulletproof, what I'd think about is something like
WHERE second.path LI
Hi,
I recently found out about critcl, which does a fairly decent job of converting
a C
fragment inside a tcl procedure into a loadable module.
Now, if I wanted to use the same code inside pltcl, would that be possible?
If yes, any special precautions other than getting the file paths right?
R
Hi Tom,
declaring another operator class helped. At first, however,
results were sorted deifferent than expected. A little gdb session revealed that
if fact only the FUNCTION 1 entry in the operator class is used
Regards
Wolfgang Hamann
>>
>> haman...@t-online.de writes:
>> > Now, in versions
Hi,
in the old days (version 7.x) I created a set of btree operators
create operator <& (
procedure = mytext_lt,
leftarg = text,
rightarg = text,
commutator = >&,
negator = >=&,
restrict = scalarltsel,
join = scalarltjoinsel
);
etc. for a completeoperator class
create operator cla
is there a way to use a table alias for the target table in an UPDATE query?
I would like to avoid spelling out things like that in psql:
update myverylongtablename set col1 = r.col1repl from repltable r
where myverylongtablename.id = r.id and myverylongtablename.col2 > 0
Regards
Wolfgang Ham
Hi,
I am currently doing something like
select ordercode, descr, codes into temp table x from products where ...
Here codes is a bit-mapped field
update x set codes = codes | 512 from othertable t where ordercode =
t.ordercode and
select * from x
Is there a way to avoid that temp table?
Scott Marlowe wrote:
2012/3/12 François Beausoleil :
> Hi all,
>
> When using COPY FROM STDIN to stream thousands of rows (20k and more hourly),
> what happens with indices? Are they updated only once after the operation, or
> are they updated once per row? Note that I'm not replacing the table'
>>
>> On Tue, 28 Feb 2012, haman...@t-online.de wrote:
>>
>> > one thing you might want to consider is system lifetime: some distro may
>> > be set up so that you more or less have to reinstall within 2 years, if
>> > you plan to use update service - others may be longer. Now, fast
>> > developme
>>
>> If we move to Linux, what is the preferred Linux for running Postgres
>> on. This machine would be dedicated to the database only.=20
>>
>> I'd like a recommendation for both a GUI hosted version and a non-GUI
>> version. I haven't used Linux in the past but did spend several year s
>> in
Hi, I am just trying to build a duplicate of our database server.
Like the main one, I compiled it from source, ran initdb, created a
superuser, and then proceded to creating a database.
However, db creation is denied to the superuser. Likewise,
if I create the db as user postgres and then change o
Reuven M. Lerner wrote:
>> Hi, everyone. I'm working on a project on PostgreSQL 9.0 (soon
>> to be upgraded to 9.1, given that we haven't yet launched). The
>> project will involve numerous text fields containing English,
>> Spanish, and Portuguese. Some of those text fiel
Hi,
I have a table of names, and searches are usually performed on prefix match.
This could nicely translate into an index search
Suppose first name is stored as either 'Jim' or 'Jimmy', searching
... where firstname ~* '^jim';
gets proper result. I had hoped that creating a functional
index on l
Alban Hertroys wrote:
>> > Hi,
>> >=20
>> > the strings are not really known before.
>> > Let me explain the scenario; there is one table about products, and =
>> code is the
>> > manufacturer's (or resellers') product id.
>> > So, if ABC were a maker of laptops, ABC123 and ABC456 might be two
Eduardo Morras wrote:
>>
>> At 08:04 25/09/2011, haman...@t-online.de wrote:
>>
>> > >> select * from items where regexp_matches(items.code,'(ABC) (DE1)
>> > >> (any_substring)')<>{};
>> > >>
>> >
>> >Hi Eduardo,
>> >
>> >it is clear that scanning the table once with a list of matches will
>>
Eduardo Morras wrote:
>> >
>> >Hi,
>> >
>> >if I understand this right, it does not mean "check if the string
>> >appears at position 0"
>> >which could translate into an index query, but rather "check if the
>> >string appears anywhere
>> >and then check if that is position 0", so the entire ta
>> >
>> > Hi Alban,
>> >
>> > I already did that - the test set is just all records from the real table=
>> (about a million
>> > entries) that match the common 'ABC' prefix
>>
>> I think you misunderstood what I wrote. Notice the difference between
>> "which strings match the pattern" and "which
Alban Hertroys wrote:
>> So you're comparing a variable field value to a variable pattern - yeah,
>> that's going to hurt. There's no way you could index exactly that.
>>
>> Perhaps there's some way you can transform the problem so that you get
>> something indexable?
>> For example, if your matc
Eduardo Morras wrote:
>> You can try these, i doubt they will use any index but its a
>> different approach:
>>
>> select * from items where
>> length(items.code)<>length(rtrim(items.code,'ABC'));
>>
>> select * from items where strpos(items.code,'ABC')=0 or
>> strpos(items.code,'any_substrin
Alban Hertroys wrote:
>> What is the output of explain?
>>
>> You say 'the other table', so presumably we're dealing with a foreign key
>> here. Is there an index on that column?
Albe Laurenz wrote:
>> Is the index used for "where code ~ '^ABC3563'"?
>>
>> If not, then the result is fast only
Hi,
I have one large table (about a million entries) with an indexed column
containing codes
like ABC3561A, ABC3563X, 72-451-823 etc. (lots of order numbers from different
manufacturers)
When I ask for a specific item
select code where code = 'ABC3563X'
I get fast result. I also get fast
56 matches
Mail list logo
