Rajesh Kumar Mallah mentioned :
=> Can anyone tell me how to convert epoch to timestamp ?
=>
=> ie reverse of :
=>
=> SELECT EXTRACT( epoch FROM now() );
=> +--+
=> |date_part |
=> +--+
=> | 1130317518.61997 |
=> +--+
Here is one way (In m
5 at 02:12:11PM -0400, Keith Worthington wrote:
> On Fri, 8 Apr 2005 12:51:47 -0400, Stef wrote
> > Hello Everyone,
> > Currently, here at work, I am doing the whole
> > 'advocacy' part of postgreSQL. It's not really hard to
> > do, as the other data
Hello Everyone,
Currently, here at work, I am doing the whole
'advocacy' part of postgreSQL. It's not really hard to
do, as the other database's are MySQL and Sybase ;)
There is obviously a whole spat of data munging
going on in the background, and I noticed that psql in
8.0.1 now
or so for +- 450 tables on my machine.
It may break if you have some exotic definitions that I didn't test for,
but I think it's pretty solid as it is here.
Kind Regards
Stefan
Stef mentioned :
=> Here's my final solution that runs in less than a minute for +- 543 tables :
=> fo
Jim Buttafuoco mentioned :
=> I use dblink to attach to both databases and query pg_namespace, pg_class,
pg_attribute ... to get the diffs. See
=> attached as an example. look for the dblink_connect lines to specify your
database. You will need to install
=> contrib/dblink. I used this with 7
John DeSoi mentioned :
=> I'm not sure you can use \d directly, but if you startup psql with the
=> -E option it will show you all the SQL it is using to run the \d
=> command. It should be fairly easy to get the strings you need from the
=> results of running a similar query. The psql source is
Tom Lane mentioned :
=> > The problem I have with this, is that I have to run the command per table,
=>
=> Why?
=>
=> If the problem is varying order of table declarations, try 8.0's
=> pg_dump.
Yes, this will solve the global schema check, but I will still need to split
it into "per table" dump
Markus Schaber mentioned :
=> Some weeks ago, I posted here a script that uses psql to create split
=> dumps. Maybe you can reuse some of its logics to create per-table
=> md5sums for all tables in a database automatically.
Thanks, but I've got something very similar to this already. I almost
tho
John DeSoi mentioned :
=> Develop a function that builds a string describing the tables/schemas
=> you want to compare. Then have your function return the md5 sum of the
=> string as the result. This will give you a 32 character value you can
=> use to determine if there is a mismatch.
OK, this
[EMAIL PROTECTED] mentioned :
=> Are you just synching the schemas, or do you also need to synch the data?
Schemas now, data later.
To do the data part, I'm thinking of using slony, because it seems to be able to
do pretty much everything I need from that side. But, unfortunately I can't
even st
Markus Schaber mentioned :
=> There are (at least) two independently developed pgdiff applications,
=> they can be found at:
=>
=> http://pgdiff.sourceforge.net/
=>
=> http://gborg.postgresql.org/project/pgdiff/projdisplay.php
Thanks a lot!
=> I did not try the first one, but the latter one wor
Hi all,
I've got a master database with many other databases that
all have (or is supposed to have) the same exact same schema
as the master database (the master database is basically an empty
template database containing the schema definition).
The problem is that none of the schemas actually
dify pieces of the "create table" statement to drop all the indexes
and constraints first.
Is there an easier way around this?
Stef
---(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
Andrew Sullivan mentioned :
=> I'm not sure why you want to do the former, but in any case, it's
Because lazy people write inserts without specifying column names.
=> possible by creating a new table which has things the way you want;
=> select all the old data from the old table into the new tabl
Hi all,
I'm struggling with a situation where I
want to recreate a table (in more than 30 databases) to
fix the column order (attnum sequence) and in another case,
fix different definitions for the same column in a table e.g.
amount numeric(16,2)
in stead of :
amount numeric(16,5)
The complica
Erm .. sorry list people. This one slipped to the wrong address...
Stef mentioned :
=> Hi Marcus,
=>
=> Here's something interesting for you :
=> http://www.gnoppix.org/
=>
=> It looks like it may be more easy to install, as it's
=> entirely geared towards instal
Hi Marcus,
Here's something interesting for you :
http://www.gnoppix.org/
It looks like it may be more easy to install, as it's
entirely geared towards installation rather than just booting.
It says it's based on woody, so I don't know if the 2.6 kernel is
a boot option here. Maybe check it out
Hilary Forbes mentioned :
=> Can I guarantee that if I have more than one Rule on a table they will be executed
in a given order? I know triggers are done alphabetically but is the same true of
rules?
http://www.postgresql.org/docs/7.4/static/sql-createrule.html#AEN37748
pgpgCnrDhs4Og.pgp
Desc
Hi all,
I've narrowed my problem down to the following
Java Timestamp that I managed to insert into
a postgres 7.3.4 database :
Timestamp : '1475666-11-30 02:00:00.0'
My problem is, that when I try and select from the table I inserted
this timestamp into, I get the following error :
ERROR: Una
Hello Eric,
Are you looking for something like :
select to_char(timestamp 'now',' MM DD HH MI SS');
or the values in your example below :
select to_char(timestamp '20041010 00:00:00',' MM DD HH MI SS');
Eric Lemes mentioned :
=> Hello there,
=>
=> I'm with a little trouble with postgresq
te of : 16 Feb 2004
and hides the bottom of my e-mail folder.
Cheers
Stef
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
#x27;m sticking to psql though, and managed to create the query with a nested
nullif and coalesce to make my query fail if there are 0 rows matching for
an update or delete.
Cheers
Stef
---(end of broadcast)---
TIP 8: explain analyze is your friend
Hi all,
I've been looking on Google,the archives and documentation,
but cannot find what I'm looking for. Maybe I've read the
answer, but it's still evading me.
I'm working with postgres 7.3.4 using the psql client.
I want to know if it's possible to raise an error in a
transactional BEGIN->EN
Forgot to mention that I use postgres 7.3.4
Stef mentioned :
=> Hi all ,
=>
=> I'm trying to find out if there is a specific setting
=> to make transactions time out faster in a scenario
=> where there's an update on a table in a transaction
=> block, and another up
Hi all,
It seems I always find a solution just after
panicking a little bit.
Anyway, I found that statement_timeout solved
my problem. When I tested it earlier, I actually
made an error, and skipped it as a possible
solution.
Cheers
Stef
Stef mentioned :
=> Forgot to mention that I
Hi all ,
I'm trying to find out if there is a specific setting
to make transactions time out faster in a scenario
where there's an update on a table in a transaction
block, and another update process tries to update
the same column.
It looks like the second process will wait until you
end the t
Stef mentioned :
=> The postmaster logs to a separate log file, but at the moment
=> it's impossible to tell which sql comes from which connection.
=> Is there an easy way to accomplish this?
Erm... sorry ,
It appears my postgresql.conf is not an original. It didn't have
th
Hi all,
I've switched on log_statement in postgresql.conf
for debugging purposes. I tried logging connections
and pids as well, but I need to match up the logged statements
to specific connections.
The postmaster logs to a separate log file, but at the moment
it's impossible to tell which sq
ach day and then sort them all and ... but thats a bit of
a pain if there is already such a thing in existence.
regards
Stef
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
esentation 'valid'
I looked in the documentation and on the net, but cannot find
much reference to this "ex-feature"
I'm 80% sure no one actually inserted these 'invalid' values into tables,
and what I need to know is :
What else can insert a value of 'invalid&#
Thanks guys,
I had a feeling this was the case, but wasn't sure.
The one-version pg_dump looks like a winner.
Regards
Stefan
##START##
=> Rod Taylor <[EMAIL PROTECTED]> writes:
=> >> What I did next, is put a trigger on pg_attribute that should, in theory,
=> >> on insert and update, fire up a f
Correction on the function :
The function currently on the database did has
select int4(description) + 1 into v_new_version from pg_description
where objoid = NEW.attrelid;
in stead of
select int4(description) into v_new_version from pg_description
where objoid = NEW
Hi all,
I'm trying to create some kind of table version control
system for approximately 300 postgres databases
ranging in version from 7.1.2 to 7.3.4.
I compared the "pg_dump -s" output between
the various versions of databases, but the format is inconsistent,
and I can't do diff's to check th
On Fri, 8 Aug 2003 09:24:48 -0700
Jonathan Gardner <[EMAIL PROTECTED]> wrote:
=> Try the performance list.
Thanks for the tip
Stef
---(end of broadcast)---
TIP 8: explain analyze is your friend
Hi all,
I have a problem :
A select statement that selects from 7 tables,
groups the information by 6 columns of the
tables involved.
When there are no rows in pg_statistics,
the query runs under 3 minutes.
When I analyze the biggest table of the 7
(approx 100 rows), the query takes longer
hello again everyone,
I seem to have hit what i -think- may be a bug (but i am not crying
wolf jst yet ;).
I have three tables. action, client and order_details. action has a primary
key 'order', client references action (along with adding the client_id as
part of its pr
Stephan Szabo wrote:
> Hmm, don't know why it's not in changelog, but the spec requires that
> the target fields of a foreign key constraint are themselves constrained
> by a unique or primary key constraint.
maybe its time for me to go and re-read the changelog with a fine tooth
comb (it has be
ingly obvious but
i cant seem to understand why it was working and now isnt. i even went
through the changelog!
regards
Stef
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister comm
eral-purpose templating modules.
> They'll all handle tables.
>
thank you for the input, and if i was jst starting out i would agree with
you. I cant really explain it any better than i have previously, but
hopefully you will see that i want to use the database to do this.
hopefully that
Hello,
i find i must turn once again to the list for help, in what
is probably another silly request.
I have a view (made of 4 tables) and now find that users
need to have the view different based on some criteria from the
database. its. well. its rather icky to go into. but i can
Richard wrote:
> > Hello everyone,
> > I have hit on a limit in my knowledge and i am looking for
> > some guidance. Currently I have two seperate databases, one for
> > live data, the other for historical data. The only difference really
> > being that the historical data has a Serial in it so th
Hello everyone,
I have hit on a limit in my knowledge and i am looking for
some guidance. Currently I have two seperate databases, one for
live data, the other for historical data. The only difference really
being that the historical data has a Serial in it so that the tables
can keep mo
42 matches
Mail list logo
