Re: [SQL] writing a function to mimic mysql last_insert_id

ame; END LOOP; FOR idval IN SELECT currval(seqname) AS id LOOP idv := idval.id; END LOOP; RETURN idv; END; ' LANGUAGE 'plpgsql'; Chris. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Re: [SQL] Case Sensitive "WHERE" Clauses?

using MySQL in these examples .. not Postgres :) (FYI - Just tried this with 7.3beta and I got the same results as everyone else .. it is case sensitive). Chris. >mysql> create temporary table foo (ch char(2), vc varchar(2)); >Query OK, 0 rows affected (0.12 sec) > >mysql> i

Re: [SQL] [GENERAL] pgcrypto-crypt

ryption at the script as well as database level why do you need it encrypted? Please do reply-all - you will get a quicker response (from me and the list(s) might have suggestions I don't). On 4/6/06, *chris smith* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote

Re: [SQL] Bit by "commands ignored until end of transaction block" again

Joshua Tolley wrote: On Thu, Jul 23, 2009 at 02:04:53AM -0400, Glenn Maynard wrote: On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton wrote: - Let me use SAVEPOINT outside of a transaction, You are never outside a transaction. All queries are executed within a transaction. "Transaction block",

[SQL] getting extract to always return number of hours

Hi, I'm trying to get extract() to always return the number of hours between two time intervals, ala: => create table t1(timestart timestamp, timeend timestamp); => insert into t1(timestart, timeend) values ('2010-01-01 00:00:00', '2010-01-02 01:00:00'); => select timeend - timestart from t

Re: [SQL] getting extract to always return number of hours

Chris wrote: Hi, I'm trying to get extract() to always return the number of hours between two time intervals, ala: => create table t1(timestart timestamp, timeend timestamp); => insert into t1(timestart, timeend) values ('2010-01-01 00:00:00', '2010-01-02 01:00

Re: [SQL] indexes

Seb wrote: Hi, I have some views and queries that take a bit too long to return, so perhaps some judicious indexes might help, but I don't know much about how to use them. The PostgreSQL manual has a good section on indexes, but I can't find guidance on (unless I missed something): o How to de

Re: [SQL] postgre2postgre

F. wrote: Hello, I am trying to migrate from postgresql-8.1.11-1.el5_1.1 (i386) to postgresql-8.3.4-1.fc10.x86_64. But I can not. Database uses ltree and tsearch and the problem seems to be this. I am using, pg_dump in first computer and psql in second computer to execute script. First error:

Re: [SQL] Trigger

Craig May wrote: > > Could someone send me a quick example of a trigger. Hope this helps. Chris Ryan <<< Clip below and execute to create example >>> -- -- FUNCTION: trigger_last_updated -- -- DESCRIPTION: -- This is a function called by the table triggers to update t

Re: [SQL] Trigger

Chris Ryan wrote: > > Craig May wrote: > > > > Could someone send me a quick example of a trigger. > > Hope this helps. > > Chris Ryan > -- snipped code -- I am so sorry but you may have noticed my email client wrapped lines it shouldn't have. I have at

[SQL] two tables - foreign keys referring to each other...

ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ); COMMIT; of course I get the ERROR, that admin_t doesn't exist. So? help the stupid! pls! best greets, Chris

Re: [SQL] two tables - foreign keys referring to each other...

ity. as beginner I appreciate good hints, cheers, Chris > > IMHO, you should consider not having the admin table have a link to the > institute table. If you want to find the institute a particular admin > is connected with, find that look in the institutes table. The astute &g

[SQL] DELETE FROM fails with error

ere are 20 tables with alltogether 120 MB of data in it chris -- +-+ | chris | | Database Developer | | uscreen GmbH| | | | --- | | eM

[SQL] realising a counter in an pgpsql-trigger

Hi folks, I want to do the following: I have a column in every table, named c_update, it's purpose is to hold how many times the dataset was updated. This should be done automatically by the trigger - which already set sthe update-user, -time and date. How can I do this ???

