Re: [SQL] Question about domains.
On 08/07/10 2:27 PM, Dmitriy Igrishin wrote: 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.: Currently in PG, adding constraint on Domain, which is already in use is not supported. 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 > 0); ALTER DOMAIN superid DROP CONSTRAINT superid_check; produces the following output: dmitigr=> CREATE DOMAIN Time: 23,809 ms dmitigr=> CREATE TYPE Time: 44,875 ms dmitigr=> CREATE TABLE Time: 134,101 ms dmitigr=> ERROR: cannot alter type "superid" because column "mytab"."id" uses it dmitigr=> ALTER DOMAIN Time: 0,270 ms As you can see, adding constraint to the domain produces an error, while dropping constraint is possible! Any comments? If you want, you can try following: CREATE DOMAIN superid1 AS integer check(value > 0); create type idtype1 as (id superid1); create or replace function idtype2idtype1(idtype) returns idtype1 as $$ select row($1.id)::idtype1; $$ language sql; create cast (idtype as idtype1) with function idtype2int(idtype) as implicit; Then execute the alter table command to convert the data type: alter table mytab alter column id type idtype1; -- Thanks& Regards, Vibhor Kumar. EnterpriseDB Corporation The Enterprise Postgres Company -- 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
On Sep 24, 2010, at 6:02 PM, Nicholas I wrote: > > pg_dump -Dt --insert table dbname > table.sql; > > i am not able to get the output. is this correct ? Try following: pg_dump --insert -t Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation -- 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] TABLE PARTITION
On Feb 1, 2011, at 10:31 PM, NEVIN ALEX wrote: > Hi, > I am Nevin Alex and I am using postgresql database for a year . But I > have’nt used table partitions: Please help me to do it in a better way. I got > it from the documentation that it is an inheritance capability and the > Trigger working but for dynamic data how can I implement that. > Please elaborate more about the data which you are talking about. > Thanks in advance -- 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] Compare two Data bases Structure
On Feb 24, 2011, at 5:33 AM, manuel antonio ochoa wrote: > How Can I do to compare two structures of data bases ? > > DBA != DBB I need wich functions and wich tables are not equals > > thnks You can try with apgdiff. http://apgdiff.startnet.biz/ Thanks & Regards, Vibhor -- 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] Problem with serial counters
On Feb 25, 2011, at 2:39 AM, Jacques Lebrun wrote: > What can I do to force PostGres to update the internat serial counter when I > do an insert with a specified value for the serial? > After inserting the all the Data, use ALTER SEQUENCE Command: http://www.postgresql.org/docs/8.4/static/sql-altersequence.html Thanks & Regards, Vibhor -- 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] what's wrong in this procedure?
On Feb 25, 2011, at 11:16 PM, Camaleon wrote:
> This error is returned Erro de SQL:
>
> ERROR: column "Aguardando Pagto" does not exist at character 352 >>>
>
>
> create or replace function get_historico() RETURNS SETOF
> twiste.type_cur__historico AS '
>
> SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS
> transacoes
> FROM ofertas o
> JOIN transacao t ON o.ofertas_id = t.ofertas_id
> JOIN municipio m ON o.municipio_id = m.municipio_id
> WHERE o.data_fim <= now() AND t.status IN("Aguardando Pagto", "Em análise",
> "Aprovado", "Completo")
> GROUP BY o.data_fim;
> '
> language 'sql';
try Following:
create or replace function get_historico() RETURNS SETOF
twiste.type_cur__historico AS $$
SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS
transacoes
FROM ofertas o
JOIN transacao t ON o.ofertas_id = t.ofertas_id
JOIN municipio m ON o.municipio_id = m.municipio_id
WHERE o.data_fim <= now() AND t.status IN('Aguardando Pagto', 'Em análise',
'Aprovado', 'Completo')
GROUP BY o.data_fim;
$$
language 'sql';
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
vibhor.ku...@enterprisedb.com
Blog:http://vibhork.blogspot.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] converting big int to date
On Mar 24, 2011, at 2:42 AM, Sree wrote: > How can i convert bigint to date format. > > bigint=6169625280 Please explain what;s this value shows. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.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] How to realize ROW_NUMBER() in 8.3?
On Apr 20, 2011, at 9:15 PM, Emi Lu wrote: > ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to get > row_number > select row_number(), col1, col2... > FROM tableName Following is a link of deepsz which has a way of implementation of rownum. http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/ Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.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] new user on mac
On Oct 19, 2011, at 7:38 AM, Adam Cornett wrote: > > On Tue, Oct 18, 2011 at 5:47 PM, Scott Swank 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? > > Thank you, > Scott > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > > psql + editor is the basic tool set, certainly the most flexible. > Also be sure to check out pgadmin: http://www.pgadmin.org/download/macosx.php +1 for pgAdmin3. If you have already used Toad, u would like to check it. Thanks & Regards, Vibhor Kumar Blogs:http://vibhork.blogspot.com http://vibhorkumar.wordpress.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
