[SQL] bibliographic schema
hello we're looking for a SQL database schema for bibliographical references. the goal is to extract all the bibliographical references contained in our various existing pgsql scientific databases in only one specific database and to interconnect them with external keys and perl scripts. 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
Karsten Hilbert wrote: we're looking for a SQL database schema for bibliographical references. the goal is to extract all the bibliographical references contained in our various existing pgsql scientific databases in only one specific database and to interconnect them with external keys and perl scripts. Your best bet might be to take a look at OSS library management solutions and see whether that fits your needs or is suitable as a starting point. I'm not interested in manage a library. That kind of projects schema are mostly complex and lending and storing oriented. I just want to store scientific references in a satisfactory way. I'm surprised that all the scientific databases schema are simplistic for references, and that no-one has published or produced such a database schema, probably useful and reusable. so, I may have to create it, as said Josh, and publish it :-) thanks Guillaume ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] UNION & LIMIT & ORDER BY
Hi, Le Samedi 14 Juin 2003 17:01, Emmanuel Engelhart a écrit : > [...] Taken from the postgresql manual (http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-select.html#SQL-UNION): ORDER BY and LIMIT can be attached to a sub-expression if it is enclosed in parentheses. 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 command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CREATE table1 FROM table2
Hi, Le Mardi 17 Juin 2003 20:46, Achilleus Mantzios a écrit : > On 17 Jun 2003, Rado Petrik wrote: > > How I create table1 from other table2 . > > > > "cp table1 table2" > > create table table2 as select * from table1; > Another way would be: select * 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 using it the wrong way. Thank you for your answer
[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 using it the wrong way. Thank you for your answer
Re: [SQL] Getting last insert value
Le Samedi 15 Novembre 2003 15:18, vous avez écrit : > Hello, > I have a table with many fields, and one of the fields is of type serial. > I will do many inserts, and there will be many repeats of the other fields > in the table, but, obviously, the serial field will be unique for all the > rows. How can I get the value of serial field of the last row I inserted. > I know I can select the maximum value from the serial field, but is there > a better way, preferably something that takes constant time. > Thanks, > Yasir Malik > Doing a "select currval() from 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
Le Samedi 15 Novembre 2003 16:40, vous avez écrit :
> Doing a "select currval() from my_table" after your insert should work.
>
Actually, this is
select currval('my_sequence')
Sorry about this.
> For more details, see
> http://www.postgresql.org/docs/7.3/interactive/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...*
Johnny C wrote: I have the following tables: TABLE A month | year | item | num 1 2005 myitem 003 TABLE B num| date | descr 003 02-01-2005 blahblah 003 01-01-2005 toratora I am trying to come up with something like: select date,item,descr from TABLEA a LEFT OUTER JOIN TABLEB b ON b.num=a.num WHERE date=month-01-year; How can you make month (append) - 01 - (append) year? and pass that as a condition to the field date? Is this possible? This works for me : WHERE (month||'-01-'||year)::timestamp=date; Here is my session : galette=# create table a (month int4, year int4, item varchar(255), num int4); CREATE TABLE galette=# insert into a values (1,2005,'myitem',3); INSERT 17296 1 galette=# create table b (num int4, date timestamp, descr varchar(255)); CREATE TABLE galette=# insert into b values (3,'02-01-2005','blahblah'); INSERT 17299 1 galette=# insert into b values (3,'01-01-2005','toratora'); INSERT 17300 1 galette=# select date,item,descr from a LEFT OUTER JOIN b ON b.num=a.num WHERE (month||'-01-'||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
Hi,
tnodev a écrit :
> I'm using postGre with tables which contain French character (éèçàù...).
> Is there a fonction which performs a like in replacing é (e cute) by e ?
>
select translate('forêt', 'àâäéèêëîïôöùûü', 'aaaiioouuu');
Change the 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 PostgreSQL, not postGre.
Regards,
--
Guillaume.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Add column and specify the column position in a table
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)); > . alter table test add column givename varchar(12) after id; > > > I am looking for similar things in postgresql to add a new column to the > correct position in a table. > > Could someone hint me please. > There's no similar thing in PostgreSQL. You have to duplicate the table to do it. You can do it in a transaction : CREATE TABLE test (id varchar(3), name varchar(12)); then later : BEGIN; ALTER TABLE test RENAME TO oldtest; CREATE TABLE test (id varchar(3), givename varchar(12), name varchar(12)); INSERT INTO test (id, name) SELECT id, name FROM oldtest; DROP TABLE oldtest; COMMIT; Not really interesting if you have really big tables but, in fact, you shouldn'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)); > > . alter table test add column givename varchar(12) after id; > > > > > > I am looking for similar things in postgresql to add a new column to the > > correct position in a table. > > There's no similar thing in PostgreSQL. You have to duplicate the table > to do it. ... which is the same thing MySQL does, only you must do it explicitely. 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 data and finally drops the old table ? I mean, there's the same performance problem with big tables ? -- Guillaume. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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 > data and finally drops the old table ? I mean, there's the same > performance problem with big tables ? MySQL does that for a great deal of its DDL. Yes, the performance is awful for this on big tables. The reason I didn't answer the OP's question, though, is that I can't think of a legitimate reason to do this anyway. The physical layout of the columns should not be of concern to the developer, who should be naming the columns anyway. I totally agree on the physical layout. Just being curious :) -- Guillaume. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] .psql_history": No such file
Hi Ivan, Le vendredi 26 juin 2009 à 17:53:15, ivan marchesini a écrit : > [...] > I have installed a postgres db using a datadir different > from /var/lib/pgsql/.psql_history. > > then: >su postgres >psql postgres > > All went fine but when I exit from psql from a db I obtain: > ___ > could not save history to file "/var/lib/pgsql/.psql_history": No such > file or directory > ___ > > how can I fix this problem? where can I say psql that it must > write .psql_history into the datadir? > psql tries to write there 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT BASED ON PERCENT
Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : > No, It doesn't. > In my machine: > > First select > ERROR: syntax error at end of input > LINE 1: select * from rapadura.cliente limit 20% > ^ > Second 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 to do this in a single query. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT BASED ON PERCENT
Le jeudi 19 novembre 2009 à 01:20:24, Kris Kewley a écrit : > Could you not create a function to do this instead? > > Set var_limit = 20% of row count > Replace subquery with var_limit > Sure, see the previous mails from Pavel. You can also put the percent as a parameter of the function. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CREATE SERVER - what is this?
Le mardi 24 novembre 2009 à 16:45:27, Richard Broersma a écrit : > I noticed a few new SQL references in the manual: > > CREATE SERVER > CREATE FOREIGN DATA WRAPPER, > CREATE USER MAPPING > > Is this similar to DBI-Link? > They appear on the 8.4 release. They were added 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] rename primary key
Le 15/01/2010 04:39, Seb a écrit : > On Fri, 15 Jan 2010 12:34:15 +0900, > Ian Barwick wrote: > [...] > On a related note: how come pgadmin3 shows "Indexes (0)" for such a > table, even though an index does exist for the primary key? Are these > indexes created in a separate 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 http://dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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. > >> There is the same behaviour for unique constraints. > > Thanks. Would there be any problem with listing the index used to > enforce the primary key constraint in the Indexes node to let us know of > its existence? In fact, psql does report it with the meta-command \d. > It was somewhat confusing to see Indexes(0), and then having to rename > an index to rename a primary key constraint. > Sorry, forgot to answer this mail. It was asked a few times before, and AFAICT the answer is still the same. It is a constraint before being an index. The fact that it is also an index is an implementation detail. I understand this is confusing. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pgAgent stats
Hi, Le 16/03/2010 08:40, Marcin Krawczyk a écrit : > Hi list, does anyone know the reason for pgAdmin not showing the stats > neither for selected pgAgent step nor whole job ? The pga_joblog and > pga_jobsteplog both get populated with data on run so I was thinking that > maybe I'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 pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] insert into help
Le 22/09/2010 09:32, Nicholas I a écrit : > Hi, > > i have two tables, > --- > *table1 > > id type serial, name varchar;* > *-- > table 2 > > name varchar;* > --- > > i want to insert the values of table 2 into table 1, with automatic id's. > > insert into table1(select * from table2); > > is not working, how can i append the data to table 1 with 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pgdump with insert command help
Le 24/09/2010 14:32, Nicholas I a écrit : > hi, > > i am trying to dump a table with insert command rather tahn copy. > > pg_dump -Dt --insert table dbname > table.sql; > > i am not able to get the output. is this correct ? > > > -Nicholas I > You should 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 ([email protected]) 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
Le 26/11/2010 18:14, Thomas Kellerer a écrit : > ndias, 26.11.2010 17:22: >> When doing a insert row with less columns mentioned in "into table(col1, >> col2, col3,..." than the columns that exist on the table, on 1.10.1 it >> returns an error saying "INSERT has more expressions than target columns" >> (the error is translated so maybe the text is not exactly like this). >> Although, when this is insert is done on our test environment, where the >> version is 1.10.3 it works fine. The tables have the same columns and >> so 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 unmaintained. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Specifying column level collations
On 05/07/2011 01:19 PM, Thomas Kellerer wrote:
> Hi,
>
> I'm playing around with 9.1beta1 and would like to create a table where
> one column has a non-default collation.
>
> But whatever I try, I can't find the correct name that I have to use.
>
> My database is initialized as follows:
>
> postgres=# select version();
> version
>
> PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit
> (1 row)
>
> postgres=# select name, setting
> postgres-# from pg_settings
> postgres-# where name in ('lc_collate', 'server_encoding',
> 'client_encoding');
> name | setting
> -+-
> client_encoding | WIN1252
> lc_collate | German_Germany.1252
> server_encoding | UTF8
> (3 rows)
>
>
> Now I'm trying to create a table where one column's collation is set to
> french:
>
> create table foo (bar text collate "fr_FR") --> collation "fr_FR" for
> encoding "UTF8" does not exist
> create table foo (bar text collate "fr_FR.1252") --> collation "fr_FR"
> for encoding "UTF8" does not exist
> create table foo (bar text collate "fr_FR.UTF8") --> collation "fr_FR"
> for encoding "UTF8" does not exist
> create table foo (bar text collate "French_France.1252") --> collation
> "French_France.1252" for encoding "UTF8" does not exist
>
> So, how do I specify the collation there?
>
You first need to use "CREATE COLLATION", such as:
b1=# CREATE COLLATION fr (locale='fr_FR');
CREATE COLLATION
Then, you'll be able to create your table:
b1=# CREATE TABLE foo (bar TEXT COLLATE fr);
CREATE TABLE
b1=# \d foo
Table "public.foo"
Column | Type | Modifiers
+--+
bar| text | collate fr
> And is there a command to show me all available collations that I can use?
>
b1=# select * from pg_collation;
collname | collnamespace | collowner | collencoding | collcollate |
collctype
+---+---+--+-+
default|11 |10 | -1 | |
C |11 |10 | -1 | C | C
POSIX |11 |10 | -1 | POSIX | POSIX
en_AG |11 |10 |6 | en_AG | en_AG
en_AG.utf8 |11 |10 |6 | en_AG.utf8 | [...]
fr_FR |11 |10 |6 | fr_FR.utf8 |
fr_FR.utf8
fr_FR.utf8 |11 |10 |6 | fr_FR.utf8 |
fr_FR.utf8
fr_LU |11 |10 |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 uppercase) inside psql:
b1=# \dO
List of collations
Schema | Name | Collate | Ctype
+--++
public | fr | fr_FR.UTF8 | fr_FR.UTF8
(1 row)
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subselects not allowed?
On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote:
> Can anybody tell me why this doesn't work?
>
Because it's not supported. The START clause expects a value, not a
subquery.
> pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT
> MAX(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.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Looking for a "show create table " equivalent
On Tue, 2011-07-12 at 10:33 +0200, B.Rathmann wrote: > [...] > I've been trying to find out how to find out which sql was run to create > a certain table. 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). > 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? > No. pgAdmin does 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Partition over a sliding date window
Hi, I have a simple table with 3 fields: date, value and username, which can hold identical rows. What I am trying to do is to have for each date the count of distinct username for the 30 previous days. I was hoping to get this with a partition, as in the following non-working exemple SELECT t.date , t.value , COUNT(DISTINCT t.username) OVER (PARTITION date BETWEEN t.date - INTERVAL '29 days' and t.date) FROM table t GROUP BY date , value , username ; There are many issues with this query: - distinct not implemented for window function - COUNT () OVER is not seen as an aggregate function, I thus need to add username in the GROUP BY clause, which leads to wrong result - I am not convinced that the date BETWEEN is valid either, but the other issues prevent me to check this. Is there a way to do what I am looking for with partitions, or should I just give up and use 'usual' sql? Thanks, Guillaume -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Use select and update together
On Sat, 2011-09-17 at 16:56 +0200, Andreas wrote: > Am 13.09.2011 07:50, schrieb pasman pasmański: > > In 8.4 this syntax is not implemented. > > select * from ( > update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning * > ) as x > > wouldn't work 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, 4, 5 ) returning *) SELECT * FROM x; -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [GENERAL] Creating rule for sliding data
Hi,
On Sun, 2011-10-09 at 18:50 +0200, F. BROUARD / SQLpro wrote:
> I am answering to myseilf...
>
> the good syntax is something like :
>
>
> CREATE RULE R_U_MSR_BEFORE2000x
> AS
> ON UPDATE TO T_MESURE_MSR
> WHERE ( NEW.MSR_DATE < DATE '2000-01-01' )
> DO INSTEAD
> (
> -- rows does not change partition :
>UPDATE T_MESURE_BEFORE2000_MSR
>SET MSR_ID = NEW.MSR_ID,
>MSR_DATE = NEW.MSR_DATE,
>MSR_MESURE = NEW.MSR_MESURE
>WHERE ( OLD.MSR_DATE < DATE '2000-01-01' );
> -- rows does change partition (first INSERT NEWs then DELETE OLDs)
>INSERT INTO T_MESURE_MSR
>SELECT MSR_ID,
> MSR_DATE,
> MSR_MESURE
>FROM NEW
>WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ); ;
>DELETE FROM T_MESURE_MSR
>WHERE MSR_ID = OLD.MSR_ID
> AND MSR_DATE = OLD.MSR_DATE
> AND MSR_MESURE = OLD.MSR_MESURE
> AND NOT ( OLD.MSR_DATE < DATE '2000-01-01' );
> );
>
> The problem is nowhere in the doc there is a mention where much more
> than one commande must be place into brackets !
>
As a matter of fact, it does:
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
^ ^
| |
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 ([email protected])
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...
> >>
> >> the good syntax is something like :
> >>
> >>
> >> CREATE RULE R_U_MSR_BEFORE2000x
> >> AS
> >> ON UPDATE TO T_MESURE_MSR
> >> WHERE ( NEW.MSR_DATE< DATE '2000-01-01' )
> >> DO INSTEAD
> >> (
> >> -- rows does not change partition :
> >> UPDATE T_MESURE_BEFORE2000_MSR
> >> SET MSR_ID = NEW.MSR_ID,
> >> MSR_DATE = NEW.MSR_DATE,
> >> MSR_MESURE = NEW.MSR_MESURE
> >> WHERE ( OLD.MSR_DATE< DATE '2000-01-01' );
> >> -- rows does change partition (first INSERT NEWs then DELETE OLDs)
> >> INSERT INTO T_MESURE_MSR
> >> SELECT MSR_ID,
> >>MSR_DATE,
> >>MSR_MESURE
> >> FROM NEW
> >> WHERE NOT ( OLD.MSR_DATE< DATE '2000-01-01' ); ;
> >> DELETE FROM T_MESURE_MSR
> >> WHERE MSR_ID = OLD.MSR_ID
> >> AND MSR_DATE = OLD.MSR_DATE
> >> AND MSR_MESURE = OLD.MSR_MESURE
> >> AND NOT ( OLD.MSR_DATE< DATE '2000-01-01' );
> >> );
> >>
> >> The problem is nowhere in the doc there is a mention where much more
> >> than one commande must be place into brackets !
> >>
> >
> > As a matter of fact, it does:
> >
> > CREATE [ OR REPLACE ] RULE name AS ON event
> > TO table [ WHERE condition ]
> > DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
> >
> > ^ ^
> > | |
> > See -+
> >
> > Extract from
> > http://www.postgresql.org/docs/9.1/interactive/sql-createrule.html
> >
> >
>
> Please give a real example instead of copying the doc that I have read a
> lot !
>
> I am not so stupid to have post this topic without having try many
> syntaxes wich does not works !
>
I don't think you're stupid. You said the doc was wrong, and I answered
you it wasn't. But I understand it didn't help you solve your issue...
Anyway, if you gave us the error message, it would be easier to answer
you. Here is the error message I get:
ERROR: relation "new" does not exist
LINE 18:FROM NEW
^
And actually, you can't use "FROM NEW". And this:
INSERT INTO T_MESURE_MSR
SELECT MSR_ID,
MSR_DATE,
MSR_MESURE
FROM NEW
WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' )
has no meaning at all in PostgreSQL.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with DROP ROLE
On Wed, 2011-10-19 at 12:11 +0200, Brice André wrote: > Hello everyone, > > I would want to implement an SQL query where I would be able to suppress all > information from a registered user. I am currenlty able to suppress > everything except the user role. The name of the role is present in a table > and so, I would want to perform something like this : > DROP ROLE (SELECT ...) > but this is not considered as valid, as DROP ROLE is expecting a name and > not a text field. So, I tried the following, but with no success : > DROP ROLE CAST((SELECT...) AS name) > > 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] new user on mac
On Wed, 2011-10-19 at 01:13 -0700, Basil Bourque wrote: > >> I have a postgres 9.1 database up & running, no problem. Purely in > >> terms of writing sql (ddl, dml & pg/plsql), what tools are > >> recommended? > >> > >> Coming from an Oracle world, I'm thinking of toad, sql developer, etc. > >> > >> 1. psql & text editor of choice (if so, which one?) > >> 2. navicat > >> 3. textmate with pgedit > >> 4. eclipse plugin > >> 5. other? > > >> +1 for pgAdmin3. If you have already used Toad, u would like to check it. > > Being new to SQL (but old to other relational databases) and a Mac guy, I > have found pgAdmin to work surprisingly well. Thanks :) > Surprising because I have a knack for breaking/corrupting/crashing nearly > every developer tool I start using as a newbie. Don't we all do? > But pgAdmin has worked nearly flawlessly for me. It looks goofy from a Mac > aesthetics perspective, but it works. Yeah, the wxWidgets toolkit doesn't really have a native UI for every widget, so it can look goofy in some windows. > I may have once had an inexplicable glitch, but after restart all was well. > I've only been bitten by 2 recurring bugs: > > • (Cosmetic) Changing font size for use on projectors in a meeting makes > fonts bigger, but the rows of the Output Pane in a SQL window fail to grow in > height. > Yeah, someone already reported this bug. It shouldn't be hard to fix. You can always use the mousewheel in the query tool. The new setting will be temporary but it should work for a meeting. > • (Serious) Tools > Server Configuration > pg_hba.conf has a nasty > anti-feature. When loading a saved conf file with incorrect syntax (usually I > forget to put the slash+number on an IP address such as 127.0.0.1/32), > pgAdmin parses the file, identifies the flaw, and chooses to ignore the rule > by not displaying it. Unfortunately, pgAdmin does NOT parse the entries when > entering or saving them. So if you screw up a rule: > (a) You won't realize you saved incorrect syntax. To the user, it seems the > rule you entered simply vanished. > (b) You can't fix it in pgAdmin. You'll have to gain access to the filesystem > as the Postgres superuser (usually that's the Unix user 'postgres'), and edit > the file. This is not easy to do as a Mac GUI user. > This issue has been acknowledged in the mailing lists. > Yes, it was already reported and we have to fix this. > But otherwise, pgAdmin has served me well for connecting to the Postgres > server, creating databases, creating tables, creating columns, creating a few > initial rows of data, editing some field values, and so on. > Good. > When first starting out creating tables, I used the GUI dialogs in pgAdmin. > Nowadays I take advantage of the feature where pgAdmin generates and shows > you the SQL that would re-create the table on which you've clicked. When > creating a new table, I copy the SQL from a similar table, paste into a text > editor, and edit appropriately. Then I paste the SQL back into a SQL window > in pgAdmin to execute. > When you are on a PostgreSQL object, you can simply click the query tool button, and the query tool will open with the SQL create query for this object. > My usual choice in text editors is TextMate, running the surprisingly > productive "Zenburnesque" Fonts & Color scheme in Preferences, where you can > force the text to be interpreted as SQL without bothering to save the file by > choosing "SQL" from the popup at the window's bottom frame. > > Other good text editors include JEdit (Java-based, free-of-cost), > TextWrangler (free of cost), and BBEdit. > http://www.jedit.org/ > http://www.textwrangler.com/products/textwrangler/ > TextWrangler's commercial big-brother BBEdit is also a popular text-editor on > Mac OS X. > http://www.textwrangler.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 tried them yet as pgAdmin is sufficient > for now. > And we are interested in hearing about other users' feelings/reviews about pgAdmin to make it even better. That can happen on the usual PostgreSQL mailing lists or on the pgadmin ones. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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: > > > > • (Cosmetic) Changing font size for use on projectors in a meeting makes > > fonts bigger, but the rows of the Output Pane in a SQL window fail to grow > > in height. > > > > Yeah, someone already reported this bug. It shouldn't be hard to fix. > You can always use the mousewheel in the query tool. The new setting > will be temporary but it should work for a meeting. > This is fixed. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] new user on mac
On Thu, 2011-10-20 at 04:15 -0700, Scott Swank wrote: > Here is a seeming quirk in pgadmin3. I say seeming, because I may > simply not be sufficiently familiar with this tool. > > [A quick note for non-mac users, the Finder is the gui file browser, > equivalent to Nautilus/Dolphin in linux or the Window Explorer in MS > Windows.] > > I have associated sql files with pgadmin. > > When I open pgadmin I have a "pgadmin" window and I can open > additional "query tool" windows. However, if from the finder I open a > sql file I do not see it in a query tool window. Application focus > changes from the finder to pgadmin, but I do not see the sql. > > Conversely, if I do not have pgadmin open and from the finder I open a > sql file I see it in a query tool window. I, however, I open > additional sql files from the finder they do not open in a query tool > window, but again focus changes to pgadmin. Also, if I start pgadmin > this way I only have query tool windows, how do I open the basic > pgadmin window? > > This behavior does not seem to depend on how I open the file, e.g. > double-click, 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.info http://www.dalibo.com PostgreSQL Sessions #3: http://www.postgresql-sessions.org -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ignore case in where clause
On Thu, 2012-03-22 at 16:26 -0400, Edward W. Rouse wrote: > I am currently using lower(column) = '' for matching case insensitive. I > know that there are ways to do this with regular expressions too. I recently > noticed that including even one lower causes severe performance issues (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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] \copy multiline
On Wed, 2012-11-28 at 21:21 -0600, Seb wrote: > Hi, > > I use \copy to output tables into CSV files: > > \copy (SELECT ...) TO 'a.csv' CSV > > but for long and complex SELECT statements, it is cumbersome and > confusing to write everything in a single line, and multiline statements > don't seem to be accepted. Is there an alternative, or am I missing an > continuation-character/option/variable that would allow multiline > statements in this case? > A simple way to workaround this issue is to create a view with your query and use the view 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ALTER USER abc PASSWORD - what's going on ???
On Thu, 2013-04-18 at 13:21 +0200, Marcin Krawczyk wrote: > I figured it out... when changing role from pgAdmin, it has a default VALID > UNTIL 1970 set and after confirming changes it just made my abc user > account invalid... FYI, this pgAdmin bug has been fixed. The fix will be available in the next minor release. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE...RETURNING problem with libpq
On Sat, 2013-05-25 at 14:49 +0200, Brice André wrote: > Hi Wolfe, > > First, thanks for your help. > > I tried your code, but it does not work... the function returns a string : > "0". When I check, this command properly modifies one row, as expected. > It works for me with PQntuples. > I don't know if it may help, but just in case... The DELETE operation is > not performed on a table : it is performed on a view. I have a rule on that > view that, instead of performing a delete on the corresponding table, > performs an update with table entry tagged as deleted (in a dedicated > column of the table). This code (sql part) works for years and, when > executing this command by other ways (php and pgadmin), it deletes and > returns expected data. With libpq, it deletes, but does not return anything. > You'll find my test 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 all the rows have already been deleted. Hope it helps. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com example.tgz Description: application/compressed-tar -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] lower() not working correctly...?
Andreas Joseph Krogh a ecrit le 15/09/2006 10:06: I have the following query: select lower(firstname) || ' ' || lower(lastname) from person firstname and lastname are VARCHAR lower() returns NULL when firstname OR lastname is NULL, is this correct? This is 8.2devel from 24.08.2006. 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 to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] INSERT INTO
Bart Degryse a ecrit le 16/03/2007 10:03:
I don't use ASP but in PHP I would do something in the line of
$valuetoinsert = "SANT'ANGELO LODIGIANO";
$query = "INSERT INTO TABLE2 (TE_INDI) VALUES ('" . str_replace("'",
"''", $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.
--
Guillaume.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
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 the replace function. Something like that : <%= replace(string,"'","''") %> might work. But remember I don't use ASP. Regards. -- Guillaume. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] join problem
A. R. Van Hook a écrit : > I have join problem: > "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, > sum(i.tax) as tax, > sum(i.tax + i.rowtot) as totalP, > (sum(i.tax + i.rowtot) - v.deposit) as balance > from invoice v > left outer join > invoiceitems i > on v.ivid = i.ivid > where v.cusid = $cusid > and v.cusid = cai.cusidgroup by > i.ivid, v.eventdate, v.deposit, v.invdate, cai.db > ERROR: missing FROM-clause entry for table "cai" > > If I add cai to the from clause "from invoice v, cai, I get > ERROR: missing FROM-clause entry for table "cai" > ERROR: invalid reference to FROM-clause entry for table "v" > > 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
Yohanes Purnomo a écrit : > [...] > I create a function: > > CREATE OR REPLACE FUNCTION ReProses() > RETURNS BOOLEAN > AS $$ > > DECLARE > nHasil Numeric; > > BEGIN > CREATE TEMP TABLE tmpTtlRekening WITHOUT OIDS ON COMMIT DROP > AS > SELECT Rekening, SUM(Debet) AS Debet, SUM(Kredit) AS Kredit > FROM Jurnal > GROUP BY Rekening; > > SELECT COALESCE(SUM(Debet - Kredit), 0) > INTO nHasil > FROM tmpTtlRekening; > > -- bla,bla > RETURN '1'; > END; > $$ LANGUAGE 'plpgsql'; > > > Execute Query: > > SELECT ReProses() > > Result is fine, but when i execute again > When you run a function for the first time in the session, PostgreSQL keeps in cache some information... query plans for example. > SELECT ReProses() > > ERROR: relation with OID 41573 does not exist > When you run it a second time, it uses the query plan in cache. As tmpTtlRekening has been drop at the end of the first run, its OID will change but the old OID is still in cache. So PostgreSQL tries to 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
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 note that i still want to discuss the titled problem or to be given an exact pointer to documentation regarding the currval() behavior in the described situation, that i had. Well, your situation is weird, to say the least. currval() doesn't change the value of a sequence. Adding a trigger that calls nextval() will change the 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 Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com ---(end of broadcast)--- TIP 1: 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] 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 note that i still want to discuss the titled problem or to be given an exact pointer to documentation regarding the currval() behavior in the described situation, that i had. Well, your situation is weird, to say the least. currval() doesn't change the value of a sequence. Adding a trigger that calls nextval() will change the 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. my fault -- i forgot to say. The trigger is "for each row". It is understandable, i agree, but it is surprising -- alternative behavior (constant result off currval()) is understandable too. It isn't surprising. A "for each row" trigger will execute the trigger function for each individual row, which will executes nextval each time... so each currval will get a different value. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] undefined relations in pg_locks
Sabin Coanda wrote: Hi there, I have a procedure where a dead-lock occurs, and I'm trying to find the tables involved in the lock. Unfortunatelly, I don't find the related objects of the oids of "relation" field. Also all the fields "classid" and "objid" 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. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Concat field result in select query
Nacef LABIDI a écrit : > Hi all, > > I was wondering if there is a way to concatenate the results of a field > in a select to return it as a single string. > > Example : > > my_field > -- > 1 > 2 > 3 > > select concat_something(my_field) from my_table group by something; > the result expected would be someting like that : 1/2/3 (with a > separator it would be really nice) > > I hope that I am not asking for too much ;) > Something like: SELECT array_to_string(array(select myfield from mytable), ',')); 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://dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Simple Problem ?
Hengky Lie a écrit : > [...] > I really amazed with this problem and need your help. > > I run simple query from Query window of PgAdmin3: SELECT * FROM mytable > limit 10; > > and the result are NON UPDATABLE QUERY, while i need the result are > updatable. > > I run the same query from Passthrough SQL in Microsoft Access, the > result are the same (non updatable). But when i run this query from EMS > SQLMANAGER for postgre, the result are UPDATABLE. I really amazed. > > Could someone help me ? > Do you have a primary key on mytable 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 via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Simple Problem ?
Hengky Lie a écrit : > This is the same as what i think but the table has primary key and has > no relation to other table. This is the table definition : > [...] > > These query result non updateable records : > > SELECT kode, namabarang from tblproduk; > > SELECT * 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://dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Simple Problem ?
Hengky Lie a écrit : > I have to use sql command because i want to retrieve data according to > several criteria. > The SQL command just a sample to show that the data i retrieve not > updateable. The real query like : > > SELECT * FROM TBLPRODUK WHERE SUBKAT='abc'; > > 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 mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Copy question
Judith Altamirano a écrit : > Hello every body I'm trying to extract a query as follows: > > copy (select * from clientes where id_cliente = 7895) to > '/home/paso/sel.copy'; > > But it returns the next error: > > ERROR: error de sintaxis en o cerca de «(» 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 later. It won't with earlier releases. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extract last months data
Antti Linno wrote:
> Lo.
> Today I digged in manuals and other docs, but didn't find any hint, how
> to get data from table, where some cols are in date or datetime format,
> and the data was about last month, or about special month.
> Psql help was unhelpful too.
> Here's example:
> In table workers is data with different date. When I need to make summary,
> I just want to extract last months data, but I don't know whether the
> month ended with 29,30 or 31 (external program, that uses postgres),
> so I cant use
> select * from workers where 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.
> All suggestions are welcome :P
>
> Stucked alligator :)
--
Guillaume Perréal - Stagiaire MIAG
Cemagref (URH), Lyon, France
Tél: (+33) 4.72.20.87.64
