Re: [GENERAL] alter column to inet get error.
In response to Steve Clark : > >Try this with explicet cast: > > Thanks guys, that seems to do the trick. Postgresql ROCKS!!! Yeah, definitively! You are welcome, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] alter column to inet get error.
In response to Steve Clark : > Hello List, > > I want to change some columns in a database > that were originally created as char varying to > inet. > > When I try I get an error. Is there anyway to work > around this? > > See below for table definition. > > Table "public.kernel_gre" > Column | Type | Modifiers > -+---+--- > interface | character varying(15) | not null > source_ip | character varying(16) | > dest_ip | character varying(16) | > physical_ip | character varying(16) | > status | boolean | default false > physical_src_ip | character varying(16) | > tunnel_netmask | character varying(16) | > key | character varying(32) | > state | boolean | default false > broadcast | boolean | default false > > alter TABLE kernel_gre ALTER COLUMN source_ip TYPE inet; > ERROR: column "source_ip" cannot be cast to type "inet" Try this with explicet cast: test=# create table ip (ip text); CREATE TABLE Zeit: 247,763 ms test=*# copy ip from stdin; Geben Sie die zu kopierenden Daten ein, gefolgt von einem Zeilenende. Beenden Sie mit einem Backslash und einem Punkt alleine auf einer Zeile. >> 127.0.0.1 >> \. Zeit: 5199,184 ms test=*# alter table ip alter column ip type inet using ip::inet; ALTER TABLE Zeit: 242,569 ms test=*# \d ip Tabelle »public.ip« Spalte | Typ | Attribute +--+--- ip | inet | test=*# Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Index File locations
In response to Callum Scott :
> Hi All,
>
> I am looking for a way to define where index files should be located.
>
> I am wanting to store the index files on a separate SSD from the database
> itself. I can see that there is a way to define this from within the
> database. Is there a way to do this at a system level within the
> configuration
> file?
You have to define a own tablespace and then you can create new indexes
on this tablespace.
test=# \h create tablespace
Command: CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory'
test=# \h create index
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS {
FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] return setof : alternatives to holder table
In response to zhong ming wu : > Hello List, > > I have a plpgsql function returning a set of records. The record is > effectively a join of some tables. > > For example, table a (column a1,column a2,column a3,column a4) > > table b(column b1,column b2,column b4) > > I am returning a set of (a2,a4,b2). What I do now is to create a empty table > > foo(column a2,column a4,column b2) > > then in my function I have > > record r foo%rowtype > > I'm not happy with this solution because this foo tables has to be kept around > > Thanks for any better solution to this You can create a aown typ or you can use IN/OUT-Parameters. I'm prefering IN/OUT-Parameters, see here: http://www.postgresonline.com/journal/archives/129-Use-of-OUT-and-INOUT-Parameters.html http://bytes.com/topic/postgresql/answers/674690-output-parameters-functions Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] could you tell me this..?
In response to ?? : > so. i killed Session 1's PID with kill -9 commands What have you killed, the client or the postmaster? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] could you tell me this..?
In response to ?? : > today, I found this situation. > > > > Session 1. - > > begin; > delete from ; > > > Session 2 - > > delete from ; > > > thus, it occured row level locking. > > > > > so. i killed Session 1's PID with kill -9 commands > > > but. both session are crashed. > > > is this normal ? or bug? Can't reproduce that. Until the kill, session 2 waits, after the kill session 2 finished the delete-command. Without problems, expected behavior. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] optimal memory
In response to Sim Zacks : > > > On 03-Aug-2010 11:18 AM, A. Kretschmer wrote: > > In response to Sim Zacks : > > > >> Is there a way to tell what the optimal memory is for a specific > >> postgresql instance? > >> > >> I am configuring Xen virtual machines and I don't want to give it more > >> then it needs. > >> > >> Would looking at the swap be an indication? As soon as it starts to use > >> swap, that means I need more, but until that point, I have enough? > >> > > You can't have enough ;-) > > > > Fits your DB into the RAM? > > > > If you don't have enough, for instance, work_mem, sort-operations > > performed on disk and not in the ram. That's much slower. So, as i said, > > you can't have enough ram ;-) > > > In theory that's a great answer. ;-) > If my database is 400MB (du on the base directory) and there are 10 > active users running functions and queries, that for the most part take > less then 1 sec each. > I would assume that 10GB of RAM is overkill. Maybe. > Is 2 GB RAM also overkill? Is there a way to know when you have reached > the overkill level? I think, you should try it. Set your virtual machine to 2 GByte, set shared_buffers to 512 MByte, effective_cache_size to 1.5 gbyte and work_mem to, for instance, 20 mbyte. Monitor the machine, watch the logfile (set log_min_duration_statement properly). Reduce all parameters to 50% and compare the results. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] optimal memory
In response to Sim Zacks : > Is there a way to tell what the optimal memory is for a specific > postgresql instance? > > I am configuring Xen virtual machines and I don't want to give it more > then it needs. > > Would looking at the swap be an indication? As soon as it starts to use > swap, that means I need more, but until that point, I have enough? You can't have enough ;-) Fits your DB into the RAM? If you don't have enough, for instance, work_mem, sort-operations performed on disk and not in the ram. That's much slower. So, as i said, you can't have enough ram ;-) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] pg_hba.conf
In response to quickinfo quickinfo : > Dear all, > > I am using postgres. when I try to connect to the database it is showing me > following error. Please look into that and help me out. > > an error occurred: > > FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres", database > "template1", SSL off. > > How do I proceed with this error. What are the things I need to change. You have to read the doc about the hba-file first! http://www.postgresql.org/docs/current/static/client-authentication.html Usually you should not work as user postgres... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] How to improve: performance of query on postgresql 8.3 takes days
In response to Dino Vliet : > I arrived at 15 functions because I had 7 or 8 joins in the past and saw that > my disk was getting hid and I had heard someplace that RAM is faster so I > rewrote those 7 or 8 joins as functions in pl/pgsql. They were just simple > lookups, although some of the functions are looking stuff up in tables > containing 78000 records. However, I thought this wouldn't be a problem > because > they are simple functions which look up the value of one variable based on a > parameter. 3 of the more special functions are shown here: I disaagree with you. The database has to do the same job, wherever with 7 or 8 joins or with functions, but functions (in this case) are slower. You should run EXPLAIN and show us the result, i believe there are missing indexes. > # - Memory - > > > shared_buffers = 512MB # min 128kB or max_connections*16kB How much RAM contains your server? You should set this to approx. 25% of RAM. > work_mem = 50MB # min 64kB That's maybe too much, but it depends on your workload. If you have a lot of simultaneous and complex queries you run out of RAM, but if there only one user (only one connection) it's okay. > effective_cache_size = 256MB # was 128 That's too tow, effective_cache_size = shared_buffers + OS-cache > Questions > > > 1. What can I do to let the creation of table B go faster? Use JOINs for table-joining, not functions. > > 2. Do you think the use of indices (but where) would help me? I didn't go > that > route because in fact I don't have a where clause in the create table B > statement. I could put indices on the little tables I'm using in the > functions. Yes! Create indexes on the joining columns. > > 3. What about the functions? Should I code them differently? Don't use functions for that kind of table-joining. > > 4. What about my server configuration. What could be done over there? see above. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] select a list of column values directly into an array
In response to Gauthier, Dave :
> Is there a way to select a list of column values directly into an array?
>
>
>
> create table foo (col1 text);
>
> insert into foo (col1) values (?aaa?),(?bbb?),(?ccc?),(?ddd?),(?eee?);
>
>
>
> I?d like to load up an array with {aaa,bbb,ccc,ddd,eee}, prefferably with a
> single select statement.
test=*# select array_agg(col1) from foo;
array_agg
---
{aaa,bbb,ccc,ddd,eee}
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] Re: What to do if space for database decrease and no additional hard drive is possible?
In response to Bill Thoen : > > > A. Kretschmer wrote: > >In response to Ivan Voras : > > > >>* buy external storage (NAS, or even an external USB drive), move the > >>database to it > >> > > > >buy external USB-Drive, and create a new tablespace, and move some large > >table into this new tablespace and/or use the new tablespace for new > >tables. You can also use table-partitioning with different tablespaces. > > > Can you then unmount that USB drive without causing any damage to the > other databases? No! Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] What to do if space for database decrease and no additional hard drive is possible?
In response to Vincenzo Romano : > 2010/7/26 A. Kretschmer : > > In response to Ivan Voras : > >> * buy external storage (NAS, or even an external USB drive), move the > >> database to it > > > > buy external USB-Drive, and create a new tablespace, and move some large > > table into this new tablespace and/or use the new tablespace for new > > tables. You can also use table-partitioning with different tablespaces. > > Table space on a USB drive? > You must be really sinking for this very option! > I'd rather move everything else from the crowded partition onto the USB drive, > as I would suppose that the database (performance and reliability) is > more important by far ... Maybe, depends but why not? Maybe there are some big, but rarely used, read-only tables? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Re: What to do if space for database decrease and no additional hard drive is possible?
In response to Ivan Voras : > * buy external storage (NAS, or even an external USB drive), move the > database to it buy external USB-Drive, and create a new tablespace, and move some large table into this new tablespace and/or use the new tablespace for new tables. You can also use table-partitioning with different tablespaces. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime
In response to Piotr Gasid??o : > Hello, > > I have strange problem. > > I test/optimize my queries with EXPLAIN ANALYZE. I get for example: > > Total runtime: 40.794 ms > > But when I run query without EXPLAIN ANALYZE i get, for example: > > Time: 539.252 ms > > Query returns 33 rows. Why? Maybe cheaply or virtuell hardware? There are some issues with functions like gettimoofday(), see here: http://archives.postgresql.org/pgsql-general/2007-01/msg01653.php (and the whole thread) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Rescue data after power off
In response to tuanhoanganh : > Is there anyway to rescue data afer power off. > I have postgres database version 8.3.9 on windows 2003. > Yesterday my server is power off, when i start server, some of table is blank. > Is there anyway to rescue it. Restore the data from your backup. You haven't a backup? Your problem. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] resource management, letting user A use no more than X resource (time, CPU, memory...)
In response to Ivan Sergio Borgonovo : > > If you need strong user resource limits, user storage limits, etc > > PostgreSQL might not be your best option. There are some things > > you can do, but there's not much. > > What about an external process that monitor backend and kill them > gracefully if they suck too many resources accordingly to the user > linked to that backend? Bad idea - imho. > > Or... gluing together a load balancing solution that divert > accordingly to the user to different slaves accordingly that have > slightly different setup? Sounds better, maybe possible. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] resource management, letting user A use no more than X resource (time, CPU, memory...)
In response to Ivan Sergio Borgonovo : > If I'd like to learn how to manage resources in postgres and grant > different users different time slot/memory/CPU? > > eg. I'd like to grant to user A to execute queries that last less > than 1min or that occupy no more than X Mb... etc... Isn't (real) possible. Okay, you can do something like: alter user foo set work_mem to '1MB'; but i think, that's not a usefull option, because with this setting you have other side effects (for instance, sort-operations on disk instead of in-memory -> more I/O and more LOAD for the whole machine). Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Why can't I see the definition of my relations
In response to Andrew Falanga :
> Hi,
>
> I just finished defining a couple of tables with PgAdmin III and I'm
> seeing something peculiar. I'm not sure what the problem is. When I
> connect to the DB using psql and do "\d " I get an error saying
> that there's not relations by that name. What? When I do, "\d" I see
> the tables listed. Here's a sample of the output:
>
> cgems=# \d
> List of relations
> Schema | Name | Type | Owner
> +-+---+---
> public | Mineral | table | cgems
> public | Stone | table | cgems
> (2 rows)
>
>
> cgems=# \d Stone
> Did not find any relation named "Stone".
>
> I'm guessing that it has something to do with permissions, but I'm
No, the reason is another:
test=# create table "Stone"(id serial);
NOTICE: CREATE TABLE will create implicit sequence "Stone_id_seq" for serial
column "Stone.id"
CREATE TABLE
test=*# \d Stone
Did not find any relation named "Stone".
test=*# \d "Stone"
Table "public.Stone"
Column | Type | Modifiers
+-+--
id | integer | not null default nextval('"Stone_id_seq"'::regclass)
You have to use the " if the table-name contains upper-case characters.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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 \dp equivalent or similar query?
In response to Davor J. :
> I couldn't find it on the net. I also coudn't find any reference to it in
> the psql source?
>
> Anyone any suggestions?
Start your psql with option -E to display the query behind:
kretsch...@tux:~$ psql -E test
psql (8.4.2)
Type "help" for help.
test=# \dp foo
* QUERY **
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S' THEN
'sequence' END as "Type",
pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS "Column access privileges"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S')
AND c.relname ~ '^(foo)$'
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
**
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
+--+--+---+--
public | foo | sequence | |
(1 row)
test=#
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] extracting total amount of time from an interval
In response to Andrew Geery :
> Is there a way to extract the total amount of time in a given unit from an
> interval? For example, I would like to know the total number of milliseconds
> in an interval.
>
> The expression
>
> select extract('seconds' from '5 minutes'::interval)
>
> returns the value of the seconds field (0) in the interval; not the total
> number of seconds in the interval. Is there a function like extract, but
> which
> extracts the value not *from* the given field, but *in* the given units (the
> opposite of the justify* functions)?
You can use extract:
test=*# select extract(epoch from '5 minutes'::interval);
date_part
---
300
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] Postgres table contents versioning
In response to John Gage : > Is there an equivalent of svn/git etc. for the data in a database's > tables? > > Can I set something up so that I can see what was in the table two > days/months etc. ago? You can use tablelog: 15:53 < akretschmer> ??tablelog 15:53 < pg_docbot_adz> For information about 'tablelog' see: 15:53 < pg_docbot_adz> http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html 15:53 < pg_docbot_adz> http://pgfoundry.org/projects/emaj/ 15:53 < pg_docbot_adz> http://pgfoundry.org/projects/tablelog/ -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Problem with temporary tables
In response to Andrea Lombardoni : > Hello. > > > The strange part is that the second time, the OID of the idmap is the > same as the one in the first invocation! > > Am I doing something wrong or is this a bug? The plan is cached, to avoid this problem, use dynamic SQL. In your case: EXECUTE 'CREATE TEMPORARY TABLE idmap ...' Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Scheduling backup
In response to RP Khare : > Is there any way to schedule PGSQL databases backups? I want to take hourly > dumps of my production database. You can use the OS-scheduler, for instance, CRON for UNIX-like systems. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] alter table schema, default sequences stay the same
In response to Scott Marlowe :
> On Tue, Jun 29, 2010 at 8:20 AM, A. Kretschmer
> wrote:
> >
> >
> > Just for info: works well with 8.4:
>
> Works in 8.3.9 on ubuntu 9.10...
>
I think, this is the problem:
You have created the table first and later the sequence, like this:
test=# create table public.s (i int);
CREATE TABLE
test=*# create sequence my_seq;
CREATE SEQUENCE
test=*# alter table s alter column i set default nextval('my_seq');
ALTER TABLE
test=*# create schema bla;
CREATE SCHEMA
test=*# alter table s set schema bla;
ALTER TABLE
test=*# \d bla.s
Table "bla.s"
Column | Type | Modifiers
+-+-
i | integer | default nextval('my_seq'::regclass)
You have now a table in schema bla and the sequence still in public. To
avoid that you have to alter the sequence too:
test=# create table public.s (i int);
CREATE TABLE
test=*# create sequence my_seq;
CREATE SEQUENCE
test=*# alter table s alter column i set default nextval('my_seq');
ALTER TABLE
test=*# alter sequence my_seq owned by s.i;
ALTER SEQUENCE
test=*# create schema bla;
CREATE SCHEMA
test=*# alter table s set schema bla;
ALTER TABLE
test=*# \d bla.s
Table "bla.s"
Column | Type |Modifiers
+-+-
i | integer | default nextval('bla.my_seq'::regclass)
But i'm not sure if 'alter sequence owned ...' available in 8.2.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] alter table schema, default sequences stay the same
In response to Sim Zacks :
> I am using 8.2.17
>
> I added a new schema and moved tables into it using
>
> ALTER TABLE tblname SET SCHEMA newschema;
>
>
> This moves the sequences referred to by the table to the new schema as
> is specified by the manual.
>
> > Associated indexes, constraints, and sequences owned by table columns
> > are moved as well.
>
> I was very surprised to find that the default nextval functions still
> refer to the sequence public.sequencename
>
> I discovered this when I tried to insert and it told me the sequence
> does not exist.
>
>
> > id integer NOT NULL DEFAULT
> > nextval(('public.tblname_id_seq'::text)::regclass)
> Shouldn't this change automatically as well?
> Is there an easy way to modify all the default values now?
Just for info: works well with 8.4:
test=*# create schema bla;
CREATE SCHEMA
test=*# create table public.s (i serial);
NOTICE: CREATE TABLE will create implicit sequence "s_i_seq" for serial column
"s.i"
CREATE TABLE
test=*# \d s
Table "public.s"
Column | Type | Modifiers
+-+---
i | integer | not null default nextval('s_i_seq'::regclass)
test=*# alter table s set schema bla;
ALTER TABLE
test=*# \d bla.s
Table "bla.s"
Column | Type | Modifiers
+-+---
i | integer | not null default nextval('bla.s_i_seq'::regclass)
test=*# select version();
version
PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
4.3.2-1.1) 4.3.2, 64-bit
(1 row)
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] Need Some Recent Information on the Differences between Postgres and MySql
In response to John Gage : > Forgive me for being somewhat stupid, but is MyISAM a text search > engine? The Wikipedia article doesn't make it sound like one. MyISAM provides textsearch and other features, but no referential integrity. It's just one of many storage engines. > > Could you be more specific as to how, for example, MySQL implements > regular expressions or the tsvector funcitionality? I think, this is the wrong place to explain mysql-features... Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Need Some Recent Information on the Differences between Postgres and MySql
In response to Dave Page : > On Fri, Jun 25, 2010 at 9:52 AM, Thom Brown wrote: > > > Didn't PostgreSQL used to have more than 1 storage engine in the past? > > I thought I read somewhere it did, but it was decided it was a > > compromise on stability and/or quality, so ended up using a single > > kick-ass engine? > > Yes, many, many moons ago. Really? Do you have a link? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Single quotes vs. double quotes when setting a pwd and other cmds
In response to Alexander Farber : > And identifiers means column names (eventually containing whitespace)? Right. test=# select 'foo' as "my new column"; my new column --- foo (1 row) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Single quotes vs. double quotes when setting a pwd and other cmds
In response to Alexander Farber : > Hello, > > why aren't double quotes accepted below? > > db1=# alter user user1 password "pass1"; > ERROR: syntax error at or near ""pass1"" > LINE 1: alter user user1 password "pass1"; > ^ > db1=# alter user user1 password 'pass1'; > ALTER ROLE > > Is there a thumb rule to know when to use which quotes? > I'm often confused by them when using psql. Use ' for variable strings (values), and " for identifier (table-name, column-name). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] disable password prompt - command line
In response to Ravi Katkar : > > > Hi List, > > > > I need a small help regarding the password options available with PGSQL, I > found POSTGRE SQL has ?W and ?password options available which is prompting > for > the password. But I want to take the password thru command line argument and > keep the password in a variable. Is there any option which will help me to > take > the password and used in the below way so that it will connect to the DB > without asking password again. http://www.postgresql.org/docs/current/static/libpq-pgpass.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Ideal Disk recommendation for PostgreSQL
In response to Bhaskar Sirohi : > Hi All, > > We are right now in initial phase to setup a production server having > PostgreSQL database installed and would require help with Disk configuration. > The database size would grow approx to 500 GB. I have gone through the > following link http://momjian.us/main/writings/pgsql/hw_performance/index.html > which was very helpful. However what I would like to understand is, do you > have any figures around minimum / optimal disk performance for postgresql to > operate? As an example I remember MS have a document on Exchange that gives Not really. If you have enough money, you can divide your disk-space into 2 or 3 parts (separate disks and controllers) for data, indexes and WAL-logging, using tablespaces. But all depends on your workload (and the available money ...) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Is there a way to backup Postgres via SQL commands?
In response to Frank Church : > Are there SQL commands that can do a backup over a client connection, > rather than from the command line like pgsql etc? > > By that I mean some kind of SELECT commands that can retrieve the > database's content as SQL commands that can be replayed to a server to > restore it, rather than something that saves directly to file, or > passes it through a pipe? What's wrong with pg_dump? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Is it possible to make the order of output the same as the order of input parameters?
In response to Sam Mason : > Isn't this fun; here's another version using window functions (from PG > 8.4 onwards) this time: > > SELECT c.* > FROM customer c, ( > SELECT *, row_number() OVER () > FROM (VALUES (23), (56), (2), (12), (10)) x) x(val,ord) > WHERE c.id = x.val > ORDER BY x.ord; Wow, that's really cool and a nice case for row_number(). Thx. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Is it possible to make the order of output the same as the order of input parameters?
In response to Stephen Frost : > * m. hvostinski (makhv...@gmail.com) wrote: > > I have a simple query like: > > > > SELECT * FROM customer WHERE id IN (23, 56, 2, 12, 10) > > > > The problem is that I need to retrieve the rows in the same order as the set > > of ids provided in the select statement. Can it be done? > > Not very easily. My first thought would be doing something like: > > SELECT > customer.* > FROM > customer a > JOIN (VALUES (1,23),(2,56),(3,2),(4,12),(5,10)) b > ON (a.id = b.column2) > ORDER BY b.column1 Nice ;-) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Transaction with in function
In response to Ravi Katkar : > > > I looking for solution for commit, rollback with in function. You can't use transactions within functions, use savepoints instead. http://www.postgresql.org/docs/current/static/sql-savepoint.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] pg_dump cannot connect when executing by a script
In response to Luca Ferrari : > Hi all, > I've found in the net a lot of problems similar to mine, but not the solution > for my case: when I executed pg_dump against a database from a script (that > will be executed by cron) I got the following error: > > pg_dump: [archiver (db)] connection to database "webcalendardb" failed: could > not connect to server: No such file or directory > Is the server running locally and accepting > connections on Unix domain socket "/tmp/.s.PGSQL.5432"? Your unix-scket isn't in /tmp. Start psql -h localhost and type: show unix_socket_directory; This will show you the corrent path to the unix-socket. You can use that for pg_dump with option -h Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] getting all constraint violations
In response to Gauthier, Dave : > Hi: > > > > I have a table with many constraints. A user tries to insert a record that > violates many of them. The error message I get back lists the first > violation. > How cani I (or can I) get them all? I think that isn't possible: the first violation throws the error, the transaction stopped. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] copy data from one db into another via copy & psql
In response to Kevin Kempter : > Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so > I can load the data into a table in the second db 'inline' without writing to > & reading from a flat file? Yes, but keep in mind, COPY cant create the table on the destination. If the destination contains a (empty) table it's no problem. If the destination-db doesn't contain the table you can also use pg_dump: kretsch...@tux:~$ createdb new_test kretsch...@tux:~$ pg_dump -t foo test | psql -X new_test -f - SET SET SET SET SET SET SET SET SET CREATE TABLE ALTER TABLE kretsch...@tux:~$ psql new_test -c "select * from foo" t -- a short test (1 row) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] default ordering of query result - are they always guarantee
In response to Guillaume Lelarge : > > This is the default behavior I want. However, I am not sure whether this is > > always guarantee? Or shall I explicitly make the query in the following > > form? > > An you're right. It's not guaranted. The only guaranted way is to use > ORDER BY your_column. ACK. One reason is the new feature 'synchronised scan' sind 8.3. It's a big performance boost, but engendered unsorted results. (if there is not the ORDER BY - statement) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] creating a table based on a table in stored in another database
In response to Malm Paul : > Hi list, > in a database I have different kind of tables. I would like to take the meta > data from one of those tables and create the same type of table (but empty) > in another database. > Can anyone, please, tell me how to do this? Create a schema-only dump and restore it into the other database. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Weird unique constraint
In response to Mike Christensen : > I have the following constraint which almost works: > > ALTER TABLE ingredientforms ADD CONSTRAINT > ingredientforms_UniqueIngredientForm UNIQUE(IngredientId, > FormDisplayName); > > However, I want to allow rows that have the same IngredientId > FormDisplayName /iff/ FormDisplayName is null. If FormDisplayName is > not null, then it must be unique. > > 1, NULL > 1, NULL > > Would be allowed. > > 1, 'Foo' > 1, 'Foo' > > would violate the constraint. > > 1, 'Foo' > 1, 'Bar' > > would be allowed. > test=# \d mike Table "public.mike" Column | Type | Modifiers +-+--- id | integer | t | text| test=# create unique index idx_mike_unique on mike (id, t) where t is not null; CREATE INDEX test=*# insert into mike values (1, null); INSERT 0 1 test=*# insert into mike values (1, null); INSERT 0 1 test=*# insert into mike values (1, 'Foo'); INSERT 0 1 test=*# insert into mike values (1, 'bar'); INSERT 0 1 test=*# insert into mike values (1, 'Foo'); ERROR: duplicate key value violates unique constraint "idx_mike_unique" Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Pulling data from a constraint def
In response to Gauthier, Dave :
> I have a constraint defined on a table
>
> constraint design_style_is_invalid check (design_style in
> ('rls','sdp','rf','ssa','rom','rpt','analog','sdprpt','clkdist','global')),
>
> Is there a way to get the valid values in the list from the metadata somehow?
Sure, read the answer from Josh Kupershmidt. But keep in mind, internal
tables maybe changed in the next version -> your code isn't portable.
> I?d rather not put these in a table and implement with a foreogn key
> constraint
> for performance reasons. (Does that make sense?)
If i were you, i would use exactly this way.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] log database in which error occurs
In response to Alexander Pyhalov : > May 11 01:36:46 pgsql pgsql[73794]: [7-3] STATEMENT: select count(*) > from forum_msg where grp_id=6709 (200 ok) ACCEPTED and is_blocked='f' > > Are there any ways to log database, to which invalid query was issued ? Sure: log_line_prefix = '%t ' # special values: # %u = user name # %d = database name ... > Are there any ways to separate general server errors and errors per each > database in several log files? I'd like general errors like SEVERE ones > to go to the /var/log/postgresql/general.log, and per-db errors on user > queries go to the /var/log/postgresql/db_$dbname.log . IIRC no, but you can use tools like grep to achieve that. Do you know pgfouine? http://pgfouine.projects.postgresql.org/ Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Performance issues when the number of records are around 10 Million
In response to venu madhav : > Hi all, >In my database application, I've a table whose records can > reach 10M and insertions can happen at a faster rate like 100 > insertions per second in the peak times. I configured postgres to do > auto vacuum on hourly basis. I have frontend GUI application in CGI > which displays the data from the database. > When I try to get the last twenty records from the database, > it takes around 10-15 mins to complete the operation.This is the query > which is used: > > select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name, > e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1, > e.wifi_addr_2, e.view_status, bssid FROM event e, signature s WHERE > s.sig_id = e.signature AND e.timestamp >= '1270449180' AND > e.timestamp < '1273473180' ORDER BY e.cid DESC, e.cid DESC limit 21 > offset 10539780; First, show us the table-definition for both tables. Secondly the output generated from EXPLAIN ANALYSE I'm surprised about the "e.timestamp >= '1270449180'", is this a TIMESTAMP-column? And, to retrieve the last twenty records you should write: ORDER BY ts DESC LIMIT 20 With a proper index on this column this should force an index-scan. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] PG 8.3.7. Windows 7. select inet_server_addr() returns ::1 - the loopback adrs.
In response to Abraham, Danny : > The machine is on IPV4. > > How can I retrieve the real IP adrs? kretsch...@tux:~$ psql -h localhost test Password: Timing is on. psql (8.4.2) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. test=# select inet_server_addr(); inet_server_addr -- 127.0.0.1 (1 row) Time: 0.529 ms test=*# \q kretsch...@tux:~$ psql -h 10.0.0.102 test Password: Timing is on. psql (8.4.2) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. test=# select inet_server_addr(); inet_server_addr -- 10.0.0.102 (1 row) Time: 0.500 ms You can see the difference? > > Is 8.3.7 supported on Windows 7? I think yes, but i'm not sure. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Dynamic SQL with pgsql, how to?
In response to Andre Lopes : > Hi, > > I need to write some dynamic SQL in pgsql. > There is documentation on how can I do this in pgsql? Sure, http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] temp tables
In response to Geoffrey : > Do temp tables need to be explicitly dropped, or do the go away when the > process that created them leaves? The latter one. But explicitely delete them isn't an error. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] using between with dates
In response to Geoffrey Myers : > I'm trying the following: > > ship_date between '04/30/2010' AND '04/30/2010' + 14 > > But this returns: > > ERROR: invalid input syntax for integer: "04/30/2010" > > Can I use between with dates? Sure, why not, but you have to CAST your STRING into a DATE, or you have to use to_date(). test=*# select '04/30/2010'::date; date 2010-04-30 (1 row) And yes, write "+ '14 days'::interval " instead of just only +14. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Complete row is fetched ?
In response to Raymond O'Donnell : > On a related note, what happens when you do something like this? - > > select count(*) > > Does any data actually get read? No, it check's only the visibility for each record -> seq-scan. > > Is there any difference internally to saying "count(1)" instead? No, it's the same execution plan. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Complete row is fetched ?
In response to Satish Burnwal (sburnwal) : > I have a ques - say I have a table that has 10 columns. But in a simple > select query from that table, I use just 3 columns. I want to know > whether even for fetching 3 columns, read happens for all the 10 columns > and out of that the required 3 columns are returned ? ie Does the > complete row with all the 10 columns are fetched even though I need just > 3 columns ? OR only 3 columns are fetched ? Depends, large columns (TEXT, BYTA) are TOASTed¹, that means, the content of this columns stored in a other table. So it is always a good idea to specify all rows you needed and don't use select *. ¹ http://www.postgresql.org/docs/8.4/interactive/storage-toast.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] How to get whether user has ALL permissions on table?
In response to dipti shah :
> Thanks Kretschmer but I have seen those function. The below query returns
> error
> but you could see that 'user1' has ALL permissions on table 'techtable'.
>
> techdb=# SELECT has_table_privilege('user1', 'techtable', 'ALL');
> ERROR: unrecognized privilege type: "ALL"
>
> Do I have to run this command as below which includes all permissions
> explicitly? Did I miss anything?
Right, you have to name all privileges.
The desired access privilege type is specified by a text string, which
must evaluate to one of the values SELECT, INSERT, UPDATE, DELETE,
TRUNCATE, REFERENCES, or TRIGGER.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] How to get whether user has ALL permissions on table?
In response to dipti shah :
>
> Hi, I have granted ALL permissions on 'techtable' to 'user1'.
>
> techdb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn where
> pc.relnamespace=pn.oid and pn.nspname='techdb' and pc.relname='techtable';
> relname | relacl
> ---+---
> techtable | {postgres=arwdDxt/postgres,=ar/postgres,user1=arwdDxt/postgres}
> (1 row)
>
> Could anyone please tell me if there is any function or command in PostGreSql
> which returns True if given user has ALL permissions on given table? Is there
Sure, read
http://www.postgresql.org/docs/8.4/interactive/functions-info.html
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] [SOLVED] Error in Trigger function. How to correct?
In response to Andre Lopes : > Thanks a lot, it works! > > I'am using Postgres Plus Advanced Server 8.3R2 in development.In production I > user PostreSQL 8.3.9. Yeah, AFAIK is the "Postgres Plus Advanced Server" the version of the regular PG-version plus 1. So you have 8.2 as development and 8.3 as production version. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Error in Trigger function. How to correct?
In response to Andre Lopes : > Hi, > > I have a trigger that runs in my Development machine but not in my Production > machine. the code is the following: > SQL Error: > > ERROR: function replace(text, unknown, integer) does not exist > LINE 1: select replace(replace(replace(replace(replace(replace( $1 ,... > ^ Let me guess: you have 8.2 on your Development machine and 8.3 or 8.4 on your production machine, right? I think you need to add explicit casts, let me explain: > HINT: No function matches the given name and argument types. You might need > to > add explicit type casts. > QUERY: select replace(replace(replace(replace(replace(replace( $1 , > '@numero_anuncio@', a.id_anuncio_externo), '@nome_anuncio@', a.n_anuncio), > '@idade@', EXTRACT(year from AGE(NOW(), a.dat_nasc))), '@telefone_anuncio@', EXTRACT(year ...) returns an INT, but replace(...) expects a TEXT or a similar type. Try to add a ::TEXT after the EXTRACT(...) - function: extract (year from ...)::text Maybe there are more occurrences ... Greetings from saxony, germany. -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] beginner problems with count(*)
In response to Me Self : > Hello Im just getting started using postgres and Ive run run into a > problem with count(*): > > When I do "select count(*) from mytable" or "select count(*) from > mytable where mycolumn=x" then I get wrong number. The number of rows > returned is lower than the actual number. How can that happen? The What exactly do you mean with 'actual number'? Let me guess: you have a SERIAL column, and the current value is greater than the number of rows returned by your select, right? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Running/cumulative count using windows
In response to Oliver Kohll - Mailing Lists :
> Hello,
>
> I'm still reasonably new to windowing functions, having used a few since 8.4
> came out. I wonder if anyone can help with this one.
>
> I've got a table of email addresses in a CRM system similar to the following:
>
> CREATE TABLE test(
> signup_date timestamp,
> email_address varchar(1000)
> );
> INSERT INTO test(signup_date, email_address) VALUES(now(), 't...@test.com');
> INSERT INTO test(signup_date, email_address) VALUES(now(), 't...@test1.com');
> INSERT INTO test(signup_date, email_address) VALUES(now() - '1
> month'::interval, 't...@test2.com');
>
> I'd like a running count, or cumulative count of the number of signups per
> month. I'm pretty sure a window function would do it but I can't work it out.
>
> So a plain count by month would be
>
> SELECT date_part('year',signup_date) as year, date_part('month',signup_date)
> as
> month, count(*)
> FROM test
> GROUP BY year, month
> ORDER BY year, month;
>
> giving
>
> year | month | count
> --+---+---
> 2010 | 2 | 1
> 2010 | 3 | 2
>
> How would you make the count a cumulative one? The output should then be
>
> year | month | count
> --+---+---
> 2010 | 2 | 1
> 2010 | 3 | 3
>
test=*# select * from test;
signup_date | email_address
+
2010-03-30 13:12:17.908418 | t...@test.com
2010-03-30 13:12:17.908418 | t...@test1.com
2010-02-28 13:12:17.908418 | t...@test2.com
(3 rows)
test=*# select extract (year from signup_date)::text || '/' ||
extract(month from signup_date)::text, count(email_address),
sum(count(email_address)) over (ROWS UNBOUNDED PRECEDING) from test
group by 1 order by 1;
?column? | count | sum
--+---+-
2010/2 | 1 | 1
2010/3 | 2 | 3
(2 rows)
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] How long will the query take
In response to John Gage : > I ran a query out of pgAdmin, and (as I expected) it took a long > time. In fact, I did not let it finish. I stopped it after a little > over an hour. > > I'm using 8.4.2 on a Mac with a 2.4GHz processor and 2GB of RAM. > > My question is: is there a way to tell how close the query is to being > finished. It would be a great pity if the query would have finished > in the 10 seconds after I quit it, but I had no way of telling. > > As a postscript, I would add that the query was undoubtedly too > ambitious. I have a reduced set version which I will run shortly. > But I am still curious to know if there is a way to tell how much time > is left. No, not really. But you can (and should) run EXPLAIN to obtain the execution plan for that query, und you can show us this plan (and the table-definition for all included tables). Maybe someone is able to tell you what you can do to speed up your query. And yes, have you tuned your postgresql.conf? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] best practice in archiving CDR data
In response to Juan Backson : > Hi, > > I am using Postgres to store CDR data for voip switches. The data size > quickly > goes about a few TBs. > > What I would like to do is to be able to regularly archive the oldest data so > only the most recent 6 months of data is available. > > All those old data will be stored in a format that can be retrieved back > either > into DB table or flat files. > > Does anyone know how should I go about doing that? Is there any existing tool > that can already do that? Sounds like table partitioning: create, for instance, a table for each month and DROP old tables after 6 month or so. http://www.postgresql.org/docs/current/static/ddl-partitioning.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question (or feature-request): over ( partition by ... order by LIMIT N)
Hello @all, I know, i can do: select * from (select ... row_number() over (...) ...) foo where row_number < N to limit the rows per group, but the inner select has to retrieve the whole set of records and in the outer select most of them discarded. Why isn't there an over ( ... LIMIT N) ? Other (better) solution? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] PL/pgSQL & OVERLAPS operator
In response to Tuo Pe : > Hello! > > I am teaching myself PL/pgSQL. I am trying to write a function that tests > whether two time periods overlap. I want to test the function parameters > against these two values in "overlaptest" table: > > select * from overlaptest; > id |alku |loppu > +-+- > 1 | 2010-03-23 10:00:00 | 2010-03-23 12:00:00 > (1 row) > > I have written this function, > > CREATE OR REPLACE FUNCTION TryOverlap(text, text) RETURNS boolean AS $$ > DECLARE > ts_start timestamp with time zone := CAST ($1 AS TIMESTAMP WITH TIME > ZONE); > ts_end timestamp with time zone := CAST ($2 AS TIMESTAMP WITH TIME ZONE); > alku timestamp with time zone; > loppu timestamp with time zone; > BEGIN > SELECT alku,loppu FROM overlaptest WHERE id = 1 INTO alku,loppu; > IF ((alku,loppu) OVERLAPS (ts_start,ts_end)) THEN > RETURN true; > END IF; > RETURN false; > END; > $$ LANGUAGE plpgsql; > > However, it always seems to return the value false. What's the problem here? You have alku and loppu as variable and as table-column, that's a bad idea, maybe that's an error, i'm not sure. Btw.: you can use the PERIOD-datatype: 11:16 < akretschmer> ??period 11:16 < pg_docbot_adz> For information about 'period' see: 11:16 < pg_docbot_adz> http://wiki.postgresql.org/wiki/RangeTypes 11:16 < pg_docbot_adz> http://pgfoundry.org/projects/temporal 11:16 < pg_docbot_adz> http://github.com/davidfetter/PostgreSQL-Temporal And 9.0 contains a new feature: exclusion constraints: http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Create a function that updates the record with and timestamps
In response to Richard Sickler : > > I am not sure if there is a very simple way of doing this? > > > > Or, do I need to create a function and a trigger to call the row and > update > > with new data and set the last_modified to current_date? > > Yes, that's the way, a TRIGGER on UPDATE for each row. I think, the doc > contains an example. > > > Andreas > > > From a novice: I use last_updated_at timestamp without time zone NOT NULL > DEFAULT now() This works only for INSERT, but not for UPDATE. test=# create table richard (id int, last_updated_at timestamp without time zone NOT NULL DEFAULT NOW()); CREATE TABLE test=# insert into richard (id) values (1); INSERT 0 1 test=# select * from richard ; id | last_updated_at + 1 | 2010-03-23 06:54:28.656668 (1 row) test=# select now(); now --- 2010-03-23 06:54:42.443224+01 (1 row) test=# UPDATE richard set id=2 where id=1; UPDATE 1 test=# select * from richard ; id | last_updated_at + 2 | 2010-03-23 06:54:28.656668 (1 row) As you can see, the last_updated_at isn't up-to-date ;-) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Create a function that updates the record with and timestamps
In response to Chris Barnes : > I would like to have postgres update the last_modified column with the > current_date on an update of the record. > > I am not sure if there is a very simple way of doing this? > > Or, do I need to create a function and a trigger to call the row and update > with new data and set the last_modified to current_date? Yes, that's the way, a TRIGGER on UPDATE for each row. I think, the doc contains an example. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Help me with this tricky join
In response to Jay : > Thanks! > But, since the master can contain many users (user2, user3, and so on) > I suppose this won't be a proper solution? > Sorry if I was a bit unclear in my description. > > I.e., the master is of the form: > > user_id date > User1 20010101 > User1 2101 > User1 19990101 > User1 19970101 > User2 ... > ... That's not the problem ... > > Btw, I'm using Postgre version 8.2 and I cannot use subqueries do the but this. lag() over () and similar windowing functions new since 8.4. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] How to dump JUST procedures/funnctions?
In response to Carlo Stonebanks : > Is pg_get_functiondef an 8.4 appears to be an 8.4 function? Yes, new since 8.4. > > I don't see it in the 8.3 documentation and the servers in question are all > 8.3. > > Any alternatives for 8.3? pg_proc has the code body, but not the function > declaration, etc. Afaik no, you can make a schema-dump and extract the function declarations from the dump. Or, upgrade to 8.4 ;-) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] return row from plpgsql?
In response to zhong ming wu :
> Hello
>
> I have tried to return rowtypes and record from plpgsql
> but they don't look like anything what is returned from select a,b,c
> from table d;
Can you show us your function?
> I prefer to do this simply as
>
> select aplpgsqlfunction('%u')
>
> The only way it comes close to this postgres documentation is by using
> view but it's not possible within my imap server
> requirement.
No, you can use a function, no problem. I will show you an example:
test=# select * from foo;
a | b
---+---
1 | 1
2 | 2
3 | 3
(3 rows)
test=# create or replace function f_foo() returns setof record as $$begin
return query select * from foo; end; $$language plpgsql;
CREATE FUNCTION
test=# select * from f_foo() as (a int, b int);
a | b
---+---
1 | 1
2 | 2
3 | 3
(3 rows)
As you can see, you have to define the returnig table-structure.
Without the 'as (...)' you got an error:
test=# select * from f_foo();
ERROR: a column definition list is required for functions returning "record"
LINE 1: select * from f_foo();
^
To avoid the eror and the table-definition in your query you can use
IN/OUT-Parameters for your function:
test=# create or replace function f_foo(out x int, out y int) returns setof
record as $$begin return query select * from foo; end; $$language plpgsql;
CREATE FUNCTION
test=# select * from f_foo();
x | y
---+---
1 | 1
2 | 2
3 | 3
(3 rows)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
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] Unable to connect to Postgres database from email marketing software on the same host
In response to Major Services : > Not sure how to check the server-log? Please help. > > New to Postgres & Linux! Just read the logfile, it's usually somewhere under /var/log/postgresql. On my machine for instance /var/log/postgresql/postgresql-8.4-main.log > > > On Mon, Mar 15, 2010 at 5:48 PM, Major Services > wrote: > > Error message is "Server call failed for unknown reason" Your quoting-style is arduously. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Unable to connect to Postgres database from email marketing software on the same host
In response to Major Services : > Error message is "Server call failed for unknown reason" Okay, i know from the other post, you have also a proper host-entry. Well, this error-message isn't helpfull, can you see in the server-log for a more detailed error message? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Unable to connect to Postgres database from email marketing software on the same host
In response to Major Services : > Hi, > > I have an email marketing software installed on the same server as > PostgresSQL database. > Am unable to connect to the database from this application. The > database owner is dbadmin. Error-message? > > My pg_hba.conf has one entry as: > > local all dbadmin trust > > What other setup is required for this? Is your software trying to establish a connection via UNIX-Sockets or via TCP/IP? If TCP/IP, you needs a proper host - entry in your pg_hba.conf. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] querying the value of the previous row
In response to Chris Velevitch : > I'm to write a query like:- > > select > case when column_name1 <> value_of_previous(column_name1) > then column_name1 end as column > ,column_name2 > from table > ordered by column_name1, column_name2 Okay, with this table: test=# select * from foo; col1 | col2 --+-- 1 |1 1 |2 1 |3 2 |4 2 |5 2 |6 2 |7 3 |8 4 |9 5 | 10 (10 rows) you can do: test=# select case when col1::text <> coalesce(lag::text,'NULL') then col1 else null end as col1, col2 from ( select col1, lag(col1) over (range unbounded preceding ), col2 from foo order by col2 ) foo order by col2; col1 | col2 --+-- 1 |1 |2 |3 2 |4 |5 |6 |7 3 |8 4 |9 5 | 10 (10 rows) > > How do I do this? (I'm using pg 7.4) Unfortunately (for you), i'm using a window-function, in this case lag(), new since 8.4. Your version 7.4 has reached End-of-Lifetime, so i suggest you update to 8.4. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Statement Triggers
In response to Gordan Bobic : > Specifically, what features of the SQL statement that triggered the event > are available to the function invoked by the trigger? Say I wanted to write http://www.postgresql.org/docs/8.4/interactive/plpgsql-trigger.html Except for NEW and OLD. > all INSERT statements executed on a table into a log file. How would I > access the original statement in the triggered function? If I cannot access You have no access, the statement is not visible. You can use such a TRIGGER, for instance, to send a message to clients via LISTEN/NOTIFY. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Finding duplicates only.
In response to Greenhorn : > Hi, > > Can someone please help me with this duplicate query. > > I'm trying to: > > 1. Return duplicates only. (without including the first valid record), and I will try to help you. Assuming this table: test=*# select * from greenhorn order by id; id | inspection_time + 1 | 2010-03-11 07:14:14.290259 1 | 2010-03-11 07:14:14.290259 2 | 2010-03-11 07:14:14.290259 3 | 2010-03-11 07:15:14.290259 4 | 2010-03-11 07:16:14.290259 5 | 2010-03-11 07:24:14.290259 6 | 2010-03-11 07:34:14.290259 (7 rows) The record with id=1 is twice. > 2. Return as duplicate if the difference between a.inspection_time > and b.inspection time is under 5 minutes. Assuming you have a 8.4-version: with the table above, and time-difference < 2 minutes, rows 2, 3 and 4: test=*# select * from ( select id, inspection_time, lag(inspection_time) over (order by inspection_time RANGE UNBOUNDED PRECEDING) from greenhorn group by 1,2 ) foo where inspection_time-lag < '2minutes'::interval; id | inspection_time |lag ++ 2 | 2010-03-11 07:14:14.290259 | 2010-03-11 07:14:14.290259 3 | 2010-03-11 07:15:14.290259 | 2010-03-11 07:14:14.290259 4 | 2010-03-11 07:16:14.290259 | 2010-03-11 07:15:14.290259 (3 rows) > > Here's the query string I'm using to retrieve the duplicates but it is > returning every duplicate records. > > select a.rego, > a.inspection_date, > a.inspection_time, Why do you have 2 fields, one for date and one for time? Use one timestamp-field instead. Regards, hope that helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] to_timestamp() and quarters
In response to Tom Lane : > Asher Hoskins writes: > > I can't seem to get to_timestamp() or to_date() to work with quarters, > > The source code says > > * We ignore Q when converting to date because it is not > * normative. > * > * We still parse the source string for an integer, but it > * isn't stored anywhere in 'out'. > Ouch, sound like MySQL / myisam: we accept things like foreign key, but we don't implement that... +1 for raise an error or something, but no 'accept & ignore'. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] current transaction id
In response to AI Rumman : > How to find the current transaction id of the database? select txid_current() Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Two Versions of PostgreSQL Installed - How to uninstall one particular version
In response to Wang, Mary Y : > Thanks Andreas for the info. I'm working on a development server right now, > and currently I don't have any data loaded yet. As matter of fact, I was > trying to load the database data from a dump file that generated by > "pg_dump". > Here is the thing: > I've a /usr/bin/initdb -> this is probably from version 7.3 and other > postgres related executable files. > I've a /usr/local/pgsql/bin/initdb -> this is probably from version 8.3.8. I > think all the postgres related executable files are located in this > directory. I like this organization much better in 8.3.8. > My question is what files should I delete from /usr/bin directory? Is there > an automatic way to uninstall a particular version? I want to delete the 7.3 > version and don't want to have multiple versions of postgres running on the > same server. > If there is not an automatic way, then I can probably remove files like > initdb, dropdb, createdb, psql and etc from the /usr/bin directory. > Or may be just specify the path when I run the initdb command > "/usr/local/pgsql/bin/initdb" (other wise it would use /usr/bin/initdb) > > Any thoughts? I'm not familiar with RH, maybe someone else can better help you. But i think, you should use the packaging system, RPM, for software-(de)installation. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Two Versions of PostgreSQL Installed - How to uninstall one particular version
In response to Wang, Mary Y : > Hi, > > I got this error: > "-bash-2.05b$ /usr/local/pgsql/bin/pg_ctl start > server starting > -bash-2.05b$ FATAL: database files are incompatible with server > DETAIL: The data directory was initialized by PostgreSQL version 7.3, which > is not compatible with this version 8.3.8." > > I think I must have two versions of Postgres installed. How do I > uninstall the 7.3 version? Do I need to do a manual uninstall by > removing Postgres related files from /usr/bin and etc? Or is there an > automatic way? The problem is that I'm unclear what files need to be > removed. I think might also need to delete a file in the /etc/init.d > directory. I think, you have a problem ;-) You have a data directory initialized by PostgreSQL version 7.3. Because of this, you need a running 7.3-Server to acces to this data. But you have installed 8.3.8. You should now install a 7.3 - Server tu access to the data. Make a Backup of the data-directory before! If the 7.3 - Server is running, you can make a Backup. You should make this Backup with the 8.3-Server (yes, you can have multiple versions installed on the same computer and the same time) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Global Temp Table
In response to Shameem Ahamed : > Hi, > > I want to create a global temp table in database, which can be > accessed from any session to the database. Is it possible ?. No. > > I tried with create global temp table , but the table created exist > only in that specific session. No other session is able to access the > table. > > How can i sort it out ?. Use a regular table instead. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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 install fails with 1603 error
In response to Mitesh Patel : > PostgreSQL version: 8.2.15 > Operating system: Windows 2003 > > PostgreSQL 8.2 install fails with exit code 1603. > > Any idea?? what could be wrong. I am running install from console. I mean no > RDP and using administrator AD account. I can't help you, i'm not familiar with windows, but why this old version? Current version is 8.4.2. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Subqueries or Joins? Problems with multiple table query
In response to A. Kretschmer : > In response to Thom Brown : > > On 23 February 2010 13:43, Stefan Schwarzer > > wrote: > > >>>> Select countries.name, basel.year, basel.value, cites.year, cites.value > > >>>> From countries > > >>>> Left Join basel on basel.id_country = countries.id_country and > > >>>> basel.value=1 > > >>>> Left Join cites on cites.id_country = countries.id_country and > > >>>> cites.value=1 > > >>> > > >>> I would have thought so, but the query turns forever. > > >> > > >> How many rows in each of your tables, and what indexes do you have? > > > > > > around 5000 rows > > > > > > > It takes a long time for just 5000 rows? Could you provide the > > execution plan for it by putting EXPLAIN ANALYZE at the front of your > > query and post the result here? > > I think, this query: Ouch, i'm wrong, sorry. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Subqueries or Joins? Problems with multiple table query
In response to Thom Brown : > On 23 February 2010 13:43, Stefan Schwarzer > wrote: > Select countries.name, basel.year, basel.value, cites.year, cites.value > From countries > Left Join basel on basel.id_country = countries.id_country and > basel.value=1 > Left Join cites on cites.id_country = countries.id_country and > cites.value=1 > >>> > >>> I would have thought so, but the query turns forever. > >> > >> How many rows in each of your tables, and what indexes do you have? > > > > around 5000 rows > > > > It takes a long time for just 5000 rows? Could you provide the > execution plan for it by putting EXPLAIN ANALYZE at the front of your > query and post the result here? I think, this query: SELECT countries.name, (SELECT yearAS basel FROM basel WHERE value = 1 AND countries.id = basel.id_country) AS basel, (SELECT yearAS cites FROM cites WHERE value = 1 AND countries.id = cites.id_country) AS cites FROM countries, basel, cites (copy & paste from his post) will produce a cross-join: test=*# select count(1) from (select c.name, (select name from con_1 where id=c.id), (select name from con_2 where id=c.id), (select name from con_3 where id=c.id) from con c) foo; count --- 3 (1 row) test=*# select count(1) from (select c.name, (select name from con_1 where id=c.id), (select name from con_2 where id=c.id), (select name from con_3 where id=c.id) from con c, con_1, con_2, con_3) foo; count --- 54 (1 row) (the first query is similar to his query but without all tables in the enclosing from-list, the second query is very similar to his query) So i'm not astonished about a long time for only 5000 rows... Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Subqueries or Joins? Problems with multiple table query
In response to Stefan Schwarzer : > >You may also wish to review Andreas' suggestions as they propose a > >more sensible table structure rather than having a table for each > >convention. > > The table proposal really looks nice. But our database is structured > by variable - so each convention has its own table. It is a really bad design - it can't scale. A new convention, and you have to redesign your database schema (add a new table, rewrite code). I think, it makes absolutely no sense to invest more work into this, sorry, shit. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Subqueries or Joins? Problems with multiple table query
In response to Stefan Schwarzer : > Hi there, > > gush, shouldn't be that complicated. But neither in Postgres, nor in Access I > succeed in getting the result I wish. > > I have a couple of times for the Environmental Conventions (Kyoto, Montreal, > CITES etc.). They look like this: > > id_country,year,value > 4,1992,0 > 4,1993,0 > 4,1994,0 > 4,1995,0 > 4,1996,1 > 4,1997,0 > 4,1998,0 > 4,1999,0 > 4,2000,0 > > so that I can see the year where the treaty has been ratified by a country. > (The rows with the zero values seems to be unnecessary, I have to check that > again with the data supplier.) > > Now, I would like to have a list of all (european) countries and the treaties > they have signed, in the following style: > > country_name,year_kyoto,signed_kyoto,year_montreal,signed_montreal, > Germany 1996 1 1992 > > 1 > France 1995 1 1994 > >1 ... > > Again, the field with the "signed_..." is not necessary, but I just want to be > sure that the query is running correctly. > > I tried it with subqueries - something like this: Not sure if i understand you corrently, if not, provide more information (table structure and data), if possible copy&paste - able. Okay, let me try: test=*# select * from country ; id | name +- 1 | germany 2 | use 3 | france (3 rows) test=*# select * from conventions ; id_country | convention | year ++-- 1 | Kyoto | 1996 1 | Montreal | 2002 2 | Kyoto | 1998 (3 rows) test=*# select c.name, sum(case when c2.convention='Kyoto' then c2.year else null end) as kyoto, sum(case when c2.convention='Montreal' then c2.year else null end) as montreal from country c left join conventions c2 on c.id=c2.id_country group by c.name; name | kyoto | montreal -+---+-- germany | 1996 | 2002 use | 1998 | france | | (3 rows) (i know, silly and wrong data, only for example) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] about effective_cache_size
In response to AI Rumman : > * What is the difference between shared_buffers and effective_cache_size? effective_cache_size: Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes. shared_buffers: Sets the amount of memory the database server uses for shared memory buffers. It's all copied from the doc, there are much more details about this parameters. Read the doc! Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Postgres physical directory structure meaning
In response to S Arvind : > I want to know about the meaning of various directory present in data folder. > Mostly what will the 'base' folder contains? The reason of this is recently in > the datafolder 'global' was deleted unknowingly which leads to entire DB crash > in a second. We found a tool to get the data from the base folder as sql > queries, but many table content are missing. > Pls tell what kind of data will be present in each folder, if possible sent > the > doc link. http://www.postgresql.org/docs/8.4/interactive/storage.html Read there and the sub-chapters. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] possible bug with inheritance?
Hi, Our documentation says: "All check constraints and not-null constraints on a parent table are automatically inherited by its children." Okay, this works as expected: test=# create table parent (name text primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent" CREATE TABLE test=*# create table child (age int) inherits (parent) ; CREATE TABLE test=*# \d child Table "public.child" Column | Type | Modifiers +-+--- name | text| not null age| integer | Inherits: parent Nice, the 'not null' constraint is in the child-table. test=*# rollback; ROLLBACK test=# create table parent (name text); CREATE TABLE test=*# create table child (age int) inherits (parent) ; CREATE TABLE test=*# alter table parent add primary key (name); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "parent_pkey" for table "parent" ALTER TABLE test=*# \d child Table "public.child" Column | Type | Modifiers +-+--- name | text| age| integer | Inherits: parent Doh, there isn't the 'not null' - modifier. The parent-table contains this modifier as part of the primary key - definition. Other test: test=# create table parent (name text); CREATE TABLE test=*# create table child (age int) inherits (parent) ; CREATE TABLE test=*# alter table parent alter column name set not null; ALTER TABLE test=*# \d child Table "public.child" Column | Type | Modifiers +-+--- name | text| not null age| integer | Inherits: parent Okay, we have again the 'not null'. I think, that's not really clearly. In some case the 'not null' - constraint are in the child-table, in other case they are not. Version 8.4.2. Bug, feature? What have i overlooked? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Function that creates a custom (temporary) table AND returns a pointer to it = impossible in pg?
In response to Davor J. : > What I want is something similar to this: > > > Basically, what I want is a similar function f() that returns me a "pointer" > to the table which I can use in some query like this: SELECT * FROM > regclass(f()); Currently, this query only gives me one row 'tbl_temp'..., > but not what I want: "SELECT * FROM tbl_temp;" Can this be done in Postgres? Take a look at http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Postgres Triggers issue
In response to Igor Neyman : > > > > CREATE TRIGGER tafter > > AFTER INSERT OR UPDATE > > ON r.m_a > > FOR EACH ROW > > EXECUTE PROCEDURE r.m_t(); > > > > > > Trigger function for an insert/update trigger should return "NEW", not > NULL (OLD - for "on delete" trigger): It's an AFTER TRIGGER, so the RETURN-Value ignored. It works with NULL, see my other posting (the example there). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] trouble with unique constraint
In response to Khin, Gerald : > The following SQL leads to a unique constraint violation error message You have already got the answer ... for the same question from you. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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 Installation
In response to db.subscripti...@shepherdhill.biz : > Hi, > > Please why is it that we must register at EnterpriseDB and register > each Windows installation of postgreSQL these days? That's not true. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] migrating data from 8.4 to 8.3
In response to Thomas Kellerer : > Marc Lustig, 08.02.2010 11:36: > >Hi, can we safely put the data from /var/lib/postgresql/8.4/main/ of > >server x to /var/lib/postgresql/8.3/main/ of server y, considering > >that the new target machine is running 8.3 whereas the old one ran > >8.4 ? > > No, a dump & restore is required (as stated in the release notes) > > You can also try pg_migrator. It was introduced with 8.4 and will upgrade > the data "in-place" (i.e. without a dump and restore) I don't believe that pg_migrator can do a downgrade ... Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] migrating data from 8.4 to 8.3
In response to Marc Lustig : > Hi, > can we safely put the data from /var/lib/postgresql/8.4/main/ of server x to > /var/lib/postgresql/8.3/main/ of server y, considering that the new target > machine is running 8.3 whereas the old one ran 8.4 ? No! Make a regular Backup und restore that Backup. Consider, 8.4 contains features that not in 8.3, maybe you are not able to do this. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] trouble with unique constraint
In response to Khin, Gerald : > The following SQL leads to a unique constraint violation error message > (PostgreSQL 8.4.1). > > > > > > create table test (val integer); > > > > create unique index test_uni on test(val); > > > > insert into test (val) values (1); > > insert into test (val) values (2); > > insert into test (val) values (3); > > > > update test set val = val + 1; update test set val = val + 10; update test set val = val -9; Thats the only way now, but the upcoming new release 9.0 contains deferrable unique constraints. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] PHP and PostgreSQL boolean data type
In response to Thom Brown : > Hi, > > A long-standing problem we've had with PostgreSQL queries in PHP is > that the returned data for boolean columns is the string 'f' instead > of the native boolean value of false. http://andreas.scherbaum.la/blog/archives/302-BOOLEAN-datatype-with-PHP-compatible-output.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] 8.5devel: unexpected and illogical error during transaction, but transaction don't rolled back
In response to Greg Stark : > On Wed, Feb 10, 2010 at 6:59 AM, A. Kretschmer > wrote: > > test=*# analyse table_a; > > ERROR: canceling autovacuum task > > CONTEXT: automatic vacuum of table "test.public.table_a" > > ANALYZE > > Time: 1235,600 ms > > > > > > I think, that's not an ERROR, just a NOTICE for me. And yes, the > > transaction isn't rolled back, so it isn't an error. > > Did you start the server from the same terminal? I think the ERROR and > CONTEXT line come from the server, not psql and are expected > behaviour. Hi Greg, back from FOSDEM? Yes, I started the server from the same terminal. It's my private PC @home, I'm remote there. First, i have started the Server manually in the background, then i called psql. So it is my fault, a typically layer-8 - error. Thank you ;-) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.5devel: unexpected and illogical error during transaction, but transaction don't rolled back
Hi all, I'm playing with 8.5devel aka 9.0 and got that: test=# with a as (insert into table_a select s, md5(s::text) from generate_series(0,25) s returning *), b as (insert into table_b select id, md5(value) from a where substring(value,1,1) between '0' and '9') select true; bool -- t (1 row) Time: 4167,579 ms test=*# analyse table_a; ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "test.public.table_a" ANALYZE Time: 1235,600 ms I think, that's not an ERROR, just a NOTICE for me. And yes, the transaction isn't rolled back, so it isn't an error. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] How do I drop a CONSTRAINT TRIGGER?
In response to Wang, Mary Y : > Hi, > > How do I drop a CONSTRAINT TRIGGER? Just with DROP TRIGGER: test=# create table foo(a int); CREATE TABLE test=*# create function foo_proc() returns trigger as $$begin return new; end; $$ language plpgsql; CREATE FUNCTION test=*# create constraint trigger foo_trg after insert on foo for each row execute procedure foo_proc(); CREATE TRIGGER test=*# \d foo Table "public.foo" Column | Type | Modifiers +-+--- a | integer | Triggers: foo_trg AFTER INSERT ON foo NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE foo_proc() test=*# drop trigger foo_trg on foo; DROP TRIGGER test=*# \d foo Table "public.foo" Column | Type | Modifiers +-+--- a | integer | test=*# Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Postgres wal shipping from 8.33 to 8.42.
In response to Chris Barnes : > We are trying to minimize our downtime in production to upgrade from 8.33 to > 8.42. > > What we would like to be able to do is this: > Upgrade the slave we are currently shipping to, to version 8.4.2. Continue to > pitr to this server. And switch over and then upgrade the master. > > My question is, will pitr wal logs ship and be processed from a machine > running > 8.33 to 8.42. No. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Output float number with hex format
In response to Vincenzo Romano : > 2010/1/29 ?? : > > Hi! > > > > In C Language, there is a way to format float numbers into a hex string by > > using "%a" in printf. > > eg: > > the value: 1.2345 can be expressed as '0x1.3c083126e978dp+0' which is the > > hex representation of a float number. > > > > I have tried this in Postgres: > > SELECT '0x1.3c083126e978dp+0'::float; > > float8 > > > > 1.2345 > > (1 row) > > This means that Postgres can accepty hex float as *input*. However I cannot > > find any format function for *output*. > > to_char(..., ...) does not have "%a"-like format. Is there any way to do > > that? > > > > Thank you in advance. > > > > -- > > ShenLei > > Well, your question sould be about a scanf, not printf, as this is > input, not output. I think, you have misinterpreted the question, he is looking for a output in a hex representation, and he found a way for input. Hrm, i think, this is possible with pl/perl. For instance. A. Kretschmer -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Correct Concept On Table Partition
In response to Yan Cheng Cheok : > Currently, I plan to use table partition to solve the following problem. > I have a table which is going to grow to a very huge row, as time goes on. > As I know, as table grow larger, the read operation will be slower. > > Hence, I decide to use table partition, in order to improve read speed. > ... > > First 1st millions rows will be write to measurement_1, 2nd millions into > measurement_2, > > > Is this the correct expectation, on table partition? Depends on your selects. You needs an attribute to decide which child-table contains your data. For instance, create tables for every month. Now you can 'select ... where date >= '2010-01-01'::date and date < '2010-02-01'::date to select all data for this particular month. Your child-tables should contains contraints to enforce this partitioning-schema. There are a lot of examples in the internet how to do that, for instance: http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/ Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Log full of: statement_timeout out of the valid range.
In response to Abraham, Danny : > Our setup is a real valid one. Looks like it has to do more with remote > connections. No. 'statement_timeout out of the valid range' has nothing to do with remote connections. > > Any idea? Show us your statement_timeout - setting. If you work with psql, just type: show statement_timeout; and show us the output. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
