Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stairway to SQL Dialects Level 2: Postgres Expand / Collapse
Author
Message
Posted Thursday, May 10, 2012 8:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:20 PM
Points: 1,945, Visits: 3,067
Comments posted to this topic are about the item Stairway to SQL Dialects Level 2: Postgres

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1297934
Posted Friday, September 21, 2012 1:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:09 PM
Points: 1, Visits: 25
Other Data Types
...
Does not support NULL values.


I think this statement may need clarification:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\rbroersma>psql
Password:
psql (9.2.0)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.

rbroersma=> SELECT CAST( TRUE AS Boolean ) AS "True", CAST( FALSE AS Boolean) AS "False", CAST( NULL AS Boolean) AS "Null";
True | False | Null
------+-------+------
t | f |
(1 row)

rbroersma=>

Also,

Temp Tables

... You cannot create a temp table with column declarations but only with a query. ...

Here is the syntax:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS query DML in Postgres


rbroersma=> CREATE TEMPORARY TABLE Testtable ( foo INTEGER, bar CHARACTER VARYING, baz TIMESTAMP WITHOUT TIME ZONE );
CREATE TABLE
rbroersma=>

Post #1362949
Posted Monday, September 24, 2012 3:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 7:40 AM
Points: 1, Visits: 8
Just adding a couple more corrections, after what Richard noted earlier.

Postgres also has a BIGINT data type which is exactly 8 bytes wide.

SQL Server's IDENTIY column may be better mapped to SERIAL pseudo data type, which is a combination of INT DEFAULT NEXTVAL(). There's also BIGSERIAL which user BIGINT under the covers.

Postgres' BOOLEAN data type does support NULL values.


You cannot create a temp table with column declarations but only with a query

I am not sure what you implied by the above statement, but one can create a temporary table in Postgres without having to use a query at the same time:
CREATE TEMPORARY TABLE test( a INT );

- Gurjeet
Post #1363732
Posted Wednesday, November 14, 2012 4:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 2, 2014 12:52 PM
Points: 2, Visits: 54
SQL Server's "READ_COMMITTED_SNAPSHOT" isolation level overcomes different concurrency control and it is closest to Postgres' MVCC.
Post #1384530
Posted Wednesday, November 14, 2012 7:09 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:27 AM
Points: 573, Visits: 636
Interesting article. I've only been working with databases for about 10 years, but I have only seen porting to SQL Server from Postgres. Never the other way around. It's rare for a company to go away from the Microsoft platform once they've already made a major investment which generally involves multiple products and support agreements.

Aigle de Guerre!
Post #1384603
Posted Wednesday, November 14, 2012 7:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 2, 2014 12:52 PM
Points: 2, Visits: 54
actually, porting FROM commercial DB solution to PostgreSQL is not uncommon if you have a product and you want to lower its overall cost.


Post #1384620
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse