New answers tagged datatypes
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 ( ...
It would depend on the size of the data you want to save in that field. You should know that there is a limit for VARCHAR, and that limit is in bytes, so number of characters that can be stored will depend on the character set you use. For example, if you use UTF 8, you will be able to store less amount of data. To sum it up, there is no "best" solution, ...
Top 50 recent answers are included