[SQL] [Fwd: [Gborg-bugs] BUG: reference error when using inherited tables (ID: 269) (new)]

I received this bug on a project I administer that Isn't related to my project. I forwarded it here to see if any of you could help this person. [EMAIL PROTECTED] wrote: > > Title: reference error when using inherited tables > Bug Type: Software bug > Severity: Serious > Software Versio

[SQL] query PostgreSQL from c++

might be able and willing to give me a few tips. Maybe there's even a document somewhere which I could consult? So if someone has tipps and/or examples or maybe a suggestion for a book, please mail me, chris ---(end of broadcast)---

[SQL] Select question

1 | 1 | 15 | "9871162" | "71" | 3889 Although I specified that I want only dates > 5/18/2001, I get dates 4/1/2001. Clearly, I ask the system the wrong question. How do I ask this question the correct way? Best regards, Chris _

[SQL] Stored Procedures?

I read something about stored procedures in the Great Bridge User's Manual (Page 74 under "PG_LANGUAGE"). It is only mentioned briefly and there are no explanations of how it works. Can anybody let me know, how I can write a stored procedure and how to run it? Best

[SQL] Unique record Identifier?

es could get changed during an update. In order to be able to re-read a record, I would like to use some unique identifier of that record, like a unique db-wide rec-id. Does this exist in PostGres and if so, how do I access it? Best regards, Chris __

Re: [SQL] Unique record Identifier?

on 05/30/2001 16:33, Josh Berkus at [EMAIL PROTECTED] wrote: > Chris, > Thanks Josh - that was my next step. At the moment, the database is not in production, I'm running a Progress database there, but I'm trying to learn the dos and don'ts in PG. I don't really need

[SQL] Re: [PHP] PHP-PostgreSQL - install

find libpq.so.2.1 in the tree from the compile (find . -name libpq.so.2.1 -print) then copy it to /usr/libexec manually. you might also want to check the permissions on the library, it should be 755 (rwxr-xr-x). Best regards, Chris - Original Message - From: "Dorin Grunberg&quo

Re: [SQL] rpt

Hilkiah, you could use any report writing tool which connects to databases via ODBC. Download the Windows ODBC driver and install it. Set the '-i' flag on the postmaster, set up the ODBC interface to PostGreSQL in Windows and you're ready to fly :). Best regards, Chris - O

[SQL] Unknown values in int8 fields?

Hi Chris, When I load records with copy from ... And one of the fields (last one) is left empty, I want the default of nextval('sequence_table') to kick in - but it doesn't. Now, the field with a unique index on it stays blank. No big deal if I could go and say 'update rate

[SQL] First steps in plpgsql - language not recognized?

Recognized languages are sql, C, internal, and created procedural languages. The first error is ok - the error in line 9 is what I'm worried about. The code is what's in the tutorial, no biggy ;). Best regards and thanks for the help, Chris __

[SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)

Languages (PostGreSQL 7.1.2 and 7.2) but there is no info on it. Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6

Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ...:)

o a temp record, which I then cut to pieces, extracting the info I want. Once I have what I need to create the actual record, I can delete the temp record. Best regards, Chris on 07/17/2001 16:06, Tom Lane at [EMAIL PROTECTED] wrote: > "Chris Ruprecht" <[EMAIL PROTECTED]> wr

Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ...:)

Hi Josh, on 07/17/2001 18:31, Josh Berkus at [EMAIL PROTECTED] wrote: > Chris, > > Hmmph. People are in a bad mood this week; obviously few other people > on the list have had to write applications for the banking industry, > which trades everything in flat files. Giv

[SQL] TODO List

e loop all together. There doesn't seem to be functionality for that right now. Best regards, Chris - Original Message - From: "Bruce Momjian" <[EMAIL PROTECTED]> [...] > > Both LIMIT and OFFSET seem to have that restriction

Re: [SQL] TODO List

e one or other thing. I'd love to see pl/pgsql develop into what I saw the other day in the Oracle PL/SQL book.. Best regards, Chris - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: "Bruce Momjian" <[EMAIL PROTECTED]>; "Chris Ru

