[SQL] bibliographic schema

ripts. database schema of existing free projects (refdb, refbase, etc.) are not satisfactory. any link or suggestion will be welcome. (I hope this is the good list) thanks Guillaume ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] bibliographic schema

h it :-) thanks Guillaume ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] UNION & LIMIT & ORDER BY

es. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression. Regards. -- Guillaume . ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister comman

Re: [SQL] CREATE table1 FROM table2

t * into table2 from table1; (See http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-selectinto.html). -- Guillaume . ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

[SQL] FOR : Structure control pb

I am using the For controle structure as follows   FOR i IN 0..23 LOOP   query   END LOOP;   The query is tested and is working OK.   When I launch the loop in psql interface, I get the error parse error at or near FOR.   As this is the first time I use this kind of struture I am probably us

[SQL] FOR : control structure

I am using the For controle structure as follows   FOR i IN 0..23 LOOP   query   END LOOP;   The query is tested and is working OK.   When I launch the loop in psql interface, I get the error parse error at or near FOR.   As this is the first time I use this kind of struture I am probably usi

Re: [SQL] Getting last insert value

my_table" after your insert should work. For more details, see http://www.postgresql.org/docs/7.3/interactive/functions-sequence.html -- Guillaume . ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Getting last insert value

eractive/functions-sequence.html -- Guillaume . ---(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] append fields for *where...*

-'||year)::timestamp=date; date | item | descr -+----+-- 2005-01-01 00:00:00 | myitem | toratora (1 ligne) -- Guillaume. signature.asc Description: OpenPGP digital signature

Re: [SQL] Like with special character

he first word (forêt) by the string you want characters to be replaced. For more, see : http://docs.postgresqlfr.org/pgsql-8.1.3-fr/functions-string.html (french docs) http://www.postgresql.org/docs/8.1/interactive/functions-string.html (english docs) BTW, it is Postgr

Re: [SQL] Add column and specify the column position in a table

houldn't rely on columns' order. Regards. -- Guillaume. ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Add column and specify the column position in a table

2006/5/18, Alvaro Herrera <[EMAIL PROTECTED]>: Guillaume LELARGE wrote: > Emi Lu a écrit : > > I am trying to insert one column to a specific position in a table. > > > > In mysql, I can do: > > . create table test(id varchar(3), name varchar(12)); > >

Re: [SQL] Add column and specify the column position in a table

2006/5/18, Andrew Sullivan <[EMAIL PROTECTED]>: On Thu, May 18, 2006 at 05:43:19PM +0200, Guillaume Lelarge wrote: > Do you mean that, using "alter table test add column" with the "after" > option, MySQL creates a new table, populates it with the old table > d

Re: [SQL] .psql_history": No such file

because the home directory of the postgres user is /var/lib/postgres. Probably better to use HISTFILE to change it (\set HISTFILE '/path/to/histfile'). Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgres

Re: [SQL] LIMIT BASED ON PERCENT

nd one: > ERROR: argument of LIMIT must not contain subqueries > > Postgres 8.3 > It works for Lee because obviously he's working on a 8.4 server. You can use subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For earlier releases, there's no way t

Re: [SQL] LIMIT BASED ON PERCENT

of the function. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- 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] CREATE SERVER - what is this?

ed to support the use of SQL/MED compliant FOREIGN DATA WRAPPER, SERVER, and USER MAPPING as method to supply dblink connect parameters. dblink can already use these informations AFAIK. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql

Re: [SQL] rename primary key

eparate table that is looked up by the foo table? > Primary keys are constraints. They are enforced with an index, but actually they are constraints. So we put them on the constraints nodes. There is the same behaviour for unique constraints. -- Guillaume. http://www.postgresqlfr.org

Re: [SQL] rename primary key

Le 15/01/2010 17:13, Seb a écrit : > On Fri, 15 Jan 2010 07:35:17 +0100, > Guillaume Lelarge wrote: > > [...] > >> Primary keys are constraints. They are enforced with an index, but >> actually they are constraints. So we put them on the constraints >> nodes.

