[GENERAL] Altering columns order

Hi everybody. Is there any way to alter the order the columns appear in a table (apart from using views)? I tried changing attnum from pg_attribute... but I could only mess the server up! Successive INSERT command crashed it until I restored the previous order... :-\ If the answer was You

Re: [GENERAL] Anonymous record type and inner types.

On Fri, Jul 1, 2011 at 9:37 AM, Ronan Dunklau rdunk...@gmail.com wrote: When I execute a statement such as Select (an_int, a_varchar) as a_record from test, the type returned for a_record is record (oid 2249). Is the inner type information propagated somehow (that an_int is an integer, and

Re: [GENERAL] How to find freak UTF-8 character?

On Sat, Oct 1, 2011 at 10:16 PM, Leif Biberg Kristensen l...@solumslekt.org wrote: Yes I know that this is a perfectly legal UTF-8 character. It crept into my database as a result of a copy-and-paste job from a web site. The point is that it doesn't have a counterpart in ISO-8859-1 to which I

Re: [GENERAL] missing chunk 0 for toast value ...

On Wed, Oct 26, 2011 at 1:04 AM, Andrew Hammond andrew.george.hamm...@gmail.com wrote: On Tue, Oct 25, 2011 at 4:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: [ pokes around for a bit ... ]  Hmm, can you say how the failing query was submitted, exactly?  I'm wondering if it came in via simple

[GENERAL] Using a domain

Hello, I'm trying to use a domain to define a data type constraint, let's say an hypothetical uk_post_code with pattern LNNLL. I'd enforce no whitespaces, all uppercase. I would also need a way to normalize before validate: given an input such as w3 6bq, normalize it to W36BQ before trying to

Re: [GENERAL] Duplicated entries are not ignored even if a do instead nothing rule is added.

On Wed, Jan 4, 2012 at 8:09 PM, Alban Hertroys haram...@gmail.com wrote: On 4 Jan 2012, at 3:03, 邓尧 wrote: True, I don't need transactions, neither do I want them, but psycopg2 create transactions for me automatically :-( Well, if psycopg didn't, Postgres would wrap each statement in a

Re: [GENERAL] python3 module for accessing postgresql

On Wed, Feb 22, 2012 at 11:10 AM, Andrew Gould andrewlylego...@gmail.com wrote: Does anyone have any advice regarding the selection of a Python 3 module for PostgreSQL?  The modules I've found so far are psycopg2 and py-postgresql.  My skill level with Python is fairly basic. Psycopg works

Re: [GENERAL] How to get a signal from the database when a INSERT INTO is done?

On Tue, Feb 28, 2012 at 2:15 PM, Marti Raudsepp ma...@juffo.org wrote: On Tue, Feb 28, 2012 at 13:07, Andre Lopes lopes80an...@gmail.com wrote: This is a mixed question Python/PostgreSQL... I need to get a signal in my python application when a new insert is done. How can this be done, any

Re: [GENERAL] How to get a signal from the database when a INSERT INTO is done?

On Tue, Feb 28, 2012 at 3:45 PM, Marti Raudsepp ma...@juffo.org wrote: On Tue, Feb 28, 2012 at 17:41, Daniele Varrazzo daniele.varra...@gmail.com wrote: On Tue, Feb 28, 2012 at 2:15 PM, Marti Raudsepp ma...@juffo.org wrote: On the Python end, you have to call psycopg2 connection.poll() method

Re: [GENERAL] rounding a timestamp to nearest x seconds

On Wed, Mar 7, 2012 at 3:09 PM, Andy Colson a...@squeakycode.net wrote: Took me a while to figure this out, thought I'd paste it here for others to use: create or replace function round_timestamp(timestamp, integer) returns timestamp as $$        select date_trunc('minute', $1) +

[GENERAL] Reading storage parameters

Hello, is there a way to read the storage parameters values set on a table (i.e. what set with ALTER TABLE foo SET (autovacuum_enabled=false) and so on...)? I can't find it in the docs. -- Daniele -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] Reading storage parameters

On Thu, Jun 21, 2012 at 1:26 PM, Thom Brown t...@linux.com wrote: On 21 June 2012 13:12, Daniele Varrazzo daniele.varra...@gmail.com wrote: Hello, is there a way to read the storage parameters values set on a table (i.e. what set with ALTER TABLE foo SET (autovacuum_enabled=false) and so

Re: [GENERAL] Can't figure out how to use now() in default for tsrange column (PG 9.2)

On Mon, Jul 16, 2012 at 3:56 PM, Craig Ringer ring...@ringerc.id.au wrote: On 07/16/2012 07:41 PM, Alban Hertroys wrote: BTW, that second value looks a whole lot like a poorly thought out Yup. The 'infinity' value doesn't play well with all database access APIs and languages, though. It

Re: [GENERAL] Can't figure out how to use now() in default for tsrange column (PG 9.2)

On Mon, Jul 16, 2012 at 7:08 PM, David Johnston pol...@yahoo.com wrote: Given: A) extract() returns a double precision B) ... In addition to ordinary numeric values, the floating-point types have several special values: ... 'Infinity ... I would vote that the epoch result should be