Re: [SQL] Full-text Indexing and Primary Keys

file on hard drive by content" - which, if not indexed, takes forever. Hope this makes the issue a little more clear. Best regards, Chris - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, July 23, 2001 10:32 AM S

[SQL] Big table - using wrong index - why?

lt;= 9123; NOTICE: QUERY PLAN: Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39 rows=607 width=137) EXPLAIN phones=# \d i_pl_loadtimestamp Index "i_pl_loadtimestamp" Attribute | Type ---+-- entity| cha

Re: [SQL] Big table - using wrong index - why?

Hi Joe, I found the problem - it was a typical "rrrhhh" - error. Since pseq is declared int8, I need to say select * from phonelog where entity = '001' and pseq >= 9120::int8 and pseq <= 9123::int8; (casting the two numbers). Then, it works like a char

Re: [SQL] More Database Design Stuff

reate a unique primary key on this table on two columns, 'cust_no' and 'addr_type'. I don't really care if people say it's bad to do this kind of thing, I like to tell them that I will do what works for me, and there is usually not much to argue about that point ;).

[SQL] Functions returning more than one value

work, since v_val3 and v_val4 are treated as constants in test2() and can not be changed. Is there a way this can make it into a future release? Best regards, Chris dropfunctiontest1(); create functiontest1() returns integer as ' declare v_res boolean; v_val1 integer;

Re: [SQL] Functions returning more than one value

s is going to slow down my program'. But this reminds me: where are temp tables kept? On disk or in memory? - that might be a way out of the dilemma. Best regards, Chris - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: "Chris Ruprecht" <

Re: [SQL] Temp tables being written to disk. Avoidable? [Another TO-DO]

$TMP. Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] More on the TO DO wishlist

? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] challenging query

Consider the following table: A B C D select? --- 1 FOO A1 100 n 1 BAR Z2 100 n 2 FOO A1 101 y 2 BAR Z2 101 y 3 FOO A1 102 y 4 BAR Z2 9

Re: [SQL] Selecting latest value II

what about using 'distinct' in you select statement? - Original Message - From: "Haller Christoph" <[EMAIL PROTECTED]> To: "Patrik Kudo" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, September 20, 2001 11:02 AM Subject: Re: [SQL] Selecting latest value II > What do you mea

[SQL] Search by longitude/latitude

Hi all, I need to implement "Find all hospitals in a 5 mile radius". Say I have all the coordinates on them stored in a table with the fields longitude and latitude. Has anybody some sample code for that? Best regards, Chris __

[SQL] sql question:

ed some kind of algorithm. Does anyone have any ideas? email: [EMAIL PROTECTED] thanks, Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Last record

Select * from where <...> desc limit 1; Desc = from the bottom up, limit 1 = just one record. Best regards, Chris On Wednesday 24 July 2002 10:36 am, Leao Torre do Vale wrote: > Dear Sir, > > If you already have the answer of the question below please, send to me. > >

Re: [SQL] [GENERAL] 1 milion data insertion

orrying about log files over filling Logging is controled likely from the startup script. Maybe in /etc/rc.d details depend on your OS. = Chris Albertson Home: 310-376-1029 [EMAIL PROTECTED] Cell: 310-990-7550 Office: 310-336-5189 [EMAIL PROTECTED] ___

[SQL] FIFO Queue Problems

I'm having a race condition with a FIFO queue program that I've created... CREATE TABLE fifo ( id serial, data varchar(100), status int4 DEFAULT 0 ); I have a program that claims a row for itself my $processid = $$; my $sql_update =

[SQL] Using VIEW to simplify code...

Right now I dynamicly generate the SQL for an incredibly ugly 4 table join based on user information client-side. I got the bright idea to create a view and then run a MUUUCH simpler client-side query on that view. The problem is that PostgreSQL apparantly runs the view FIRST and then applies the c

Re: [SQL] Using VIEW to simplify code...

> If you want help, you must provide details. The PG version number is > relevant also. Understood. PostgreSQL 7.2.3. Here's the generated client side sql: select case when (hasflag(ut.flags,1) or (current_timestamp - '1 day'::interval < trl.trans_date)) then case when trl.trans_data