Re: [SQL] pgAgent stats

27;m missing some view associated with statistics tabs ?? Any ideas ? > Which release of pgAdmin? AFAICT, 1.10 Jobs' and Steps' statistics are last run time, status, start time, stop time, and duration. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgs

Re: [SQL] insert into help

auto incremented > or nextval. > INSERT INTO table1 (name) SELECT name FROM table2; -- Guillaume http://www.postgresql.fr http://dalibo.com -- 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] pgdump with insert command help

d put the table name right after the -t command-line option. This way: pg_dump -Dt table --insert dbname > table.sql -- Guillaume http://www.postgresql.fr http://dalibo.com -- 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] Insert row in 1.10.1 and 1.10.3

on. > > > What versions are you talking about? > PostgreSQL is currently at 9.0.1, the previous version was 8.4.something > I doubt there ever was a Version 1.10.3 of PostgreSQL > This looks like a pgAdmin release. There was a 1.10.1 and a 1.10.3, which are a bit old now, and unmai

Re: [SQL] Specifying column level collations

6 | fr_LU.utf8 | fr_LU.utf8 fr_LU.utf8 |11 |10 |6 | fr_LU.utf8 | fr_LU.utf8 ucs_basic |11 | 10 |6 | C | C fr | 2200 |10 |6 | fr_FR.UTF8 | fr_FR.UTF8 (47 rows) Or \dO (o in uppercas

Re: [SQL] Subselects not allowed?

X(source_id) FROM sources); > ERROR: syntax error at or near "(" > LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX... > ^ > pgslekt=> > > regards, Leif > -- Guillaume http://blog.gu

Re: [SQL] Looking for a "show create table " equivalent

es it this way because it has no other way to do it. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Partition over a sliding date window

ld I just give up and use 'usual' sql? Thanks, Guillaume -- 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] Use select and update together

k even in PG 9.1. > So what data structure is coming out of an "update ... returning *" > statement? > It obviously doesn't work like a subquery. > The only way to make something like this work in 9.1 would be: WITH x AS (update tbl set val = 1 where key in ( 1, 2, 3,

Re: [SQL] [GENERAL] Creating rule for sliding data

| | See -+ Extract from http://www.postgresql.org/docs/9.1/interactive/sql-createrule.html -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- 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] [GENERAL] Creating rule for sliding data

On Sun, 2011-10-09 at 21:17 +0200, F. BROUARD / SQLpro wrote: > Hi, > > Le 09/10/2011 19:07, Guillaume Lelarge a écrit : > > Hi, > > > > On Sun, 2011-10-09 at 18:50 +0200, F. BROUARD / SQLpro wrote: > >> I am answering to myseilf... > &g

Re: [SQL] Problem with DROP ROLE

gt; > So, does someone knows how to handle this problem ? > You can't do it with only one query. You need to use two queries: the SELECT, and then the DROP. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list (pgsql

Re: [SQL] new user on mac

rangler.com/products/bbedit/ > > Other Java-based IDEs are free-of-cost, run well on Mac OS X, and include SQL > editing tools: IntelliJ, NetBeans, Eclipse. > > There are many other SQL tools that run on Mac OS X, especially the > Java-based tools using JDBC. I've not tr

Re: [SQL] new user on mac

On Thu, 2011-10-20 at 12:22 +0200, Guillaume Lelarge wrote: > On Wed, 2011-10-19 at 01:13 -0700, Basil Bourque wrote: > [...] > > I may have once had an inexplicable glitch, but after restart all was well. > > I've only been bitten by 2 recurring bugs: > > > &

Re: [SQL] new user on mac

ck, or "open with: pgadmin3". > > Is this simply an os integration issue on mac, or am I missing something? > Seems to be an integration issue. I created a ticket to work on this later. Sorry for the (really really) late answer. -- Guillaume http://blog.guillaume.lelarge.in

Re: [SQL] ignore case in where clause

ssues (from > 290ms to over 80Kms). > Probably because it cannot use the index anymore. Try creating an index on lower(column), and see if it helps. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org