Re: [GENERAL] Can't figure out how to use now() in default for tsrange column (PG 9.2)

On Mon, Jul 16, 2012 at 10:30 PM, Chris Bartlett c.bartl...@paradise.net.nz wrote: At 8:35 PM +0100 16/7/12, Daniele Varrazzo wrote: Even without this quirk, the problem of mapping timestamps to other languages data types could be an even stronger design factor. I've personally settled

[GENERAL] Handling error during async query

Hello, I'm trying to fix psycopg2 issue #113: network disconnection not handled correctly in async mode. If I'm in the middle between the PQsendQuery and the PQgetResult and an error is detected (let's say we don't know yet if application-related or connection-related), is there a way to abort

[GENERAL] Handling of tz-aware literals in non-tz-aware fields

Hello, the issue can be show with this example: piro= SET TIMEZONE = 'America/New_York'; SET piro= select '1970-01-01T00:00:00+03:00'::timestamp; timestamp - 1970-01-01 00:00:00 (1 row) piro= select

Re: [GENERAL] Handling of tz-aware literals in non-tz-aware fields

On Tue, Aug 20, 2013 at 2:23 PM, Adrian Klaver adrian.kla...@gmail.com wrote: PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as timestamp without time zone. To ensure that a literal is treated as timestamp with

[GENERAL] Info about concurrent sequential scans

Hello, at Prato PgDay in 2007 I remember hearing in a speech about a (then yet to come) seqscan piggyback feature, allowing concurrent sequential scans to use the same disk reads. I've now googled for info about this feature, but I found nothing conclusive (e.g. [1], [2] - which I don't know

Re: [GENERAL] Info about concurrent sequential scans

On Mon, Feb 22, 2010 at 7:59 PM, Andreas Kretschmer akretsch...@spamfence.net wrote: Daniele Varrazzo daniele.varra...@gmail.com wrote: Hello, at Prato PgDay in 2007 I remember hearing in a speech about a (then yet to come) seqscan piggyback feature, allowing concurrent sequential scans

Re: [GENERAL] [HACKERS] getting composite types info from libpq

On Wed, Dec 15, 2010 at 6:56 PM, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Dec 15, 2010 at 1:25 PM, Daniele Varrazzo daniele.varra...@gmail.com wrote: Hello, when a query returns a composite type, the libpq PQftype() function reports the oid of the record type. In psycopg

Re: [GENERAL] Any way to use refcursors from python?

On Wed, Dec 29, 2010 at 1:46 PM, Andrew Sullivan a...@crankycanuck.ca wrote: Hi all, For a little application I'm working on (written in Python), I have a number of potentially large result sets that I'd like to return from a PL/pgSQL function.  My natural inclination would be to return a

[GENERAL] Constraint exclusion with box and integer