Re: [SQL] Using VIEW to simplify code...

--- Tom Lane <[EMAIL PROTECTED]> wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Tue, 19 Nov 2002, Chris Gamache wrote: > >> Understood. PostgreSQL 7.2.3. > > > 7.3 will be better for this. There were questions about the safety > > of p

[SQL] Problem with a lookup table! Please help.

kup table, the column names for each of the four categories in the main default to the column name in the lookup table and hence are all the same.  What SQL expression should I use to translate the cryptic numbers into plain english whilst preserving the column headings in the main table?   Regards,   Chris J

Re: [SQL] Problem with a lookup table! Please help.

mple, 1 => Excellent, 2=> Good, etc etc. What I need to do is to substitute the integers set in the "Activity..." columns in tblantibiotics with the strings in 'Plain_english' according to the integer set in 'Efficacy_code'. Any ideas? Chris J - Original Mes

[SQL] A Costly function + LIMIT

PostgreSQL 7.2.3 I have a function that is quite costly to run on 1000's of records... Let's call it "widget". I have a query (SELECT name, address, city, state, zip, widget(name, address, city, state, zip) FROM eastern_usa ORDER BY state, city, zip, name LIMIT 5000) UNION ALL (SELECT name

Re: [SQL] PgSQL 7.3: /opt/pgsql/bin/postgres: Execute permission denied.

Try running the initdb program first. Best Wishes, Chris Travers - Original Message - From: "Zengfa Gao" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 08, 2003 9:43 AM Subject: [SQL] PgSQL 7.3: /opt/pgsql/bin/postgres: Execute permission

[SQL] Race condition w/ FIFO Queue reappears!

nfig option would control something so basic. I can't find any reference to it in the 7.3 docs, and my tired eyes did not pick any fixes remotely pertaining to this type of locking problem in the HISTORY file. I'm (sadly) switching back to 7.2 until we can figure this out. CG >Chris Gamache

[SQL] Splitting text into rows with SQL

Using Postgresql 7.2.3 ... In order to search using indexes I need to split a composite field into its components and return it as rows... If this would only work: create table table_with_composite_fields ( data1 serial, data2 varchar(100), composite_field text ); insert into table_with_compos

[SQL] Elegant SQL solution:

There are so many (bad) ways to skin this cat... I'm looking for a more elegant solution. If I SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP BY month; It might only return month | rows ---+-- 1 | 234 3 | 998 4 | 403 5 | 252 10|

[SQL] Inquiry From Form [pgsql]

I know this is basic, but couldn\'t find and in a hurry to know the answer. When interfacing with PostgreSQL through PSQL, it appears that DML statements are auto-commited, that is, a change I make in one session is seen from another without the original session issueing a COMMIT. Is this a re

[SQL] How to determine the currently logged on username

currently logged in user. How do I do this? Any ideas? Best Wishes, Chris Travers ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] SECURITY DEFINER changes CURRENT_USER?

t user. The CURRENT_USER then returns the name of the definer rather than the invoker of the function. So this being said-- are there any workarounds that don't allow anyone to change anyone else's password? Best Wishes, Chris Travers ---(end of broadcast

[SQL] length of array

Hello Is there a function in postgres to return the length of an array field ? I have seen array_dims(array) but this returns a character value. Ideally, I'd like something numeric returned. Thanks Chris ---(end of broadcast)--- TIP 6: Hav

Re: [SQL] length of array

1] = 0 and N.level[2] = 0 and N.level[3] = 0 and N.level[4] = 0 but my row with 2 elements in the array won't be returned with this condition. Chris -Original Message- From: Joe Conway [mailto:[EMAIL PROTECTED] Sent: 28 August 2003 01:40 To: Chris Faulkner Cc: [EMAIL PROTECTED] Su

[SQL] BEFORE UPDATE Triggers

PostgreSQL Version: 7.2.3 Procedural Language: PL/pgSQL I have a table which contains a field for the user who last modified the record. Whenever a row in this table is updated, I want to have an UPDATE trigger do the following things: 1) Ensure the UPDATE query supplied a value for the action_u

