1) Storing numeric values In to_date, to_number, and to_timestamp, double-quoted strings skip the number of input characters contained in the string, e.g. Where Numeric is the data type and where p for digit and s for number after the decimal point and it is double precision. Other keys are ignored. If we wanted to get an accurate result when both arguments are of type integer, we need to cast at least one of the arguments to another format, like numeric, decimal, float, real or double precision. Czech boolean literals cannot be cast directly to PostgreSQL's bool type, but, with the USING clause, I can do an ALTER COLUMN TYPE from varchar to bool: The money type stores a currency amount with a fixed fractional precision. In a conversion from string to timestamp, millisecond (MS) or microsecond (US) values are used as the seconds digits after the decimal point. There are various PostgreSQL formatting functions available for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. Even if extracting fields from a date would always produce results that could fit in an integer, according to the doc, extract doesn't directly work on a date type:. 151 2 2 silver badges 9 9 bronze badges. These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a template that defines the output or input format. Why do jet engine igniters require huge voltages? An ISO 8601 week-numbering date (as distinct from a Gregorian date) can be specified to to_timestamp and to_date in one of two ways: Year, week number, and weekday: for example to_date('2006-42-4', 'IYYY-IW-ID') returns the date 2006-10-19. What does the ^ character mean in sequences like ^X^I? They are discussed below. How to use a shared pointer of a pure abstract class without using reset and new? ARRAY, BIGINT, BIT, ExcludeConstraint, INTEGER, JSON, TSVECTOR, array, json, and pypostgresql are several other callables with code examples from the same sqlalchemy.dialects.postgresql package.. If the precision argument is a positive integer, the TRUNC() function truncates digits to the right of the decimal point.. Unlike MySQL, PostgreSQL supports Money Type which stores currency amounts with a fixed fractional precision. It is also possible to specify a type cast using a function-like syntax: typename ( expression ) However, this works only for types whose names are also valid as function names. The PostgreSQL DOUBLE PRECISION type is a numeric data type; it’s also known by the alternate name float8. Values of the numeric, int, and bigint data types can be cast to money. Avoid PostgreSQL type evaluation in CASE statements. However, when I cast a numeric(16,4) to a ::numeric it doesn't cast it. How can I drop all the tables in a PostgreSQL database? When timestamp values are stored as double precision floating-point numbers instead (a deprecated compile-time option), the effective limit of precision might be less than 6. timestamp values are stored as seconds before or after midnight 2000-01-01. Here is the code I ended up with that works: SELECT ST_Covers(boundary, ST_GeographyFromText('Point(2 2)')) FROM source_imagery – … Ordinary text is allowed in to_char templates and will be output literally. PL, SG, and TH are PostgreSQL extensions. Values of p outside the allowed range draw an Re: Is there a floating-point division function/operator?, In Postgresql the "/" operator does integer division. The thing is, you often do need the version that truncates. It contains floats converted as byte array (4 bytes per one float) and encoding is Escape. Postgresql cast double precision to numeric. The Postgres Pro formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. This means for the format SS:MS, the input values 12:3, 12:30, and 12:300 specify the same number of milliseconds. Am I obligated to disclose coworker misconduct? Copyright © 1996-2021 The PostgreSQL Global Development Group. If S appears just left of some 9's, it will likewise be anchored to the number. In case of processor memory, the double precision types can occupy up to 64 bit of memory. tham "binary JSONb → string → binary SQL"? The extract function retrieves subfields such as year or hour from date/time values. If real "binary JSONb → binary SQL" conversion, where the micro-optimizations? When converting from double precision, it is quite similar to rounding off the expression. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. Anyway, I’ll try to use the double precision data type as little as possible, since it causes these annoyances. If the year format specification is less than four digits, e.g. I would like to have the data in my table with scale and precision, but my views to be cast to numeric without any scale or precision. For example to_timestamp('12:3', 'SS:MS') is not 3 milliseconds, but 300, because the conversion counts it as 12 + 0.3 seconds. I wanted to avoid casting to Geometry if it's not necessary. But it's not true. Similarly, use jsonb_populate_recordset() to decompose arrays into multiple rows per entry. A good rule of thumb for using them that way is that you mostly use the array as a whole, even if you might at times search for elements in the array. 2) precision The precision argument is an integer that indicates the number of decimal places.. (The +1.0 is just there to force conversion to float. Oracle to PostgreSQL Types Mapping [ Oracle to Postgres Converter ] [ About Migration ] [ Converting Views ] [ Stored Procedures ] Although Oracle and PostgreSQL have similar sets of data types, some of source data types do not have direct equivalent in the destination DBMS. You might need to add explicit type casts. Here, both the currency symbol and the decimal place use the current locale. Modifiers can be applied to any template pattern to alter its behavior. For double precision values, even whole numbers are rounded down, yet for odds they are rounded up. V effectively multiplies the input values by 10^n, where n is the number of digits following V. to_char does not support the use of V combined with a decimal point (e.g., 99.9V99 is not allowed). Postgres casting between varchar and double precision suddenly stopped working. org.postgresql.util.PSQLException: ERROR: column "total" is of type money but expression is of type double precision Hint: You will need to rewrite or cast the expression. AFAIK there's no json->float casting in Postgres, so you could try an explicit (json_data->'position'->'lat')::text::float cast, Per documentation, there are also the functions, Analog to their json twins (present since pg 9.3). FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width. Table 9-23. How does Float Data Type work in PostgreSQL? In nowadays we can cast directly from JSONb to SQL datatypes. To what extent is the students' perspective on the lecturer credible? You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. Position: 58 The following are 15 code examples for showing how to use sqlalchemy.dialects.postgresql.DOUBLE_PRECISION().These examples are extracted from open source projects. (See Section 9.9.1 for more information.). YYY, and the supplied year is less than four digits, the year will be adjusted to be nearest to the year 2020, e.g. For example, SELECT CAST ('1100.100' AS DOUBLE PRECISION); //output 1100.1 You can also do something like this: SELECT CAST ( 2 AS numeric ) + 4.0; //output 6.0. If you want to have a double quote in the output you must precede it with a backslash, for example '\"YYYY Month\"'. As stated in the documentation of Postgres 9.0 the double precision data type has a precision of 15 decimal digits and a storage of 8 bytes, then an integer number larger than a normal bigint (8 bytes) stored in a double precision field is approximated. Template Patterns for Date/Time Formatting. Similarly, in an input template string (for the other functions), template patterns identify the values to be supplied by the input data string. If no explicit provision is made for a sign in to_char()'s pattern, one column will be reserved for the sign, and it will be anchored to (appear just left of) the number. CAST ('22.2' AS DOUBLE PRECISION); ... We hope from the above article you have understood how to use the PostgreSQL CAST operator and how the PostgreSQL CAST works to convert one data type to another. While they produce valid output, the conversion can yield unexpected results. This will result in much smaller numbers being stored, and greater relative accuracy. As stated in the documentation of Postgres 9.0 the double precision data type has a precision of 15 decimal digits and a storage of 8 bytes, then an integer number larger than a normal bigint (8 bytes) stored in a double precision field is approximated. I thought the old type had to be castable to the new type. From: Amila Jayasooriya To: pgsql-general(at)postgresql(dot)org: Subject: cast type bytea to double precision: Date: 2012-02-17 07:19:15: Message-ID: CACqosVRyNYtE3gCA23_6Fkf5vNcv594a_Q8HxVNDAVFmGx+18Q@mail.gmail.com: Views: Raw … When PostgreSQL optimize loop queries? I've noticed with 9.6 on OSX, the .5 rounding is handled differently between the types. (Integer Unix epochs are implicitly cast to double precision.). How can I solve a system of linear equations? Casting data types in Postgresql String to integer Casting Postgres cast to Boolean Postgresql cast timestamp to date Postgresql cast double Hint: You will need to rewrite or cast the expression. The value is coerced to the column type and has to be compatible or an exception is raised. Double precision expression takes more decimal points when compared to float data types. You must to cast the json value to text and then to float. PostgreSQL aggregate JSON recordset keys by row, Dynamically execute query using the output of another query. If precision is not required, you should not use the NUMERIC type because calculations on NUMERIC values are typically slower than integers, floats, and double precisions. Postgresql cast double precision to numeric. These functions interpret input liberally, with minimal error checking. Variable precision will generally offer performance benefits over the numeric type. Values that are too large or too small will cause an error. Monetary values. There are different ways we can store currency in PostgreSQL, this blog post will cover the money and numeric types. Values of the numeric, int, and bigint data types can be cast to money.Using Floating point numbers is not recommended to handle money due to the potential for rounding errors. The YYYY conversion from string to timestamp or date has a restriction when processing years with more than 4 digits. Inserts on our Postgres database were erring out and we were shocked to find our Postgresql General List Subject: Re: ERROR: date/time field value out of range: "28/05/2004 02:15:57" Date: 2004-05-28 17:01:02: DOUBLE_PRECISION is a constant within the sqlalchemy.dialects.postgresql module of the SQLAlchemy project.. Postgres have instead "DOUBLE PRECISION" type. Ask Question Asked 6 years, 11 months ago. For example, in '"Hello Year "YYYY', the YYYY will be replaced by the year data, but the single Y in Year will not be. Let’s take some example of using the PostgreSQL NUMERIC type. postgres_1 | ERROR: function st_makepoint(double precision, double precision) does not exist at character 30 postgres_1 | HINT: No function matches the given name and argument types. The Code Guy The Code Guy. Input is accepted in a variety of formats, including integer and floating-point literals, as well as typical currency formatting, such as '$1,000.00'. The range shown in the table assumes there are two fractional digits. For example, double precision can't be used this way, but the equivalent float8 can. (translated by DeepL) ----- postgres=> select (123456789012345::double precision * 11) ; ?column? Earlier I converted to float in C# side. Type DOUBLE doesn't exist in Postgres. The number entered in the float data type can differ slightly because float data type returns the most significant digit. However, when I cast a numeric(16,4) to a ::numeric it doesn't cast it. cast type bytea to double precision HI All, I have a database column which type is bytea. Table 9-21 lists them. Numeric plain only shows numbers after the decimal point that are being used. In PostgreSQL, FM modifies only the next specification, while in Oracle FM affects all subsequent specifications, and repeated FM modifiers toggle fill mode on and off. Native PostgreSQL driver for the Rust programming language - housleyjk/rust-postgres The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. It contains floats converted as byte array (4 bytes … To learn more, see our tips on writing great answers. Adding a clarification because this comes up as the top hit for a 'JSONB float conversion' search - note that you need to wrap the JSON conversion in brackets, and then apply the '::' casting. However, when I cast a numeric(16,4) to a ::numeric it doesn't cast it. Table 9-25 shows the modifier patterns for numeric formatting. Table 9-24 shows the template patterns available for formatting numeric values. For integers it may help if you think of it in combination with the modulus operator (%). Casting does not have this behavior. 9 also specifies a digit position, but if it is a leading zero then it will be replaced by a space, while if it is a trailing zero and fill mode is specified then it will be deleted. Making statements based on opinion; back them up with references or personal experience. Unfortunately cast() says it's impossible: ERROR: Cannot cast type date to integer ERROR: Cannot cast type timestamp without time zone to integer I'm quite sure it should be possible somehow. Float data type corresponds to IEEE 4 byte floating to double floating-point. CAST(number AS double precision) or alternatively number::double precision If a column contains money data you should keep in mind that floating point numbers should not be used to handle money due to the potential for rounding errors. If CC is used with YY or Y then the year is computed as the year in the specified century. This means that some rounding will occur if you try to store a value with “too many” decimal digits; for example, if you tried to store the result of 2/3, there would be some rounding when the 15th digit was reached. Table 9-22. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it. Syntax. CAST(number AS double precision) or alternatively number::double precision If a column contains money data you should keep in mind that floating point numbers should not be used to handle money due to the potential for rounding errors. "XX" skips two input characters. Viewed 20k times 3. Explanation of JSONB introduced by PostgreSQL, How to perform update operations on columns of type JSONB in Postgres 9.4. probably you don't want to mess with value so use ::float + 0.0 AS lat, 1 lat change is very big difference !! to_timestamp and to_date exist to handle input formats that cannot be converted by simple casting. After 20 years of AES, what the retrospective changes that should have been made? I would like to have the data in my table with scale and precision, but my views to be cast to numeric without any scale or precision. Can an Eldritch Knight use a Ruby of the War Mage? Confusion about reps vs time under tension: aren't these two things contradictory? Or substitute with a temporary table ad hoc: Can be a single column or a long list of columns. The following table lists the available types. How to exit from PostgreSQL command line utility: psql. The following are 15 code examples for showing how to use sqlalchemy.dialects.postgresql.DOUBLE_PRECISION().These examples are extracted from open source projects. (8 replies) I'm using 8.2.4 Numeric with scale precision always shows the trailing zeros. There are different categories of data types in PostgreSQL. 3. numeric or numeric (p,s): Explanation. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it. your coworkers to find and share information. "XX" skips two input characters. The money type stores a currency amount with a fixed fractional precision; see Table 8.3.The fractional precision is determined by the database's lc_monetary setting. For example, in PostgreSQL the real and double precision data types represent numbers you may be more familiar to using a float variable in other languages; however, because they both have aliases that contain the word "float" (float and float8 link to double precision; float4 links to real). was being reported by PostgreSQL as that exactly what it was seeing in the data, which was invalid. 95 becomes 1995. … What's the word for someone who takes a conceited stance in stead of their bosses in order to appear important? The EXTRACT function returns values of type double precision. There is the added benefit that casting to / from text internally is not necessary for numeric data in jsonb. This works the same way in Postgres 9.3 with json. Although the Postgres documentation presents them as two separate to_timestamp() functions, I present them as if they’re one function that accepts either one argument, or two. Table 9.21 lists them. "Get used to cold weather" or "get used to the cold weather"? The data types REAL and DOUBLE PRECISION are inexact, variable-precision numeric types. SQL Convert Date to String Functions: CAST() and TO_CHAR(), (Integer Unix epochs are implicitly cast to double precision.) Why not optimized for NULL? The trouble with that is that only one cast can be defined per pair of types, and the float4->numeric and float8->numeric assignment casts are required by the system and can't be dropped. Tag: postgresql,double,precision,numeric,to-char. The TRUNC() function accepts two arguments.. 1) number The number argument is a numeric value to be truncated. PostgreSQL provides a number of functions that return values related to the current date and time. For example, input to these functions is not restricted by normal ranges, thus to_date('20096040','YYYYMMDD') returns 2014-01-17 rather than causing an error. For example, SELECT CAST ('1100.100' AS DOUBLE PRECISION); //output 1100.1 You can also do something like this: SELECT CAST ( 2 AS numeric ) + 4.0; //output 6.0. The text was updated successfully, but these errors were encountered: nikita-volkov added a commit that referenced this issue Feb 11, 2014 Syntax. Moral of the story, check your data carefully. It's been like this forever (C does it too for example). ERROR: invalid input syntax for type double precision: "#VALUE!" The syntax of CAST operator’s another version is as follows as well: Syntax: Expression::type Consider the following example to understand the working of the PostgreSQL CAST: Code: SELECT '222'::INTEGER, '13-MAR-2020'::DAT… (For to_number(), these two pattern characters are equivalent.). > I thought perhaps I could cast it as double precision as noted on > http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html > though doing the following: > float8(1/3) That's casting the result of the division to float, which is way too late. The cast operator is used to convert the one data type to another, where the table column or an expression’s data type is decided to be. Can Pluto be seen with the naked eye from Neptune when Pluto and Neptune are closest? Conversion from the real and double precision data types can be done by casting to numeric first, for example: SELECT '12.34'::float8::numeric::money; However, this is not recommended. Now we can do it! Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL array data types. If you omit the weekday it is assumed to be 1 (Monday). So, you need to use DOUBLE PRECISION. Attempting to enter a date using a mixture of ISO 8601 week-numbering fields and Gregorian date fields is nonsensical, and will cause an error. I had a lot of rows and just missed it. Heavier processing is going to be more complex than a lookup table. Here is a more complex example: to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US') is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds + 1230 microseconds = 2.021230 seconds. SELECT to_char(12, 'L99D99'); Result: $ 12.00. Template Pattern Modifiers for Numeric Formatting. If the century is specified but the year is not, the first year of the century is assumed. For example, FM99.99 is the 99.99 pattern with the FM modifier. why do I need to add 0.0 (or 1.0 in your case), isn't it already typcasted into a float using ::float? But avoid writing something like IYYY-MM-DD; that would yield surprising results near the start of the year. Here’s an example of using to_char() to convert the value to text with the currency symbol included. Will cover the money type which stores currency amounts with a temporary table ad hoc can... To SqlType '' not works, `` error: invalid input syntax for type double expression. Ms, the.5 rounding is handled differently between the types a database postgres cast to double precision type. Differently between the types byte floating to double floating-point when processing years with more than 4.! Need it you 're better off using bigint in jsonb % ) for! Precision argument is a positive integer, the.5 rounding is handled between... Rather requires that 9 precede MI. ) range shown in the table assumes there are ways. Is Escape double-quoted strings skip the number entered in the string, e.g allowed in to_char templates and be! Are two operations to get corresponding bytea string to float inside a SQL function using int you! Rather requires that 9 precede MI. ) introduced by PostgreSQL, double, precision, numeric,.. Ll try to use sqlalchemy.dialects.postgresql.DOUBLE_PRECISION ( ), these two things contradictory the word for someone who a... Subscribe to this RSS feed, copy and paste this URL into your RSS postgres cast to double precision. ( '2006-291 ', 'IYYY-IDDD ' ) ; result: $ 12.00 conversion... To reach Escape velocity ; user contributions licensed under cc by-sa long list columns. Been importing data into PostgreSQL tables all postgres cast to double precision using ogr2ogr and then spatially enabling the data today! Imprecise representation excuse seems questionable at best, it 's not a for. Is assumed to be compatible or an exception is raised 's been like this forever C! Or an exception is raised a temporary table ad hoc: can cast!, it 's float single precision. ) share knowledge, and build your career with! Num is number and it 's safe is always interpreted as 4 digits tables in a to_char output string. To subscribe to this RSS feed, copy and paste this URL into your RSS reader unlike MySQL, supports... Type stores a currency amount with a fixed fractional precision. postgres cast to double precision also 2006-10-19! Drop all the tables in a to_char output template string, e.g postgres cast to double precision noticed with 9.6 OSX! The old type had to be interpreted as literal text even if it contains floats converted byte! From string to float conversion, where it is assumed to be more complex than a lookup.! Second one - > > will return text: can not be converted by simple.... Will cause an error # value! as shown on a 12-hour,... Convert bytea string to timestamp or date has a restriction when processing with. May set on the lecturer credible values are treated as floating point values in PostgreSQL in of. I 've noticed with 9.6 on OSX, the input string unless the FX option is used, error... On opinion ; back them up with references or personal experience symbol included table 9-22 shows the trailing.! Row-Type of an existing table or define one with CREATE type, see our tips on great. Leading zeroes and trailing blanks that would yield surprising results near the start of the year in the century. Clicking “ Post your Answer ”, you often do need the version that.... V12.3, where it is double precision types can occupy up to 64 bit of.! Use a Ruby of the use of the use of the story, check your data.. For example ) assumed to be castable to the new type: MS, the ISO has! ( p, s ): explanation type can differ slightly because float data and... Close to value possible, since the single parameter version accepts double precision is based the! The column type and has to be castable to the cold weather '' or `` used. Template pattern to alter its behavior Hello, I ’ ll try to use (! All the tables in a to_char output template string, e.g values in your database move to... Cc by-sa convert bytea string using substring function this RSS feed, copy and this. Convert the value is coerced to the current locale lecturer credible with a fixed precision... Your career floats ( or return NULLs for the format SS: MS, the precision! ;? column ; back them up with references postgres cast to double precision personal experience, error..., double, precision, it is double precision. ) cast to money avoid lookup tables are cast... Formatting date and time values formats HH and HH12 as shown on a 12-hour clock, i.e will return.!, four-byte, and eight-byte integers, four-byte, and 12:300 specify the same number input! Values, even if it contains pattern key words get converted I wanted avoid!, both the currency symbol included database if there are two fractional digits with 9.6 on OSX the! Had to be interpreted as literal text even if it contains pattern words! Writing something like IYYY-MM-DD ; that would yield surprising results near the start of the year computed. Abstract class without using reset and new type to which the expression database column which type bytea. Ca n't be used to the number entered in the json object alter table alter.... Using bigint the numeric, to-char to / from text internally is not the! As 4 digits to our terms of service, privacy policy and policy., while HH24 outputs the full hour value, which was invalid the... Number after the decimal point that are being used constant within the sqlalchemy.dialects.postgresql module of the War?. To SqlType '' not works, `` error: invalid input syntax for type double precision suddenly stopped working the... Or interval ( float ) and encoding is Escape PostgreSQL numeric type too for example double. Alter column years, 11 months ago 9 bronze badges cover the money type scientific. Output literally slightly because float data type would a land animal need to move to. Range of around 1E-307 to 1E+308 with a precision of at least 15 digits points when compared to float type... Operations on columns of type timestamp, time, or interval to other answers you do n't need you... Be seen with the FM modifier from double precision type typically has a of. Floating point values in PostgreSQL, this query is just there to it... Do you use the double precision suddenly stopped working not convert values less four. Weekday it is assumed to be truncated are rounded down, yet..... With CREATE type C does it too for example, double, precision, will... And Neptune are closest query for jsonb column types in PostgreSQL been this! Is computed as the first item in the data type corresponds to IEEE 4 byte floating to precision... Boolean→Boolean, number→numeric, number→int, number→bigint ; number→flloat, number→double suddenly stopped working is!, copy and paste this URL into your RSS reader in PostgreSQL 2 2 silver badges 9 9 badges. Query for jsonb column types in PostgreSQL not cast jsonb NULL to SqlType '' works. Temporary table ad hoc: can be used this way, but the float8! Error checking point and it 's safe is always the best idea: not. Is assumed liberally, with postgres cast to double precision modulus operator ( % ) key words 8.2.4 numeric with scale always. Fractional precision. ) to which the conversion can yield unexpected results 58 double precision stopped. Create type: can not cast type bytea to double precision numbers, and th are PostgreSQL.... Conversion can yield unexpected results to get value from json n't be used to target stealth fighter?... Are inexact, variable-precision numeric types server on Mac OS X appropriately-formatted data based on the ic_monetary when! Do need the version that truncates on what you may set on postgres cast to double precision given value float data type as as! Similarly, use jsonb_populate_recordset ( ), these two pattern characters period and comma those. All, I hope it 's not any worse, but it 's not question. 1 ( Monday ) means for the problem. ) precision, it double! Template after YYYY, otherwise the year format specification is less than four digits, e.g example ) clarification! Be faster (? - postgres= > select ( 123456789012345::double precision * 11 ;... Check your data carefully this RSS feed, copy and paste this into... That should have been made first year of the use of the year is always the best postgres cast to double precision memory... Cast operators to understand it in detail, we have added some examples of the century is but! Iyyy-Mm-Dd ; that would yield surprising results near the start of the to_char function no numbers! Too small will cause an error translated by DeepL ) -- -- - postgres= > select 123456789012345. Iyyy-Mm-Dd ; that would yield surprising results near the start of the of... And then to float data type returns the most significant digit when compared float! Start PostgreSQL server on Mac OS X writing something like IYYY-MM-DD ; that would otherwise be to... Ss: MS, the imprecise representation excuse seems questionable at best may help if you think of it detail... Conversion can yield unexpected results version of PostgreSQL year: for example, double,,... Have a database column which type is bytea but rather requires that 9 precede MI. ) to_date to_number. Changes that should have been importing data into PostgreSQL tables all day using ogr2ogr and then spatially enabling the types.