New answers tagged datatypes
What makes you think you are comparing dates? Actually, you are comparing string literals which - in the absence of a cast context and any explicit cast - default to text SELECT '20150526' > '2015-05-26' AS text2text , '20150526'::date > '2015-05-26'::date AS date2date; text2text | date2date ----------+---------- t | f Try: SELECT ...
Replace the comma with a dot, which is the default "decimal point". ALTER TABLE lca_test ALTER COLUMN prevailing_wage TYPE numeric(10,0) USING translate(prevailing_wage, ',', '.')::numeric; But as long as you are not going to store fractional digits (numeric(10,0)), you should really use integer (covers -2147483648 to +2147483647 or bigint (covers ...
I'm ignorant of postgres SQL syntax, however this appears to be an issue with either the TYPE numeric(10,0) not containing decimal granularity (numeric(10,2)) would work, or the fact that the currency is in a european format that utilizes commas instead of decimals.
To help those who follow... I ended up packing the data into a comma separated string for insert into a varchar(max) column. Not what I wanted to do but it works and I'm on my way. Another gotcha with this method is that although varchar(max) can store 2GB the table read from an external connection in excel only returns the first 32765 bytes of the string. ...
If you are working with other people (or you want to remind yourself), you can use the data type BOOLEAN to suggest that the data is only intended to take on a value of 1 or 0.
Connor MacDonald blogged about this over in NUMBER data type... what harm can it do? as well as Ask Tom: "How do I determine how much storage will be required for NUMBER(p, s)?". In short, it does matter. Take this table: CREATE TABLE T ( x1 number, x2 number(6,3) ); x1 will be 21 bytes while x2 will be 2 bytes. By not specifying the data type, ...
You can do this without generating a warning by creating a type and casting the records to it: create type t as (a integer, b varchar(255)); select * from unnest(array[(1,'hello'), (3,'world')]::t); ┌───┬───────┐ │ a │ b │ ├───┼───────┤ │ 1 │ hello │ │ 3 │ world │ └───┴───────┘ tested on 9.4 and 9.3 (SQLFiddle here)
Should do it: SELECT a, b FROM unnest(ARRAY[(1,varchar 'hello'), (3,varchar 'world')]) AS t(a integer, b varchar(255));
It is ugly, but you can try: SELECT a, b::text FROM unnest(ARRAY[(1,'hello'), (3,'world')]) AS t(a integer, b unknown); This way the type defined in AS matches the output of unnest(), which you can cast to your needs in the SELECT list. You can try this in a small SQLFiddle.
Here's how I did it (the code is in the blog post): http://selectallfromideas.blogspot.com.au/2014/08/searching-all-columns-in-all-tables-in.html (It's my blog and my code) It's hardly elegant code, but if you need to find certain data in string type columns in all user tables in a database, this is one way to do it. It will tell you what table the data is ...
Is the application going to search for these one by one? Is the performance of this query important? Easy (ish) solution would be to create a stored procedure that accepted some arguments and then returned the results from searching each table. Results from the stored procedure could include the table it found a match on, the primary key and the field ...
Either use a simple integer and represent BC as negative: CREATE TABLE information ( id serial PRIMARY KEY, year integer NOT NULL ); or use a date that's constrained to the 1st of Jan and use 2014-01-01 etc in input/output: CREATE TABLE information ( id serial PRIMARY KEY, year date NOT NULL, CONSTRAINT year_must_be_1st_jan CHECK ( ...
Top 50 recent answers are included