[SQL] undefine currval()

I'm using sequences and currval() to retrieve the last inserted row in a table. If currval() is undefined, as it is when a connection is made, then I know no rows were inserted in that table and can take a different action. This is problematic when using a connection pooling library, as the value

[SQL] create new field

date tab set new_col = "OLD_COL"::int4; ERROR: Cannot cast type character to integer I understand this - some tables might have characters in the varchar but how to get around it in my case ? I know that my character field has only integers in it ? Thanks Chris --

[SQL] monitor sessions

Hello Can anyone tell me - is there a system table or view that I can query to show all current sessions ? Thanks Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

[SQL] sql performance and cache

n (cost=0.00..85.74 rows=2 width=91) Index Cond: (n."TILE_REF" = "outer"."TILE_REF") Filter: (("TILE_REF" = 'TQ27NE'::bpchar) OR ("TILE_REF" = 'TQ28SE'::bpchar) OR ("TILE_REF" = 'TQ37NW'::bpchar) OR ("TILE_REF" = 'TQ38SW'::bpchar)) I am seeing this message in my logs. "bt_fixroot: not valid old root page" Maybe this is relevant to my performance problems. I know this has been a long message but I would really appreciate any performance tips. Thanks Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] sql performance and cache

R ("TILE_REF" = 'TQ38SW'::bpchar)) AND ("FEAT_CODE" = 3500)) -> Index Scan using idx_on_tile_ref on oscar_node n (cost=0.00..85.74 rows=2 width=91) (actual time=0.06..150.07 rows=4797 loops=67) Index Cond: (n."TILE_REF"

[SQL] Query planner: current_* vs. explicit date

PsotgreSQL 7.2.4: Query planner is behaving strangely. It operates differently for explicit dates and derived dates... any ideas on why? ( and why I might not have noticed this before... ) CREATE TABLE trans_table ( id serial, user_name varchar(50), trans_type varchar(50), trans_data v

Re: [SQL] Query planner: current_* vs. explicit date

Thanks Tom (and others!) Right-on-the-money, as always... By giving it a definitive range I was able to coax query planner to use the index: SELECT id FROM trans_table WHERE trans_date >= (SELECT current_date::timestamptz) AND trans_date < (SELECT current_timestamp); gave me from midnight to th

Re: [SQL] [HACKERS] Schema boggle...

dreadfully slow queries dealing with traffic stats from a table that was growing painfully monolithic ... the idea is/was that it would be easier to backup/remove all data pertaining to a specific client if they decided to close their account ... On Wed, 5 Nov 2003, Josh Berkus wrote: > Chris, >

[SQL] Do update permissions require select permissions

I have run across a problem while creating a database with row level permission checking via views and triggers. The just of which is update does not work for a user unless they also have select permissions. Select permissions are handled with a view, while insert, update and delete permissions a

[SQL] How to completely move a table to another schema?

led me to discover that I needed to put in the second update query. Just figured I would check. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send &

Re: [SQL] INHERITS and Foreign keys

Hi Pedro; I understand that at the moment it is more of a design limitation than a bug. I think we should vote the desired behavior as a feature request, however. Best Wishes, Chris Travers - Original Message - From: "Pedro" <[EMAIL PROTECTED]> > >> Foreign

[SQL] Historic Query using a view/function ?

...Postgresql 7.2... I'm building the history of a table using rules. I've been trying to figure out a way to select on a table as it would have appeared in a point in time. I can't seem to wrap my brain around the problem, tho. Given some tables CREATE TABLE list ( num int4 NOT NULL, name

Re: [SQL] Distributed keys

indicating whether the employee is a manager, teacher, or sub. Best Wishes, Chris Travers - Original Message - From: "Michael Glaesemann" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, December 24, 2003 4:42 AM Subject: [SQL] Distributed keys Hell

Re: [SQL] Anti log in PostgreSQL

=y. Hope this explains things. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] sort by on two columns

Title: Message SELECT * FROM customers ORDER BY last_name, first_name Works for me. - Original Message - From: Andy Lewis To: [EMAIL PROTECTED] Sent: Saturday, January 03, 2004 8:15 AM Subject: [SQL] sort by on two columns Hi All, Is it possible to

[SQL] Historic Query using a view/function ?

...Postgresql 7.2... I'm building the history of a table using rules. I've been trying to figure out a way to select on a table as it would have appeared at a point-in-time. I can't seem to wrap my brain around the problem, tho. Given some tables CREATE TABLE list ( num int4 NOT NULL, name

Re: [SQL] Calendar Scripts - Quite a complex one

cation. I think that the source for this would likely be one of those things that might be best LGPL'd if added to my app. Best Wishes, Chris Travers - Original Message - From: "Kumar" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Peter Eisentraut" &

Re: [SQL] Calendar Scripts - Quite a complex one

actly what you need. Best Wishes, Chris Travers - Original Message - From: "Kumar" <[EMAIL PROTECTED]> To: "Chris Travers" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Peter Eisentraut" <[EMAIL PROTECTED]>; "psql" <[

[SQL] Problem with plpgsql function

; line 15 at for over execute statement Jan 15 13:42:56 jupiter 5439[3164]: [134-1] ERROR: set-valued function called in context that cannot accept a set Jan 15 13:42:56 jupiter 5439[3164]: [134-2] CONTEXT: PL/pgSQL function "get_account_info" line 20 at return next Can anyone see anything that I missed? Or has any suggestions? -- Chris Bowlby <[EMAIL PROTECTED]> Hub.Org Networking Services ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Problem with plpgsql function

Ack, I knew it was something small, I was under the impression that I had been using that, it just took someone to point it out to make me look again :>... thanks.. On Thu, 2004-01-15 at 14:47, Joe Conway wrote: > Chris Bowlby wrote: > > select get_account_info('test.com')

Re: [SQL] Is it possible in PostgreSQL?

it on my system (cygwin), at the moment.  Perhaps someone else can help.     Failing that, you can write your own function to return each row.  I was working on a quick proof of concept but it was not working properly.   Best Wishes, Chris Travers   - Original Message - From: Bronx

Re: [SQL] comparing nulls

ike so: > 'select * from table where field = null' > would give all the rows where that field had no value. > on porting to 7.3.2, this doesnt work. How to do this? -- Chris Bowlby <[EMAIL PROTECTED]> Hub.Org Networking Services ---(end of broadcast)

Re: [SQL] comparing nulls

To achieve a higher level of SQL compliancy.. On Tue, 2004-01-20 at 10:24, Kenneth Gonsalves wrote: > On Tuesday 20 January 2004 19:26, Chris Bowlby wrote: > > Hi Ken, > > > > Under 7.3.x this option was removed, you need to test via: > > > > SELECT * from t

Re: [SQL] Database diagram

There is a free Perl script which is called something like pgautodoc which creates DIA diagrams from databases. Take a look for it on Freshmeat. - Original Message - From: "Ganesan Kanavathy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, January 20, 2004 1

[SQL] How to retrieve N lines of a text field.

ing). Is there a workaround? Any other help? Or do I need to write a UDF? Best Wishes, Chris Travers ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED]