Hello, I have the typical problem of overlapping periods exclusion and I'm trying to give constraint exclusion a go as we have recently switched to a PG 9.0 server. All the examples I've found check a record against the whole table. But in practice I will have a table of resources (hotel rooms,

Re: [GENERAL] Constraint exclusion with box and integer

On Fri, Jan 21, 2011 at 6:36 PM, Daniele Varrazzo daniele.varra...@gmail.com wrote: All the examples I've found check a record against the whole table. But in practice I will have a table of resources (hotel rooms, whatever) and independent periods for each resource. Clarification: I have

Re: [GENERAL] Constraint exclusion with box and integer

On Fri, Jan 21, 2011 at 9:11 PM, Jeff Davis pg...@j-davis.com wrote: On Fri, 2011-01-21 at 18:36 +, Daniele Varrazzo wrote: = alter table commission_rate add constraint check_overlapping exclude using gist (payer_id with =, box( point(extract(epoch from start_date), extract(epoch from

[GENERAL] Binary params in libpq

Hello, I'm thinking about adding support for PQexecParams and PQprepare in Psycopg. I've posted more details yesterday on the Psycopg mailing list http://archives.postgresql.org/psycopg/2011-02/msg00076.php. I have a few preliminary questions: How stable is the binary representation for the

Re: [GENERAL] Binary params in libpq

On Mon, Feb 28, 2011 at 2:42 PM, Merlin Moncure mmonc...@gmail.com wrote: On Sun, Feb 27, 2011 at 7:39 PM, Craig Ringer AFAIK, the `timestamp' type moved from a floating-point to an integer representation internally, which would've affected the binary protocol representation. That was even a

[GENERAL] Saving bytes in custom data type

Hello, I'm writing a variable size custom datatype in C. The variable part is an array of unsigned long, and it needs to be aligned. I further need to store a few flags, for which a single byte would be more than enough (I would actually need just a single bit, but I'd probably keep some bits to

Re: [GENERAL] Saving bytes in custom data type

On Wed, Mar 16, 2011 at 6:29 PM, Merlin Moncure mmonc...@gmail.com wrote: question: if you are storing just flags and bytes, why not use a bytea and store the flags out of line? I'm not sure I understand your question. I am writing a custom datatype with variable size more or less following

[GENERAL] Non-storable data type

Hello, writing an extension library, I have a type only used to perform efficient in-place aggregation, but absolutely not to be used as a data type into a table (it contains pointers, so it would be a guaranteed crash). Is there a way to mark the type as non-storable? Thanks, -- Daniele --

Re: [GENERAL] Non-storable data type

On Sat, Mar 26, 2011 at 10:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Daniele Varrazzo daniele.varra...@gmail.com writes: Hello, writing an extension library, I have a type only used to perform efficient in-place aggregation, but absolutely not to be used as a data type into a table

Re: [GENERAL] setting connection/ query timeout

On Tue, Apr 19, 2011 at 8:09 PM, tamanna madaan tamanna.ma...@globallogic.com wrote: Sometimes this query gets stuck for unknown reason . So, for this particular query I want to set a timeout . I dont want to change statement_timeout in postgresql.conf as this would affect all the

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Thu, Apr 28, 2011 at 7:26 PM, Joshua D. Drake j...@commandprompt.com wrote: Well there is no fact to back that up but, I will say that most toolkits require the use of a synthetic key, rails, django etc Usually such tools are born with surrogate keys only, because it's easier, and

[GENERAL] psql and query buffer mangling

Hello, I see psql performs some elaboration on the input query before storing it in the query buffer: for instance variables are replaced: =# \set test 10 =# select :test; ?column? -- 10 (1 row) =# \p select 10; and comments are stripped:

Re: [GENERAL] bgwriter and pg_locks

On Wed, Sep 12, 2012 at 8:27 PM, Ben Chobot be...@silentmedia.com wrote: In an attempt to get a hackfix for http://pgfoundry.org/tracker/index.php?func=detailaid=1011203group_id=1000411atid=1376, I'm wonder if it's true that, when looking at pg_locks, the only pid which will have virtualxid

[GENERAL] Python mapping with new 9.2 data types

Hello, I've worked at Python-Postgres mapping for the new PostgreSQL 9.2 data types. They should be released with the next psycopg2 version. The current design, susceptible to changes if needed, is documented here: - JSON adaptation: http://initd.org/psycopg/docs/extras.html#json-adaptation -

Re: [GENERAL] Running CREATE only on certain Postgres versions

On Mon, Sep 24, 2012 at 2:32 PM, Robert James srobertja...@gmail.com wrote: I have some code which creates a function in Postgres, taken from http://wiki.postgresql.org/wiki/Array_agg . DROP AGGREGATE IF EXISTS array_agg(anyelement); CREATE AGGREGATE array_agg(anyelement) (

Re: [GENERAL] Question about PARTIAL DATE type/s

On Sun, Oct 7, 2012 at 12:28 PM, r d rd0...@gmail.com wrote: Hi, I have text fields which contains dates in the format 'MM' (four positions for the year, two for the month). These fields are contained in text files which I load into the DB. When I convert this field to a date using

Re: [GENERAL] Question about PARTIAL DATE type/s

On Sun, Oct 7, 2012 at 3:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Daniele Varrazzo daniele.varra...@gmail.com writes: Note: it exploits to_date() parsing '200013' as '2001-01', which is reasonable but haven't found documented and don't know how much reliable. Writing a safer one month later

Re: [GENERAL] libpq compatibility

On Tue, Feb 12, 2013 at 10:19 AM, Philipp Kraus philipp.kr...@flashpixx.de wrote: Hello, I'm using PG 9.2.2 with Qt for accessing the server. If I compile Qt with the 9.2.2 libpg, can I use this libpg also with eg 8.3 ? So can I use the libpg with earlier Postgres database versions? Yes,

Re: [GENERAL] Selecting timestamp from Database

On Mon, Apr 8, 2013 at 3:15 PM, Richard Harley rich...@scholarpack.com wrote: That returns nothings also. But I have spied the problem now: select ATTENDANCE.timestamp::text from attendance order by timestamp desc limit 1 return the actual timestamp: 2013-04-08 12:42:40.089952 So the

[GENERAL] OIDs for jsonb type

Hello, I'm going to add support to the jsonb data type in psycopg2, in order to have the type behaving like json currently does (http://initd.org/psycopg/docs/extras.html#json-adaptation). Is it correct that oid and arrayoid for the type will be 3802 and 3807 and that they won't change before

[GENERAL] Constraint exclusion on tables partitioned over range types

Hello, we are setting up a partitioned table based on tstzrange in PG 9.3, something like: create table offer ( during tstzrange not null, ... constraint virtual check (false) no inherit ); create table offer_201408 ( check (during @

Re: [GENERAL] How to insert either a value or the column default?

On Sat, Aug 23, 2014 at 7:10 PM, W. Matthew Wilson m...@tplus1.com wrote: I have a table that looks sort of like this: create table tasks ( task_id serial primary key, title text, status text not null default 'planned' ); In python, I have a function

Re: [GENERAL] How to insert either a value or the column default?

On Mon, Aug 25, 2014 at 3:26 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 08/25/2014 06:32 AM, W. Matthew Wilson wrote: First of all, thanks for showing this trick! But I am confused. Will this trick stop working in a future version of psycopg2? Should I avoid using it? Here is

[GENERAL] Sequences in foreign tables

Hello, I'm learning now something about foreign tables in PG 9.3. I wonder if there is a clean way to use a sequence on the remote side, so that an insert into remote_table values ([data not including id]) returning id would ask the remote server to generate a new value for id. As it stands now

Re: [GENERAL] Sequences in foreign tables

On Tue, Sep 16, 2014 at 6:04 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Sep 16, 2014 at 8:05 AM, Daniele Varrazzo daniele.varra...@gmail.com wrote: I'm learning now something about foreign tables in PG 9.3. I wonder if there is a clean way to use a sequence on the remote

Re: [GENERAL] which Update quicker

On Tue, Sep 23, 2014 at 8:35 PM, Emi Lu em...@encs.concordia.ca wrote: Hello list, For a big table with more than 1,000,000 records, may I know which update is quicker please? (1) update t1 set c1 = a.c1 from a where pk and t1.c1a.c1; ..

[GENERAL] Missing space in message

Patch attached. diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 595a609..c8c4eed 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -2461,7 +2461,7 @@ static struct config_int ConfigureNamesInt[] = { {wal_retrieve_retry_interval,

Re: [GENERAL] Missing space in message

On Tue, Jul 7, 2015 at 12:08 AM, David Rowley david.row...@2ndquadrant.com wrote: On 7 July 2015 at 10:52, Daniele Varrazzo daniele.varra...@gmail.com wrote: Patch attached. Thanks for the patch. Would you be able to post it to pgsql-hack...@postgresql.org instead? Oops, sorry. Got

[GENERAL] upsert: is there a shortcut?

Hello, it seems not, but I feel like asking. Is there a way to express a statement like "if you have a conflict on insert replace all the values in the conflicting record" without specifying all the fields explicitly? I.e. in a replication system (where occasional accidents mean replication may

Re: [GENERAL] Function not inserting rows

On Wed, Aug 23, 2017 at 4:23 PM, Frank Foerster wrote: > Any ideas ? commit? -- Daniele -- 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] [HACKERS] SSL and Encryption

On Fri, Nov 3, 2017 at 5:22 AM, John R Pierce wrote: > On 11/2/2017 10:12 PM, Jeff Janes wrote: >> >> https://wiki.postgresql.org/wiki/List_of_drivers >> >> What is 'python native'? psycopg works as long you update your libpq. > > I thought pythonistas preferred using a

Re: [GENERAL] [HACKERS] SSL and Encryption

On Fri, Nov 3, 2017 at 4:12 PM, Jeff Janes wrote: > On Thu, Nov 2, 2017 at 10:22 PM, John R Pierce wrote: >> >> On 11/2/2017 10:12 PM, Jeff Janes wrote: >>> >>> https://wiki.postgresql.org/wiki/List_of_drivers >>> >>> What is 'python native'? psycopg