Re: [SQL] Unique index and unique constraint

2013/7/27 Alvaro Herrera > > PostgreSQL implements unique constraints by way of unique indexes (and > it's likely that all RDBMSs do likewise). Also, the syntax to declare > unique indexes allows for more features than the unique constraints > syntax. For example, you can have a unique index tha

Re: [SQL] transactions and ecpg

Hey Wayne 2012/9/22 Wayne Cuddy > I'm looking at some code that a coworker ported from Informix to > Postgres 9.0. The Informix database did not have transaction logging > enabled thus no begins/aborts/commits/rollbacks where in the original > code. The way the code has been ported there are sti

Re: [SQL] Finding Max Value in a Row

2012/5/14 Tom Lane > Carlos Mennens writes: > > I'm not understanding why I'm not able to change this column type from > > char to integer? > > > forza=# ALTER TABLE customers > > ALTER COLUMN cust_id TYPE integer; > > ERROR: column "cust_id" cannot be cast to type integer > > Try "ALTER ... cu

Re: [SQL] array dimensions, pg_catalog.pg_attribute and the \d command

Hey Achilleas, 2012/2/3 Achilleas Mantzios > Hello, > just a question regarding multidimensional arrays. > When i create an array, its number of dimensions is recorded in > pg_catalog.pg_attribute (attndims), but they are not enforced upon > insertion, > nor is this presented by \d command. (i d

Re: [SQL]How to transform table rows into Colum?

Hey Eric, 2011/3/9 Eric Ndengang > Hi Guys, > I have the following table: > > Name Value rang salary > > name1 value1 12500 > name2 value2 22600 > name3 value 3 3300 > > and want to obtain the following result: > > name1 name2 name3

Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause?

Hey Gnanakumar, You can wrap you UPDATE query into SQL function returning TABLE, e.g: CREATE OR REPLACE FUNCTION public.update_mytable() RETURNS TABLE(email text, column1 text, column2 text, column3 text) LANGUAGE sql AS $function$ UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE

Re: [SQL] Retrieve the column values of a record without knowing the names

Hey, 2011/2/16 arthur_info > > Hello, > > I've got the following function and I want to access the fields values of > my > record by index. The problem is that my select is retrieving each record > line with all values and not each one of each row on my view... How can I > solve this problem? >

Re: [SQL] Get postgresql server ip address

2011/2/8 Gera Mel Handumon > Hello, > > Is it possible to get postgresql server ip address using sql command? > Oh yes, use inet_server_addr() function. Please note, that NULL is returned if connection established via socket. > > TIA, > > Geramel > > > ---

Re: [SQL] why does seq scan instead of index scan

Hey, To avoid creation of custom GiST-index you can use hstore, which supports GiST and GIN indexes. I propose you to add hstore column to the table and write trigger function which will generate hstore from array on insert or update. Or you can use hstore column instead of array. Please, see http

Re: [SQL] Correct usage of FOR UPDATE?

Hey Joe, You can wrap the given query in the PL/pgSQL function, catch the exception and return NULL if it occurs. Please see http://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING 2010/12/9 Joe Carr > Hello, > I have a simple table-based queue system, a

[SQL] Fwd: Extended query protocol and exact types matches.

Hey sql@, -- Forwarded message -- From: Dmitriy Igrishin Date: 2010/12/9 Subject: Extended query protocol and exact types matches. To: pgsql-gene...@postgresql.org Hey general@, To be assured and just for calmness. Problem: 1. CREATE TABLE test_tab (id integer, dat varchar

Re: [SQL] concatenate question

Hey Tony, Because there is no implicit conversion to text. 2010/12/8 Tony Capobianco > Ok, that worked. Why did I need to cast both as text though? > > plsql_dw=# select memberid::text||addeddate::text from > tmpsv_parent_master limit 5; > ?column? > -- >

Re: [SQL] PostgreSQL array, recursion and more

Hey Ferruccio, Also consider ltree contrib module as alternative. http://www.postgresql.org/docs/9.0/static/ltree.html ;-) 2010/11/17 Ferruccio Zamuner > MESH Data Tree: > > example: > Hallux;A01.378.610.250.300.792.380 > > where: > A01 is Body Regions > A01.378 is Extremities > A01.378.610 is

Re: [SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause

Ooops, sorry UPDATE foo SET x = y WHERE id = ANY(string_to_array(blah, ',')::integer[]); 2010/10/29 Dmitriy Igrishin > Hey Axel, > > How about this solution: > UPDATE foo SET x = y WHERE ANY(string_to_array(blah, ',')) = id; > ? > > 2010/10/25 Axel Rau

Re: [SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause

Hey Axel, How about this solution: UPDATE foo SET x = y WHERE ANY(string_to_array(blah, ',')) = id; ? 2010/10/25 Axel Rau > Good morning, > > I have a function argument blah of type text containing something like >33,44,55,66 > . Can I cast it in some way to use it in an IN clause as intege

Re: [SQL] Random sort with distinct

Hey Ozer, How about dynamic queries? 2010/10/2 Ozer, Pam > I have the following query > > > > Select Distinct VehicleMake, VehicleModel > > From VehicleYearMakeModelTrim > > Order by random() > > Limit 10; > > > > I don’t want to bring back the random number I just want the sort order to > be

Re: [SQL] Question about PQexecParams

Hey Kenneth, There are three benefits: > > - reduces the CPU overhead in both the client and the DB server > for converting to/from ASCII numbers > You solution based on PL/pgSQL function. I am doubt that execution of PL/pgSQL parser (loadable module, which aren't built into the PostgreSQL server

Re: [SQL] Question about PQexecParams

Hey Kenneth, Thank you for solution. But sorry, personally, I don't clearly understand the benefits of this code compared with using simple array literals or even array constructors... Conversion "overheads" from text? Doubtfully... -- // Dmitriy.

Re: [SQL] Jagged/ragged arrays

Hey Craig, Florian Ahh, this. Why not just yes NULLs as values of array? 2010/9/21 Craig Ringer > On 21/09/2010 3:42 AM, Dmitriy Igrishin wrote: > >> Hey Florian, >> >> What do you mean by "ragged" arrays? >> > > At a guess: > > cr

Re: [SQL] Jagged/ragged arrays

Hey Florian, What do you mean by "ragged" arrays? -- Regards, Dmitriy

Re: [SQL] Question about PQexecParams

Hey Steve, 2010/9/11 Steve > Hello list, > > I would like to call a function from my C application by using libpq and > PQexecParams. My problem is that I don't know how to specify that I want to > send an array to the function. > > Assume the function is called lookup_data and takes the followi

Re: [SQL] Boolean output representation.

Hey Pavel, Thanks for you answer ! And many thanks to developers because my task a bit simplified because of that :) Regards, Dmitriy 2010/8/26 Pavel Stehule > Hello > > 2010/8/26 Dmitriy Igrishin : > > Hey all, > > > > As is known, there are many valid literal va

[SQL] Boolean output representation.

Hey all, As is known, there are many valid literal values for the "true" and "false" state of boolean data type. True whether that output of boolean type shows only using the letters 't' and 'f' or its possible to change the output representation, e.g., to "true" or "false" ("1", "0")? Regards, D

[SQL] libpq

Hey all, Is it guaranteed that functions, which accept PGconn* properly works if PGconn* is NULL (or just 0) and returns in such cases the same values as for not-NULL PGconn* ? If so, I think it should be documented ?! Regards, Dmitriy

Re: [SQL] how to escape _ in select

Hey James, Because in future releases the default value of the "standard_conforming_strings" parameter will change to "on" for improved standards compliance. You should use string constants with C-Style escapes. Please, see http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNT

Re: [SQL] howto delete using a join ?

Hey Andreas, You may want to use the USING clause: DELETE FROM events USING event_types WHERE events.event_type_fk = event_types.event_type.id AND relevance_level IN ( 1, 3, 5, 7) AND ; Regards, Dmitriy

[SQL] How oids of the base data types often change?

Hey all, For example, when using libpq(-xx), it is possible to get the oid of the type of any column. Is there a guarantee that oids of base types (void, integer, ... ) will not change in future releases of Postgres? Regards, Dmitriy

[SQL] Question about domains.

Hey all, Is there a way to add constraint to the domain that used by a composite type that used by a table? E.g.: CREATE DOMAIN superid AS integer; CREATE TYPE idtype AS ( id superid ); CREATE TABLE mytab (id idtype NOT NULL); ALTER DOMAIN superid ADD CONSTRAINT superid_check CHECK (VALUE >

[SQL] Domains based on composite types.

Hey all, Is there are way to create domain based on a composite type? Regards, Dmitriy

[SQL] Emacs sql-postgres (please, sorry for question not about PostgreSQL).

nd loads fine when I load it from file directly from psql. I think, the problem with Emacs buffers... Please, help! And please sorry, for question not about PostgreSQL... Regards, Dmitriy Igrishin

[SQL] SQL code beautifier for PostgreSQL.

Hey all, Is there any good SQL code beautifier for PostgreSQL to use with Emacs or other text editors? Unfortunately, pg_dump(1) does not format views and rules... It would be nice if we could format unreadable views and rules definitions in favorite editor... Regards, Dmitriy Igrishin

[SQL] DROP RULE ... CASCADE

Hey all, What objects may depend on the rule (and which of them will removed via cascade rule deletion)? Regards, Dmitriy Igrishin

Re: [SQL] Selecting names of indexes that are not dependent on constraints.

Of course, I meant the indexes of which constraints do not depend. :) 2010/3/11 Dmitriy Igrishin > Hey all, > > How can I select names of indexes of table (or even whole schema) that are > not dependent on constraints? > > Unfortunately there is no way to select only "in

[SQL] Selecting names of indexes that are not dependent on constraints.

Hey all, How can I select names of indexes of table (or even whole schema) that are not dependent on constraints? Unfortunately there is no way to select only "independent" indexes from pg_indexes view. How can I do it? Regards, Dmitriy Igrishin

Re: [SQL] create user xxx with md5 string?

Sorry for my prev post. I mean md5(password || username). 'test10' is a username on which i checked. :) Sorry! 2009/12/3 Dmitriy Igrishin > Hello all. > Tom right. Encrypted MD5 passwords computed as md5(password || test10). I > checked. > > Regards, Dmitriy Igrishi

Re: [SQL] create user xxx with md5 string?

Hello all. Tom right. Encrypted MD5 passwords computed as md5(password || test10). I checked. Regards, Dmitriy Igrishin. 2009/12/3 Tom Lane > Gerardo Herzig writes: > > Hi all. Im triyng, with no success, to create a user, and set the > > password with a md5 string. > > T

Re: [SQL] How to get the previous date?

Hello. current_date - 1 is the solution. 2009/10/14 Shruthi A > Hi, > > I'm using Enterprise DB PostgresPlus version 8.3. > Is there is a simple function or command like previous_date(mydate) which > when passed a particular date, simply returns the previous date? I tried > > mydate - 1 > > but

Re: [SQL] ERROR: null value in column "id" violates not-null constraint

ay require manual editing of the dump file to remove syntax not understood by the older server." Regards, Dmitry Igrishin 2009/10/11 Robert Paulsen > On Sunday 11 October 2009 3:32 am, Dmitriy Igrishin wrote: > > Hello. > > Note, that you may use SERIAL data type and Postgre

Re: [SQL] ERROR: null value in column "id" violates not-null constraint

Hello. Note, that you may use SERIAL data type and PostgreSQL will implicitly create sequence for you column, for example, CREATE table test ( id SERIAL NOT NULL PRIMARY KEY, -- PostgreSQL will implicitly create 'test_id_seq' dat text ); Regards, Dmitiy Igrishin 2009/10/11

Re: [SQL] select result into string's array

Hello. You should use an array constructor: DECLARE m_array text[]; [..] BEGIN FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP [..] END LOOP; Regards, Igrishin Dmitriy. 2009/10/9 Alberto Asuero Arroyo > Hi, > > I need to store the result of select into an array of