Re: [SQL]

You can also use PGexecParams() (see the libpq documentation).  It can be a little more cumbersome to use, though.   Best Wishes, Chris Travers - Original Message - From: MUKTA To: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 8:08 PM Subject: [SQL

[SQL] Could not convert UTF-8 to ISO8859-1

I've noticed a difference in behavior between 7.2 and 7.3 with regards to character recoding and I'm a little perplexed about how to work around. I have a database in LATIN-1 that is accessed read-write by a Java app. Naturally, the Java code keeps all of its strings in UTF8 so when I prepare

Re: [SQL] Storing a range of numbers

rows will work fine ... a numeric range type seemed obvious at the time so I figured I just missed it in the documentation or something. Thanks for the reply. Chris ---(end of broadcast)--- TIP 6: Have you searched our list arc

[SQL] tsearch2 trigger alternative

Tsearch2 comes with its own tsearch2 trigger function. You pass column names to it, and it puts a vanilla tsvector into the column names in TG_ARGV[0] (zero based, yes?). Not only can you pass column names to it, but you can pass simple functions to it as well. This is magical to me. :) I'm trying

Re: [SQL] IMPORT TOOL

[EMAIL PROTECTED] (Louie Kwan) writes: > Do anyone of you aware of any PG import tool same as SQL*LOADER in ORACLE > > We are doing some DataWarehouse work and need to import a large set of data > from csv files. > > We are trying a import tool from ems-hitech.com, but I have troubles when > loadin

