Re: [SQL] User defined types -- Social Security number...
Josh Berkus wrote: > Greg, > > > Anyone have a good pre-built user-defined type definition for creating / > > maintaining / manipulating a SSN ... where valid chars are in the range > > 000-00- through 999-99-. > > Actually, the range is more narrowly defined than that. I'm not sure of the > exact rules, but you will never see a leading 0 or a -00- in an SSN. Actually I know someone who's SSN starts as 003. Agreed on the DOMAIN suggestion. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Error Number in Function
Thanks for the clarification Tom. I have one more query. Is there a way to get the error number for the last Transact-SQL statement executed in a Function in postgresql? In MSSQL Stored Procedures we used @@ERROR variable to get the same. Is there an equivalent in postgresql functions? Regards, Sumita -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, February 23, 2004 8:59 AM To: Sumita Biswas Cc: [EMAIL PROTECTED] Subject: Re: Postgres DB "Sumita Biswas" <[EMAIL PROTECTED]> writes: > But when I type the command "psql" and don't specify a DB name, it > says > that: > psql: FATAL: Database "postgres" does not exist in the system catalog. Yes, because psql's default behavior is to try to connect to the DB with the same name as your user name. That doesn't mean that such a DB is guaranteed to exist, or even that anything is going to try to create it for you. It's just a default behavior that people have found handy. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] EXECUTing QUERY:
I had the following code in an MSSQL Stored Procedure: SET @QueryToPopulateTemp= "INSERT INTO #Tmp_Conference(ConferenceType, CallManagerId, ClusterId, DestConversationId, ConnectDate, FinalDestination)" +" SELECT 1, Global_CallId_CallManagerId, globalCallId_ClusterID, DestConversationID, StartDate = MIN (Connect_Date), Final_Destination " +" FROM Tbl_Billing_Data WHERE (connect_date BETWEEN '"+ cast(@ld_FromDate as NVARCHAR) +"' AND '"+ cast(@ld_ToDate as NVARCHAR) +"' OR disconnect_date BETWEEN '"+ cast(@ld_FromDate as NVARCHAR)+"' AND '"+cast(@ld_ToDate as NVARCHAR)+"') AND DestConversationID <> 0 AND Call_Type='" + @lv_Adhoc_Conf + "'" +" GROUP BY Global_CallId_CallManagerId, globalCallId_ClusterID, DestConversationID, Final_Destination order by startdate"; EXEC sp_executesql @QueryToPopulateTemp; - I have changed it to the following in the Postgres Function: QueryToPopulateTemp := ''INSERT INTO Tmp_Conference(ConferenceType, CallManagerId, ClusterId, DestConversationId, ConnectDate, FinalDestination)'' +'' SELECT 1, Global_CallId_CallManagerId, globalCallId_ClusterID, DestConversationID, StartDate = MIN (Connect_Date), Final_Destination '' +'' FROM Tbl_Billing_Data WHERE (connect_date BETWEEN + cast(ld_FromDate as NVARCHAR) + AND + cast(ld_ToDate as NVARCHAR) + OR disconnect_date BETWEEN + cast(ld_FromDate as NVARCHAR)+ AND +cast(ld_ToDate as NVARCHAR)+) AND DestConversationID <> 0 AND Call_Type= + lv_Adhoc_Conf + '' +'' GROUP BY Global_CallId_CallManagerId, globalCallId_ClusterID, DestConversationID, Final_Destination order by startdate''; PERFORM QueryToPopulateTemp; - Does this look fine? Regards, Sumita ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Postgres DB
The following Query string , when executed does not give an Error, even though the table Tbl_Billing_Data is not present. QueryToPopulateTemp := ''INSERT INTO Tmp_Conference(ConferenceType, CallManagerId, ClusterId, DestConversationId, ConnectDate, FinalDestination)''+'' SELECT 2, Global_CallId_CallManagerId, globalCallId_ClusterID, DestConversationID, StartDate = MIN (Connect_Date), Final_Destination ''+'' FROM Tbl_Billing_Data''; PERFORM QueryToPopulateTemp; I some how feel that the Query is not getting executed at all, otherwise its sure to give an error. So can you please comment what would have gone wrong? Regards, Sumita ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] DATEADD
I had the following code in my MSSQL Stored Procedure: --Adding 23Hrs 59Mins 59Secs to the ToDate(@ld_ToDate is DateTime datatype) SET @ld_ToDate = DATEADD(ss, 86399, @ld_ToDate); I have changed it to the following for Postgres Function: --Adding 23Hrs 59Mins 59Secs to the ToDate --Here $2 is an input variable(timestamp datatype) lv_ToDate := $2 + interval ''86399 seconds''; Does this make sense? Any pointers appreciated. Thanks, Sumita ---(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] Enterprice support in PostgreSQL
George A.J wrote: > Hi all, > > We are providing database solutions in postgreSQL... > Now using PostgreSQL 7.3. It is performing well. > But Now we have some enterprice level requirements. > One of Our requirement is to provide a distributed solution in PostgreSQL. > The questions are... > 1. Is it posible to provide a distributed solution in PostgreSQL. > 2. Does PostgreSQL Support distributed transactions. > 3. If not does it included in the next release. Or when will be the distributed > version of postgreSQL available. > 4. Is there a replication solution availbale for postgreSQL. > 5. Does postgreSQL support 2 phase commit and Distributed transaction standards. > 6. Is there a transaction manager(or co-ordinater) available for postgreSQL. > 7. Can we use MTS (Microsot Transaction Server) wtih postgreSQL. > 8. Does postgreSQL support Load balancing and all other enterprice features. > Can we expect these features in the next version.. We are working on all these fronts. We have replication solutions on gborg.postgresql.org, and some are working on 2-phase commit, perhaps for 7.5, dues in maybe 6 months. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
