Re: [SQL] Order of execution
On 2013-05-06, JORGE MALDONADO wrote: > --089e013d1eb83325e504dc01710d > Content-Type: text/plain; charset=ISO-8859-1 > > I have an UPDATE query which performs several opertions in one table. > > UPDATE table01 SET > field1 = (query1 may contain any field), > field2 = (query1 may contain any field), > field3 = (query1 may contain any field) > WHERE (condition) > > query1, query2 and query3 perform a logic to determine which records will > be updated. Does field1 updates first, then field2 and, lastly, field3? Updates are one row at a time in an unpredictble order. this will be wrapped in a transaction so either all will updated or none. testing suggests that in simple cases the expressions are evaluated in the order the columns appear in the table definition, but I wouldn't rely on that. > or What is the order in which updates are executed? If I need fields to be > updated in a certain order, should I use 3 UPDATE commands instead? yeah, if "simultaneously" isn't acceptable you'll need to do separate updates. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] execute
On 2013-04-25, Mauricio Cruz wrote: > declare > > aCad > text[][] > > begin > > ... > > execute 'aCad:=aCad > array[['||var1||','||var2||']]' execute 'select array[['||var1||','||var2||']]' into acad; -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Peer-review requested of soft-delete scheme
On 2013-04-16, Mark Stosberg wrote: > My challenge is that I want to make very hard or impossible to access > the soft-deleted rows through SELECT statements. There are lots of > selects statements in the system. > > My current idea is to rename the "foo" table to something that would > stand-out like "foo_with_deleted_rows". Then we would create a view > named "foo" that would select all the rows except the soft-deleted views. [...] > Is this sensible? Is there another approach to soft-deletes I should be > considering? yes, rename the table and replace it with a view that excludes the soft deleted records. Make "do instead" rules to handle inserts, updates and deletes on the view by rediecting them to the base table. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Restrict FOREIGN KEY to a part of the referenced table
On 2013-04-11, Matthias Nagel wrote: > Hello, > > is there any best practice method how to create a foreign key that only > allows values from those rows in the referenced table that fulfill an > additional condition? tes. make the key wide enough to capture this state. I dom't like it either. or partition the child table (which may not work for other constraints) > First I present two pseudo solutions to clarify what I would like to > do. They are no real solutions, because they are neither SQL standard > nor postgresql compliant. The third solution actually works, but I do > not like it for reason I will explain later: > FOREIGN KEY ( parent_id, 42 ) REFERENCES parent ( id, discriminator ) I have wanted this before too. > FOREIGN KEY ( parent_id ) REFERENCES ( SELECT * FROM parent WHERE discri I hadn't thought of expressing it like that. or similarly using a view instead of a select. but I think I have tried FOREIGN KEY ( parent_id ) REFERENCES parent ( id ) where discriminator = 42 and it didn't work. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Advice for index design
On 2013-04-10, JORGE MALDONADO wrote: > Our application offers a catalog of artists where a user can select a range > of birthdays and/or sex. For example, a user can get an artists catalog for > those male artists who were born between May 1, 1970 and May 1, 1990 > ordered by birthday and, within each birthday date, ordered by name. I can > think of defining one index for birthday, one index for name, and one index > for sex. Also, I can think of defining a compound index for birthday + > name. Also there could be a compound index for sex + name. Another option > could be a compound index for birthday + sex + name. There are many > possible combinations. What is a good index design approach? the best approach depends on where you need the most improvement. For your example query an undex on (sex,birthday,name) would be best. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ZIP function
On 2013-03-16, Victor Sterpu wrote:
>
> =_MBF521B7A8-BC31-4E93-BC62-8C86007F2089
> Content-Transfer-Encoding: quoted-printable
> Content-Type: text/plain; format=flowed; charset=utf-8
>
> Is there a function that will give the resulting zip content for a=20
> string?
> Like SELECT zip('test data');?
no. you could write one that calls gzip in one of the untrusted
languages. or in C you could call zlib.
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Concatenating bytea types...
On 2013-02-28, Marko Rihtar wrote: > --047d7b603fca8e330f04d6c63f7b > Content-Type: text/plain; charset=ISO-8859-1 > > Hi all, > > i have a little problem. > cv1 := CONCAT(cv1, DECODE(TO_HEX(11), 'escape')); what's that supposed to do? if I were to fix it how would I know? -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Conditional expression in an UPDATE statement
On 2013-02-07, JORGE MALDONADO wrote: > Can I use a conditional expression in an UPDATE query like this: > > UPDATE table_1 SET > field_1 = > CASE WHEN (condition) THEN (COALESCE(query_1, -1)) > ELSE (COALESCE(query_1, -2)) > END > yesh, that should work. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Setting a default value for a select statement without results
On 2013-02-06, JORGE MALDONADO wrote: > --f46d0401fb2fcb805e04d50354b1 > Content-Type: text/plain; charset=ISO-8859-1 > > I have an UPDATE query with the following general structure: > > UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY > order_field LIMIT 1) assuming you mean this, and you are happy with its performance. UPDATE table1 SET field1 = (SELECT field FROM table2 WHERE conditions ORDER BY order_field LIMIT 1) > Is it possible to assign a default value in case no results are returned by > the SELECT statement? use coalesce. UPDATE table1 SET field1 = coalesce( (SELECT field FROM table2 WHERE conditions ORDER BY order_field LIMIT 1) , default_value ) -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: returning the number of rows output by a copy command from a function
On 2013-01-16, James Sharrett wrote: > The problem I have is that I get nothing back when the COPY is run inside > the function other than what I explicitly return from the function so I > don't have anything to parse. It's odd that the record count in the > function is treated differently than from sql query in GET DIAGNOSTIC > since the format and information in the string (when run outside of the > function) are exactly the same. look into "get diagnostics" -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Can't get upsert working.
On 2012-12-02, Jasen Betts wrote: > On 2012-11-30, Bert wrote: > >> Anyone here with upsert experience? This guy "Depesz" does: http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] organizing cron jobs in one function
On 2012-11-17, Louis-David Mitterrand wrote: > Hi, > > I'm planning to centralize all db maintenance jobs from a single > pl/pgsql function called by cron every 15 minutes (highest frequency > required by a list of jobs). In pseudo code: centralising execution of cron jobs into a single function is a recipe for locking problems, if your jobs are not of the type that acquire exclusive locks on tables, it might work for you. . -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Trigger triggered from a foreign key
On 2012-10-19, Victor Sterpu wrote: > I have this trigger that works fine. The trigger prevents the deletion > of the last record. > But I want skip this trigger execution when the delete is done from a > external key. > How can I do this? perhaps you have to use a rule instead of a trigger? -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Calling the CTE for multiple inputs
On 2012-10-04, air wrote: > I have a CTE that takes top left and bottom right latitude/longitude values > along with a start and end date and it then calculates the amount of user > requests that came from those coordinates per hourly intervals between the > given start and end date. However, I want to execute this query for about > 2600 seperate 4-tuples of lat/lon corner values instead of typing them in > one-by-one. How would I do that? The code is as below: I see that your're using the CTE only to fill-in the nulls in main query group-by is it the same plan for the coordinates (non-overlapping ranges)? how are your coordinates respresented? -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] checking the gaps in intervals
On 2012-10-05, Anton Gavazuk wrote:
> Hi dear community,
>
> Have probably quite simple task but cannot find the solution,
>
> Imagine the table A with 2 columns start and end, data type is date
>
> start end
> 01 dec. 10 dec
> 11 dec. 13 dec
> 17 dec. 19 dec
> .
>
> If I have interval, for example, 12 dec-18 dec, how can I determine
> that the interval cannot be fully covered by values from table A
> because of the gap 14-16 dec? Looking for solution and unfortunately
> nothing has come to the mind yet...
perhaps you can do a with-recursive query ?
create temp table Gavazuk
(id serial primary key, start date ,fin date);
insert into Gavazuk (start,fin)
values ('2012-12-01','2012-12-10')
,('2012-12-11','2012-12-13')
,('2012-12-17','2012-12-19');
-- this version treats ('2012-12-01','2012-12-10') ('2012-12-11','2012-12-13')
-- as contiguous
with recursive a as (
select max (fin) as f from Gavazuk
where ('2012-12-12') between start and fin
union all
select distinct (fin) from gavazuk,a
where a.f+1 between start and fin and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;
-- this version treats ('2012-12-01','2012-12-10') ('2012-12-11','2012-12-13')
-- as non-contiguous
with recursive a as (
select max (fin) as f from Gavazuk
where ('2012-12-12') between start and fin
union all
select distinct (fin) from gavazuk,a
where a.f between start and fin-1 and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Reuse temporary calculation results in an SQL update query
On 2012-09-29, Matthias Nagel wrote: > Hello, > > is there any way how one can store the result of a time-consuming calculation > if this result is needed more than once in an SQL update query? This solution > might be PostgreSQL specific and not standard SQL compliant. Here is an > example of what I want: > > UPDATE table1 SET >StartTime = 'time consuming calculation 1', >StopTime = 'time consuming calculation 2', >Duration = 'time consuming calculation 2' - 'time consuming calculation 1' > WHERE foo; > > It would be nice, if I could use the "new" start and stop time to calculate > the duration time. First of all it would make the SQL statement faster and > secondly much more cleaner and easily to understand. > > Best regards, Matthias use a CTE. http://www.postgresql.org/docs/9.1/static/queries-with.html with a as ( select 'time consuming calculation 1' as tcc1 , 'time consuming calculation 2' as tcc2 ) update table1 SET StartTime = a.tcc1 StopTime = a.tcc2 Duration = a.tcc2 - a.tcc1 WHERE foo; you man need to move foo into the CTE too. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] HOw to convert unicode to string
On 2012-09-23, Abhijit Prusty -X (abprusty - UST Global at Cisco)
wrote:
> --_000_8A2A33BFAA5E2F408D0BBB80844412720487D0xmbalnx03ciscocom_
> Content-Type: text/plain; charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
>
> I have a query in oracle like this mentioned below
>
> Insert into TEST
>(TEMPLATE_ID, TEMPLATE_NAME, CREATED_BY, CREATED_DT, UPDATED_BY,
> UPDATED_DT, TEMPLATE_KEY)
> Values
>(1, UNISTR('\D3C9\BA85\B3C4 \B514\C2A4\D50C\B808\C774'), 'dmin', SYSDATE=
> , 'admin',
> SYSDATE ,'FLOOR');
>
> Now the oracle uses the UNISTR function to convert and insert the Unicode to
> string and store in database.
oracle uNISTR-like UTF-16 can be written like this:
U&'\D3C9\BA85\B3C4 \B514\C2A4\D50C\B808\C774'
it's not a function, it a way of writing strings... if you need a
it probably wouldn't be hard to write.
but you can also write in UTF-8 (literal or escaped) or unicode escaped
see docs:
u&'\+021502' -- unicode
u&'\D845\DD02'-- utf16 (docs tell methis is legal with recent versions)
e'\xF0\xA1\x94\x82' -- utf8 hex escape
e'\360\241\224\202' -- utf8 octal escape
'𡔂' -- utf8 string literal
the first 2 can be intermixed as can the last three forms.
http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html
select length('𡔂'), octet_length( '𡔂' ), length('test'),
octet_length('test');
length | octet_length | length | octet_length
+--++--
1 |4 | 4 |4
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with committing the update
On 2012-09-13, BeeBee wrote: > Hi all, > > I have a problem updating the record using store procedure (LANGUAGE > plpgsql).I have attach the query. it all looks good to me. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] generated dates from record dates - suggestions
On 2012-08-20, Gary Stainburn wrote: > Hi folks. > > I've got a table with three dates which are populated from an external source. > I then want to have a view with two calculated dates in it, e.g. > > if date_1 is null and date_2 is null then date_a=NULL > if date_1 is not null and date 2 is null then date_a=date_1+'90 days' > if date_1 > date3 rhen date_b=date1 else date_b=date2 > etc. > > What's the best way to do this? CASE looks good. > I know it's a quite open question but I'm interested to hear different > responses "date_b" on line three, is that a typo? if not how am i to interpret it? -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Simple Upgrade from PostgreSQL version 8.1.11 (With schemas)
On 2012-07-10, Chris Preston wrote: > > Hello all, > How far can I get to a higher version of PostgreSQL by just entering a > command line instruction to upgrade without any major effort? 8.1.23 going to 8.2.x or later is going to need more effort, especially if any of your code relies on any of 8.1's quirks (like comparing integers to strings) - if you do decide to abandon 8.1.x you may as well make it a big change and go to 9.1 -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On 2012-06-15, Richard Huxton wrote: > On 15/06/12 16:32, Achilleas Mantzios wrote: >> On Παρ 15 Ιουν 2012 18:03:26 Scott Marlowe wrote: >>> Not talking about going to something after 8.3.19, just updating to >>> the latest 8.3 version. On most systems it's a simple: >>> >>> sudo apt-get upgrade >>> >>> or similar and sit back and watch. >> >> Thanx, unfortunately we dont have TCP/IP connectivity to (most of) the >> ships, and AFAIK apt-get does not yet work >> over advanced UUCP/minicom/kermit or other equivalent hich-tech dial up >> connection. >> just joking :) > > Can you run rsync over a serial connection? Never tried, but if you had > something that took the same options as ssh I daresay you could get it > working. probably possible. it may be possible to create a wrapper that makes its user interface behave like lszrz, then it can be launched over an existing serial connection but for uploading packages lszrz should be enough, -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On 2012-06-15, Achilleas Mantzios wrote: > On Παρ 15 Ιουν 2012 18:03:26 Scott Marlowe wrote: >> Not talking about going to something after 8.3.19, just updating to >> the latest 8.3 version. On most systems it's a simple: >> >> sudo apt-get upgrade >> >> or similar and sit back and watch. > > Thanx, unfortunately we dont have TCP/IP connectivity to (most of) the ships, > and AFAIK apt-get does not yet work > over advanced UUCP/minicom/kermit or other equivalent hich-tech dial up > connection. > just joking :) If you were serious I'd suggest writing a backend for it :) minicom terminal emulations sucks, use ckermit instead it does no terminal emulation at all, less is not more more, but in this case none is all. the worst bit about upgrades is when you hit some newly invalid characters in a UTF8 text column. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] possible bug in psql
On 2012-05-29, chester c young wrote: > do not know if right mailing list > > in psql in Ubuntu, when beginning with a smaller terminal, eg, 80 > col wide, then moving to a larger terminal, eg, 132 col wide, the > readline(?) editor in psql still treats like 80 cols, making it > impossible to edit longer text. As far as I know (as this is the behavior in debian stable) the ubuntu people use libgettext and not libreadline. You can force it to use libreadline by using LD_PRELOAD. I reccomend that you do I did this by editing /usr/bin/psql which is a perl script In any case linux doesn't propogate SIG_WINCH to the other processes that are using the pty so even if you are using readline you have to force this by resizing the window again after exiting the pager, and then press ctrl-l to clear the screen. see also: ld.so(8) kill(7) /usr/share/doc/postgresql-common/* -- ⚂⚃ 100% natura -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order by different on mac vs linux
On 2012-05-27, Wes James wrote: >> >> Why is there a different order on the different platforms. postgres uses the text comparison operators provided by the host platform. for linux these are defined in the locales package this may be part of the glibc package. > I'd like to bring up my question again after testing on windows xp. Why > does xp and mac os x sort properly when linux does not? osx and windows hare broken sorting, > I tested this last > week with ubuntu 11.10 and it is doing the same thing as 12.04. UTF8 > encoding and lc_collate = en_US.UTF-8 on mac and linux and american_usa on > windows which is the same thing as en_US.UTF-8. All are using UTF8 > encoding. > > I'd really like to use linux to host this DB (and have it hosted in vmware > ESXi), but the order is not coming out correct. I've got foreign titles of > books in this db, so I need the UTF8 all the way through. "C" won't cut it. I briefly worked for a library software company. They had defined their own ordering operator in the form of a function that mangled strings (they were using paradox database) As I understand it postgres allows you to define your own types with their own ordering operators even ignoring the issue of non-alphabetic symbols there are problems that should be addressed for sorting titles and names. Many of these are due to abbreviations which shouls be sorted asif expanded. some examples: St. Stephen of Hungary Saint Stephen the Martyr St. Rita Street art. Wall Stickers Wall st. Wall Street McArthur MacArthur, Douglass Maccaroni Wye Road Y Road -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Understanding Binary Data Type
On 2012-05-22, Carlos Mennens wrote: > Hello everyone! I wanted to ask the list a question about the 'bytea' > data type & how I can picture this in my head. I've been reading SQL > for about a few months now and since then, I've only been working with > textual data. Basically I'm familiar with storing text and numerical > characters into tables but my friend told me that databases can hold > much more than just ASCI text. In so I've read up on some pages that > describe the bytea data type: > > http://en.wikipedia.org/wiki/Binary_large_object > > http://www.postgresql.org/docs/9.1/static/datatype-binary.html > > So my question is can and in fact does PostgreSQL and most other RDBMS > have the ability to store large binary files like photos, music, etc > etc into an actual table? I'm guessing the data is dumped into the > table but rather linked or parsed through the file system store path > into the database itself, right? I would just like to know in a basic > round about way how databases store and handle large files like .jpg > or .png files & regardless how relative this term is, how common is it > to use these files or 'bytea' data in tables? > > Thanks for any info! postgres has a maximum logical row size just under 2GiB so there's plenty of room there for photos etc (DVD images might not fit). postgres uses a strategy called TOAST to move "wide" values out of the file that holds the main table. implementation detals: http://www.postgresql.org/docs/9.1/static/storage-toast.html -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help in grouping records
On 2012-05-19, Andreas wrote: > Hi, > > I'm trying to fight against double entries in tables. > I got as far as I can find similar records with trigram string matching. > If I do this with a table compared to itself I get something like this: > > id_a, id_b > 3, 5 > 3, 7 > 5, 3 > 5, 7 > 7, 3 > 7, 5 > 11, 13 > 13, 11 > > so the records with the IDs 3, 5, 7 and 11, 13 are similar enough to > form a group. > > How would I get a list of record-IDs with a group-ID like this > > record_id, group_id > 3, 1 > 5, 1 > 7, 1 > 11, 2 > 13, 2 > > Is there a way to get this by SQL ? select id_a,min(least(id_a,id_b)) from SOMETHING group by id_a close enough? or this: ? select id_a, rank() over order by g from ( select id_a,min(least(id_a,id_b)) as g from SOMETHING group by id_a ) as foo -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
On 2012-05-11, Carlos Mennens wrote: > I have a problem in SQL I don't know how to solve and while I'm sure > there are 100+ ways to do this in ANSI SQL, I'm trying to find the > most cleanest / efficient way. I have a table called 'users' and the > field 'users_id' is listed as the PRIMARY KEY. I know I can use the > COUNT function, then I know exactly how many records are listed but I > don't know what the maximum or highest numeric value is so that I can > use the next available # for a newly inserted record. Sadly the > architect of this table didn't feel the need to create a sequence and > I don't know how to find the highest value. If you need pecisely the next value a sequence won't get you there anyway. select max(id) from users; that's only going to be reliable if you have a single concurent database user doing inserts. > > Thank you for any assistance! > -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] getting the OS user name
On 2012-04-23, John Fabiani wrote: > Hi, > In my app it is possible to login as one name and use a different name to > login to postgres. > > Is it possible to get the actual OS login name using plsql. not a chance. RFC1413 man 3 ident looks like the parameters you need can be found here http://www.postgresql.org/docs/current/static/functions-info.html dunno if you can do ident calls from any of the available PLs other than PLC RFC1413 can be forged -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to html-decode a html-encoded field
On 2012-04-10, JORGE MALDONADO wrote:
> --000e0ce0d2ee43edb104bd553408
> Content-Type: text/plain; charset=ISO-8859-1
>
> I have a table with a varchar field, such a field is HTML ENCODED. So, for
> example, the string "PLAIN WHITE T'S" is saved as "PLAIN WHITE
> T''S" (double quotes are not part of the string, I use them for
> clarity only). I need to perform a SELECT statement on this table and get
> the values HTML DECODED and I wonder if there is a function that I can
> include in such a statement for this purpose, for example "SELECT
> htmldecode(fld1) FROM table1". I will appreciate anu comments about my
> issue.
if you don't mind gettin an exception or the wrong answer on invalid
input you can abuse xpath thusly:
with sample as ( values ('magic'),('s&witch'),('toaster'))
select
column1 as input ,
(xpath('/z/text()', (''|| column1 ||'')::xml))[1] as output
from sample
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Wrong output from union
On 2012-04-02, Gary Stainburn wrote: > Thanks for this David. > > Does anyone know why it too until Saturday for my post to appear on the listr? > > Gary If you haven't signed up for the mailing list with exactly the same email address that you post from the message will be held for moderation. (standard weak antispam procedure) -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problems with ODBC connections
On 2012-03-02, Sandeep Reddy wrote: > --e89a8ffba9b7723ad004ba47c368 > Content-Type: text/plain; charset=ISO-8859-1 > > Hi, The Error message is like this > > "Could not connect to server; A connection attempt failed because the > connected party did not properly respond after a periord..." > I have run postgres with option -i and changed the pg_hba.conf the IPv4 > details to 0.0.0.0.0/0 to accept form all clients still facing the same > issues. > Any suggestions? listen_addresses in postgresql.conf -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] No sort with except
On 2012-03-01, reto.buc...@wsl.ch wrote: > Dies ist eine mehrteilige Nachricht im MIME-Format. > --=_alternative 002D2CF5C12579B4_= > Content-Type: text/plain; charset="US-ASCII" > > Dear all, > > When I run the following SQL with PostgreSQL 9.1: > > -- > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, > status > FROM person > > WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD') > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') > > ORDER BY pernr, eindt DESC; > -- > > it works. I get the most recent persons, even if one came back within this > time range. > > But if i do this: > > --- > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, > status > FROM person > > WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD') > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') > EXCEPT > > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, > status > FROM person >RIGHT JOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid = 10 > WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD') > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') > ORDER BY pernr, eindt DESC; > --- > > In this case the ORDER BY does not work: I will get the same person data, > either with DESC as with ASC, even when this should change. > > Does anyone have an explanation for this? the ORDER BY is evaluated over the final result set, you can bind it to the except part using parentheses. SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, status FROM person WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD') AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') EXCEPT ( SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, status FROM person RIGHT JOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid = 10 WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD') AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') ORDER BY pernr, eindt DESC; ) -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to split up phone numbers?
On 2012-02-20, Andreas wrote: > Hi, > is there a way to split up phone numbers? several. I wouldn't trust a computer to do any of them. > I know that's a tricky topic and it depends on the national phone number > format. > I'm especially interested in a solution for Germany, Swizerland and Austria. > > I've got everything in a phone number column that makes hardly sense like: > +49432156780 > 0049 4321 5678 0 > 04321/5678-0 > and so on... > Those 3 samples are actually the same number in different notations. normalise them. push them all towards the first format. the rules are relatively simple. now if you want to split that into parts that means something you can probably get the gorey details from the phone company. but if you just want do do it for display split it wherever you like. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] time interval math
On 2012-02-09, Edward W. Rouse wrote: >> the operation abs() is meaninless on the type interval >> eg: what is abs( '1 month - 32 days + 24 hours'::interval ) > > If you need to add 30 intervals together, then +- is not meaningless. if you stop reading after one line you miss the answer. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] time interval math
On 2012-02-08, Edward W. Rouse wrote:
> I'm still working on getting this to work, but the summary is this:
>
> I am getting several (many) intervals of hour, minutes and seconds. I need a
> sum of the absolute value these intervals, similar to the SUM(ABS())
> function for numbers; and I need to divide this sum by an integer (bigint).
> Getting the intervals is no problem, but I can't find built in functions for
> the rest. Currently on 8.3, want to upgrade to 9.x but I can't until this is
> finished.
the operation abs() is meaninless on the type interval
eg: what is abs( '1 month - 32 days + 24 hours'::interval )
howevwer since all your intervals are in seconds (postgres pretends that all
hours are 3600 seconds long) converting to seconds is probably the
best way to go.
> Do these functions exist, or will I be forced to convert to seconds, do the
> math and then convert back to hour-minute-second format (I am assuming from
> current data that, after the divide, the result should be in the minute:
> second range).
Yeah, you need to do that, it's not hard,
select ( sum(abs(extract('epoch' from YOUR_INTERVAL ))) / SOMETHING ) *
'1s'::interval
from ...
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] type cast about int to bit
does the mailing list mangle these, or is it just GMANE? On 2012-02-06, zoulx1982 wrote: >hi， >there is a problem about type cast that i don't understand, follow is my test. > >postgres=# select 10::bit(3); > bit >- > 010 >(1 row) >postgres=# select 10::bit varying(3); >ERROR: cannot cast type integer to bit varying >LINE 1: select 10::bit varying(3); > ^ >postgres=# > >my question is why int can cast to bit , i want to know the reason. >thank you for your timing. possibly postgres doesn't know what size to make the result. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] MS-SQL Store Procedure to Postgresql Function
On 2012-01-30, Rehan Saleem wrote: >hi , how i can convert this store procedure to PostgreSQL function, >especially I really dont know how to set type to readonly in PostgreSQL. >thanksUserACUserAC I don't actually understand T-SQL so I'm going from the structure more than from the actual meaning of the code given it looks like the same effect could be had in a different way "delete ... using" instead. this is assuming that you set up a cascading delete of UserAccountDetails assuming the thie makes sense: ALTER TABLE UserAC ADD PRIMARY KEY(UserDataAcountId); -- the important part is UserAC.UserDataAcountId has an index -- with the unique property, I'm guessing id is actually a primary key. ALTER TABLE UserAccountDetails ADD UserDataAcountId REFERENCES UserAC(UserDataAcountId) ON DELETE CASCADE; -- again i'm guessing from the names of the columns. -- nut if that all fits your data, and usage patterns -- you can do the following: DELETE FROM UserAC USING /*some-query*/ WHERE /*some-condition*/; and get pretty-much the same effect. here /*some-query*/ would be your table expression and /*some-condition*/ would join it to UserAC postgresql treats all unquoted identifiers as lower case, if you are have stuff with mixed-case names you need to quote them with double quotes, I have assumend that the mixed case is just for menmonic reasons and not significant. although postgres doesn't do table parameters there are three other possibilities, dynamic sql refcursors agregate functions. thses are all considered advanced topics, expect EVERYTHING that is not covered by SQL standards to be totally different, there is usually a way to do what you want, it may well be completely different to the old way. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Token separation
On 2012-01-16, Tim Landscheidt wrote: > Tom Lane wrote: > >>> [ "0x13" is lexed as "0" then "x13" ] > >>> Is this behaviour really conforming to the standard? > >> Well, it's pretty much the universal behavior of flex-based lexers, >> anyway. A token ends when the next character can no longer sensibly >> be added to it. > > I know, but - off the top of my head - in most other lan- > guages "0abc" will then give a syntax error. In most other languages "0 abc" would also be a syntax error. 0and doesn't give a syntax error in php eg: -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ignore unique violation OR check row exists
On 2012-01-03, Misa Simic wrote: > If exists is better, though insert line by line and check if exists may > be very slow... > > The best would be if you can use copy command from csv to staging table > (without constraints) and then > > Insert to live from stage where stage constraint column not exist in > live... > Its a good idea to check that the staging table doesn't conflict with itself, before tryign to insert it. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] lo_import
On 2012-01-03, Maurício Cruz wrote: > Hi all, > > I'm trying to use lo_import to import a file into my database, if I > execute from postgres runing in my local machine > it works perfectly, but if I do it in the postgres runing in the server, > it says "No such file or directory" > > I Guess postgres only see file on the machine it is runing and not > through the network... It can only see the files the database server can see. > I will have to upload the file into the server and then use import ? that would work > is there any other way ? the "\lo_import" command in psql, which reads files as the current user anp pushes them through the database connection. the best way is probably to add the file import feature to your appplication. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: Current transaction is aborted, commands ignored until end of transaction block
On 2011-12-30, Jan Bakuwel wrote: > This is a cryptographically signed message in MIME format. > > What annoys me is that I don't think that a constraint violation made by > a user should result in an aborted transaction. There is probably a very > good reason to do that however the logic escapes me... the reason for it is it allows several updates (or other DML) to be run without checking for success and then success only checked at the commit stage. this makes it easier to the DBA to enforce databse consistancy against wayward applications and not suffer from partial inserts. > Of course I can start testing existing values in the database before > accepting them in the user interface but that's putting the horse behind > the cart. I much rather use the constraints at the database level to > tell me a particular update can't be done and do that without loosing > everything else I happened to have done in that transaction until that > point. > > Any suggestions? checkpoints can probably do what you want, but long-lived transactions are a bad idea in general, especially if you expect to have several physical users accessing your database simultaneously. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] internal format of timstamp?
On 2011-12-29, Lars Gustafsson wrote: > Hi, > > I am trying to recover a lot of deleted rows from a database ( pg > 8.2.3 ) , not my database, I promise….. >> When using the tool pgfsck I get good results, but timestamp is not >> implemented. > > When trying to export as int8 i get fx. 4735129360236469258 > representing december 29, 2011, 16:30 > > But how should I do the conversion from the numeric value to the actual > timestamp ? possibly that's a floating point timestamp try it as float8 instead of int8. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] partitions versus databases
On 2011-12-08, chester c young wrote: > have an db with about 15 tables that will handle many companies. no data > overlap between companies. is it more efficient run-time to use one database > and index each row by company id, and one database and partition each table > by company id, or to create a database for each company? > > it is a web-based app using persistent connections. no copying. > if you know you will never want to aggregate data across several companies. databases are cheap, portable, easily duplicated, and self-contained, can easily be dumped, restored, and dropped individually, go with one per company. if there's a possibility you may want to merge two companies, or aggregate data in some other way you want to put them all in the same database so that sequences can be shared to ensure that ids are unique etc... you still have the option of partitioning by schema, table name, or just by tagging each record. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question on imports with foreign keys
On 2011-12-08, Andreas wrote:
> Hi,
>
> suppose you need to import a csv with standard ciolums like name,
> adress, phone, ... and some additional text columns that need to be
> split off into referenced tables.
...
> How is the easiest way to to find the customer.id of the new customers
> so I can insert the projectinfos?
create table tmp.customer (id integer, name text, addr text)
copy tmp.customer ( id,name,addr ) from stdin ;
...
alter table tmp.customer add column new_id integer default
nextval('customer_id.seq'::regclass);
(here the default is the same default that the customer table uses for
its id.)
now you can use "insert ... select ..." to insert these new records
explicitly using new_id to fill the id column of the customer table.
iport the other csv data into similar tables also
and use join on the old id in tmp.customer to get the new id for
copying the other imported tabled.
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] conditional FROM
On 2011-12-10, Richard Klingler wrote: > Good day... > > I'm trying to build a query for PGSQL 9.1 where a table has two > references with only one being used depending of the type of entry.. > > For example, the table has following simplified structure: > > portid primary key > port2node index to table node > port2card index to table card > > So how can I do a conditional FROM clause in the query depending on the > column port2node and port2card? > If port2card is Null or 0 I don't want it in the FROM clause as the > query will return unneccessary duplicate > row...the same goes for port2node being Null or 0... use left outer join. SELECT * FROM port LEFT OUTER JOIN node ON node.nodeid=port.port2node LEFT OUTER JOIN card ON card.cardid=port.port2card or something like that. You may find coalesce() useful to combine columns where node and card both carry equivalent information. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] updating a sequence
On 2011-11-16, Scott Marlowe wrote:
> You need to wrap a subselect in ():
>
> select setval('foo', (select max(some_id) from some_table));
I prefer to do it in once select like this:
select setval('foo', max(some_id)) from some_table;
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Updatable view should truncate table fields
On 2011-11-08, Russell Keane wrote: > > We can extend the table to accept more than 5 characters but the view must = > return 5 characters. > If we try to extend the table to accept, say, 10 characters the view will d= > isplay 10. > If I also cast the view field to 5 characters then any insert with more tha= > n 5 characters still fails. > > Any ideas??? re-load the view and functions, they are sill defined with the char(5) column (you'll probably yneed to drop them all (but not the table) first). plpgsql functions are partially compiled at the time they are defined subsequent modifictions to the datatypes in their definition will cause errors until they are re-defined -- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Partitionning + Trigger and Execute not working as expected
On 2011-11-08, Sylvain Mougenot wrote:
> --f46d043c7fbad4a6b104b1357041
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> Hello,
> I'm trying to use table partitionning on a table called JOB.
> Each month a new table is created to contain the rows created on that month=
> .
> ex : JOB_2011_11 for rows created during november 2011.
>
> To do that I followed this advices on that page :
> http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
> I also would like to create code dynamically into the trigger in order to
> have all "INSERT INTO" inheritated tables (tables like JOB__MM) queries
> done.
>
> But I can't make it work. I've an error when the insert is done using
> EXECUTE.
> *Working :* INSERT INTO job_2011_11 values (NEW.*);
> *Not Woking : *EXECUTE 'INSERT INTO '|| currentTableName || ' values
> (NEW.*)';
>
> Could someone tell me how to make this EXECUTE work?
EXECUTE 'INSERT INTO '|| currentTableName || ' select
('||quote_literal(NEW)||'::job%ROWTYPE).*';
or
EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)' USING NEW;
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] the use of $$string$$
On 2011-11-07, Richard Huxton wrote: > On 05/11/11 00:12, John Fabiani wrote: > OK, so it seems psycopg is quoting your strings for you (as you'd > expect). It's presumably turning your query into: > ... values (E'123', $$E''$$) > So - the $$ quoting is unnecessary here - just use the % placeholders. > > Incidentally, should it be %s for the numeric argument? psycopg2 seems to only accept %s as a place-holder, it's not printf it just looks a bit like it. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Number timestamped rows
On 2011-11-02, Jan Peters wrote: > Dear all, > maybe a stupid question, but: I have a table that is ordered like this: > Tables aren't ordered. Sometimes they may seem to be ordered, but they seldom stay that way for long. > and I would like to number them according to their timestamps like this: > How would I do this with an UPDATE statement (e.g.) in pgsql? If you want ordered data use an ORDER BY clause in the select, there is no other reliable way. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Edit multiple rows concurrent save
On 2011-09-29, Péter Szabó wrote: > users_has_cards.auctions + users_has_cards.decks never can be higher > then users_has_cards.total. It should be also granted that > users_has_cards.total - users_has_cards.auctions number of cards from > a specific type can be in any decks. > > The deck assembly input comes from web, and parsed with PHP, so it is > absolutely not trusted. > > I am started to write a PL/PgSQL function to handle the save of a > deck, but it seems unable to solve this issue. I don't know how to go > forward, so any idea is appreciated. so users_has_cards has a check ( total >= decks + auctions ) and decks_has_cards needs triggers on insert, update, and delete that manipulate the decks column of the users_has_cards record that corresponds to that card. This is probably goiung to make updates to the decks_has_cards table about 10 times slower (should still be faster than you could have done in PHP) -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Dynamic sql
On 2011-09-10, Gabriel Filipiak wrote: > --bcaec517adbceea3c804ac90a376 > Content-Type: text/plain; charset=ISO-8859-1 > > Hi, this is my first on this list. > > I want to know the basics of dynamic sql especially in PostgreSQL. I was > googling for a while but have no luck for getting a good described examples. > Maybe someone here could give me some links to the materials from which you > where studying this subject. Usually I go to the manual first. http://www.postgresql.org/docs/9.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN some things to consider: use quote_identifier and quote_literal when passing names and values into the sql statements: you can't pass variables in, only their values. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Want some basic compare of data type on PostgreSQL and MySQL
On 2011-09-01, bhavesh1385 wrote: > Hello All, > > I Want some basic compare of data type on PostgreSQL and MySQL. > > [1] How to make Primary Key as a Auto Increment...? you can't, use the pseudo-type serial (or bigserial) instead which does something similar, but subtly different. > [2] Suppose I want to put 'ENUM' data type then how i can do that ..? I think you have to create an enum type first. > [3] Please suggest me basic Data type Comparesion between PostgreSQL and > MySQL .. like :- > > PostgreSQL MySQL > - > characte varying VARCHAR > integer INT postgres understands varchar and int as well as aliases fior the expected types > like that i want all the possible data type comparision. assuming you understand mysql types already: http://www.postgresql.org/docs/9.0/interactive/datatype.html -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
On 2011-08-30, Emi Lu wrote:
> Hi Tom,
>
select * from tablename
where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));
>>
>>> If next version could have "not ilike ('', '')" added into window
>>> functions, that's will be great!
>>
>> Why? And what's this got to do with window functions?
>
> First, where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])) will
> work for me.
>
> But I feel " ilike ('str1', ... 'strN')" is more intuitive, isn't it?
no, "('str1', ... 'strN')" looks a lot like a tuple.
if you know these strings beforehand use a single regular expression
instead because ilike is just regex in drag (postgres uses regex to do
ilike), and while ilike can only check one pattern at a time regex can
check several simultaneously.
foo ~* '^(str1|str2|str3...|strN)$'
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] exclusion constraint for ranges of IP
On 2011-08-23, Herouth Maoz wrote: > EXCLUDE USING GIST ( customer_id WITH =, is_default WITH AND ) > Basically, each customer can have several rows in this table, but only = > one per customer is allowed to have is_default =3D true. Is this exclude = > constraint correct? I don't really understand exclude, but instead of EXCLUDE... I would do CREATE UNIQUE INDEX "invoice_definitions-unique-default" ON invoice_definitions(customer_id) WHERE is_default; Which would create a smaller (and probably faster) BTREE index containing only the rows with is_default true. There seems to be no way to create this in the create-table command. (using 8.4 here) -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Confused about writing this stored procedure/method.
On 2011-08-22, JavaNoobie wrote: > Hi All, > I'm trying to write a stored procedure /function to re-order a set of > calendar months.I have a set of calendar months stored from January to > December in my tables. And as of now when I do order by on this column , > the data is ordered alphabetically , starting April, august etc. and so on > I want to order these months starting from April through March in order to > sync with the financial calendar . I'm trying to write a stored procedure to > do the same (I'm not aware of any other method that Postgres offers this > reordering , if there's any , please do let me know!). order by (case month when 'January' then 1 when 'February' then 2 ...[I'm too lazy to type the rest]... when 'December' then 12 end) get the idea? (except change the numbers to match financial calendar) you can index on that expression too if you have them as numbers instead of words you can use an array instead of the case. For as task like this an SQL function may be more efficient than a PLPGSQL function. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] exclusion constraint for ranges of IP
On 2011-08-21, Herouth Maoz wrote: > Hi, > > I'm designing a new database. One of the table contains allowed IP ranges for > a customer (Fields: customer_id, from_ip, to_ip) which is intended to check - > if an incoming connection's originating IP number falls within the range, it > is identified as a particular customer. > > Naturally, I'd like to have constraints on the table that prevent entering of > ip ranges that overlap. Is there a way to do that with exclusion constraints? > Or do I have to define a new type for this? > > Herouth if you can use CIDR instead of ranges it should be relatively simple -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] which is better: using OR clauses or UNION?
On 2011-08-16, adam_pgsql wrote:
>
> Hi,
>
> I have a query hitting a table of 25 million rows. The table has a
>text field ('identifier') which i need to query for matching rows. The
>question is if i have multiple strings to match against this field I
>can use multiple OR sub-statements or multiple statements in a UNION.
>The UNION seems to run quicker is this to be expected? or is there
>anything else I can do improve the speed of this query? Some query
>details:
> WHERE
> ( lower(identifier) LIKE lower('BUGS001884677') OR
>lower(identifier) LIKE lower('BUGS001884678') OR
>lower(identifier) LIKE lower('BUGS001884679') OR
>lower(identifier) LIKE lower('SpTIGR4-2210 (6F24)') )
> ORDER BY a.identifier;
don't use like use regex.
...
WHERE identifier ~*
E'^(BUGS001884677|BUGS001884678|BUGS001884679|SpTIGR4-2210
\\(6F24\\))$'
or
where lower(identifier) ~*
lower(E'^(BUGS001884677|BUGS001884678|BUGS001884679|SpTIGR4-2210
\\(6F24\\))$')
on the other hand you aren't doing any pattern stuff. - you you could
just use 'in':
WHERE lower(identifier) in
(lower('BUGS001884677'),
lower('BUGS001884678'),
lower('BUGS001884679'),
lower('SpTIGR4-2210(6F24)')
)
or if you need like, use like any:
WHERE lower(identifier) like ANY
(lower('BUGS001884677'),
lower('BUGS001884678'),
lower('BUGS001884679'),
lower('SpTIGR4-2210(6F24)')
)
> Also which should scale better if I add more strings to match? would there be
> any better design patterns for this problem?
use one of the above: preferably in, else regex, or failing that like any.
"= any" will also work but I don't thing it will ever be better than "in"
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Generic design: char vs varchar primary keys
On 2011-08-04, Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) wrote: > Thanks, that's useful for benchmarking the various textual data types. > Anything specific about using CHAR vs VARCHAR for primary keys that are > going to be referenced from multiple tables that comes to mind? that page he pointed you to says they are all stored the same. (excepte char(8) is padded and that may bloat the index a bit) -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Generic design: char vs varchar primary keys
On 2011-08-03, Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) wrote:
> Hi,
>
> Can you point me to any pages that explain the difference between using,
> say CHAR(8) vs VARCHAR(8) as the primary key for a table? Is there any
> impact on the database in terms of:
look in the data types chapter of the manual
> - Speed of indexed retrieval
> - Speed of join from a referencing table
> - Storage (I presume VARHAR(8) would have a slight edge, in general)
> - Any other issue
Unless you need the padding, and/or the length constraints use text.
("usr/share/doc/postgresql-doc-8.4/html/datatype-character.html")
. Tip: There is no performance difference among these three types,
. apart from increased storage space when using the blank-padded type,
. and a few extra CPU cycles to check the length when storing into a
. length-constrained column. While character(n) has performance
. advantages in some other database systems, there is no such advantage
. in PostgreSQL; in fact character(n) is usually the slowest of the
. three because of its additional storage costs. In most situations text
. or character varying should be used instead.
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] to_char() accepting invalid dates?
On 2011-07-18, Thomas Kellerer wrote:
> Hi,
>
> I just noticed that to_char() will "accept" invalid dates such as 2011-02-31
> and "adjust" them accordingly:
>
> postgres=> select to_date('20110231', 'mmdd');
>
>to_date
>
> 2011-03-03
> (1 row)
>
> is there a way to have to_date() raise an exception in such a case?
it's possible the odd behaviour you get is required by some standard.
however 'mmdd' is a format that postgres understands natively,
so just cast the string to date.
jasen=# select '20110303'::date;
date
2011-03-03
(1 row)
jasen=# select '20110231'::date;
ERROR: date/time field value out of range: "20110231"
LINE 1: select '20110231'::date;
^
postgres also understands -MM-DD and possilby a locale
dependant form with the year last
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Looking for a "show create table " equivalent
On 2011-07-12, B.Rathmann wrote: > Hello, > > I've been trying to find out how to find out which sql was run to create > a certain table. That's like trying to find what change was used to create $1.83 there several possible answers all but one of them wrong, but many of them may . > As I need this in a program which may access the database remotely, > using pg_dump --schema-only or psql is not an option > (the system my program runs on may not even have those tools installed). why do you think you need this information? > Looking at the source of pgadmin3 it seems as if the program collects > all info about the given table (columns, indeces etc) and creates the > needed SQL by itself, is there no easier way? > > E.g. MySQL offers a simple "show create table ". I am using > Postgresql 9.0.4 on Gentoo. can you find what you need to know in the information schema? http://www.postgresql.org/docs/8.4/static/information-schema.html It's an industry standard, and thus should work with every SQL database. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
On 2011-07-06, Kevin Crain wrote: > That's why you need to do this inside a function. Basically just make > an insert function for the table and have it calculate the count and > do the insert in one transaction. you will still get duplicates, so include code in the function to retry if there is an error. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pagination problem in postgresql need help
On 2011-06-17, hatem gamal elzanaty wrote: > hi, > please see this code > > select aiah_number.aiah_number_id, aiah_number.aiah_number, ... > order by rank_value desc limit 1 offset 0; > > and this code > > select aiah_number.aiah_number_id, aiah_number.aiah_number, ... > order by rank_value desc limit 1 offset 1; > suppose i have 200 hundred record and i want to display records in pages > one record per page i'm facing a problem even if i'm running the script > through phppgadmin it's only display the first record in the page only > no more no less in the php code it display record 1 and records byound > 180 and so on > > can you help in that issue ? check that the PHP query is actualy what you think it is. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Storage of Indian Language text in postgresql through PHP
On 2011-06-14, INDER wrote:
> Hello Everyone. I am new to this group and as well as to the Postgres
> also. Can anybody tell me that how to insert hindi text into postgres
> that a user has entered from html input with the use of PHP. Please I
> am waiting for the reply.
add this PHP before any content is emitted:
header('Content-type: text/html; charset=utf-8');
or set a web servr option that has the same effect.
--
⚂⚃ 100% natural
1
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ANY for Array value check
On 2011-06-10, Emi Lu wrote:
> Good morning,
>
> String array compare command, I forgot how to do it.
>
> E.g.,
> create table z_drop(id varchar[]);
> insert into z_drop values('{"a1", "a2", "b1", "b2", "b3"}');
>
> I'd like to do:
>
> select * from z_drop where id = any('a1', 'b1');
use the array overlap operator:
select * from z_drop where id && ARRAY['a1'::varchar, 'b1'];
If you define the column as text[] instead of varchar you don't need
the ::varchar cast above. there's no postgres reason to prefer
(unbounded) varchar to text.
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting records X minutes apart
On 2011-06-03, lists-pg...@useunix.net wrote: > > IDTS (HH:MM) > --- > 0 20:00 > 0 20:05 > 0 20:10 > 1 20:03 > 1 20:09 > > > Does my question make sense? no, why is (1,20:04) excluded, but (0,20:05) included? both records are 5 minutes from the newest. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: Order of evaluation in triggers for checks on inherited table partitions
On 2011-05-27, Kevin Crain wrote: > I am trying to create a trigger on updates to a table that is > partitioned. The child tables are partitioned by month and include > checks on a timestamp field. > However when I try to update an existing record with a > timestamp that would place it in a child table different from the > child table it is in I get an error due to the check on the child > table it is currently in. My best guess as to what is happening is > that the trigger is evaluating the check before it evaluates the > trigger function and thus cannot tell that the update to the original > table should never take place. I have included an example below. The > error that results is "new row for relation "t_foo_2011_6" violates > check constraint "t_foo_2011_6_f_timestamp_check"" the problem is the check is running before the trigger. perhaps you can use a rule instead of a trigger? -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] self join
On 2011-05-16, Steve Crawford wrote: > On 05/14/2011 07:36 PM, Jasen Betts wrote: >> >> use the "NOT IN" operator with a subquery to retch the disallowed >> values > Hmmm, "retch" as a synonym for "output"? I've seen more than one case > where that is an appropriate description. :) :) was a typo for 'fetch' -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] self join
On 2011-05-14, Seb wrote: > Hi, > > This probably reflects my confusion with how self joins work. > > Suppose we have this table: > If I want to get a table with records where none of the values in column > b are found in column a, I thought this should do it: use the "NOT IN" operator with a subquery to retch the disallowed values. select * from tmp where a NOT IN (select b from tmp); -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] help on select
On 2011-04-20, Saulo Venâncio wrote:
> --bcaec52e65e9b2f22304a15f3840
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> Hi guys,
> I need your help.
> I have a table called medidas, in this table i have some ocurrences that ha=
> s
> id_medida(primary key) id_ponto (sec_key) and also datetime field as
> timestamp.
> i would like to know from a set of idpontos, e.g. 10,11,23,24 how can i
> get the most recent date that is common to all??
> for example, if idponto das date 2011-02-03 but none of others have this
> date in the db i dont want this. i want one common for all..
> thanks.
the trick seems to be to GROUP BY datetime
and to use a HAVING clause to reject the unwanted groups using
count(distinct()) to ensure coverage of the list.
-- a table
create temp table medidas(id_medida serial,id_ponto integer,datetime timestamp);
-- some test data.
insert into medidas (id_ponto,datetime) select
floor(random()*30+1),('today'::timestamp +
floor(generate_series(0,10)/10)*'1s'::interval);
-- the query:
-- note you need to paste the list of number in two different places
-- in the query, postgres only counts the length once.
select datetime
from medidas
where id_ponto in (10,11,23,24,27)
group by datetime
having count(distinct(id_ponto)) = array_length( array[10,11,23,24,27],1)
order by datetime desc limit 1;
-- confirmation
select * from medidas where datetime = (
select datetime
from medidas
where id_ponto in (10,11,23,24,27)
group by datetime
having count(distinct(id_ponto)) = array_length(array[10,11,23,24,27],1)
order by datetime desc limit 1
)
order by id_ponto;
what's this for?
Are you looking at keno results to see how recently your pick would have won?
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get id of a tuple using exception
On 2011-04-14, f vf wrote: > --000e0cd2bf6a60c30804a0dec84b > Content-Type: text/plain; charset=ISO-8859-1 > > Hello, > i'm using a pl/sql procedure and I prevent inserting duplicate tuples using > an exception for example: > > BEGIN >INSERT INTO "Triples"(id, subject, predicate, "object") > VALUES (id, sub_i, pred_i, obj_i); > * EXCEPTION WHEN unique_violation THEN > --do something. > > *In some cases I have interest in getting the id of the tuple that was > already in the table when the exception is triggered. Is there a way for the > EXCEPTION to return that id instead of using a select to know wich was the > id of the triple already existing in the table? if the unique violation is on the ID column that's easy, if it's on some other constraint then no there's no way to get the id. do a select first looking for the colliding row then fall back to an insert. there may be weaknesses with this, it depends on why you need the Id. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] convert in GMT time zone without summer time
On 2011-04-15, LaraK wrote:
> Hello,
>
> I want write a function that converts a timestamp with time zone to the UTC
> zone. But it should all be stored in the winter time.
>
> For example, it must now, in the summer, the German time back by 2 hours and
> in the winter time only 1 hour. But it expects only back one hour.
>
> Is there a function or a specific time zone?
if I undestand your goal correctly you want to subtract the daylight
savings offset from the given timezone if daylight-savings is in use
in the current time locale.
you can detect daylight-savings by setting testing the timezone offset
at 3 month intervals ( timestamp, timestamp+3months timestamp-3months,
timestamp+6months, timestamp-6months)
the one(s) of them with the least (most negative) offset from UTC will
represent non daylight-saving time.
if your given time has a different offset it's daylight saving time,
add the difference.
calling:
> [CODE]
> SELECT
> to_char(CONVERT_TO_UTC(to_timestamp('2011-03-22 14:17:00', '-MM-DD
> hh24:MI:SS'), 'CET'), '-mm-dd hh24:MI:SS') AS winter,
> to_char(CONVERT_TO_UTC(to_timestamp('2011-04-22 14:17:00', '-MM-DD
> hh24:MI:SS'), 'CET'), '-mm-dd hh24:MI:SS') AS summer
> [/CODE]
>
> must come out:
> [CODE]
> WINTER| SUMMER
> +-
> 2011-03-22 13:17:00 | 2011-04-22 12:17:00
> [/CODE]
that test case is ambiguous your inputs are timespamptz but
have an unspecified timezone (and so get the zone appropriate to
your time locale). I'm assuming your time locale is "Europe/Berlin"
and you really mean the following:
SELECT to_char(CONVERT_TO_UTC( '2011-03-22 14:17:00+01'::timestamptz
,'CET'),'-mm-dd hh24:MI:SS') AS winter, to_char(CONVERT_TO_UTC(
'2011-04-22 14:17:00+02'::timestamptz ,'CET'),'-mm-dd hh24:MI:SS')
AS summer;
CREATE OR REPLACE FUNCTION
CONVERT_TO_UTC ( timestamptz, text) returns timestamp as
$$ SELECT $1 at time zone 'UTC'; $$
language sql;
In that this function does not use the second parameter it may not be
what you want, on the other hand it's function matches it's name well.
what are you trying to do?
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] converting big int to date
On 2011-03-26, Jasen Betts wrote: > that's got the right number of zeros to > be a date expressed as microseconds since epoch except the code which produces a likely data treats it as nanoseconds -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] converting big int to date
On 2011-03-23, Sree wrote: > --90e6ba2123fbe15f02049f2ccf73 > Content-Type: text/plain; charset=ISO-8859-1 > > How can i convert bigint to date format. > > bigint=6169625280 that's got the right number of zeros to be a date expressed as microseconds since epoch in which case the magic spell is select 'epoch'::timestamptz + '1s'::interval * (6169625280::bigint /10.0); gives me a date in 1989 if it's some from a microsoft inspored system instead of AT&T inspired you might need to add '10 years'::interval to the result. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Determine length of numeric field
On 2011-02-15, Tony Capobianco wrote:
> I'm altering datatypes in several tables from numeric to integer. In
> doing so, I get the following error:
>
> dw=# \d uniq_hits
> Table "support.uniq_hits"
>Column | Type | Modifiers
> +-+---
> sourceid | numeric |
> hitdate| date|
> total | numeric |
> hitdate_id | integer |
> Indexes:
> "uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace
> "support_idx"
> Tablespace: "support"
>
> esave_dw=# alter table uniq_hits alter sourceid type int;
> ERROR: integer out of range
>
> Sourceid should not be more than 5 digits long. I'm able to perform
> this query on Oracle and would like something similar on postgres 8.4:
>
> delete from uniq_hits where sourceid in (select sourceid from uniq_hits
> where length(sourceid) > 5);
delete from uniq_hits where sourceid in (select sourceid from uniq_hits
where length(sourceid::text) > 5);
or even: delete from uniq_hits where length(sourceid::text) > 5;
but using length on numbers is usually the wrong way.
do this instead: delete from uniq_hits where abs(sourceid) > 2^32-1;
Which will hit all the ones that can't be converted.
You may want to do a select first to see what you're deleting.
> I haven't had much luck with the length or char_length functions on
> postgres.
The length functions only work with strings. using them on numbers is
usually the wrong thing as there is not a 1 to 1 mapping between
strings an numbers.
Strings of length only 3 can be out of range for integer (eg: '9e9'),
(but numerics never look like that, larger floats can though)
care to guess the result of this query?
select '9000'::float, length('9000'::float::text);
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UTF characters compromising data import.
On 2011-02-08, Gavin Beau Baumanis wrote: > I understand the error message - but what I don't know is what I > need to set the encoding to - in order to import / use the data. if you run it through iconv --from-code=ASCII -to-code=UTF8 -c it'll strip out all the non-ascii symbols, without knowing the encoding it's impossible to assign any useful meaning to them. This step may render your data useless, it would be much better to find out what the encoding should be. perhaps you can figure it out by observation? -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE FROM takes forever
On 2011-02-10, Josh wrote: > Hi > > I'm trying to do a DELETE FROM on my large table (about 800 million > rows) based on the contents of another, moderately large table (about > 110 million rows). The command I'm using is: > > DELETE FROM records WHERE id NOT IN (SELECT id FROM unique_records); > > This process ran for about two weeks before I decided to stop it -- it > was dragging down the DB server. I can understand long-running > processes, but two weeks seems a bit much even for a big table. I find that scripting deletes of smaller numbers of records can help here, long-running queries do bad things to the efficiency of postgres. on strategy that could work for your task would be to create a temp table first: create temp table planned_deletions as select id from records except SELECT id FROM unique_records; create index badids on planned_deletions(id); the repeatedly delete from records where id in ( select id from planned_deletions limit 1 order by id); delete from planned_deletions where id in ( select id from planned_deletions limit 1 order by id); until there are none left. possibly pausing a few seconds between each slug if there is a heavy load on the server (that you were able to run the query for 2 weeks suggests that there may not be). > Is this the best way to approach the problem? Is there a better way? > > Some background: The server is version 8.3, running nothing but Pg. > The 'records' table has 'id' as its primary key, and one other index > on another column. The table is referenced by just about every other > table in my DB (about 15 other tables) via foreign key constraints, > which I don't want to break (which is why I'm not just recreating the > table rather than deleting rows). Most of the dependent tables have ON > DELETE CASCADE. The 'unique_records' table is a temp table I got via > something like: SELECT DISTINCT (other_column) id INTO unique_records > FROM records if you can accept the down-time I would drop the constraints (if you don't have them on file do a pg_dump --schema-only , and grep it for the ADD CONSTRIANT commands, use sed or similar to create matching DROP CONSTRAINT commands, run them) then rebuild the table then reintroduce the constraints, keep a copy of the grep output above -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] something simple but I can't
On 2011-01-29, John Fabiani wrote: > Hi guys, > I trying to return a 0.00 from a function it there are no records found else > return the amount. > select sum(aropen_paid) into _paidamt FROM public.aropen where > aropen_applyto is not null and (aropen_applyto = $1) ; > > IF (FOUND) THEN > RETURN _paidamt ; > END IF; > > RETURN 0.00 ; > But all I get is either a blank or the amount paid. What am I doing wrong??? > Johnf how many rows does the query return when no rows match the where? It returns 1 that looks like ( NULL ). it return 1 row, which is more than zero thus FOUND is TRUE. you can fix your function by changing the IF to IF _paidamt IS NOT NULL but if you change the sum to coalesce(sum(aropen_paid),0.00) you can do the task more simply like this: create or replace function danmeans_getpayments(text) returns numeric as $BODY$ select coalesce(sum(aropen_paid),0.00) FROM public.aropen where aropen_applyto is not null and (aropen_applyto = $1) ; $BODY$ LANGUAGE 'sql' ; -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Compare the resulta of a count sql into bash
On 2011-01-26, manuel antonio ochoa wrote:
> --0015174be152ceb275049ac2dc95
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hello,
> I have the next :
>
> COUNTONE=`/var/lib/pgsql/bin/./psql -U 'Thor' -d princlocal -p 5432 -h
> 192.170.1.82 -c "select count(*) from monterrey.${NOMBRETB}"`
> COUNTTWO=`/var/lib/pgsql/bin/./psql -U 'Thor' -dprinclocal -p 5432 -h
> 192.170.1.82 -c "select count(*) from monterrey.$nombre where recibo
> between '$FI' and '$FF'"
>
> I want to compare the result countone with countwo how does it works ?
you need -t
COUNTONE=`/var/lib/pgsql/bin/./psql -t -U 'Thor' -d princlocal -p 5432 -h
192.170.1.82 -c "select count(*) from monterrey.${NOMBRETB}"`
COUNTTWO=`/var/lib/pgsql/bin/./psql -t -U 'Thor' -d princlocal -p 5432 -h
192.170.1.82 -c "select count(*) from monterrey.$nombre where recibo between
'$FI' and '$FF'"`
or like this:
CONN="user=Thor database=princlocal port=5432 host=192.170.1.82"
PSQL=/var/lib/pgsql/bin/psql
COUNTONE=`$PSQL "$CONN" -t -c "select count(*) from monterrey.${NOMBRETB}"`
COUNTTWO=`$PSQL "$CONN" -t -c "select count(*) from monterrey.$nombre where
recibo between '$FI' and '$FF'"`
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] question about reg. expression
On 2011-01-18, andrew1 wrote:
> hi all,
>
> these return t:
> select 'ab' ~ '[a-z]$'
this matches the b and the end of the string
> select 'ab' ~ '^[a-z]'
this matches the start of the string and the a
> select 'ab' ~ '^[a-z]$' returns f
> Can't I use ^ and $ at the same time to match, in this case?
> thanks.
the above expression only succeeds if the string is one character long
use '+' '*' or '{2}' etc after the '[a-z]' to allow it to match several letters
or use '^[a-z]|[a-z]$' to match any sting that starts or ends with a
letter.
what are you trying to find?
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Issue with postgres connectivity
On 2011-01-21, Arindam Hore wrote: > We are accessing database using ip address. try adding the IP addresses of some of the clients as seen by the server to /etc/hosts on the server. see if that helps. try connecting to the server locally using 'su postgres -c psql' see if that's slow too. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pattern matching with dates?
On 2011-01-05, Good, Thomas wrote:
> This dubious query worked well previously:
> select * from db_log where log_date LIKE '2011-01-%';
> (currently works on bluehost.com where they run 8.1.22)
>
> Can someone offer a (preferably ANSI compliant) way to do this on 8.4.5?
where date_trunc( log_date,'month') = '2011-01-01'::timestamp;
or standard
where extract( 'year' from log_date) = 2011 and extract ( 'month' from
log_date) = 1;
this:
where cast( log_date as varchar ) like '2011-01-%'
is as far as I can tell standard, but is almost certainly non-portable
as it is dependant of the character format used for casting dates to
varchar.
> I realize that >= and so on work well (which may explain why the docs
> are pretty silent about pattern matching with dates) but sometimes it's nice
> to
> treat the (ISO) date as a string.
">= etc" will outperform date_trunc, like , and extract if the date column
is indexed. the performance of % can be improved in recent versions by
indexing on the expression (log_date::text)
best performance is probably
where log_date between '2011-01-01'::date and '2011-01-01'::date + '1 month -
1 day' ::interval;
or standard (I think)
where log_date between cast('2011-01-01' as date) and cast ( '2011-01-01' as
date) + cast ( '1 month - 1 day' as interval) ;
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] foreign key question
On 2011-01-05, Gary Stainburn wrote: > On Wednesday 05 January 2011 09:53:43 Gary Stainburn wrote: >> Now I want to set up a new access level table specific to the itinerary, >> along the lines of >> >> u_id int4 not null references users(u_id) >> fl_level int4 not null references facility_levels(16, fl_level) >> >> Firstly, is this possible, and secondly how would I do it? > > I've managed a work-around by creating a column that defaults to 16 and then > used ALTER TABLE ADD CONSTRAINT to add a foreign key with two pairs of keys. > > This feels wrong though as my table now has a column that is ultimately > redundant, and worse can be changed to a wrong value. > > Ok, I've sorted the last bit by adding a check constraint to make sure it > always contains 16, but it still feels wrong. it feels wrong that's because it's not normalised, the column with the 16's probably should not be there. or possibly it should have rows with other values too. look at how this table is useful and look for a more general way to do it. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UPDATE in a specific order
> I need to make update of table1 with data on table2 in the order of id > of table2 that looks like EAV. is it? > I=B4m trying to do an update like this: that's not going to work. perhaps you can rewrite the from part to only return one row for every table1_fk, this one row will combine several rows from table2 > The "FROM ( SELECT * FROM table2 ORDER BY id ) tHist" is a try to > force a specific order on table2 to update table1 > but this isn=B4t working. will only work if the optimiser picks index join on table 1 > There are some way to do this with a UPDATE statement ? to do it with an update statement you need no more than one rows in the from for each row in the target. easiest non update statement approach is probably to use a plpgsql function with a loop. basically you need to find another way to do it. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] constraint with check
On 2010-12-13, Viktor Bojović wrote:
> --0015175cd20209e2030497532e39
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> Hi
> im trying to create foreign key constraint which checks not only the
> existence of key in foreign table, but it has to check if node field has
> value=3Dtrue
> but i don't know how to do that, so im asking for help.
> alter table wg.nc
> add CONSTRAINT "nc_fk" FOREIGN KEY ("Id1")
> REFERENCES "wg"."entities"("Id"),
> check (node=3Dtrue)
you can't do that.
add a node column to wg (and a check node is not distinct fron true constaint
and default true) then do this
alter table wg.nc
add CONSTRAINT "nc_fk" FOREIGN KEY ("Id1",node)
REFERENCES "wg"."entities"("Id",node);
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Regular Expression Match Operator escape character
On 2010-12-08, Gnanakumar wrote: > Hi, > > We're running PostgreSQL v8.2.3 on RHEL5. > > In some places in our application, we use Regular Expression Match Operator > (~* => Matches regular expression, case insensitive) inside WHERE criteria. > > Example: > SELECT ... > FROM ... > WHERE (SKILLS ~* > '(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)C#(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)' > OR SKILLS ~* > '(^|\\^|\\||[^0-9|^a-z|^A-Z]|$).NET(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)') > > In this case, we're trying to search/match for either "C#" OR ".NET" in > SKILLS column. > > My question here is, do I need to escape the characters "#" and "." here? yes. ( '.' especially, I don't think '#' has a special meaning in regex) but as postgres uses posix extended regex simply escaping every non-letter character is safe. (^|\\^|\\||[^0-9|^a-z|^A-Z]|$) seems to be another way to write (^|$|[^0-9a-zA-Z]) both of which are locale dependant but that may not be an issue for you. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] The best option to insert data with primary id
On 2010-12-06, - wrote:
> --0016364d26cf7fa4970496bf2224
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi everyone,
> I have a question about how best to insert and manipulate the table with
> primary key id for better productivity. I need to insert data into the table
> and get last id.
>
> 1. First option to take counter Postgres SEQUENCE:
> INSERT INTO table (id, ...) VALUES ((SELECT nextval ('seq_table')), ...)
> RETURNING (SELECT currval ('seq_table')) AS id
>
> Only thing I see, that if the row is not inserted, the counter is
> incremented every time when called. Then they will have empty unused id in
> the table and ID number will grow much. There will be many records. This id
> int8 type declared with length 64.
> Is there any option to occupy empty sequence records. I have to worry about
> this?
(assuming the default for id is nextval ('seq_table'))
INSERT INTO table ( id, ...) VALUES ( default, ...) RETURNING id;
or you can leave id and default out of the left half:
INSERT INTO table ( ...) VALUES ( ...) RETURNING id;
> 2. Second option is to take control of id and
> INSERT INTO table (id, ...) VALUES ((SELECT MAX (id) +1 FROM table), ...)
> RETURNING (SELECT MAX (id) +1 FROM table) AS id
you run into concurrency issues that way. (two concurrent inserts
could pick the same ID, one will fail with an error)
> Quero your opinions on how best to insert data to have less maintenance and
> better productivity with concurrent users.
> Thank you very much.
INSERT INTO table ( ...) VALUES ( ...) RETURNING id;
Use the sequence, that's what they were designed for.
Let id get the default value and pull that from the returning.
you will get gaps in the serquence due to failed or cancelled
transactions but there will probably not be many gaps.
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Union Question
On 2010-12-03, Shaun McCloud wrote: > --_000_7742DD496427B743BC8B7BBF6D380BA0A2F114EXCHANGE10geocomm_ > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > Hello, > > I need to union three PostgreSQL tables and this won't be a problem but the= > tables are on different servers. Basically, I have an administrative serv= > er that needs the tables viewable in a web administrator and three query se= > rvers that log the needed data locally. Is there a way I can do this witho= > ut using Slony-I to replicate the data to the administrative server? modify the web adminsitrator to connect to the three servers and do the union itself. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE WHERE EXISTS unexpected results
On 2010-11-30, Jeff Bland wrote: > This is a multipart message in MIME format. > --=_alternative 007A6509852577EB_= > Content-Type: text/plain; charset="US-ASCII" > > I want to delete certain rows from table USER_TBL. > Two tables are involved. USER_TBL and OWNER_TBL. delete ... using was invented for this purpose. > In the end I expect the USER_TBL to not contain the 3 HOME entries. > But what is happening is the whole USER_TBL is empty after the query. your subselect is being effected by the table used in the delete. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] subselect and left join not working?
On 2010-11-29, Jorge Arenas wrote: > select zona_id from zonas where zona_id not in (select zona_id from usuarios ### ###### > where per_id =2) select 'FRED' from from usuarios where per_id =2 what'shappening is your not in subquery is being 'corrupted' by the surrounding query, the expression zona_id is being replaced with the value from the main query. so the inner query return multiple copies of the value from the outer query and the not-in fails. to avoid that confusion do it this way: select zona_id as z from zonas where z not in (select zona_id from usuarios where per_id =2) or this way: select zona_id from zonas where zona_id not in (select usuarios.zona_id from usuarios where per_id =2) or this way: select zona_id from zonas where zona_id not in (select u.zona_id from usuarios as u where per_id =2) -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] multi table import from 1 denormalized source table
On 2010-11-16, Andreas wrote: > Hi, > > I frequently get tables from spreadsheets to import into the DB. > > Usually it looks like this: > A1, A2, A3, A4, B1, B2, B3, with optional C1, C2, D1, D2, ... > > and there is a 1:n relation between A and B. > If provieded the C would be 1:1 to A and D 1:1 to B. > > Up until now I let a VBA script order the source table by A, then scan > the table line by line and create a new entry in the target table A* and > fetch its serial ID everytime the script figures that A changed. > With this IDa create 1 C* and as many B*s until A changes again ... and > of course fetch IDb to attach the D* records with a foreign key column. > > Now I'm trying to get away w/o the VBA stuff. > > Is there a clever way to split such denormalized sources while still > obtaining the needed IDs to connect everything? create a temporary table and put the IDS in there. then do a select distinct to populate table B, do an update from to put the IDs from B into the temp table then do a select to populate table A etc... -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Round integer division
On 2010-06-25, Lee Hachadoorian wrote: > > > On 06/25/2010 07:00 PM, Scott Marlowe wrote: >> That all floating point representations are approximate? >> > But if it's error due to approximation, shouldn't the result be random? > I tried this for a handful of larger numbers, and it appears to > consistently round to the even number. Wouldn't that have to be > intentionally programmed that way? Yes it is, an enginneer at intel made that decision in the 70s when the 8087 coprocessor was developed. If you're not running on x86-like hardware the round operation may do something different. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Round integer division
On 2010-06-25, Lee Hachadoorian wrote: > Is it documented anywhere that floating-point numbers round > "scientifically", that is 0.5 rounds to the nearest even number? That's swiss rounding. And no, as I understand it documented that most arithmetic) is platform specific. Postgres is written in C and the relevant portions of the C standards douments (and discussions thereof) give a good picture of the functioning of postgres arithmetic. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] question about partitioning
On 2010-06-24, Joshua Gooding wrote: > Right now I am in the process of migrating an Oracle DB over to Postgres > 8.4.3. The table is partitioned by size. Is there anyway to partition > the new postgres table by size? I created some partitions for the new > table, but I didn't give postgres any rules to partition by, so I have > 250M test records in one table. Any ideas or thoughts on how to build > the rules for the table by size would be greatly appreciated. by size of what? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ORDER BY is case insensitive
On 2010-06-22, Bryan White wrote: > I was suprised to find out that ORDER BY is case insensitive. Is > there a way to do a case sensitive ORDER BY clause? use bytea instead of a text type. > This transcript demonstrates what I am seeing: > > bryan=# select * from t order by f; > f > --- > a > b > B > c > (4 rows) try this: select * from t order by replace(f,e'\\', e'')::bytea you may want to index on replace(f,e'\\', e'')::bytea > > bryan=# \q > ~ $ psql -l > List of databases > Name | Owner | Encoding | Collation |Ctype| > Access privileges > -+--+--+-+-+--- > bryan | bryan| UTF8 | en_US.UTF-8 | en_US.UTF-8 | > postgres| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > =c/postgres >: > postgres=CTc/postgres > template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > =c/postgres >: > postgres=CTc/postgres > (4 rows) > > > > -- > Bryan White > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to construct sql
On 2010-06-02, Wes James wrote: > On Wed, Jun 2, 2010 at 10:55 AM, Oliveiros > wrote: >> Hi, >> Have you already tried this out? >> >> select MAX(page_count_count) - MIN(page_count_count) from page_count group >> by page_count_pdate. >> >> >> Best, >> Oliveiros > > Oliveiros, > > Thx that mostly works. I just tried it and on the days there is only > 1 entry it is 0 since max is the same as min so max - min is 0. Is > there a way to take in to account the 1 entry days? from your original requirement 0 is the correct answer. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extending Regular Expression bounds limit of 255.
On 2010-05-29, Tim Landscheidt wrote:
> Jasen Betts wrote:
>
>>> It looks like most of our tools are using the Perl version of regular
>>> expressions with an upper limit of a bound being 32766. Is there any way to
>>> change this in PG? Or can I change from POSIX to Perl?
>
>> perhaps you can do something in pl-perl?
>
>> posix regular expressions are different to perl regular expressions in
>> several ways.
>
> Another last resort possibility would of course be to "pre-
> compile" the regular expressions from "A{2000}" to
> "A{255}A{255}A{255}A{255}A{255}A{255}A{255}A{215}"
(A{200}){10}
might work better.
> (with the headaches of "A{1000,2000}" left as an exercise to the read-
> er :-)).
easy enoungh to write, but probably easy to write an expression with
factorial complexity too, but this one should work, having at worst
two ways of matching any string.
(A{200}){5,9}A{0,200}
but
(A{5,10}){200}
is asking for trouble.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extending Regular Expression bounds limit of 255.
On 2010-05-27, Brent DeSpain wrote: > --00504502c13812967604879b4ba3 > Content-Type: text/plain; charset=ISO-8859-1 > > It looks like most of our tools are using the Perl version of regular > expressions with an upper limit of a bound being 32766. Is there any way to > change this in PG? Or can I change from POSIX to Perl? perhaps you can do something in pl-perl? posix regular expressions are different to perl regular expressions in several ways. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] cast record type to array?
On 2010-05-26, Gerardo Herzig wrote: > Hi all. Im not being able to cast a record variable into an array. > > Im writing a trigger, and i would like to store NEW (and OLD) as text[]. > There is a way to do it in plpgsql? (w/o any contrib modules) why not store them as text instead? new::text is a valid cast. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