[SQL] SCHEMA's the easy way?

I'm planning on dipping my toes into the world of schemata. I have tables, created in the Public schema, that I'd like to move to the new schema: SELECT * INTO new.tablename FROM public.tablename; CREATE SEQUENCE ...; CREATE INDEX ...; ALTER TABLE ...; BLAH ...; BLAH ...; BLAH ...; DROP public.tab

[SQL] Schemata & User-Defined-Type casting issues

PostgreSQL 7.4.2 ... Background: I'm attempting to migrate tables which were created in the pre-schema days to a sensible schema setup. I'm using the "uniqueidentifier" column in some of these tables. When I created the new schema, I created an instance of "uniqueidentifier" and its supporting fun

[SQL] Schema + User-Defined Data Type Indexing problems...

PostgreSQL 7.4.2 -- All vacuumed and analyzed. I inserted the uniqueidentifier datatype into a new schema that I'm working on by changing the search_path to "my_schema" in the contrib SQL. It effectively created the datatype within the schema, all of its functions, operators, and operator classes.

Re: [SQL] Schema + User-Defined Data Type Indexing problems...

--- Tom Lane <[EMAIL PROTECTED]> wrote: > Chris Gamache <[EMAIL PROTECTED]> writes: > > I'm having a heck of a time, and it seems like in my thrashing about > > to find a solution to this problem I have ruined the uniqueidentifier > > datatype in the sch

Re: [SQL] Schema + User-Defined Data Type Indexing problems...

--- Tom Lane <[EMAIL PROTECTED]> wrote: > In practice I'm not sure that this is really a situation that we need to > fret about, because using a datatype that isn't in your search path has > got notational problems that are orders of magnitude worse than this > one. The functions and operators tha

Re: [SQL] Converting integer to binary

Once upon a time in PostgreSQL there was a function : bitfromint4 ... Any idea where it has disappeared to? You can do # select B'10101101'::int4; int4 -- 173 (1 row) but you want to go # select 173::varbit; which is what bitfromint4 used to do. CG --- Bruno Wolff III <[EMAIL PROTECT

[SQL] LIKE on index not working

ndex Cond: (lower((tsurname)::text) = 'weaver'::text) Filter: (bmember = 1) Total runtime: 2.36 msec (4 rows) As you can see, using the '=' operator it works just fine, but as soon as the 'like' operator comes into it, no good. Is this a bug in 7.3

Re: [SQL] LIKE on index not working

[EMAIL PROTECTED] ("Chris Cox") writes: > Hi all, > > For some reason I just can't get this to use the index for the following > query. I'm using PostgreSQL 7.3.4. > > Here's the details (let me know if you need anymore information to provide > any

Re: [SQL] LIKE on index not working

abases? What a pain! Chris - Original Message - From: "Peter Eisentraut" <[EMAIL PROTECTED]> To: "Chris Cox" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, July 22, 2004 10:41 PM Subject: Re: [SQL] LIKE on index not working > Am Do

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

_id FROM t1) group by case_id; CREATE VIEW t3 AS select case_id, min(timekeeper_id) AS tk_id FROM authorized_timekeepers WHERE tk_id NOT IN (SELECT tk_id FROM t1) AND tk_id NOT IN (SELECT tk_id FROM t2) GROUP BY case_id; Etc. Then you do a left join among the views. Hope that this helps.

  1   2   3   >