Re: [SQL] \copy multiline

ew in the \copy meta-command of psql. Of course, it means you need to have the permission to create views in the database. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- 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] ALTER USER abc PASSWORD - what's going on ???

ilable in the next minor release. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- 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...RETURNING problem with libpq

st case attached. It's C code, not C++, but I guess it won't be a big issue :) It gives me this result: $ ./example connection OK prepare OK execute OK ntuples: 81 cleanup OK 81 is the number of lines to delete. If I launch it another time, it gives me 0 as a

Re: [SQL] lower() not working correctly...?

6. NULL is an unkown value. lower of an unkown value is unknown... thus NULL. This is correct behavior. -- Guillaume. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command

Re: [SQL] INSERT INTO

, $valuetoinsert) . "')"; I'm sure ASP has also a string replacement function This doesn't answer Shavonne's question but, instead of str_replace, you should better use pg_escape_string : http://www.php.net/manual/en/function.pg-escape-string.php Regards. --

Re: [SQL] INSERT INTO

Shavonne Marietta Wijesinghe a ecrit le 16/03/2007 11:31: i took a look at the link Guillaume gave me. But it also explains on PHP i'm using ASP :( Yes, that's why I said that it didn't answer your question. Sorry if this wasn't clear. After a bit of googling, I found

Re: [SQL] join problem

> Where do I add the cai table reference??? > You probably need to change the order of the tables in the FROM clause. Replace FROM invoice v, cai with FROM cai, invoice v Regards. -- Guillaume. ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Error OID

get information from the old temp table. If you don't want that PostgreSQL put the query plan in cache, use the EXECUTE statement. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] currval() within one statement

value. But you're not telling us which kind of trigger... per statement or for each row ? if it's for each row, then that's quite understandable. BTW, sequence functions are described here : http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html Regar

Re: [SQL] currval() within one statement

sad wrote: Guillaume Lelarge wrote: sad wrote: A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only

Re: [SQL] undefined relations in pg_locks

ot; are null. May I suppose there were references to temporary objects ? However, how cand I get the related objects involved in this lock ? Maybe they're not objects of the database you're connected to. You need to check the "database" column of pg_locks. Regards. -

Re: [SQL] Concat field result in select query

; For example: sherkin=# select * from t1; c1 | c2 + 1 | 2 | 3 2 | 4 2 | 5 (4 lignes) sherkin=# SELECT array_to_string(array(select c1 from t1), ','); array_to_string - 1,2,2,2 (1 ligne) -- Guillaume. http://www.postgresqlfr.org http://dalib

Re: [SQL] Simple Problem ?

or OID ? If you don't, pgAdmin3 can't allow modification because it doesn't know how to select a line uniquely. I suppose it is the same for Access. And it is a really bad behaviour of EMS SQL Manager. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent v

Re: [SQL] Simple Problem ?

* from tblproduk; > > What is the problem ? > Oops, I was wrong on this. You can't edit results in the query tool. You need to use the data viewer (menu Tools/View Data/View All Rows ; there's also a button in the toolbar). -- Guillaume. http://www.postgresqlfr.org http://d

Re: [SQL] Simple Problem ?

gt; > So i do not need to view data from data viewer, but using query with an > updateable result. > > Any suggestion ? > Yes, use the data viewer : menu Tools/View Data/View Fitered Rows. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql m

Re: [SQL] Copy question

» at character 6 > LINE 1: copy (select * from clientes where id_cliente = 7895) to '/h... > > > I was reading the manual and I think the sintaxis is correct, any idea > wich is the cause of the error? > It depends on your PostgreSQL release. It should work with 8.2 and

Re: [SQL] extract last months data

ere date<31.1.2000 and date>1.1.2000 > I think I can get month and year number at least, but I can't use mday > attribute. Try: SELECT * FROM workers WHERE DATE_TRUNC('month', date) = '2000-31-1'::DATE; See the manual for exact syntax of DATE_TRUNC. > A