convert oracle sql to postgresql

Tags: Oracle SQL Server---------Mathematical Functions1. Absolute valueS:select ABS ( -1) valueO:select ABS ( -1) value from dual2. Rounding (Large)S:select Ceiling ( -1.001) valueO:select ceil ( -1.001) value from dual3. Rounding (small)S:select Floor ( -1.001) valueO:select Floor ( -1.001) value from dual4. Rounding (interception)S:select cast ( -1.002 as int) valueO:select trunc ( -1.002) value from dual

andTruncate tableOperation BesidesDeleteCommand,OracleYou can also useTruncate tableCommand to delete table data. HoweverTruncate tableStatement andDeleteStatements are essentially different.DeleteStatement andInsert,UpdateStatementDML --The category of the data operation language. After the data is modified, you can roll back to ignore the data modification. WhileTruncate tableThe statement isDDL --Category of a Data Definition Language. data cannot be rolled back after it is deleted.

(ID int,name varchar (), Q1 int, Q2 int, Q3 int, Q4 int); INSERT into Fruit values (1, ' Apple ', 1000,2000,3300 INSERT into Fruit values (2, ' orange ', 3000,3000,3200,1500), insert into Fruit values (3, ' banana ', 2500,3500,2200,2500); INSERT into Fruit values (4, ' grapes ', 1500,2500,1200,3500); select * FROM Fruitcolumn to row querySelect ID, name, Jidu, Xiaoshou from Fruit unpivot (Xiaoshou-Jidu in (Q1, Q2, Q3, Q4))Note: Unpivot does not have an aggregate function, and the Xiaoshou and J

Copy Code code as follows: If you have multiple tables in the--GT;FROM clause, select the table with the least number of record bars as the underlying table --> parsing WHERE clause is a bottom-up filter condition must be in order -->oracle will convert ' * ' to column name -->delete will be able to store recoverable information in rollback segment and try Truncate -->commit will release:

One: Select Cardid, borrowdate from borrow where To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy ') = ' all ' and to_char ( To_date (borrowdate, ' yyyy-mm-dd '), ' mm ') >= ' "and To_char (to_date (borrowdate, ' yyyy-mm-dd '), ' mm ') Way two: Select Cardid, borrowdate from borrow where To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy ') = ' all ' --query and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') >= ' 2014-02 ' and to_char (to_date (Borrowdate, ' Yyyy-mm-dd '

Use the Hibernate framework to generate executable SQL statements for hql-Oracle DialectIntroduction: Why is this requirement derived from the company's project requirements? The company's project uses java's struts2 + spring2.5 + oracle middleware tuxedo. The database uses Oracle, but because it does not directly conn

[SQL statement classification] DQL-Data Query Language (SELECT) DML-data operation language (INSERTUPDATEDELETEMERGE) DDL-Data Definition Language (CREATEALTERDROPTRUNCATE) DCL-Data Control Language (GRANTREVOKE) TCL-transaction control statement (COMMITROLLBACKSAVEPOINT) [query the base [SQL statement classification] DQL-Data Query Language (SELECT) DML-data operation language (INSERT/UPDATE/DELETE/MERGE)

Comparison of common functions of SQL Server and Oracle --------- Mathematical functions1. Absolute ValueS: select abs (-1) valueO: select abs (-1) value from dual2. INTEGER (large)S: select ceiling (-1.001) valueO: select ceil (-1.001) value from dual3. Round (small)S: select floor (-1.001) valueO: select floor (-1.001) value from dual4. Round (truncation)S: select cast (-1.002 as int) valueO: select trunc

index. Such as:Select ID from t where substring (name,1,3) = ' abc '//Oracle The total is substr function.Select ID from t where DATEDIFF (day,createdate, ' 2005-11-30 ') =0//checked out indeed there is no DateDiff function.should read:Select ID from t where name like ' abc% 'Select ID from t where createdate>= ' 2005-11-30 ' and createdateOracle time should convert char to date such as: CreateDate >= to_d

target object of the delete action is at the record level, you do not need to specify the column name information. We can use SQL> delete from students where student_id> 10; 3.2 delete Besides SQL> rollback; Lower function:This function can change all uppercase letters to lowercase letters.SELECT lower ('abcde') FROM dual;Dual table is a virtual table. Upper function: converts the input string to upper

First, let's take a look at SQL server. Previously, we used the foreground to select a start time and an end time (in the smallest unit of day), and then used it as a condition for query, if we use "between starttime and endtime" as the condition, we will automatically convert "" to "00:00:00 ", therefore, the following query condition is "between '2014-06-16 'and '2014-06-17'". Only data on the 16th day ca

') = '2017-05-12'And AMPM = 'am '; After reading the following statement, we can see that it must be the error of yyck = v_ckh. When comparing values of numeric type and numeric type, Oracle will implicitly convert the numeric type to numeric type. Assume that the data type of the ID column is varchar2:Select * from t where id = 1; equivalent to select * from t where to_number (ID) = 1 In this case, it m

establish an index can. In this case, no matter how many rows the table has, in the number is indeterminate, is very fast. 2, corresponding to the second distribution of data in the IP column index, the effect is not good. Because the data distribution is uneven, may be a little fast, some slow 3, corresponding to the third data distribution, indexing in the IP column, the speed is certainly slow. Note: The ORDER BY name Desc Here is sorted after fetching the data. Instead of sorting the dat

PL/SQL Developer does not install a client to connect to a remote oracle database (for example, developeroracle)Labels ):Http://www.oracle.com/technetwork/cn/database/features/instant-client/index-092699-zhs.html2. Download and install PLSQL (around 16 Mb ):Http://www.onlinedown.net/soft/4902.htm3. Unzip the instantclient-basic-win32-11.2.0.1.0.zip to a new folder, such as D:/oracleclient/instantclient_11_2

SQL execution efficiency has always been a concern, how should it be improved? What better way, below share with you under the more good suggestions, interested friends can refer to, I hope to help you Copy code code as follows:If you have multiple tables in the-->FROM clause, select the table with the least number of record bars as the underlying table --> parsing WHERE clause is a bottom-up filter condition must be in order -->

Oracle SQL converts a varchar type data into multiple rows of Results records. Today, when I create a report, I want to use an SQL query to query the final results. However, I encountered some problems during implementation, one field I found is varchar type, and a combination of multiple IDs (for example, 1, 2, 3, and the result is implemented by a selete Statem

How to import data to oracle database using SQL * loader, loaderoracle When you need to import massive data into the database, the efficiency of using the graphical interface tool is very low. Here we will briefly introduce how to import the database using SQL * loader. First, observe the table structure. If the current user does not have a data table availab

Label:You can also use PL/SQL CreateDirectory "image" as 'e:\';--"image" to bring double quotes, many online without, I test error, and, ' E:\ ' is the server directory, non-local directory. User Authorization required: Grant create any directory to username; Declareb_file bfile; B_lob blob; begin Insert intoTB_SJ (SJBM,SJMC,RKSJ,XGSJ,GSBM,SJDATA,LBBM)Values(1,'Figure 2-1 C1SR sedimentary facies plan of ASA key area', Sysdate,sysdate,2, Em

In this article, the date obtained by getdate () is composed of two parts: today's date and then time: Select getdate () Use datename () to obtain the corresponding year, month, and day, and then connect them: Select datename (year, getdate () + '-' + datename (Month, getdate () + '-' + datename (day, getdate ()) In addition, datename () can also be used to obtain the hour, time, second, day of week, and week number, as shown below: Select datename (hour, getdate ()) Select datename (minute, get

Tags: use LCT method sum Sele rate logs ACL selectIn everyday SQL queries, we need to process the data that we want to query, and then query it in the pre-processed data. At this point we need to use the temporary table, pre-processing the data into the temporary table, and then in the temporary list according to the conditions we need to query.Let's say we have an income table: Incoming, the fields are: id,name,amt,cur we require that each person's A