[SQL] underscore pattern in a query doens't work
Hello everyone! Below you can find the problem I'm dealing with. I'd appreciate your help. Thank you!! - A description of what you are trying to achieve and what results you expect.: I'd like to execute a query using the underscore as a pattern. select id,etiqueta from limites_municipales where etiqueta like 'Garaf_a'; It should return some rows but it is not: id | etiqueta +-- (0 rows) This is the content in the table: palma=> select id,etiqueta from limites_municipales; id | etiqueta ---+-- 0 | El Paso 12743 | Tazacorte 12744 | Los Llanos de Aridane 12745 | Villa de Mazo 12746 | Breña Baja 12747 | Santa Cruz de la Palma 12748 | Garafía 12749 | San Andrés y Sauces 12751 | Puntallana 12741 | Puntagorda 12742 | Tijarafe 12975 | Breña Alta 12976 | Fuencaliente de la Palma 12837 | 12846 | . 12910 | 12750 | Barlovento (38 rows) - PostgreSQL version number you are running: PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 64-bit - How you installed PostgreSQL: Downloaded from postgresql.org and installed. - Changes made to the settings in the postgresql.conf file: no changes. - Operating system and version: Linux centos - What program you're using to connect to PostgreSQL: psql - Is there anything relevant or unusual in the PostgreSQL server logs?: No - For questions about any kind of error: Does the behaviour has to do with the client_encoding or server_encoding of the cluster? Both are set to SQL_ASCII. The locale list in the server is: LANG=es_ES.ISO-8859-15 LC_CTYPE="es_ES" LC_NUMERIC="es_ES" LC_TIME="es_ES" LC_COLLATE="es_ES" LC_MONETARY="es_ES" LC_MESSAGES="es_ES" LC_PAPER="es_ES" LC_NAME="es_ES" LC_ADDRESS="es_ES" LC_TELEPHONE="es_ES" LC_MEASUREMENT="es_ES" LC_IDENTIFICATION="es_ES" LC_ALL=es_ES
Re: [SQL] pg_restore problem
On 09/12/2012 12:23 AM, Kjell Øygard wrote:
Morning guys...
I have two servers , one with postgres 9.2rc1 and one with postgres
9.1.4. I need to do a restore from a dump from 9.1.4 to 9.2rc1 and I get
this error:
pg_restore: [archiver (db)] Error from TOC entry 177675; 2613 579519
BLOB 579519 primar
pg_restore: [archiver (db)] could not execute query: ERROR: duplicate
key value violates unique constraint "pg_largeobject_metadata_oid_index"
DETAIL: Key (oid)=(579519) already exists.
Command was: SELECT pg_catalog.lo_create('579519');
This just keep repeat itself in the log.
The command used is: pg_restore -O -U user -d database2 database2.dump
>dump.log 2>&1 &
Appreciate any help
Several things:
1) The production version of 9,2 is out(9.2.0).
2) When you did the dump from 9.1.4 did you use the 9.1.4 or 9.2 version
of pg_dump?
3) What was the pg_dump command you used?
--
Rgds
Kjell Inge Øygard
Electronic Chart Centre
www.ecc.no
--
Adrian Klaver
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] generate_series() with TSTZRANGE
On Thu, Sep 13, 2012 at 3:53 AM, Wolfe Whalen wrote:
> SELECT tstzrange((lag(a) OVER()), a, '[)')
> FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17
> 12:00:00', '1 hour')
> AS a OFFSET 1;
What about this form?
select tstzrange(a, a + '1 hour'::interval, '[)')
from generate_series(
'2012-09-16'::timestamp,
'2012-09-16 23:00'::timestamp,
'1 hour'::interval) as a;
>
> Basically, it's generating a series of time stamps one hour apart, then
> using the previous record and the current record to construct the
> TSTZRANGE value. It's offset 1 to skip the first record, since there is
> no previous record to pair with it.
>
> If you were looking at Josh Berkus' example at
> http://lwn.net/Articles/497069/ you might use it like this to generate
> data for testing and experimentation:
>
> INSERT INTO room_reservations
> SELECT 'F104', 'John', 'Another Talk',
> tstzrange((lag(a) OVER()), a, '[)')
> FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17
> 12:00:00', '1 hour')
> AS a OFFSET 1;
>
> Thanks!
>
> --
> Wolfe Whalen
> [email protected]
>
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sergey Konoplev
a database and software architect
http://www.linkedin.com/in/grayhemp
Jabber: [email protected] Skype: gray-hemp Phone: +79160686204
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] underscore pattern in a query doens't work
"Sergio Calero." writes: > I'd like to execute a query using the underscore as a pattern. > select id,etiqueta from limites_municipales where etiqueta like 'Garaf_a'; > [ but this fails to match 'Garafía' ] I suspect what you have here is an encoding problem. That is, probably the "í" is represented as a multi-byte character (most likely UTF8) but the server thinks it's working with a single-byte encoding so that any one character should be only one byte. You didn't say what your encoding setup is, so it's hard to do more than speculate. > PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 > 20060404 (Red Hat 3.4.6-10), 64-bit You do realize this is about 3 years out of date? The 8.4 series is up to release 8.4.13, and a lot of those updates contained fixes for serious bugs. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] underscore pattern in a query doens't work
Thanks Tom, for your suggestions. We started the cluster up with this command: ./initdb -D /usr/local/postgre/data -E UTF8 -U sir The rest of the variables related to encoding (locale) are: lc_collate=C lc_ctype=C lc_messages=C lc_monetary=C lc_numeric=C lc_time=C Could you tell me which more info could I give you? De: Tom Lane Para: Sergio Calero. CC: "[email protected]" Enviado: Jueves 13 de septiembre de 2012 16:19 Asunto: Re: [SQL] underscore pattern in a query doens't work "Sergio Calero." writes: > I'd like to execute a query using the underscore as a pattern. > select id,etiqueta from limites_municipales where etiqueta like 'Garaf_a'; > [ but this fails to match 'Garafía' ] I suspect what you have here is an encoding problem. That is, probably the "í" is represented as a multi-byte character (most likely UTF8) but the server thinks it's working with a single-byte encoding so that any one character should be only one byte. You didn't say what your encoding setup is, so it's hard to do more than speculate. > PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 > 20060404 (Red Hat 3.4.6-10), 64-bit You do realize this is about 3 years out of date? The 8.4 series is up to release 8.4.13, and a lot of those updates contained fixes for serious bugs. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] underscore pattern in a query doens't work
"Sergio C." writes: > We started the cluster up with this command: > ./initdb -D /usr/local/postgre/data -E UTF8 -U sir That doesn't prove anything about the specific database where you're having the problem ... regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] generate_series() with TSTZRANGE
That's much better, thank you! -- Wolfe Whalen [email protected] On Thu, Sep 13, 2012, at 06:52 AM, Sergey Konoplev wrote: > On Thu, Sep 13, 2012 at 3:53 AM, Wolfe Whalen > wrote: > > SELECT tstzrange((lag(a) OVER()), a, '[)') > > FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17 > > 12:00:00', '1 hour') > > AS a OFFSET 1; > > What about this form? > > select tstzrange(a, a + '1 hour'::interval, '[)') > from generate_series( > '2012-09-16'::timestamp, > '2012-09-16 23:00'::timestamp, > '1 hour'::interval) as a; > > > > > Basically, it's generating a series of time stamps one hour apart, then > > using the previous record and the current record to construct the > > TSTZRANGE value. It's offset 1 to skip the first record, since there is > > no previous record to pair with it. > > > > If you were looking at Josh Berkus' example at > > http://lwn.net/Articles/497069/ you might use it like this to generate > > data for testing and experimentation: > > > > INSERT INTO room_reservations > > SELECT 'F104', 'John', 'Another Talk', > > tstzrange((lag(a) OVER()), a, '[)') > > FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17 > > 12:00:00', '1 hour') > > AS a OFFSET 1; > > > > Thanks! > > > > -- > > Wolfe Whalen > > [email protected] > > > > > > -- > > Sent via pgsql-sql mailing list ([email protected]) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > > > -- > Sergey Konoplev > > a database and software architect > http://www.linkedin.com/in/grayhemp > > Jabber: [email protected] Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] ERROR: missing FROM-clause entry for table "new"
I'm trying to define a trigger function that looks for changes in table A (table the trigger for the function is on) and write a delta record into table B. So if a record has a value of 100 in table A, and it is updated to 50, the function should write 50 in table B. I can get the trigger to work with static SQL statements but for the actual code, I need to use dynamic SQL because I need to alter the insert statement to B depending on what column in table A is altered. I can get the correct SQL generated but when I execute the string inside the trigger function I get an error because it doesn't seem to be able to see the NEW table when it's run with EXECUTE. So, this works in the trigger function: Insert into A (col1,col2,colN) Select new.col1,new.co2new.colN) This doesn't: sql := 'Insert into A (col1,col2,colN) '; sql := sql || 'Select new.col1,new.co2new.colN)'; Execute sql; ERROR: missing FROM-clause entry for table "new" There is nothing wrong with the resulting code from sql because if I output the string and put it in as static SQL in my trigger it works. How do I build the string within the trigger and execute it with a reference to NEW? Thanks in advance for the help, James
Re: [SQL] ERROR: missing FROM-clause entry for table "new"
On Sep 13, 2012, at 20:40, James Sharrett wrote: > I'm trying to define a trigger function that looks for changes in table A > (table the trigger for the function is on) and write a delta record into > table B. So if a record has a value of 100 in table A, and it is updated to > 50, the function should write –50 in table B. I can get the trigger to work > with static SQL statements but for the actual code, I need to use dynamic SQL > because I need to alter the insert statement to B depending on what column in > table A is altered. I can get the correct SQL generated but when I execute > the string inside the trigger function I get an error because it doesn't seem > to be able to see the NEW table when it's run with EXECUTE. > > So, this works in the trigger function: > > Insert into A (col1,col2,…colN) > Select new.col1,new.co2…new.colN) > > This doesn't: > > sql := 'Insert into A (col1,col2,…colN) '; > sql := sql || 'Select new.col1,new.co2…new.colN)'; > Execute sql; > > ERROR: missing FROM-clause entry for table "new" > > There is nothing wrong with the resulting code from sql because if I output > the string and put it in as static SQL in my trigger it works. > > How do I build the string within the trigger and execute it with a reference > to NEW? > > Thanks in advance for the help, > James > Please read all of: http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN But especially 39.5.4 You want to make use of format and/or USING to pass in the values to a parameterized dynamic statement. Note I linked to 9.2 but any recent version should have the behavior, if different section numbers. In short the whole "NEW.name" is a variable and you need to build the statement the same way you would with any user-defined variable. David J.
